Diagnostics – Page 4 – LincolnBlog.net

Getting the plan handle for a sproc

Posted on 01/10/201403/06/2014 by Lincoln Bovee'

You can use this T-SQL to get the plan handle for a sproc that has a bad execution plan:

SELECT 
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    cp.cacheobjtype AS ObjectType,
    cp.plan_handle AS PlanHandle,
    deps.cached_time as CachedDate,
    deps.last_execution_time AS LastExecuted,
    deps.total_worker_time AS TotalWorkerTime,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan,
    GETDATE() AS TimeQueryExecuted
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    INNER JOIN sys.dm_exec_procedure_stats deps ON deps.plan_handle = cp.plan_handle
WHERE 
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'usp_SomeSproceName';

SELECT

OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,

cp.usecounts AS ExecutionCount,

cp.cacheobjtype AS ObjectType,

cp.plan_handle AS PlanHandle,

deps.cached_time as CachedDate,

deps.last_execution_time AS LastExecuted,

deps.total_worker_time AS TotalWorkerTime,

st.TEXT AS QueryText,

qp.query_plan AS QueryPlan,

GETDATE() AS TimeQueryExecuted

FROM

sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

INNER JOIN sys.dm_exec_procedure_stats deps ON deps.plan_handle = cp.plan_handle

WHERE

cp.objtype = 'Proc'

AND OBJECT_NAME(st.objectid,st.dbid) = 'usp_SomeSproceName';

Make sure, to…

Viewing the execution plan for a specific Sproc

Posted on 01/10/201402/07/2014 by Lincoln Bovee'

SELECT 
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'YourStoredProcedure';