SQL Server Interview Question #234

How do you identify unused or rarely used indexes?

Advanced Indexing Senior Advanced

Detailed Explanation

The sys.dm_db_index_usage_stats DMV records operations such as user seeks, scans, lookups, and updates for indexes since the relevant statistics were initialized.

An index with many writes but no observed reads may be a removal candidate, but the observation window matters. The DMV can reset after restart, failover, detach/attach, and other events.

Before dropping an index, verify that it is not needed by infrequent monthly, quarterly, reporting, maintenance, or emergency workloads.

Code Example

SELECT OBJECT_NAME(i.object_id) AS TableName,
       i.name AS IndexName,
       s.user_seeks,
       s.user_scans,
       s.user_lookups,
       s.user_updates
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
  ON s.database_id = DB_ID()
 AND s.object_id = i.object_id
 AND s.index_id = i.index_id
WHERE i.object_id > 100;