한번 썼던 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