DBCC FREESYSTEMCACHE (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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 every cache or from a specified Resource Governor pool cache.
Transact-SQL syntax conventions
Syntax
DBCC FREESYSTEMCACHE
( 'ALL' [ , pool_name ] )
[ WITH
{ [ MARK_IN_USE_FOR_REMOVAL ] , [ NO_INFOMSGS ] }
]
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
Arguments
( 'ALL' [ , pool_name ] )
ALL
Specifies all supported caches.
pool_name
Specifies a resource governor pool cache. Only entries associated with this pool are freed. To list the available pool names, run:
SELECT name FROM sys.dm_resource_governor_resource_pools;
Most, but not all, caches can be individually freed using this command.
MARK_IN_USE_FOR_REMOVAL
Asynchronously frees currently used entries from their respective caches after they're unused. After the DBCC FREESYSTEMCACHE WITH MARK_IN_USE_FOR_REMOVAL
runs, new entries created in the cache aren't affected.
NO_INFOMSGS
Suppresses all informational messages.
Remarks
Running DBCC FREESYSTEMCACHE
clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all upcoming execution plans and can cause a sudden, temporary reduction in query performance. For each cleared cache store in the plan cache, the SQL Server error log contains 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.
Result sets
DBCC FREESYSTEMCACHE
returns:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Permissions
Requires ALTER SERVER STATE permission on the server.
Examples
A. Release 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. Release 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;