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
