Post reply

Query the execution plan cache

  • September 2, 2014 at 9:54 am

    #298970

    Comments posted to this topic are about the item Query the execution plan cache

    Derik Hammer
    @SQLHammer
    www.sqlhammer.com

  • October 3, 2014 at 9:44 am

    #1750019

    Taking this one step further to investigate 'safe' ad-hoc queries that are eligible for auto-parameterization. Add on to the predicate as necessary to either eliminate or include other plans in the plan cache.

    ;WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

    SELECT

    decp.objtype

    , decp.cacheobjtype

    , decp.usecounts

    , decp.plan_handle

    , decp.size_in_bytes / 1024 AS size_in_KB

    , dest.text

    , deqp.query_plan

    , deqp.query_plan.value('(//StmtSimple)[1]/@ParameterizedPlanHandle', 'NVARCHAR(100)') parameterized_plan_handle

    , deqs.last_execution_time

    FROM

    sys.dm_exec_cached_plans AS decp

    CROSS APPLY

    sys.dm_exec_sql_text(decp.plan_handle) AS dest

    CROSS APPLY

    sys.dm_exec_query_plan(decp.plan_handle) AS deqp

    LEFT OUTER JOIN

    sys.dm_exec_query_stats AS deqs

    ON

    decp.plan_handle = deqs.plan_handle

    WHERE

    dest.[text] NOT LIKE N'%sys.dm_exec_cached_plans%';

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply