The 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;
WIDBA
Just wanted to point out that in addition to getting a solution, I learned something from this relatively simple script – Thanks Again.
SQLSoldier
You’re welcome!!
Performing Fast Searches of Query Plans | SQLSoldier
[…] in the past on using the exist() method to search for query plans with specific attributes (e.g., Find Cached Query Plans By Index Name). Another good aspect of the exist() method is that it is a much faster way to search for a […]
Andre Ranieri
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
SQLSoldier
Thanks Andre! Glad you find my blog useful.