Display procedure execution plan

Configurare noua (How To)

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

Tip solutie

Permanent
Etichetare:

Voteaza

(6 din 16 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?