Mittwoch, 26. September 2012

SQLServer Plan Info

I was debugging today a SQL statement which took about 40 seconds in one app, but only 200 ms in SSMS. I learned that SQLServer creates different execution plans depending on the SET options.

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: