데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

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
» DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 992
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 942
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1340
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 976
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1449
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 938
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1588
1766 DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1181
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1068
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1377
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1075
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1127
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1540
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1051
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1099
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1262
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 802
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1134
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 978
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1520





XE Login