안녕하세요. 이호엽 입니다.

세미나에서 사용한 캐시에 관련된 DMV 예제 내용입니다. 

스크린 캡쳐를 포함하여 재 정리해서 올립니다.

 

-- 1. SQL, SP Compiled Plan -----------------------------------------------------------

-- CLARE DB BUFFER CACHE

DBCC DROPCLEANBUFFERS

 

-- CLEAR PROCEDURE CACHE

DBCC FREEPROCCACHE

-- PLAN_HANDLE 지정

DBCC FREEPROCCACHE ('plan_handle')

 

-- 시스템 카탈로그 이용

SELECT bucketid, cacheobjtype, objtype, objid, dbid, usecounts, pagesused,sqlbytes, sql

FROM SYS.SYSCACHEOBJECTS

WHERE dbid = DB_ID('Algorizm')

 syscacheobjects.png

 

-- DMV 이용

-- objtype 조건을 사용해 해당 플랜 확인 가능(Proc, Adhoc, Prepared, Trigger .)

SELECT plan_handle, usecounts, cacheobjtype, objtype, text

FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

--AND objtype = 'Trigger'

--AND usecounts > 1

ORDER BY usecounts DESC

dm_exec_sql_text.png

---------------------------------------------------------------------------------------

-- 2. Dictionary Cache ---------------------------------------------------------------------

SELECT   b.database_id

                      , db = DB_NAME(b.database_id)

                      , p.object_id

                      , OBJECT_NAME(p.object_id) AS objname

                      , p.index_id

                      , buffer_count = COUNT(*)

FROM     sys.allocation_units a

                      , sys.dm_os_buffer_descriptors b

                      , sys.partitions p

WHERE    a.allocation_unit_id = b.allocation_unit_id

                      AND a.container_id = p.hobt_id

                      AND b.database_id = DB_ID()

GROUP BY b.database_id,p.object_id,p.index_id

ORDER BY buffer_count DESC

dm_os_buffer_descriptors.png

---------------------------------------------------------------------------------------

-- 3. Plan별 캐싱된 메모리 사용량 보기 ------------------------------------------------------- 

SELECT memory_clerk_address, type, name, single_pages_kb, multi_pages_kb, page_size_bytes, page_allocator_address, host_address

FROM SYS.DM_OS_MEMORY_CLERKS

WHERE type in ('CACHESTORE_OBJCP','CACHESTORE_SQLCP')

dm_os_memory_clerks.png

---------------------------------------------------------------------------------------

 

 

 





profile

안녕하세요. 이호엽 입니다!

ASP.NET 프론티어 

블로그: algorizm.tistory.com