Method for finding unnecessary indexes of an operational SQL Server database?

Besides wasting space on the database, unnecessary indexes on SQL Server can slow down insert and update operations. Developers who lack experience in database principles sometimes tend to create table indexes which don't make sense for the running queries.

Is there a common procedure or tool for SQL Server 2005/2008 for analyzing the database workload and giving hints which indexes are either never used or not necessary on a certain operational database?

Thank you!

Solution 1:

I'm seeing a cute little T-SQL script here (http://blog.sqlauthority.com/2008/02/11/sql-server-2005-find-unused-indexes-of-current-database/) to show unused indexes that should run on SQL Server 2005. Another one http://aspadvice.com/blogs/ssmith/archive/2008/03/31/Find-Unused-SQL-2005-Indexes-for-Current-DB.aspx here, too.

It looks like dm_db_index_usage_stats is the key to all of this. Pretty neat! (See http://msdn.microsoft.com/en-us/library/ms188755.aspx I'm gonna have to go look at some production databases I created now to see what these stats look like. (smile)

Edit: Some very nice addt'l background here: http://blogs.msdn.com/craigfr/archive/2008/10/30/what-is-the-difference-between-sys-dm-db-index-usage-stats-and-sys-dm-db-index-operational-stats.aspx

Solution 2:

A few things to bear in mind about using sys.dm_db_index_usage_stats:

1. The output only contains indexes that have been used since the last time the database was started. When a database is shutdown, all entries from the in-memory cache of information for that database are removed. Similarly, the cache does not survive an instant restart. There is no way to manually clear the entries for a particular database without restarting the database. The various articles referenced in the first answer (and also on my blog) describe how to capture the output at various times to do time-series analysis.
2. Be sure to test your entire business cycle. You don't want to remove an index that's used for an end-of-month report or CEO query, even though it may be tempting to.
3. Make sure you understand the various counts and what they mean before making a decision on whether the index is being profitably used, or simply maintained at expense.

Hope this helps.

PS One more thing for others reading this and wondering if there's an equivalent method for SQL Server 2000 - no, we (as I was on the team at the time) only added the capability for 2005 onwards.

Solution 3:

We've got a wiki article at SQLServerPedia with a script to do this, plus a tutorial video on how to use it:

One to find indexes not being used:

http://sqlserverpedia.com/wiki/Find_Indexes_Not_In_Use

And another to find indexes you should add:

http://sqlserverpedia.com/wiki/Find_Missing_Indexes

Solution 4:

try left joining onto select * from Sys.dm_db_index_usage_stats. You can see which have never been touched. Make sure they have been around long enough though, running this the day after a new index has been created may not be enough time....

Also be aware indexes may not be used today but as data size increases they are considered a better bet by the optimizer. Typically with small (new) tables the optimizer will always scan but will start to seek when the number of rows has risen above the tipping point

Dave J

Edit: Evan beat me to it, but he is spot on.

Edit 2: corrected advice, forgot the anti-join bit!