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!
Best Answer
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