SQL Server 플랜 캐쉬 (Plan Cache)

WEB TIP/MS-SQL

SQL Server 플랜 캐쉬 (Plan Cache)

제프 2017. 10. 11. 15:11

우연히 이전글을 보다가 플랜 캐쉬가 4GB가 넘으면 개발자랑 DBA랑 손잡고 한강으로....라는 것이 보이길래
현재 유지보수하고 있는곳을 살펴봤더니 4GB가 넘네요;;;;

확인 방법

플랜캐쉬 전체 사이즈 확인: 제일 상단 TYPE이 NULL인 행의 사이즈 확인
플랜캐쉬 사이즈 확인: 쿼리문을 실행해서 어느 곳에서 과도한 메모리가 사용되는지 확인
플랜캐쉬된 목록 확인: Adhoc, Proc, Prepared등을 확인하여 문제가 있는곳을 찾는다

-- 1. 플랜캐쉬 전체 사이즈 확인
SELECT TOP(20) [TYPE]
     , SUM(SINGLE_PAGES_KB) AS [SPA MEM, KB]
  FROM SYS.DM_OS_MEMORY_CLERKS
 GROUP BY [TYPE] 
  WITH ROLLUP
 ORDER BY SUM(SINGLE_PAGES_KB) DESC

-- 2. 플랜캐쉬 사이즈 확인
SELECT objtype AS [CacheType]
     , count_big(*) AS [Total Plans]
     , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
     , avg(usecounts) AS [Avg Use Count]
     , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
     , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
  FROM sys.dm_exec_cached_plans
 GROUP BY objtype
 ORDER BY [Total MBs - USE Count 1] DESC

-- 3. 플랜캐쉬된 목록 보기
SELECT cp.objtype
     , cp.cacheobjtype
     , cp.size_in_bytes
     , cp.refcounts
     , cp.usecounts
     , st.text
  FROM sys.dm_exec_cached_plans AS cp
 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
 WHERE cp.objtype = 'Adhoc'  -- Adhoc, Proc, Prepared....
   AND st.text NOT LIKE '%SELECT cp.objecttype%'
   AND st.text NOT LIKE '%@BatchID%'
   AND st.text NOT LIKE '%@_msparam%'
   AND st.text NOT LIKE '%database%'
   AND st.text NOT LIKE '%objtype%'
 ORDER BY cp.objtype


-- 플랜캐쉬 옵션값 확인
SELECT *
  FROM sys.dm_exec_plan_attributes(0x0600040053D99A184081EAAD000000000000000000000000)
 WHERE attribute = 'set_options'

해결 방법