Index Used in Which Procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Index Used in Which Procedures

Author

Topic

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-12-09 : 21:08:22

I have identified a few indexes on large tables that are getting scanned. I would like to find the specific stored procedures that are performing these scans.

Does anyone have a script that would relate a given index to the execution plan that uses it and relates that to the stored procedure?

Many thanks!

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-12-14 : 12:27:35

Here is what we came up with. It does rely on the query plan still being in the cache:


-----------------------------------------------------------------------------
--- Given an index name, find where it is used
-----------------------------------------------------------------------------

declare
	@IndexName	sysname = 'MyIndex'

select
	DB_Name(st.dbid) dbName,
	Object_Name(st.objectid) ObjName,
	st.text SqlText,
	qp.query_plan,
	cast(qp.query_plan as varchar(max)) vcQueryPlan
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_query_plan(QS.plan_handle) as QP
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
where
	qp.query_plan is not Null
and 
	cast(qp.query_plan as varchar(max)) like '%' + @IndexName + '%'
and Object_Name(st.objectid) is not Null	-- Not Null for sprocs, funcs, et al; Null includes ad hoc queries, too

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources