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.
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%'