Which queries use an index

• paul.jones

  SSC Eights!

  Points: 824

  More actions

  September 22, 2010 at 8:41 am

  #238581

  Hi All,

  is there a way to identify which queries actually use a specific index?

  I have a table with (I think) excess indexes on it, but each was originally introduced to solve a specific performance issue and all are still used frequently. I'm hoping to identify which queries use which indexes as they may be quite happy/almost as fast using a different one.

  I'm guessing there must be a way to do it with the DMVs, I'm guessing it will just be a case of looking through cached plans, but I can't see how to do it beyond that...

  Thanks in advance!

  Paul.

• george sibbald

  SSC Guru

  Points: 104210

  More actions

  September 22, 2010 at 4:07 pm

  #1225977

  the two dmvs you want are sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats

  these give stats on how often indexes are used and how many times they were used in a query plan (roughly speaking)

  ---------------------------------------------------------------------