How do I find which indexes are being used and what query is using the index?

Question

I am using SQL Server 2008. I have tables on which there are duplicate indexes (basically indexes with same definition). I wanted to know if its possible to find out which queries are using these indexes? I don't know why the duplicate indexes were created in the first place. So before removing them, I want to identify any queries which are using them.

One more question is in above cases, how does SQL Server engine determine which index to use? What's the performance impact of this?

Thanks aski

Answer 1

If you have indexes in your database that are exact duplicates, delete them, period. No harm can come from removing the duplicates, but harm CAN come from the duplicates existing.

The fact that SQL Server even allows duplicate indexes to be created in the first place is ridiculous.

Here is an article on how to find unused (and missing) indexes: http://weblogs.sqlteam.com/mladenp/archive/2009/04/08/SQL-Server---Find-missing-and-unused-indexes.aspx

Answer 2

If the indexes are truly duplicate, then it shouldn't matter what queries are using them. If you remove one, the query should use the other (unless there is a query hint that specifies an index name, which is rare).

Just make sure the indexes are truly duplicates:

• Indexed field order matters. An index on (fname, lname) is not the same as an index on (lname, fname)
• An index on (lname) is reduntant if you already have one on (lname, fname, ...other fields) (Going from the left only, order matters here, too)
• Check included fields. Similar looking indexes might have different included fields to cover different queries (although you could probably still consolidate these by making one index with all included fields)
• Other properties of the index might cause one index to behave slightly different than the other (clustered? Unique? Fill Factor? Max degree of parallelism? Filegroup? Auto-recompute stats?) (You still probably wouldn't need 2 different indexes, but worth understanding the differences, anyway)