Which stored procedures are using a specifc index

Question

I've been reviewing the indexes in our databases (adding missing indexes and removing bad ones). I stumbled on an index I created months ago which looks way to good to be true. Since the last server reboot (about 2 weeks ago) there had been only 109 writes to it, but nearly 2.7 million reads from it. Normally when I see more reads than writes I put it down as a good job and move on to the next, but this one just looks too good to be true.

So my question: It there a way to determine which store procedures are using the index? Other than reading through all stored procedures manually and checking joins and where conditions.

Answer 1

I couldn't quite get Jonathan's XML query to pull this out for me, but I was able to get it by parsing showplan_text the simple way. This is using AdventureWorks2012 and after issuing queries against Sales.SalesOrderHeader:

DECLARE @Index SYSNAME = N'PK_SalesOrderHeader_SalesOrderID';

SELECT t.[text], s.execution_count, CONVERT(XML, p.query_plan)
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_text_query_plan
(
  s.plan_handle, s.statement_start_offset, s.statement_end_offset
) AS p
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
WHERE p.query_plan LIKE '%Index="\[' + @Index + '\]"%' ESCAPE '\';

Results:

To get stored procedure names specifically (for those queries that are not issued ad-hoc), you'll want to make sure you are in the context of your database, and then add this column to the output list of the above query:

, obj = QUOTENAME(OBJECT_SCHEMA_NAME(t.objectid)) 
  + '.' + QUOTENAME(OBJECT_NAME(t.objectid))

This column will be NULL for any ad-hoc queries.

Once the query plan is in the results pane, you can of course click on the XML to load the graphical plan. And then if you have SQL Sentry Plan Explorer and the SSMS add-in installed, you can right-click the graphical plan to view in Plan Explorer.

Note that the query won't return results for queries issued from another database, so you should repeat the query in the context of any database that might be used in conjunction with the target database.

In contrast, Jonathan's query should be able to retrieve queries in the plan cache that reference any index with the specified name (there may be multiple). You can further filter to find references to only this specific one, even if those queries originated in other databases.