How do I monitor and find unused indexes in sql database

sql-server indexing maintenance
Ole Lynge picture Ole Lynge · Sep 21, 2009 · Viewed 13.5k times · Source

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

Mike Dinescu picture Mike Dinescu · Sep 21, 2009

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 sys.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.

Related questions

Do all SQL server versions rebuild indexes automatically or have a default rebuild criteria?

Do all SQL server versions rebuild indexes automatically or have a default rebuild criteria? I understand statistics are rebuilt automatically but not sure if indexes do as well.

Read More
What do Clustered and Non clustered index actually mean?

I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type …

Read More
List of all index & index columns in SQL Server DB

How do I get a list of all index & index columns in SQL Server 2005+? The closest I could get is: select s.name, t.name, i.name, c.name from sys.tables t inner join sys.schemas s on …

Read More