PLAN CACHE에서 단일 실행계획을 찾고, 또 삭제하는 캐시 쿼리 명령어

-- 현재 캐시된것들 확인하기

SELECT
	*
FROM
	SYS.dm_exec_cached_plans
GO

 

 

 

 

 

 

 

-- PLAN CACHE에서 단일 실행계획을 찾는 쿼리문(PLAN CACHE사이즈를 최적화하기위함)

아래 출력되는 것들을 재 컴파일하면 속도를 줄일 수 있음

--> 한번만 쓰고 버리는 쿼리들 위주로 재컴파일 필요,,,,, 해당 쿼리문은 재활용 하자!!!!

SELECT
	TEXT, CP.OBJTYPE, CP.SIZE_IN_BYTES
FROM 
	SYS.dm_exec_cached_plans AS cp
CROSS APPLY
	SYS.dm_exec_sql_text(CP.PLAN_HANDLE) ST
WHERE
	CP.cacheobjtype = 'Compiled Plan'
AND
-- Adhoc은 쿼리를 한번 던지는것, Prepared은 사전에 필요한 것들을 의미
	CP.OBJTYPE IN('Adhoc','Prepared')
AND
	CP.USECOUNTS = 1	--1번만 실행하고 아직 CACHE에 있다.
ORDER BY
	CP.SIZE_IN_BYTES DESC
OPTION(RECOMPILE)

GO

 

아래와 같이 출력됨

 

 

 

 

 

--한번만 실행된 쿼리문은 저장이 안되게(조금만 차지하게) 활성화시킬  수 있다.

 

 

 

 

PLAN CACHE에서 단일 사용계획에 사용되는 메모리양을 확인 할 때 사용하는 쿼리

SELECT

    objtype AS [CacheType]
    , count_big(*) AS [Total Plans]
    , sum(cast(size_in_bytes as decimal(18,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(18,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

 

 

-- 한번 썼던  PLAN CACHE 쿼리들을 삭제하는 명령어

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')	--10보다 크면 DBCC명령문으로 지워줌
		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
GO

 

 

 

코드를 실행하면 아래와같이 메세지가 뜬다.

 

 

 

그리고 다시 PLAN CACHE 단일실행 계획을 찾는 쿼리문을 실행해본다.

그럼 결과가 매우 많이 줄어든것을 알 수 있다.

 

 

 

 

 

 

 

 

 

 

 

최적화가 필요한 쿼리 찾기-- 건너뛰어도 됨......

/*
	최적화가 필요한 PlanCache 확인(찾기)
	인덱스가 없는 현재 모든 클랜 캐시를 찾음
*/
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

go


--인덱스가 있는것.
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
    cp.query_hash,
	cp.query_plan_hash,
    ConvertIssue= operators.value('@ConvertIssue', 'nvarchar(250)'),
    Expression= operators.value('@Expression', 'nvarchar(250)'), 
	qp.query_plan
FROM 
	sys.dm_exec_query_stats cp
CROSS APPLY 
	sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY 
	query_plan.nodes('//Warnings/PlanAffectingConvert') rel(operators) --xml로 나옴

go

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

그리고 다시 PLAN CACHE 단일사용계획에 사용되는 메모리양을 확인하는 쿼리를 사용해본다.

이또한 엄청 줄어든것을 알 수 있다.