DBCC Commands to Free SQL Server Memory Caches

DBCC FREESYSTEMCACHE

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. You are require to have ALTER SERVER STATE permission on the server.

The following example illustrates how to clean caches that are dedicated to a specified Resource Governor resource pool.

-- Clean all the caches with entries specific to the resource pool named "default".
DBCC FREESYSTEMCACHE ('ALL','default');

The following example uses the MARK_IN_USE_FOR_REMOVAL clause to release entries from all current caches once the entries become unused.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

DBCC FREESESSIONCACHE

Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server. Requires membership in the sysadmin fixed server role..

The following example flushes the distributed query cache.

USE AdventureWorks2008R2;
GO
DBCC FREESESSIONCACHE WITH NO_INFOMSGS;
GO

DBCC FREEPROCCACHE

Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool. You are require to have ALTER SERVER STATE permission on the server. Please refer to MSDN for more information.