SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Spring Still Not Really Hitting Montreal Yet, thus More Time to Hunt Down Useless Indexes


Hopefully, by the time we have our annual family vacation in May, the Cherry Blossoms will be out – until then I’ll just dig into some very, very heavily updated Indexes that are causing Quest’s Spotlight, at my latest contract with Transcontinental, to flash warnings for excessive disk activity.  Of course, if we were in Utopia, there would be loads of extra disks to isolate the indexes to, however Sir Thomas Moore’s head was lopped off by Henry VIII and that Utopian dream died rather quickly.  Now back to reality… 

Prerequisite References - Index Related Dynamic Management Views (SQL 2005/8):  sys.dm_db_missing_index_details , sys.dm_db_index_usage_stats 

Nobody wants to have indexes that are updated for no reason, so when you see elevated numbers for user_updates, let’s say in the millions, which will need obvious action, while at the same time user_lookups (if clustered idx) and user_scans/seeks are at zero AND the table in question is huge, then you might want to drop those indexes on the table with the goal of gaining system resources. Bonus, you might even have happier users receiving their data quicker too!

Example:  I have a table with its respective Clustered Index, and there are five non-clustered indexes based on top of that Clustered Index – since they are dependent.  If two of the non-clustered indexes (mostly likely due to the use of a code generator) have no user_scans, nor user_seeks (or only a negligible amount) the other two non-clustered indexes have fair amount of user_scans, so I have decided to Include them in the clustered index if they have a significant number of user_seeks or drop them completely.  Testing is the best way, so using profiler I shall see what the results are once applying realistic workloads – one thing for sure, is that there will not be so much updating going on for no reason. There are queries that allow you to determine the useless indexes very quickly, such as MVP Paul Nielsen's Duplicate Indexes post, and the useless index list can be huge, especially if the application generated its objects from an ORM, so we want to focus on the largest tables first as a guide through the over-indexation mess.

Thankfully, with the help of the great details within the DMVs, we have no excuse not to analyse problematic indexes.

Further reading from some great sources:

http://www.sqlservercentral.com/articles/Indexing/64134/  Missing Indexes SQL 2005/8

http://msdn.microsoft.com/en-us/magazine/cc135978.aspx  Ian Stirk’s Optimising Application Performance by Index analysis



Posted by GilaMonster on 28 April 2009

It's worth noting that just because an index has 0 seeks and 0 scans doesn't automatically mean it's never used. The DMV only has info since the last restart of the SQL instance.

Posted by Hugo Shebbeare on 29 April 2009

Thank you very much GM, caught me before I fell into that trap.  Yes, indeed, DMVs are only since the last restart - thankfully most of the Instances I am managing these days are rebooted rarely.

Posted by GilaMonster on 29 April 2009

I wouldn't say they're the last resort, but they should not be assumed to be the full and complete truth. Same goes for the missing indexes. They give an indication, but a thorough check should be done before dropping or adding indexes based on the DMVs

Leave a Comment

Please register or log in to leave a comment.