February 9, 2012 at 3:31 pm
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
February 9, 2012 at 3:44 pm
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
February 9, 2012 at 4:25 pm
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?
February 10, 2012 at 10:53 am
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
February 10, 2012 at 11:06 am
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