Leafs Stanley Cup Cabinet (or Cubs World Series Trophy Cabinet)Indexes…they are your friends. They order your data nice and neatly, they can dramatically improve your query performance and can have cool names like IX_UseMeToRunFasterLikeATurboButton.
Indexes…they are your enemy. They slow down your inserts, updates and deletes, and they take up valuable disk space.
So having indexes that are used is a great thing. You’ll see query performance improve by an order of magnitude and some freeing up of your server resources. However, if not used, an index just sits there an takes up space kind of like the Toronto Maple Leafs Stanely Cup cabinet. Let’s not forget those unused indexes are still being kept up to date and slowing down your DML statements.
A couple of months ago I was invited to add a large number of indexes to a database in production that is used for reporting. Being a good DBA I asked, quite politely, why they were needed. “They are so the queries run faster” was the response. Much banter flowed back and forth between teams and in the end all of the indexes were applied (this included 20 indexes on a 534 column table, yes, I know).
I felt these indexes were not going to be used, but as in a lot of cases it comes down to a please just do it situation. So I did.
Three months on and I had a couple of hours to take a look and see how these indexes were doing. A great post by Robert Davis (blog|twitter) entitled “How Do I … Get a List of Tables With Data Modifications in a Specific Timeframe?” got me started with the idea of using sys.dm_db_index_usage_stats to check for this.
When a DML statement gets executed the user_updates column in this DMV gets incremented by one. Likewise, when a query is executed and an index used, be it for a lookup, scan or seek then then user_lookup, user_scan or user_seek value gets incremented.
Armed with this information I was quickly and easily able to identify indexes in the database that had not been used since the last Instance restart (joining against sysindexes provides the index name)
SELECT object_name(us.object_id) as objname , si.[name] as IndexName FROM sys.dm_db_index_usage_stats us INNER JOIN sysindexes si ON us.[object_id] = si.id AND us.index_id = si.indid WHERE us.database_id = db_id() AND us.user_lookups = 0 AND us.user_scans = 0 AND us.user_seeks = 0
This gave me a nice list of unused indexes that I could contemplate removing. However I really felt that I needed to put some numbers behind those indexes. Managers love numbers. By pulling the list of indexes into a table and then using sys.dm_db_index_physical_stats I was able to get the size of each index. Adding the user_updates information from sys.dm_db_index_usage_stats provided me with a long list of indexes along with how many times they have been updated since SQL was last started and the size of each index.
Continue reading on SirSQL.net.