저장 프로시저들을 DB에 추가해서 사용하다보면 호출횟수나 CPU 소모시간 등의 통계요약 쿼리

저장 프로시저들을 호출횟수나 CPU 소모시간 등의 통계요약 쿼리

 

SELECT DB_NAME(st.dbid) DBName

,OBJECT_NAME(st.objectid) StoredProcedure

,max(cp.usecounts) Execution_count

,sum(qs.total_worker_time) total_cpu_time

,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0) avg_cpu_time

FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

group by DB_NAME(st.dbid), OBJECT_NAME(objectid)

order by sum(qs.total_worker_time) desc

 

실행결과

DBName StoredProcedure Execution_count total_cpu_time avg_cpu_time
master    NULL                14                      280082            20005.85714285714285

master    NULL                14                      280082            20005.85714285714285
msdb     SP_SELECT1      12                     17372              1447.66666666666666
msdb     SP_SELECT2      48                     17072              355.66666666666666

.

.

.

.

.