DMV를 이용한 플랜 캐시 사용 정보 확인 - Sys.dm_exec_cached_plans

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

 

 

 

[참고자료]

• http://msdn.microsoft.com/ko-kr/library/ms187404.aspx
• http://mssqlfun.com/2013/04/08/dmv-5-queries-runing-are-adhoc-or-proc-single-or-multi-use-sys-dm_exec_cached_plans/

 

 

2013-04-29 / 강성욱 / http://sqlmvp.kr