[MS-SQL] 2008 플랜캐시(Plan Cache) 삭제하기
MSSQL 2008 플랜캐시(Plan Cache) 삭제하기
출처 : http://ddoung2.tistory.com/242
엄청 오랫만에 글을 포스팅 하는것 같아요. 이래저래 정신없고 포스팅 할 여유가 없었네요 ~ ! 이번 글을 계기로 다시 한번 열심히 포스팅에 불을 -0-;;
오늘 울 DB Team.에 팀장으로 새로 오신 차주언 형님이 MSSQL 2008에 Adhoc 플랜캐시가 적재되는 bug가 있다하여 일일 작업으로 해당 Adhoc 플랜캐시를 삭제해줘야 한다고 하여, Job Schedule 로 만들어 보았습니다.
우선 아래 스크립트를 이용하여 현재 서버에 어떤 Adhoc 쿼리가 쌓여 있는지 한번 알아보았습니다. 제가 사용하고 있는 TEST 서버에서는 해당 쿼리를 실행시켰을때 사용하지도 않았던 이상한 update 쿼리문이 약 3000개가 적재되어 있더군요.
SELECT
text,
usecounts,
plan_handle
FROM SYS.DM_EXEC_CACHED_PLANS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
WHERE USECOUNTS = 1
AND OBJTYPE = 'ADHOC'
그럼 해당 Adhoc 플랜캐시가 도대체 얼마만큼이나 메모리에 올라가 있는지 아래 쿼리로 확인해 보았습니다. 제 경우는 그렇게 크게 메모리에 올라가 있지 않았지만, 주언 형님의 말씀으로는 약 2GB이상 올라가 있는것도 보았다고 하더군요.
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
자~~~ 그럼 플랜캐시가 얼마만큼 올라가 있고, 그 플랜캐쉬가 얼마만큼의 메모리를 사용하고 있는지 보았으니, 해당 플랜캐시를 주기적으로 삭제하기 위해서 MSSQL 스케쥴러에 등록해 보겠습니다.
아래 쿼리는 Adhoc 플랜캐시를 메모리상에서 지우는 쿼리를 프로시저로 만드는 쿼리 입니다.
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*************************************************************************
** Name : MSSQL 2008 Server 에서AdHoc 플랜캐쉬삭제
** Desc : MSSQL 2008 Server 에서AdHoc 플랜캐쉬삭제
** EXEC :
** Auth : 이승연(gamebible)
** Date : 2011-01-31
**************************************************************************
** Change History
**************************************************************************
** Date: Author: Description:
** ---------- -------- ---------------------------------------
**
*************************************************************************/
ALTER PROCEDURE [dbo].[usp_SYSTEM_AdHoc_FREEPROCCACHE]
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
/*******************************************************************
** 변수선언
*******************************************************************/
DECLARE @MAX INT;
DECLARE @PLAN_HANDLE VARCHAR (1000);
DECLARE @SQL_TEXT VARCHAR(MAX);
/*******************************************************************
** 변수선언
*******************************************************************/
/*******************************************************************
** 임시테이블삭제
*******************************************************************/
IF EXISTS(SELECT * FROM tempdb.sys.sysobjects where name like '#T_PLAN_HANDLE%')
DROP TABLE #T_PLAN_HANDLE;
/*******************************************************************
** 임시테이블삭제
*******************************************************************/
/*******************************************************************
** 임시테이블생성
*******************************************************************/
CREATE TABLE #T_PLAN_HANDLE (
IDX INT IDENTITY(1,1) ,
PLAN_HANDLE VARBINARY (1000)
);
/*******************************************************************
** 임시테이블생성
*******************************************************************/
/*******************************************************************
** AdHoc 플랜캐쉬핸들저장
*******************************************************************/
INSERT INTO #T_PLAN_HANDLE
SELECT
PLAN_HANDLE
--, *
FROM SYS.DM_EXEC_CACHED_PLANS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)
WHERE
USECOUNTS = 1
AND OBJTYPE = 'ADHOC'
/*******************************************************************
** AdHoc 플랜캐쉬핸들저장
*******************************************************************/
/*******************************************************************
** AdHoc 플랜캐쉬삭제
*******************************************************************/
SELECT @MAX = MAX(IDX) FROM #T_PLAN_HANDLE;
WHILE (@MAX > 0) BEGIN
SELECT @PLAN_HANDLE = SYS.FN_SQLVARBASETOSTR( PLAN_HANDLE) FROM #T_PLAN_HANDLE WHERE IDX = @MAX;
SET @SQL_TEXT = 'DBCC FREEPROCCACHE ('+CAST(@PLAN_HANDLE AS VARCHAR(8000))+')';
EXEC (@SQL_TEXT)
--print @sql_text
SET @MAX = @MAX - 1
END
/*******************************************************************
** AdHoc 플랜캐쉬삭제
*******************************************************************/
END
그리고 스케쥴러에 등록해 보아요.
USE msdb
GO
EXEC sp_add_job @job_name = 'AdHoc 플랜캐쉬삭제'
EXEC sp_add_jobstep
@job_name = 'AdHoc 플랜캐쉬삭제' ,
@step_id = 1 ,
@step_name = 'AdHoc 플랜캐쉬삭제' ,
@subsystem = 'TSQL' ,
@command = 'EXEC [dbo].[usp_SYSTEM_AdHoc_FREEPROCCACHE];' ,
@database_name = 'master'
EXEC sp_add_jobschedule
@job_name = 'AdHoc 플랜캐쉬삭제' ,
@name = '일정' ,
@freq_type = 4 , -- 매일
@freq_interval = 1 , -- 한번
@active_start_time = 60000 -- HHMMSS
EXEC sp_add_jobserver
@job_name = 'AdHoc 플랜캐쉬삭제' ,
@server_name = N'(local)'
해당 스케쥴러는 매일 한번 새벽 6시에 돌아가게 해두었습니다.