DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인

DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인

 

• Version : SQL Server 2008, 2008R2, 2012

 

내 저장 프로시저가 잘 수행 되고 있을까? 동적관리 뷰(DMV)를 통해서 캐시된 저장 프로시저에 대한 집계 성능 통계를 확인 하여 보자.

 

뷰에는 캐시된 각 저장 프로시저 계획에 대해 하나의 행을 가지고 있다. 행의 유효 기간은 저장 프로시저가 캐시에 남아 있는 기간과 같으며 캐시에서 저장프로시저가 제거되면 해당 뷰에서도 해당 행이 제거 된다. 이때 Performance Statistics SQL 추적이벤트가 sys.dm_exec_query_stats와 유사하게 발생한다.

 

select * from sys.dm_exec_procedure_stats

 

 

이름	데이터 형식	설명
database_id	int	이 저장 프로시저가 있는 데이터베이스의 ID
object_id	int	이 저장 프로시저의 개체 ID
유형	char(2)	개체의 유형 P = SQL 저장 프로시저 PC = 어셈블리(CLR) 저장 프로시저 X = 확장 저장 프로시저
type_desc	nvarchar(60)	개체 유형에 대한 설명 SQL_STORED_PROCEDURE CLR_STORED_PROCEDURE EXTENDED_STORED_PROCEDURE
sql_handle	varbinary(64)	이 저장 프로시저에서 실행된 sys.dm_exec_query_stats 쿼리와의 상관 관계를 지정하는 데 사용
plan_handle	varbinary(64)	메모리 내 계획의 식별자. 일시적이며 계획이 캐시에 있는 동안에만 일정하게 유지. 이 값은 sys.dm_exec_cached_plans 동적 관리 뷰와 함께 사용할 수 있다.
cached_time	datetime	이 저장 프로시저가 캐시에 추가된 시간
last_execution_time	datetime	이 저장 프로시저가 마지막으로 실행된 시간
execution_count	bigint	이 저장 프로시저가 마지막으로 컴파일된 이후 실행된 횟수
total_worker_time	bigint	이 저장 프로시저가 컴파일된 이후 실행되는 데 사용된 총 CPU 시간(마이크로초)
last_worker_time	bigint	이 저장 프로시저가 마지막으로 실행될 때 사용된 CPU 시간(마이크로초)
min_worker_time	bigint	단일 실행 중 이 저장 프로시저에 사용된 최소 CPU 시간(마이크로초)
max_worker_time	bigint	단일 실행 중 이 저장 프로시저에 사용된 최대 CPU 시간(마이크로초)
total_physical_reads	bigint	이 저장 프로시저가 컴파일된 이후 실행될 때 수행된 총 물리적 읽기 수
last_physical_reads	bigint	이 저장 프로시저가 마지막으로 실행될 때 수행된 물리적 읽기 수
min_physical_reads	bigint	단일 실행 중 이 저장 프로시저가 수행한 최소 물리적 읽기 수
max_physical_reads	bigint	단일 실행 중 이 저장 프로시저가 수행한 최대 물리적 읽기 수
total_logical_writes	bigint	이 저장 프로시저가 컴파일된 이후 실행될 때 수행된 총 논리적 쓰기 수
last_logical_writes	bigint	이 저장 프로시저가 마지막으로 실행될 때 수행된 논리적 쓰기 수
min_logical_writes	bigint	단일 실행 중 이 저장 프로시저가 수행한 최소 논리적 쓰기 수
max_logical_writes	bigint	단일 실행 중 이 저장 프로시저가 수행한 최대 논리적 쓰기 수
total_logical_reads	bigint	이 저장 프로시저가 컴파일된 이후 실행될 때 수행된 총 논리적 읽기 수
last_logical_reads	bigint	이 저장 프로시저가 마지막으로 실행될 때 수행된 논리적 읽기 수
min_logical_reads	bigint	단일 실행 중 이 저장 프로시저가 수행한 최소 논리적 읽기 수
max_logical_reads	bigint	단일 실행 중 이 저장 프로시저가 수행한 최대 논리적 읽기 수
total_elapsed_time	bigint	이 저장 프로시저의 실행을 완료하는 데 소요된 총 경과 시간(마이크로초)
last_elapsed_time	bigint	가장 최근에 이 저장 프로시저의 실행을 완료하는 데 소요된 경과 시간(마이크로초)
min_elapsed_time	bigint	이 저장 프로시저의 실행을 완료하는 데 소요된 최소 경과 시간(마이크로초)
max_elapsed_time	bigint	이 저장 프로시저의 실행을 완료하는 데 소요된 최대 경과 시간(마이크로초)

 

 

[캐시된 프로시저]

SELECT     CASE WHEN DATABASE_ID = 32767 THEN 'RESOURCE' ELSE DB_NAME(DATABASE_ID)END AS DBNAME     ,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]     ,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]     ,* FROM SYS.DM_EXEC_PROCEDURE_STATS

 

 

 

[total & average CPU, logical reads , logical writes & physical reads 정보 확인]

SELECT      CASE WHEN DATABASE_ID = 32767 THEN 'RESOURCE' ELSE DB_NAME(DATABASE_ID)END AS DBNAME     ,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]     ,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]     ,CACHED_TIME     ,LAST_EXECUTION_TIME     ,EXECUTION_COUNT     ,TOTAL_WORKER_TIME / EXECUTION_COUNT AS AVG_CPU     ,TOTAL_ELAPSED_TIME / EXECUTION_COUNT AS AVG_ELAPSED     ,TOTAL_LOGICAL_READS     ,TOTAL_LOGICAL_READS / EXECUTION_COUNT AS AVG_LOGICAL_READS     ,TOTAL_LOGICAL_WRITES     ,TOTAL_LOGICAL_WRITES / EXECUTION_COUNT AS AVG_LOGICAL_WRITES     ,TOTAL_PHYSICAL_READS     ,TOTAL_PHYSICAL_READS / EXECUTION_COUNT AS AVG_PHYSICAL_READS FROM SYS.DM_EXEC_PROCEDURE_STATS ORDER BY AVG_LOGICAL_READS DESC

 

 

 

[평균 경과 시간 상위 10개 목록 정보 보기]

SELECT TOP 10 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name', d.cached_time, d.last_execution_time, d.total_elapsed_time, d.total_elapsed_time/d.execution_count AS [avg_elapsed_time], d.last_elapsed_time, d.execution_count FROM sys.dm_exec_procedure_stats AS d ORDER BY [total_worker_time] DESC;

 

 

 

[참고자료]

http://msdn.microsoft.com/ko-kr/library/cc280701.aspx

http://mssqlfun.com/2013/04/10/dmv-6-how-well-my-store-procedure-doing-sys-dm_exec_procedure_stats/

 

 

2013-05-08 / 강성욱 / http://sqlmvp.kr