SQL Server 2008에 sys.dm_exec_procedure_stats DMV가 추가되었습니다.
2008이전 버젼에서는 SP별로 실행정보를 보기 위해서는 statment단위로 저장된 데이터를
GROUP BY해서 확인했지만, SQL Server 2008부터는 해당 DMV가 추가됨에 따라
쉽게 바로 확인이 가능합니다.
SELECT * FROM (
SELECT TOP 10
db_name(qt.dbid) AS 'db_name'
,OBJECT_NAME(qt.objectid,qt.dbid) as sp_name
,qp.query_plan
,execution_count as execution_count
, ISNULL(qs.execution_count/DATEDIFF(Second, qs.cached_time, GetDate()), 0) AS 'Calls/Second'
, DATEDIFF(Minute, qs.cached_time, GetDate()) AS 'Age in Cache(min)'
,qs.cached_time
,qs.last_execution_time
, ISNULL(qs.total_elapsed_time/qs.execution_count, 0) /1000.0 AS 'avg_Elapsed_Time(ms)'
, qs.total_elapsed_time/1000.0/1000.0 AS 'total_Elapsed_Time(sec)'
, max_elapsed_time /1000.0 AS 'max_Elapsed_Time(ms)'
, ISNULL(qs.total_worker_time/qs.execution_count, 0)/1000.0 AS 'avg_worker_time(ms)'
, total_worker_time/1000.0/1000.0 as 'total_worker_time(sec)'
, qs.max_worker_time/1000.0 as 'max_worker_time(msdb)'
, ISNULL(qs.total_logical_reads/qs.execution_count, 0) AS 'avg_logical_reads'
, total_logical_reads as total_logical_reads
, qs.max_logical_reads as max_logical_reads
, ISNULL(qs.total_physical_reads/qs.execution_count, 0) AS 'avg_physical_reads'
, total_physical_reads as total_physical_reads
, qs.max_physical_reads as max_physical_reads
, ISNULL(qs.total_logical_writes/qs.execution_count, 0) AS 'avg_physical_writes'
, qs.total_logical_writes as total_logical_writes
, qs.max_logical_writes as max_logical_writes
FROM sys.dm_exec_procedure_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
where db_name(qt.dbid) is not null
)X
order by
[avg_worker_time(ms)]
DESC
송 혁, SQL Server MVP
sqler.com // sqlleader.com
hyoksong.tistory.com