원본 링크

http://www.sqlskills.com/blogs/kimberly/plan-cache-adhoc-workloads-and-clearing-the-single-use-plan-cache-bloat/

http://www.sqlservercentral.com/blogs/james-sql-footprint/2012/04/06/clean-sql-server-cache/

http://blog.naver.com/PostView.nhn?blogId=hrk007&logNo=60124193909

http://hyoksong.tistory.com/entry/%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80-%EC%BA%90%EC%8B%9C%EC%9D%98-%ED%8A%B9%EC%A0%95-%ED%83%80%EC%9E%85%EB%A7%8CSQLCP-%EC%B4%88%EA%B8%B0%ED%99%94-%EC%8B%9C%ED%82%A4%EC%9E%90

http://www.sqlservercentral.com/blogs/glennberry/2009/12/28/fun-with-dbcc-freeproccache/

http://rschandrastechblog.blogspot.kr/2011/06/how-to-clear-sql-server-cache.html

 

 

ALL Cache 지우기       

 

DBCC FREEPROCCACHE

 

remove unused entries from all caches        

DBCC FREESYSTEMCACHE ('ALL','default');

 

SQL Plans 지우기

 

DBCC FREESYSTEMCACHE('SQL Plans')

 

select objtype,

count(*) as number_of_plans,

sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,

avg(usecounts) as avg_use_count

from sys.dm_exec_cached_plans

group by objtype        

 

 

 

원하는 캐쉬 항목 찾아 지우기 (By 인옥형)

 

-- 문제되는 항목 찾기        

SELECT

TOP(20) [TYPE], [NAME], SUM(PAGES_KB) AS [SPA MEM, KB]

FROM SYS.DM_OS_MEMORY_CLERKS

GROUP BY [TYPE],name

ORDER BY SUM(PAGES_KB) DESC;

 

-- 지우고 싶은 타입

DBCC FREESYSTEMCACHE ('Lock Manager : Node 0');        

 

DBCC FREESYSTEMCACHE('Object Plans') -- stored procedure 계획

DBCC FREESYSTEMCACHE('SQL Plans') -- prepared, ad-hoc 계획

 

BufferPool 지우기        

checkpoint

DBCC DROPCLEANBUFFERS

 

Procedure cache        

DBCC FREEPROCCACHE                                                -- Removes all elements from the plan cache

 

-- Example 3 (Scalpel)

-- Remove one plan from the cache

-- Get the plan handle for a cached plan

SELECT cp.plan_handle, st.[text]

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

 

-- Remove the specific plan from the cache using the plan handle

DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

 

DBCC FLUSHPROCINDB (@intDBID);                -- Flush the procedure cache for one database only

 

예)  http://www.sqlservercentral.com/blogs/glennberry/2009/12/28/fun-with-dbcc-freeproccache/

DECLARE @intDBID INT;

SET @intDBID = (SELECT [dbid]

FROM master.dbo.sysdatabases

WHERE name = 'AdventureWorks');

 

-- Flush the procedure cache for one database only

DBCC FLUSHPROCINDB (@intDBID);

 

DBCC FREESESSIONCACHE                                        -- Flushes the distributed query connection cache

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

1. Normally, use following two when you do not want query compilation also to come into picture. 

CHECKPOINT

DBCC DROPCLEANBUFFERS 

2. When you want query compilation also to come into picture,  free the proccache also : 

CHECKPOINT

DBCC DROPCLEANBUFFERS 

DBCC FREEPROCCACHE

3. The most comprehensive cleaning strategy would be : 

DBCC FREESYSTEMCACHE(All)

DBCC FREESESSIONCACHE

DBCC FREEPROCCACHE

DBCC FLUSHPROCINDB( db_id )

CHECKPOINT

DBCC DROPCLEANBUFFERS