Technical Article

Find Single use plans in Plan Cache

,

This script will return all the Adhoc Single use Plans currently in your Plan Cache.

It also excludes Plan Shells created from auto/forced parameterization. Works for 2005/2008

Please feel free to make any suggestions/comments.

SET  QUOTED_IDENTIFIER ON
WITH XMLNAMESPACES   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') 
SELECT   
 query_plan AS CompleteQueryPlan
,t.text
,n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') AS StatementOptimizationLevel
,ecp.usecounts,      ecp.size_in_bytes ,ecp.objtype
FROM sys.dm_exec_cached_plans AS ecp 
CROSS APPLY sys.dm_exec_query_plan(ecp.plan_handle) AS eqp 
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS qn(n) 
cross apply sys.dm_exec_sql_text (ecp.plan_handle) AS t
WHERE  objtype = 'Adhoc' and cacheobjtype = 'Compiled Plan'
and usecounts = 1 
and n.value('(@StatementOptmLevel)[1]', 'VARCHAR(25)') is not null 
and text not like '%sys.dm%'

Rate

5 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (1)

You rated this post out of 5. Change rating