SQL Tips #27 - Finding Which Indexes Are Not Used

Unused indexes cost you on every write but give nothing back - this query shows how often each index is actually being used.

The Query

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
    I.[NAME] AS [INDEX NAME], 
    USER_SEEKS, 
    USER_SCANS, 
    USER_LOOKUPS, 
    USER_UPDATES 
FROM   SYS.DM_DB_INDEX_USAGE_STATS AS S 
    INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE  OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
    AND S.database_id = DB_ID()

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags