최고보다,최선을...

SELECT

text,

usecounts,

plan_handle

FROM SYS.DM_EXEC_CACHED_PLANS

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE)

WHERE USECOUNTS = 1

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

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 플랜캐쉬삭제

*******************************************************************/

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 플랜캐쉬삭제' ,