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/

 

 


강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
Notice SQL강좌: 챗GPT와 함께 배우는 SQL Server 무료 강좌 목차와 소개 (2023년 9월 업데이트) 코난(김대우) 2023.08.18 14478
Notice Python 무료 강좌 - 기초, 중급, 머신러닝(2023년 6월 업데이트) 코난(김대우) 2021.01.01 8885
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1481
» DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 1040
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 976
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1370
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 998
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1485
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 962
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 2135
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1243
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1130
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1423
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1129
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1195
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1571
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1082
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1135
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1283
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 835
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1176
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 1023





XE Login