Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index Maintenance Strategy Expand / Collapse
Author
Message
Posted Friday, April 19, 2013 12:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 6:28 AM
Points: 133, Visits: 431
Hi SQL-folks,

I've asked myself, what would be the best strategy to keep my indexes always optimal and up-to-date. What are your practices.

I tried several approaches :

- Missing index views and unused index views : I marked that ones, that need to persist, allthough they are not used often through extended property comments and deleted the unused periodically and create the missing new ones. Problems : Danger of creating and deleting the same indexes over an over again, because you got other better indexes, or the index get's shown as missing allthoug you created it (MS Bug in SQL Server 2008 prior to R2). this is a lot of work and not taking care of partitioning etc. Why is impact not a counter I can rely on...what can I rely on?
- DTA : The database tuning advisor seems to be good, but the naming conventions are terrible and the dta doesn't know about indexes that need to be persisted because it speeds up a very important query executed very seldom up to 100%. So it's a lot of extra work an I never get rid of the best and optimal settings for index maintenance in my very special case, because the documentation is very bad. I don't know in deep how the dta works and I want to have control over what I do. Problems : Capturing a workload over such a long period of time, that all interesting queries get captured.
- Manual Analysis : I traced out the queries that took the longest time and tried to speed them up through analysis of the execution plans. Problems : What is the best way to have the optimal amount of indexes serving all statements as best as possible. Is it better to have wide indexes or is it better to have small indexes whit the downside of slowing down inser, updates and deletes.

I hope you know what I mean and can help me with your experience.

Thanks in advance!!! God bless you!!!


Greetz
Query Shepherd
Post #1444564
Posted Friday, April 19, 2013 1:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:05 PM
Points: 13,327, Visits: 12,820
That is a might big topic. I would not rely too heavily on DTA or the missing indexes views. In fact, I would probably not even look at them. The absolute best approach to figuring out what indexes you need on your system can't be handled on an online forum. You have to have access to so many things to dial this thing in. This type of thing also needs to be considered as a process instead of event. You can't just "add all the proper indexes" and then move on. It is an ongoing balance.

You should probably read up on indexes. The stairways series on here is excellent. It starts out basic and gets pretty deep.

http://www.sqlservercentral.com/stairway/72399/


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1444571
Posted Friday, April 19, 2013 1:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 6:28 AM
Points: 133, Visits: 431
Yeah you got it! How do I get the balance over a long time? How can I decide in consideration of all queries fired on a table which are the best indexes (covering also changing structure and changing queries). Where is the entry to the wood i can't see, because ther are so much trees? I red so much about indexing, but I need that last hint to bring light at the end of the tunnel !

Greetz
Query Shepherd
Post #1444575
Posted Friday, April 19, 2013 1:58 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 6:28 AM
Points: 133, Visits: 431
Just remembered the articles (e.g. http://sqlinthewild.co.za/index.php/2011/11/11/sql-university-advanced-indexing-indexing-strategies/) of Gail, because Sean gave me links to other posts on her blog...I gonna recheck them and come back with my questions

Greetz
Query Shepherd
Post #1444597
Posted Friday, April 19, 2013 2:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:20 PM
Points: 18,064, Visits: 16,099
Sean Lange (4/19/2013)
That is a might big topic. I would not rely too heavily on DTA or the missing indexes views. In fact, I would probably not even look at them. The absolute best approach to figuring out what indexes you need on your system can't be handled on an online forum. You have to have access to so many things to dial this thing in. This type of thing also needs to be considered as a process instead of event. You can't just "add all the proper indexes" and then move on. It is an ongoing balance.

You should probably read up on indexes. The stairways series on here is excellent. It starts out basic and gets pretty deep.

http://www.sqlservercentral.com/stairway/72399/


Bolded text is spot on. I think the most important thing to remember is that you need to know your data and the ways the data is accessed. This is a thing that is developed with time in the database and knowing that tuning is an iterative process.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1444601
Posted Monday, April 22, 2013 1:59 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 6:28 AM
Points: 133, Visits: 431
But how do you collect all queries on a table to decide which field is selected the most and interesting for a clustered index? Through long lasting Profiler traces? Or ist there a more efficient (maybe also quicker) way? Are there any DMV's for that?

Found the answer myself in Gail's blogpost! Thanks Gail!

But how do i use SQL Trace or Extended Events to capture my queries? Does anyone of you know step-by-step explanations or existing solutions?


Greetz
Query Shepherd
Post #1444889
Posted Tuesday, April 23, 2013 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 27, 2014 6:04 AM
Points: 3, Visits: 52
You should use http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html and schedule for run weekly or dayly for keep your used objects updated
Post #1445387
Posted Tuesday, April 23, 2013 7:05 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 27, 2014 6:28 AM
Points: 133, Visits: 431
Fernando Jacinto Alvarez (4/23/2013)
You should use http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html and schedule for run weekly or dayly for keep your used objects updated

Thanks, but you definitely missed the point!


Greetz
Query Shepherd
Post #1445400
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse