SQL Server Finding What Queries Are Using Specific Indexes

Using sys.dm_db_index_usage_stats is a great DMV for looking up the usage of an index. It allows you to see how many scans, lookups, updates, ect, have occurred on the index, and therefore serves to give you a mid level view of what's happening with your structures. However sometimes you need to dig a little deeper into the details such as what stored procedures are actually working against these. To get at this information I like to leverage the sys.dm_exec_query_plan DMV, to search through the plan and look for the index in question. In the query below I join to the query_stats dmv, because I prefer to have a little more info about the query. The query below shows you all the plans where the index is being referenced. Once you pump the results out, you can save the plan with a sqlplan extension and open up in management studio. If you're using 2008 double clicking on the plan will automatically open the plan for you. From here it's simply a matter of looking for where the index is referenced.

 

select
object_name(objectid)
as CallingObject,
*
from
sys.dm_exec_query_stats qs

cross
apply sys.dm_exec_Query_plan(qs.plan_handle)

where
convert(nvarchar(max),query_plan)
like
'%[index Name]%'