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