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.
— Phttps://www.sqlskills.com/blogs/glenn/ossible 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] ASC 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.
Came across this, and appreciate the effort.
Interestingly, there are situations where there are NO index stats, when there are no reads or writes. It’s an edge case, and performance won’t be affected (no writes means no overhead), but can be space consumers.
You might want to try reversing this query to start with sys.indexes and left join to dm_db_index_usage_stats
This code ends with “AS C” which should be “ASC”, and also there’s a semicolon in the longest line, followed by “as statement” which causes a syntax error.
Fixed thank you, sometimes the code plug in does funky stuff.
I’d like add one small note. Unique indexes can be created to ensure the data integrity. Usually the query optimizer willingly uses them but it’s not always the case. So, it’s one more reason to be careful especially if you inherited the database.
Which version of SQL has this as #53? I find it as #64 in SQL 2016 SP2 collection.
I already look at index usage that I got from Tim Ford and now I want to add this one to my toolkit.
Thanks for pointing it out
Something to keep in mind – it’s not WRITES>READS that really matters. It’s WRITE_IO > (READ_IO_WITHOUT_INDEX – READ_IO_WITH_INDEX)!
Perhaps you have an index on a multi-GB table, and there’s a constant stream of inserts on the table, so the index gets a lot of writes. Querying of that multi-GB table happens a few times an hour, but the index enables that query to be answered quickly and efficiently, so there isn’t that much read I/O. In the absence of that index, that query turns into a full table scan that must read the entire table into memory (perhaps the table is larger than memory).
If the index isn’t used at all, then yes, eliminate it. But if it does get used, the value isn’t in the use of the index – it’s in the I/O (and CPU) avoided!