SQL Server DBCC Commands: DBCC FREESYSTEMCACHE

DBCC FREESYSTEMCACHE is a database console command (DBCC) in Microsoft SQL Server that can be used to clear the system cache, which is a region of memory used by the database engine to store data pages read from disk and other objects such as stored procedures and triggers. Clearing the system cache can be useful for troubleshooting performance issues or for simulating a cold cache scenario, where the data pages and objects needed by a query are not in the cache and must be read from disk.

To use DBCC FREESYSTEMCACHE, you can specify one or more options to control which objects are removed from the cache. The options available are:

CLEANBUFFERS: removes all data pages from the buffer pool.

FULL: removes all data pages and objects from the cache.

PROCEDURECACHE: removes all stored procedures and triggers from the cache.

SQLPLAN: removes all query execution plans from the cache.

Here is an example of how to use DBCC FREESYSTEMCACHE to remove all data pages and objects from the cache:

-- Remove all data pages and objects from the cache
DBCC FREESYSTEMCACHE ('FULL');

The output of DBCC FREESYSTEMCACHE will be a message indicating whether the operation was successful or not. If the operation was successful, the specified objects will be removed from the cache.

It is important to note that DBCC FREESYSTEMCACHE can be a resource-intensive operation, as it requires clearing the cache of all data pages and objects. As a result, it is generally best to avoid using DBCC FREESYSTEMCACHE in production environments, or to use it sparingly and only when necessary.