How do I monitor and find unused indexes in sql database

Question

I would like to monitor index usage for an sql database, in order to find unused indexes and then drop them. How can I monitor index usage most efficiently? And which scripts could be useful?

(I'm aware of this question about identifying unused objects, but this applies only to the current run of the sql server. I would like to monitor index usage over a period of time...)

Answer 1

Currently (as of SQL Server 2005 - 2008) the SQL index stats information is only kept in memory and so you have to do some of the work yourself if you would like to have that persisted across restarts and database detaches.

What I usually do, is I create a job that runs every day and takes a snapshot of the information found in the *dm_db_index_usage_stats* table, into a custom table that I create for the database in question.

This seems to work pretty well until a future version of SQL which will support persistent index usage stats.

Answer 2

Pulled this puppy off of http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/. Note that this works for 2005 and above. The key is the JOIN to the SYS.DM_DB_INDEX_USAGE_STATS system table.

USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
                    INDEXNAME = I.NAME,
                    I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND   I.INDEX_ID NOT IN (

SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND   I.INDEX_ID = S.INDEX_ID
AND   DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
         I.INDEX_ID,
         INDEXNAME ASC
GO