DMV를 이용하여 저장 프로시저 수행 성능 확인

SQL 2005 이상에서 SP의 성능을 확인할 수 있는 모니터링 쿼리를 작성해 보았습니다.
이것은 SP가 성능 문제를 발생할 경우 어떤 SP인지 찾고 싶을 때 유용하게 사용할 수 있습니다.

-- 캐시 비우기
-- DBCC FREEPROCCACHE

-- 쿼리 통계 중 SP 관련 데이터 확인
-- SQL 2005에서는 SP 단위의 통계를 수집하는 DMV가 없기 때문에 statement의 정보를 GROUP BY하여 확인해야 함
SELECT QS.*
,         DB_NAME(ST.dbid) as dbName
,         OBJECT_NAME(ST.objectid, ST.dbid) as objectName
--,       ST.*
FROM (
          SELECT TOP 5 sql_handle
          ,         MAX(execution_count) as execution_count
          ,         SUM(last_logical_reads) as last_logical_reads
          ,         SUM(max_logical_reads) as max_logical_reads
          ,         SUM(total_logical_reads) as total_logical_reads
          FROM sys.dm_exec_query_stats
          GROUP BY sql_handle
          ORDER BY SUM(last_logical_reads) DESC
) AS QS
          CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE ST.objectid IS NOT NULL
ORDER BY QS.last_logical_reads DESC

-- SQL 2008에서 새로 추가된 DMV를 이용하여 확인
SELECT TOP 5 
          DB_NAME(database_id) as dbName
,         OBJECT_NAME(object_id, database_id) as objectName
,         *
FROM sys.dm_exec_procedure_stats
ORDER BY last_logical_reads DESC
 

-- 확인된 쿼리의 text와 plan은 다음의 2개 DMV에서 확인
sys.dm_exec_query_plan ( plan_handle )
sys.dm_exec_sql_text(sql_handle | plan_handle)
 

하만철 / Ha Man-cheol