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:
- Ad hoc query workload that fills the plan cache (cache bloat).
- Heavy use of dynamic T-SQL.
- Insufficient memory, or memory not assigned properly to the SQL Server instances.
- Memory pressure from long-running transactions.
- Frequent recompilation events.
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
DBCC FLUSHPROCINDBtakes a database id, which the source note did not give; the argument is shown as a placeholder. The command is not documented by Microsoft, so check it on a test system before use.- The second
DBCC DROPCLEANBUFFERSsyntax line is copied from the source; check the Microsoft DBCC reference for the options of your version. - The source had an empty "ALTER DATABASE" section and a duplicated
sp_recompileline; both were removed.