Indexes can be incredibly beneficial to your database performance; however, they do come with a cost—indexes both consume storage space and affect insert performance. Therefore, it is important as part of your index maintenance procedures that you periodically check to see if your indexes are being used. Many times, indexes are created in the belief they are needed but in fact they are never used. You can reduce that IO overhead on inserts when you remove unnecessary indexes.
How Do You Find Them?
Glenn Berry (B|T) has a fantastic script as part of his diagnostic scripts (link) that helps identify index utilization. In his diagnostic scripts it is listed as Script # 53.
— Possible Bad NC Indexes (writes > reads) (Query 53) (Bad NC Indexes)
SELECT d.name,OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads], user_updates - (user_seeks + user_scans + user_lookups) AS [Difference], 'USE ' + QUOTENAME(d.name) + '; ALTER INDEX ' + QUOTENAME(i.name) +' ON ' +OBJECT_NAME(s.[object_id]) +' DISABLE '; as statement FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON s.[object_id] = i.[object_id] AND i.index_id = s.index_id join sys.databases d on d.database_id=s.database_id WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1 AND s.database_id = DB_ID() AND user_updates > (user_seeks + user_scans + user_lookups) AND i.index_id > 1 and i.name not like 'PK%' and i.name not like 'f%' ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] AS C OPTION (RECOMPILE);
Things to Consider in the Results
Simply WRITES> READS.
If you are writing to your index and not or infrequently reading (Seeks and Scans) from it, you may want to consider disabling then dropping. It takes a lot of resources to update indexes, store and maintain them, don’t waste resources when you don’t need to. Also, it’s important to note that these numbers are only as good as your last SQL Server service restart. Note the index usage statistics can also be reset if you are running SQL Server 2012 version below SP2+CU11 or SP3+CU3, by executing an ALTER INDEX REBUILD of that index. For SQL Server 2014 until SP2 the ALTER INDEX REBUILD of that index also applies.
You can see in the screen shot that there are five indexes that are potential candidates for removal. We know this because the Total Writes column is significantly higher than the Total Reads. It’s just that simple. What is great about this script is that it combines user seeks, scans and lookups which makes it easy to see total reads. It also dynamicaly creates a disable script for you to use, if you choose to disable them.
I highly recommend you run this in your environment to see how your indexes are doing. When it comes to performance tuning, fixing wasted resources can make all the difference. But be careful we all have indexes that are only used during budget season or once a year reporting. I highly recommend scripting off all indexes before dropping them.
