Search This Blog

Wednesday, July 21, 2010

How to find the unused indexes in Sql Server

SELECT OBJECT_NAME(si.OBJECT_ID) as TableName, si.NAME as IndexName, si.INDEX_ID as IndexID
FROM
sys.indexes si INNER JOIN sys.objects so ON si.object_id = so.object_id
WHERE OBJECTPROPERTY(si.object_id,'IsUserTable') = 1 AND index_id NOT IN
(
SELECT index_id FROM sys.dm_db_index_usage_stats sdmv
WHERE sdmv.object_id = si.object_id AND sdmv.index_id = si.index_id
)