Identify unused indexes

Technorati-Tags: SQL Server

For a regular review it is useful to identify unused indexes. With the following query you can show unused indexes in your database. So don’t forget to select your database.

If the columns “reads” = 0 or very low, then you can assume that this index isn’t used for querying.

SELECT
  objectname=OBJECT_NAME(s.OBJECT_ID)
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, p.rows
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON
    i.index_id = s.index_id AND
    s.OBJECT_ID = i.OBJECT_ID
JOIN sys.partitions p ON
    p.index_id = s.index_id AND
    s.OBJECT_ID = p.OBJECT_ID
WHERE
    OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND p.rows > 10000
ORDER BY reads, rows DESC