데이터베이스 개발자 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
1791 메모리 관리 아키텍처 – 버퍼 관리_페이지 쓰기 jevida(강성욱) 2016.09.28 1056
1790 메모리 관리 아키텍처 – 버퍼 관리_페이지 읽기 jevida(강성욱) 2016.09.28 1250
1789 메모리 관리 아키텍처 – 버퍼 관리 jevida(강성욱) 2016.09.28 1818
1788 메모리 관리 아키텍처 – Min/Max Server Memory 효과 jevida(강성욱) 2016.09.28 2557
1787 메모리 관리 아키텍처 – 동적 메모리 관리 jevida(강성욱) 2016.09.28 1365
1786 메모리 관리 아키텍처 – 프로세스 주소 공간 jevida(강성욱) 2016.09.28 1363
1785 메모리 관리 아키텍처 – 메모리 아키텍처 jevida(강성욱) 2016.09.28 1873
1784 데이터 압축 상태에 대한 개체 크기 예상 jevida(강성욱) 2016.09.28 1368
1783 sp_MSforeachdb, sp_MSforeachtable 프로시저 활용하기 jevida(강성욱) 2016.09.28 2887
1782 SQL Server 쿼리 처리 아키텍처_분산 쿼리 아키텍처 jevida(강성욱) 2016.09.28 1145
1781 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 인덱스 작업 jevida(강성욱) 2016.09.28 1341
1780 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 - 병렬 처리 수준 jevida(강성욱) 2016.09.28 1853
1779 SQL Server 쿼리 처리 아키텍처_병렬 쿼리 처리 jevida(강성욱) 2016.09.28 1791
1778 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - Preparing SQL Statements jevida(강성욱) 2016.09.28 1009
1777 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 강제 매개 변수화 jevida(강성욱) 2016.09.28 999
1776 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 단순 매개 변수화 jevida(강성욱) 2016.09.28 880
1775 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 - 매개 변수 및 실행 계획 재사용 jevida(강성욱) 2016.09.28 1098
1774 SQL Server 쿼리 처리 아키텍처_실행 계획 캐싱 및 다시 사용 jevida(강성욱) 2016.09.28 1440
» DMV를 이용한 캐시된 저장 프로시저 통계 정보 확인 jevida(강성욱) 2016.09.28 989
1772 SQL Server 쿼리 처리 아키텍처_저장 프로시저 및 트리거 실행 jevida(강성욱) 2016.09.27 941





XE Login