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

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

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