Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

enter image description here

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.

share|improve this question
1  
Have you seen this? sqlskills.com/blogs/jonathan/… also the number of operations from that DMV may not be showing what you think. See blogs.msdn.com/b/craigfr/archive/2008/10/30/… –  Martin Smith Jan 8 '13 at 15:16
    
excellent article. thank you for the info –  Daniel Brink Jan 9 '13 at 8:45

1 Answer 1

up vote 5 down vote accepted

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:

enter image description here

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.

share|improve this answer
    
thank you, that helped a lot and explained the high usage –  Daniel Brink Jan 9 '13 at 8:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.