Index Maintenance Strategy

  • 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

  • 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/[/url]

    _______________________________________________________________

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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 :-D!

    Greetz
    Query Shepherd

  • 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

  • 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/[/url]

    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[/url]
    Learn Extended Events

  • 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

  • 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

  • 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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply