[MSSQL] 캐싱 초기화 하는 방법

⚡ 캐싱 초기화

내가 캐싱을 초기화 하는 이유는 기존에 작업 했던 쿼리와 캐싱된 쿼리의 실행 계획이 계속 똑같아서 캐싱을 초기화 하게 되었다.

캐싱 초기화에 대한 자세한 내용은 생략한다. (다른 블로그들이 너무 잘설명 해놔서 링크 가지고 있고 까먹으면 본다.)

 

📒 사용중인 캐시 추출

SELECT * FROM SYS.SYSCACHEOBJECTS WHERE [SQL] LIKE '%테이블이름%' 

SELECT plan_handle, st.text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st 
WHERE text LIKE N'%테이블이름%';

 

📒  원하는 캐시 삭제 (위의 내용을 참조 한다)

DBCC FREEPROCCACHE (0x06000500AECB3E1FD06EF56AB6020000010)
-- plan_handle 값

 

📒  모든 DB의 캐시 삭제

DBCC FREEPROCCACHE
GO

 

📒  버퍼 풀 초기화

DBCC DROPCLEANBUFFERS
GO

 

⚡ 최적화가 필요한 플랜 캐시 관련

📕  인덱스가 누락된 현재의 모든 계획

SELECT 
	dec.usecounts, 
	dec.refcounts, 
	dec.objtype,
	dec.cacheobjtype, 
	des.dbid, 
	des.text,
	deq.query_plan 
FROM sys.dm_exec_cached_plans AS dec 
CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des 
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq 
WHERE 
deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 
ORDER BY dec.usecounts DESC

 

📕  한번만 사용된 캐시 계획

DECLARE @MB decimal(19,3) , @Count bigint, @StrMB nvarchar(20)   
SELECT
	@MB = sum(cast((CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN size_in_bytes ELSE 0 END) as decimal(12,2))) / 1024/1024,
	@Count = sum(CASE WHEN usecounts = 1 AND objtype IN ('Adhoc', 'Prepared') THEN 1 ELSE 0 END),
	@StrMB = convert(nvarchar(20), @MB) 
FROM sys.dm_exec_cached_plans  
 
 
IF @MB > 10 
BEGIN 
    DBCC FREESYSTEMCACHE('SQL Plans')  
    RAISERROR ('%s MB was allocated to single-use plan cache. Single-use plans have been cleared.', 10, 1, @StrMB) 
END 
ELSE 
BEGIN 
    RAISERROR ('Only %s MB is allocated to single-use plan cache – no need to clear cache now.', 10, 1, @StrMB) 
END

 

 

📑 참조

• https://quiz12.tistory.com/87
  

저장된 Plan Cache 확인 및 활용