Situatie
Mai jos este o procedura ce afiseaza planul de executie pentru o procedura primita ca parametru. Daca nu se pune nicio valoare pentru numele procedurii, se afiseaza toate planurile de executie stocate.
Solutie
alter procedure DisplayProcExecutionPlan
@ProcName varchar(max) = null
as
if @ProcName is null
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
st.text AS SQLBatch,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’
else
SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,cp.usecounts AS UseCounts,
st.text AS SQLBatch,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) = @ProcName
Leave A Comment?