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
)

2 comments:

  1. Hi Tarun
    Good Work........
    I have some questions.First on which bases this sql statements decides that the index is unused and second is this command applicable on sql server 2000 too?

    Vivek Johari

    ReplyDelete
  2. Hi!!!!
    Microsoft introduces the dynamic management views from SQL Server 2005.
    If the index is not used by SQL Server (Dead Index) then the dynamic management view (sys.dm_db_index_usage_stats) does not have record for that index.

    ReplyDelete