Every so often, I get that itch to see what all indexes are out there on our production systems that have not been hit since the last reset of the cache. I have a script that I have been using for years and there are a bunch of them out there, but the one that I have always used for my own purposes is the one that Pinal Dave posted quite a few years ago. As I was looking through this today I figured that it would be really nice to see how much disk space was being used by the unused indexes, so it seemed worth making some modifications to in order to incorporate that data as well. Again, you will note that I have made some slight modifications to Pinal’s script and have added my own little section to grab the index size along with the data. For my purposes, I really only wanted to see those indexes that have had no reads against them at all since that last reboot, because I figure those are the ones that I want to dig into further.
As with all scripts, run at your own risk. And the results DO NOT MEAN that you want to automatically just drop these indexes, they just mean that you may want to look at them as POTENTIAL candidates to drop. Lots of things need to be taken into account before dropping indexes.
USE YourDBHere; With unused as ( SELECT o.name AS TableName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups as reads , dm_ius.user_updates AS UserUpdates , p.TableRows , 'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(s.name) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' FROM sys.dm_db_index_usage_stats dm_ius INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = i.OBJECT_ID INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 AND dm_ius.database_id = DB_ID() AND i.type_desc = 'nonclustered' AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 ), idxsize as ( SELECT i.[name] AS IndexName ,object_name(i.object_id) as tablename ,SUM(s.[used_page_count]) * 8 / 1000000. AS IndexSizeGB FROM sys.dm_db_partition_stats AS s INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.[index_id] = i.[index_id] GROUP BY object_name(i.object_id), i.[name] ) Select * from unused u join idxsize i on u.indexname = i.indexname and u.tablename = i.tablename where u.reads = 0 order by indexsizegb desc;