Find Cached Query Plans By Index Name
A following question came in via the #sqlhelp hash tag on Twitter:
Trying to find the plans using a specific index in the plan cache using XQuery – any ideas out there? #sqlhelp
I replied with a little information but felt the full answer could not be shared 140 characters at a time. So here is the full query that I wrote. I am using sql:variable() to pass the index name into the exist() method. I’m using a relative XML path to locate any Object reference stating that it is an index with the name defined above.
The Query
DECLARE @Index SYSNAME SET @Index = '[PK_IndexName]'; WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sqlx) SELECT object_name(st.objectid, st.dbid) AS ObjectName, qp.query_plan AS QueryPlan, st.text AS ObjectText FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st WHERE qp.dbid = DB_ID() AND qp.query_plan.exist('//sqlx:Object[@Index=sql:variable("@Index")]') = 1;
Twitter
LinkedIn
TLF
RSS
WritersCafe
SQLPASS
Facebook
Just wanted to point out that in addition to getting a solution, I learned something from this relatively simple script – Thanks Again.
You’re welcome!!
Pingback: Performing Fast Searches of Query Plans | SQLSoldier
Robert – Thanks for posting this. It seems that very often when I Google on some SQL challenge, I find the results in your blog.
Thanks,
Andre Ranieri
Thanks Andre! Glad you find my blog useful.