Thursday 5 March 2015

Unused Non Clustered Indexes - DMOs

SELECT OBJECT_NAME(I.object_id) as objectname,
  I.name AS indexname,
  I.index_id AS indexid
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
  ON O.object_id = I.object_id
WHERE O.object_id > 100
  AND I.type_desc = 'NONCLUSTERED'
  AND I.type_id NOT IN
           (SELECT S.index_id
            FROM sys.dm_db_index_usage_stats AS S
            WHERE S.object_id = I.object_id
                 AND I.index_id = S.index_id
                 AND database_id = DB_ID('TSQL2012'))
ORDER BY objectname, indexname

*finds non clustered indexes not used from the last restart of the instance 

No comments:

Post a Comment