SQL SERVER – Clean Cache and Clean Buffer

We do get a situation where SQL Server will not be in a position to allocate memory for the new resources. At times, we have to clear up the memory to allocate space to new resources.

DBCC FREEPROCCACHE

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

DBCC DROPCLEANBUFFERS

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREESYSTEMCACHE ('ALL')

Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache.

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.

To find the cached size per database

SELECT count(*)*8/1024 AS 'Cached Size (MB)' ,CASE database_id WHEN 32767 THEN 'ResourceDb'  ELSE db_name(database_id)  END AS 'Database'  
FROM sys.dm_os_buffer_descriptors 
GROUP BY db_name(database_id) ,database_id 
ORDER BY 'Cached Size (MB)' DESC 

To find the cached size of the plans

SELECT objtype AS 'Cached Object Type', count(*) AS 'Number of Plans', sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Plan Cache Size (MB)', avg(usecounts) AS 'Avg Use Count' 
FROM sys.dm_exec_cached_plans 
GROUP BY objtype

 

http://manage-sqlserver.blogspot.in/2012/08/clean-up-sql-server-memory.html

http://technet.microsoft.com/en-us/library/ms178529.aspx

http://technet.microsoft.com/en-us/library/ms187781.aspx