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

With the DMV (dynamic management views), you can definitely find out which indices aren't being used - those that haven't been used in a long time could be dropped.

Check out:

• Find Indexes not in use
• General intro to Dynamic Management Views

One more question is in above cases, how does SQL Server engine determine which index to use?

That's a pretty complicated process - the SQL Server query optimizer will use statistics and other methods to figure out which indices would be helpful for a given query. Which one it'll pick when you have two identical ones, is a tricky question.... I don't know, quite honestly.

What's the performance impact of this?

Of having duplicate identical indices? Those need to be maintained on any INSERT, UPDATE, and DELETE operation that touches on the columns in the index - so you're definitely paying a performance penalty for this.

Answer 3

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)