Index consolidation techniques

  • I have a couple of large tables (120M+ rows, 100+ columns) heavily used in reporting. There are ~40 indexes on each that I'm sure I can consolidate them into fewer indexes.

    Before I brute force this, are there tools available to help? Or does anyone have a 'best practice' they would like to share?

    The thought of right clicking, getting the ALTER INDEX code and trying to manually compare 10-15 column names (in the INCLUDE clause) in 5-10 similar indexes. This seems like it is an archaic way to work these days and there should be a better way. Even something that would parse the index and give a list of columns sorted alphabetically would make this easier.

    Thanks for any ideas or hints.

    Norman

  • These might get you started

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/RemovingDuplicateIndexes.aspx

    http://www.sqlskills.com/BLOGS/KIMBERLY/post/UnderstandingDuplicateIndexes.aspx

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Besides removing duplicate indexes, you should also look at removing unused indexes.

    People often create indexes without knowing what the index will do for them, so they just use space, CPU, and IO to maintain useless indexes.

    How can you tell if an index is being used?

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/04/20/how-can-you-tell-if-an-index-is-being-used.aspx

  • Thank you for the pointer Gail! It at least gets me to a better starting position. And makes me feel better that I'm not missing an obvious tool that does a decent job.

    The table I am mostly interested in has 36 indexes and Kimberly's scripts say none are duplicates. Not really surprised there but good to know.

    Thanks again for the links,

    Norman

  • good point Micheal,

    These are indexes built for specific reports and probably have been modified over time. But a quick look using a couple of different scripts don't show any of these being unused (or lightly used) in the past month.

    I am assuming the metadata gets reset after a reboot (monthly OS patches) so about the best I can get is a month's worth of usage statistics. The indexes shown might be part of the vendor supplied ( and they get a bit fussy about me changing their structure) so I need to be careful about removing them.

    What I am really trying to find is an index that can be removed because another index contains all of the columns in the original index. I am pretty sure that indexes have been created as a report gets new fields added and instead of altering an existing index, a new index is created and the old one is a zombie, not quite dead because no one notices it hanging around.

    Such is life for a DBA...

    Thanks again,

    Norman

Viewing 5 posts - 1 through 4 (of 4 total)

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