LLI Wiki — My Digital Knowledge Base

Clear SQL Server caches with DBCC commands

DBCC commands to clear the plan cache, system caches, clean buffers and authentication caches, with their use cases.

Clearing a SQL Server cache forces recompilation or cold reads, which can slow the workload down. Use these commands for a targeted reason, for example when a plan is wrong, when a cache is bloated or during testing, rather than as routine maintenance.

Typical causes that lead to a cache clean:

DBCC FREEPROCCACHE

Clears the plan cache, one plan or one resource governor pool.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Show the memory used by each resource pool:

SELECT name AS 'Pool Name',
       cache_memory_kb / 1024.0 AS [cache_memory_MB],
       used_memory_kb / 1024.0 AS [used_memory_MB]
FROM sys.dm_resource_governor_resource_pools;

Clear the plans of one resource pool, here a pool named LimitedIOPool:

DBCC FREEPROCCACHE ('LimitedIOPool');

Clear the plans of one procedure

Find the plan handle of a procedure, then pass it to DBCC FREEPROCCACHE:

SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE OBJECT_NAME(st.objectid) LIKE '%TestProcedure%';
DBCC FREEPROCCACHE (<plan_handle>);

DBCC FLUSHPROCINDB

Removes the procedure plans of one database. The command takes the database id as argument.

DBCC FLUSHPROCINDB (<database_id>);

DBCC FREESYSTEMCACHE

Clears the cache stores of the instance. Examples:

-- Ad hoc and prepared plans of the whole instance
DBCC FREESYSTEMCACHE ('SQL Plans');

-- Table variables and temporary tables
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables');

-- Entries of one user database
DBCC FREESYSTEMCACHE ('userdatabase');

-- Entries of tempdb
DBCC FREESYSTEMCACHE ('tempdb');

-- All stores, marked for removal as they are released
DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

DBCC FREESESSIONCACHE

Flushes the cache of distributed query connections.

DBCC FREESESSIONCACHE WITH NO_INFOMSGS;

DBCC FLUSHAUTHCACHE

Flushes the authentication cache. See the Microsoft DBCC reference for its exact behaviour.

DBCC FLUSHAUTHCACHE;

Mark a procedure or table for recompilation

sp_recompile makes SQL Server recompile the plans that reference the object the next time they run.

EXEC sp_recompile N'myprocedure';
EXEC sp_recompile N'mytable';

DBCC DROPCLEANBUFFERS

Removes the clean pages from the buffer pool, so the next reads come from disk.

DBCC DROPCLEANBUFFERS [WITH NO_INFOMSGS];
DBCC DROPCLEANBUFFERS (COMPUTE | ALL) [WITH NO_INFOMSGS];

Notes

Mastodon LLIMastodon SocialFosstodon