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'
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
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)'
