Find Unused Indexes in SQL Server

I recently did a bit of index maintenance on one of the larger databases I’m in charge of. I wanted to eliminate unused/rarely-used indexes that were taking up a lot of space.

As usual, Pinal Dave had a great script to build off of. To that script, I also added the index size and more useful sorting:

SELECT TOP 50
	o.name AS ObjectName
	, i.name AS IndexName
	, st.IndexSizeKB AS IndexSizeKB
	, i.index_id AS IndexID
	, dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups + dm_ius.user_updates AS AccessCount
	, dm_ius.user_seeks AS UserSeek
	, dm_ius.user_scans AS UserScans
	, dm_ius.user_lookups AS UserLookups
	, 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 (
	SELECT SUM(st.[used_page_count]) * 8 AS IndexSizeKB, st.index_id
	FROM sys.dm_db_partition_stats AS st 
	GROUP BY st.index_id
) AS st ON st.index_id = i.[index_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
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups +dm_ius.user_updates) ASC, p.TableRows ASC
GO