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

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

DMV를 이용한 플랜 캐시 사용 정보 확인

  • Sys.dm_exec_cached_plans

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012

 

SQL Server에서 빠른 쿼리 실행을 위해 실행계획을 캐시하고 있는데 sys.dm_exec_cached_plans DMV를 통하여 캐시된 쿼리 계획, 캐시된 쿼리 텍스트, 캐시된 계획이 사용한 메모리 양, 캐시된 실행 계획의 재사용 횟수 등의 정보를 확인 할 수 있다.

 

아래 스크립트를 이용하여 캐시된 계획을 확인 할 수 있다. 스크립트를 실행 하기 위해서는 서버에 대한 VIEW SERVER STATE 권한이 필요 하다.

select * from sys.dm_exec_cached_plans

 

 

열이름

데이터 형식

설명

bucketid

Int

캐시된 버킷 ID. 32bit 시스템에서 최대 10007까지, 64 bit 시스템에서 최대 4009까지가능. BountTrees 캐시의 경우 32bit 시스템에서 최대 1009, 64bit 에서 최대 4001까지 가능.Extended Stored Procedures 캐시의 경우 해시 테이블 크기는 32bit 및 64 bit 시스템에 최대 127까지 가능

Refcounts

Int

캐시 개체를 차참조하는 캐시 개체 수. 항목이 캐시에 있으려면 refcounts 가 1 이상이어야 한다.

Usecounts

Int

캐시 개체를 조회한 횟수. 매개 변수가 있는 쿼리가 캐시에서 계획을 찾는 경우에는 증가하지 않는다.

size_in_bytes

Int

개체가 사용한 바이트 수

Memory_object_address

Int

캐시된 항목의 메모리 주소. Sys.dm_os_memory_objects와 함께 사용하여 캐시된 계획의 메모리 분석을 가져 올 수 있음. Sys.dm_os_memeory_cache_entries와 함께 사용하여 항목 캐시비용을 구할 수 있음.

Cacheobjtype

Varbinary(8)

캐시에 있는 개체의 유형.

Compiled Plan

Compiled Plan Stub

Parse Tree

Extended Proc

CLR Compiled Func

CLR Compiled Proc

objtype

Nvarchar(34)

개체의 유형

Proc = 저장 프로시저

Prepared = 프리페어드

Adhoc = 임시쿼리

ReplProc = 복제 필터 프로시저

Trigger = 트리거

View = 뷰

Default = 기본값

UsrTab = 사용자 테이블

SysTab = 시스템 테이블

Check = CHECK 제약 조건

Rule = 규칙

Plan_handle

Nvarchar(16)

메모리 내 계획의 식별자. 일시적이며 계획이 캐시에 있는 동안에만 일정하게 유지.

Pool_id

int

계획 메모리 사용량이 계산된 리소스 풀의 ID

 

 

[재사용된 캐시의 SQL Text 반환]

SELECT usecounts, cacheobjtype, objtype, text

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE usecounts > 1

ORDER BY usecounts DESC;

GO

 

 

 

[캐시된 트리거의 쿼리 계획 반환]

SELECT plan_handle, query_plan, objtype

FROM sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_query_plan(plan_handle)

WHERE objtype ='Trigger';

GO

 

[플랜 컴파일시 사용된 SET 옵션 반환]

SELECT plan_handle, pvt.set_options, pvt.sql_handle

FROM (

SELECT plan_handle, epa.attribute, epa.value

FROM sys.dm_exec_cached_plans

OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa

WHERE cacheobjtype = 'Compiled Plan'

) AS ecpa

PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;

GO

 

 

 

[캐시에서 플랜 컴파일에 사용된 메모리 분석 반환]

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,

omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes

FROM sys.dm_exec_cached_plans AS ecp

JOIN sys.dm_os_memory_objects AS omo

ON ecp.memory_object_address = omo.memory_object_address

OR ecp.memory_object_address = omo.parent_address

WHERE cacheobjtype = 'Compiled Plan';

GO

 

 

 

[단일 사용 및 임의 쿼리 찾기]

SELECT

    ST.[TEXT] ,

    CASE WHEN ST.DBID = 32767 THEN 'RESOURCEDB' ELSE DB_NAME(ST.DBID) END AS DATABASE_NAME,

    CP.SIZE_IN_BYTES

FROM

    SYS.DM_EXEC_CACHED_PLANS AS CP

    CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CP.PLAN_HANDLE) AS ST

WHERE CP.CACHEOBJTYPE = 'COMPILED PLAN'

    AND CP.OBJTYPE = 'ADHOC'

    AND CP.USECOUNTS = 1

ORDER BY CP.SIZE_IN_BYTES DESC

 

 

 

[참고자료]

 


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

No. Subject Author Date Views
1771 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (4/4) – 분산형 분할 뷰(View) 확인 jevida(강성욱) 2016.09.27 1337
1770 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (3/4) – 뷰(View)의 인덱스 확인 jevida(강성욱) 2016.09.27 973
1769 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (2/4) – 뷰(View) 확인 jevida(강성욱) 2016.09.27 1447
1768 SQL Server 쿼리 처리 아키텍처 _ SQL 문 처리 (1/4) – SQL 문 최적화 및 Worktables jevida(강성욱) 2016.09.27 933
1767 SQL Server DMV를 이용한 통계 정보 확인 jevida(강성욱) 2016.09.27 1487
» DMV를 이용한 플랜 캐시 사용 정보 확인 jevida(강성욱) 2016.09.27 1172
1765 SQL Server 테이블 및 인덱스 구조 아키텍처(4/4) – 비클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1066
1764 SQL Server 테이블 및 인덱스 구조 아키텍처(3/4) – 클러스터형 인덱스 구조 jevida(강성욱) 2016.09.27 1370
1763 SQL Server 테이블 및 인덱스 구조 아키텍처(2/4) – 힙 구조 jevida(강성욱) 2016.09.27 1069
1762 SQL Server 테이블 및 인덱스 구조 아키텍처(1/4) – 테이블 및 인덱스 구성 jevida(강성욱) 2016.09.27 1127
1761 SQL Server 트랜잭션 로그 아키텍처(4/4) – 미리 쓰기 트랜잭션 로그 jevida(강성욱) 2016.09.27 1537
1760 SQL Server 트랜잭션 로그 아키텍처(3/4) – 검사점 및 로그의 활성 부분 jevida(강성욱) 2016.09.27 1048
1759 SQL Server 트랜잭션 로그 아키텍처(2/4) – 트랜잭션 로그 물리 아키텍처 jevida(강성욱) 2016.09.27 1096
1758 SQL Server 트랜잭션 로그 아키텍처(1/4) – 트랜잭션 로그 논리 아키텍처 jevida(강성욱) 2016.09.27 1254
1757 파일 및 파일 그룹 아키텍처 jevida(강성욱) 2016.09.27 801
1756 SQL Server 페이지 및 익스텐트 아키텍처(4/4) – 수정된 익스텐트 추적 jevida(강성욱) 2016.09.27 1130
1755 SQL Server 페이지 및 익스텐트 아키텍처(3/4) – 개체에서 사용하는 공간 관리 jevida(강성욱) 2016.09.27 975
1754 SQL Server 페이지 및 익스텐트 아키텍처(2/4) – 익스텐트 할당 및 빈공간 관리 jevida(강성욱) 2016.09.27 1462
1753 SQL Server 페이지 및 익스텐트 아키텍처(1/4) – 페이지 및 익스텐트 이해 jevida(강성욱) 2016.09.27 3323
1752 SQL Server Error Log 보관 주기 설정 jevida(강성욱) 2016.09.15 2184





XE Login