Check index usage in SQL Server

Check index usage for the current database

The SQL queries analyze index usage in the current database through sys.dm_db_index_usage_stats. The first lists user-table indexes with seek, scan, lookup, update counts, timestamps, and overall activity. The second identifies potentially unused nonclustered indexes, excluding primary keys and unique constraints, while prioritizing those with the highest maintenance overhead.


SELECT
    OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    ISNULL(s.user_seeks, 0) AS UserSeeks,
    ISNULL(s.user_scans, 0) AS UserScans,
    ISNULL(s.user_lookups, 0) AS UserLookups,
    ISNULL(s.user_updates, 0) AS UserUpdates,
    s.last_user_seek AS LastUserSeek,
    s.last_user_scan AS LastUserScan,
    s.last_user_lookup AS LastUserLookup,
    s.last_user_update AS LastUserUpdate
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
    ON i.object_id = s.object_id
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY
    ISNULL(s.user_seeks,0) +
    ISNULL(s.user_scans,0) +
    ISNULL(s.user_lookups,0) DESC;

Find potentially unused indexes

SELECT
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
ISNULL(s.user_seeks, 0) AS UserSeeks,
ISNULL(s.user_scans, 0) AS UserScans,
ISNULL(s.user_lookups, 0) AS UserLookups,
ISNULL(s.user_updates, 0) AS UserUpdates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id = s.object_id
AND i.index_id = s.index_id
AND s.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
AND i.type_desc = 'NONCLUSTERED'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND ISNULL(s.user_seeks, 0) = 0
AND ISNULL(s.user_scans, 0) = 0
AND ISNULL(s.user_lookups, 0) = 0
ORDER BY ISNULL(s.user_updates, 0) DESC;

Leave a Reply