DBCC FREESYSTEMCACHE (Transact-SQL)

DBCC FREESYSTEMCACHE (Transact-SQL)

Switch on low bandwidth view

Community Content

In this section

Statistics Annotations (2)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

SQL Server 2008 Books Online (October 2009)

DBCC FREESYSTEMCACHE (Transact-SQL)

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.

Topic link icon Transact-SQL Syntax Conventions

Syntax

DBCC FREESYSTEMCACHE 
        ( 'ALL' [, pool_name ] ) 
    [WITH 
    { [ MARK_IN_USE_FOR_REMOVAL ] , [ NO_INFOMSGS ]  }
    ]

Arguments

( 'ALL' [, pool_name ] )

ALL specifies all supported caches.

pool_name specifies a Resource Governor pool cache. Only entries associated with this pool will be freed.

MARK_IN_USE_FOR_REMOVAL: Asynchronously frees currently used entries from their respective caches after they become unused. New entries created in the cache after the DBCC FREESYSTEMCACHE WITH MARK_IN_USE_FOR_REMOVAL is executed are not affected.

NO_INFOMSGS: Suppresses all informational messages.

Result Sets

DBCC FREESYSTEMCACHE returns:

"DBCC execution completed. If DBCC printed error messages, contact your system administrator."

Remarks

Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

Permissions

Requires ALTER SERVER STATE permission on the server.

Examples

A. Releasing unused cache entries from a Resource Governor pool cache

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');

B. Releasing entries from their respective caches after they become unused

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;

Tags

: Add a tag

Community Content

Add new content

Annotations

Seeing more detail about the caches.

Mark Cleary | Edit | Show History

The first paragraph says "Releases all unused cache entries from all caches." but doesn't list the specific caches. You can see the caches using DMV's. The view sys.dm_os_memory_cache_counters lists the individual caches and has counts of memory allocated and in use, entries allocated and entries in use. Querying the view before and after using DBCC FREESYSTEMCACHE ('ALL') shows it at work.

First use SELECT DISTINCT TYPE FROM SYS.DM_OS_MEMORY_CACHE_COUNTERS to see there are 27 cache types.

CACHESTORE_PHDR
CACHESTORE_XMLDBTYPE
CACHESTORE_EVENTS
USERSTORE_OBJPERM
USERSTORE_TOKENPERM
CACHESTORE_FULLTEXTSTOPLIST
CACHESTORE_XPROC
CACHESTORE_BROKERRSB
CACHESTORE_XMLDBATTRIBUTE
CACHESTORE_STACKFRAMES
CACHESTORE_NOTIF
CACHESTORE_XMLDBELEMENT
CACHESTORE_OBJCP
CACHESTORE_CONVPRI
CACHESTORE_BROKERREADONLY
CACHESTORE_SQLCP
CACHESTORE_SYSTEMROWSET
USERSTORE_SCHEMAMGR
CACHESTORE_TEMPTABLES
CACHESTORE_BROKERTBLACS
CACHESTORE_BROKERTO
CACHESTORE_BROKERKEK
USERSTORE_SXC
CACHESTORE_BROKERUSERCERTLOOKUP
CACHESTORE_BROKERDSH
USERSTORE_DBMETADATA
CACHESTORE_VIEWDEFINITIONS

Most of these have a single cache associated with them (EG CACHSTORE_PHDR has one cache called Bound Trees). Some have multiple caches, eg USERSTORE_TOKENPERM has hundreds of caches but most of the entries are in a few of them like the TokenAndPermUserStore.

Compare this to FREEPROCCACHE: "Removes all elements from the plan cache...". This only affects the CACHESTORE_OBJCP and
CACHESTORE_SQLCP caches.

A. Releasing unused cache entries from a Resource Governor pool cache

B. Releasing entries from their respective caches after they become unused

Reference

Other Resources

Help and Information