Identifying Unused Non-Clustered Indexes in SQL Server
Over time, SQL Server databases accumulate indexes—some of which may no longer be in active use. Unused or rarely used non-clustered indexes can consume unnecessary resources and slow down database maintenance tasks. It's good practice to review and clean up such indexes periodically, especially if the SQL Server instance has been running continuously for a long period without a restart (which resets index usage stats).
Query to Find Unused Non-Clustered Indexes
The following SQL query helps identify non-clustered indexes that haven’t been utilized much. It lists the top 50 indexes based on the lowest total usage (seeks, scans, and lookups), providing insight into which ones may be redundant.
SELECT TOP 50
o.[Name] AS [ObjectName],
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_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) AS 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) ASC;
GO
This query helps identify potential candidates for index cleanup by showing:
- The object and index names
- Usage statistics (seeks, scans, lookups, updates)
- Number of table rows
- A ready-to-use
DROP INDEXstatement for each listed index
Before dropping any indexes, ensure they’re truly unnecessary by reviewing application requirements or running further performance tests.
Further Reading
To deepen your understanding of index usage and management, check out the following related articles:
