SQLServerPedia

In a previous post I discussed how the SQL Server 2005 DMV [sys].[dm_db_index_usage_stats] could be used to find unused indexes. The SQL Server Programmability & API Development Blog recently posted a good article on identifying overlapping indexes using a simple script.  This article is definitely worth a read because overlapping indexes are just as much of a waste of resources during update operations as are unused indexes.

“Simple script, eh?” Well, in this case we’re talking about a three-part process:

1. Create a user-defined function and a view
2. Run a SELECT statement to expose the overlapping indexes
3. Clean up the view and the function

If you’re worried about running such a thing in a production environment, you can either:

• Run the script during off-peak hours
• Alter the script to set the isolation level to READ UNCOMMITTED and include the NOLOCK hint on the objects referenced (if your environment is stable there’s no reason to fear the dirty/phantom read issues inherent in this approach)
• Create the function and view, then create another database from a snapshot, and then run the SELECT on the snapshot database

The blog outlines some common concerns when dropping existing indexes and reviews a few index usage basics to qualify the results of the query. This can be a very quick, time-saving process and a good way to jump start your own database maintenance initiatives.