Here is a SQL statement to print out some infos about cached execution plans for a specific statement:
SELECT
query_info.sql_handle,
query_info.plan_handle,
plan_options.usecounts,
query_info.total_elapsed_time,
CASE WHEN ((1 & set_options) = 1) THEN 'ON' ELSE 'OFF' END AS 'ANSI_PADDING',
CASE WHEN ((4 & set_options) = 4) THEN 'ON' ELSE 'OFF' END AS 'FORCEPLAN',
CASE WHEN ((8 & set_options) = 8) THEN 'ON' ELSE 'OFF' END AS 'CONCAT_NULL_YIELDS_NULL',
CASE WHEN ((16 & set_options) = 16) THEN 'ON' ELSE 'OFF' END AS 'ANSI_WARNINGS',
CASE WHEN ((32 & set_options) = 32) THEN 'ON' ELSE 'OFF' END AS 'ANSI_NULLS',
CASE WHEN ((64 & set_options) = 64) THEN 'ON' ELSE 'OFF' END AS 'QUOTED_IDENTIFIER',
CASE WHEN ((128 & set_options) = 128) THEN 'ON' ELSE 'OFF' END AS 'ANSI_NULL_DFLT_ON',
CASE WHEN ((256 & set_options) = 256) THEN 'ON' ELSE 'OFF' END AS 'ANSI_NULL_DFLT_OFF',
CASE WHEN ((512 & set_options) = 512) THEN 'ON' ELSE 'OFF' END AS 'NoBrowseTable',
CASE WHEN ((4096 & set_options) = 4096) THEN 'ON' ELSE 'OFF' END AS 'ARITH_ABORT',
CASE WHEN ((8192 & set_options) = 8192) THEN 'ON' ELSE 'OFF' END AS 'NUMERIC_ROUNDABORT',
CASE WHEN ((16384 & set_options) = 16384) THEN 'ON' ELSE 'OFF' END AS 'DATEFIRST',
CASE WHEN ((32768 & set_options) = 32768) THEN 'ON' ELSE 'OFF' END AS 'DATEFORMAT',
CASE WHEN ((65536 & set_options) = 65536) THEN 'ON' ELSE 'OFF' END AS 'LanguageID',
query_info.text,
plan_info.query_plan
FROM (
SELECT plan_handle, usecounts, CAST(pvt.set_options AS INT) AS set_options
FROM (
SELECT plan_handle, usecounts, epa.attribute, epa.value
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "objectid")) AS pvt
) as plan_options
JOIN (
SELECT qs.sql_handle AS sql_handle, qs.plan_handle AS plan_handle, qs.total_elapsed_time, st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
) AS query_info ON query_info.plan_handle = plan_options.plan_handle
CROSS APPLY sys.dm_exec_query_plan(query_info.plan_handle) plan_info
WHERE text LIKE 'SELECT ...%';
Thanks to:
Keine Kommentare:
Kommentar veröffentlichen