[MSSQL][DMV] SP 실행수,평균시간등의 분석 관련 정보

[MSSQL][DMV] SP 실행수,평균시간등의 분석 관련 정보

 

 

1. 쿼리

 

- 여러가지 관점에서 Stored Procedure 사용량 알아보기

 

 

[ 2005 이상 ]

SELECT TOP 50 qt.text AS 'SP명', qs.execution_count AS '실행수', qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS '[실행수/초]', qs.total_worker_time/qs.execution_count AS '[작동시간/실행수]', qs.total_worker_time AS '총작동시간', qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime', qs.max_logical_reads [논리적읽기수], qs.max_logical_writes [논리적쓰기수], qs.total_physical_reads [물리적읽기수], DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = db_id() -- Filter by current database ORDER BY qs.execution_count DESC -- 실행수별 --ORDER BY qs.total_worker_time DESC -- 총작동시간별 --ORDER BY qs.total_logical_reads DESC -- total_logical_reads --ORDER BY qs.total_physical_reads DESC -- total_physical_reads --ORDER BY qs.total_logical_writes DESC -- total_logical_writes

 

 

[ 2008 이상 ]

SELECT TOP(50) p.name AS 'SP명', qs.execution_count AS 'SP실행수', ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS '[실행수/초]', qs.total_worker_time/qs.execution_count AS '[작동시간/실행수]', qs.total_worker_time AS '총작동시간', qs.total_elapsed_time, qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.last_elapsed_time, qs.cached_time FROM sys.procedures AS p INNER JOIN sys.dm_exec_procedure_stats AS qs ON p.object_id = qs.object_id ORDER BY qs.execution_count DESC -- ORDER BY qs.total_worker_time DESC; -- ORDER BY qs.total_logical_reads DESC; -- ORDER BY qs.total_physical_reads DESC; -- ORDER BY qs.total_logical_writes DESC;