DBCC FreeSystemCache

Being day 14 of the DBCC Command month at SteveStedman.com, today’s featured DBCC Command is DBCC FREESYSTEMCACHE.

Description:

DBCC FREESYSTEMCACHE is used to free all memory associated with all of the SQL Server caches.

DBCC FREESYSTEMCACHE Syntax:

dbcc freesystemcache
(
    'ALL' [, 'POOL NAME']
)
    [ WITH
        {
            [ MARK_IN_USE_FOR_REMOVAL ]
            [, [ NO_INFOMSGS ] ]
        }
    ]

Example:

The following example we first select form sys.dm_os_memory_clerks to find all of the available system caches that could be freed.

SELECT DISTINCT name
 FROM sys.dm_os_memory_clerks;
DBCC FREESYSTEMCACHE('SQL Plans');

In order to clear all the plans for a specific database we can call DBCC FreeSystemCache witht he database name as shown here.

DBCC freesystemcache ('Northwind');

If we want to free everything we would us the ‘ALL’ parameter as shown here.

DBCC FREESYSTEMCACHE ('ALL');

And finally the option to mark the in use items for removal when they are done being used.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Notes:

For more information see TSQL Wiki DBCC freesystemcache.

DBCC Command month at SteveStedman.com is almost as much fun as samauri sudoku.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!