Is there a way to find out which queries benefit from a particular index?
I have used the DMV views and I know the index is being used in production but it would be great if there was a way to get a list of the queries positively impacted so I can make a decision if each index is worth keeping.
EDIT: I am using SQL Server
Thanks for your help!
Speaking from Oracle point of view: in Oracle, I can inspect query plan which gives enough information to guess whether an index was used or not. Remember that optimizer makes decisions based on the SQL at hand. There is no hard and fast rule re permanent use or non-use of an index. So, even if you find out that an index is being used or not, you can [almost] always modify the query so that the opposite is true!
Speaking of positive impact: again, it will only tell you how things are at this moment. For example, a table doesn't have enough records and a full table scan may be faster than using an index (due to overhead involved). But what if the situation changes (e.g. lot more records are inputted into that table)?
Bottom line: hard to make these decisions on permanent basis just by looking at what optimizer decided today, or what statistics are maintained by DB at this moment. Your knowledge of the data, its design and structure, and how it is being queried will be the real key on making these decisions.
My guess is that you are asking this question because you have lots of indices and you would like to get rid of a few. Unless the data changes rapidly, there is little overhead in maintaining indices (storage is cheap!). If that is the case, let's hope that optimizer is smart enough to make decision about using or not using an index based on cost... :-)