프로시저의 캐시된 실행계획 및 누적 성능 값 보기

Problem

성능의 이슈가 생길 경우 프로시저의 실행계획과 성능을 측정하고자 하는 경우가 생깁니다.

 

Solution

1. 프로시저의 캐시된 실행 계획과 함께 프로시저의 누적 성능 값을 보는 쿼리입니다.

SELECT QUERY_PLAN, 
	CP.USECOUNTS, 
	DB_NAME(PS.database_id) AS DBNAME, 
	OBJECT_NAME(ps.object_id) AS SP_NAME, 
	ps.cached_time, 
	ps.last_execution_time as last_run_time, 
	ps.execution_count as exec_cnt, 
	ps.total_worker_time/execution_count as avg_cpu_time, 
	ps.total_logical_reads/execution_count as avg_io_reads, 
	ps.total_elapsed_time/execution_count as avg_exec_time, 
	ps.total_worker_time as tot_cpu_time, 
	ps.total_logical_reads as tot_io_reads, 
	ps.total_elapsed_time as tot_exec_time 
FROM sys.dm_exec_cached_plans CP 
LEFT JOIN SYS.dm_exec_procedure_stats PS ON CP.plan_handle=PS.plan_handle 
OUTER APPLY sys.dm_exec_query_plan(CP.PLAN_HANDLE) TP 
WHERE OBJECT_ID = OBJECT_ID('up_SP_GroupWare_MainBoardLst_R')

2. 결과값