SQL Server DBCC FREEPROCCACHE: Clear the Plan Cache Safely

calendar Jun 6, 2026 / · 9 min read · sql server plan cache query performance parameter sniffing performance tuning database administration sql scripts dbcc commands dbcc freeproccache sys.dm_exec_cached_plans sys.dm_exec_sql_text sys.dm_resource_governor_resource_pools  ·
Share on: linkedin copy

A stored procedure that returned in 20 milliseconds yesterday is timing out today, and nothing in the code changed. Indexes are intact and statistics look current, yet the query now takes a different, slower path. One cause worth checking is a cached plan that was compiled for an unrepresentative set of parameter values. DBCC FREEPROCCACHE can remove that plan, but run without an argument it removes every plan on the instance.

Purpose and Overview

Which cached plan is dragging a query down, and can it be removed without forcing every other query on the instance to recompile? The command that answers the second half is DBCC FREEPROCCACHE. It removes all elements from the plan cache, removes a specific plan by plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

That gives four scopes: no argument (the whole cache), a plan_handle, a sql_handle, or a Resource Governor pool_name. The database engine caches query plans and reuses them, and a plan is compiled using the parameter values passed at the time of compilation. When those values are unusual, every later execution reuses a plan shaped for the unusual case. Evicting that one plan makes the next execution compile again with whatever values arrive then.

Clear the plan cache carefully: clearing it "causes all plans to be evicted, and incoming query executions will compile a new plan, instead of reusing any previously cached plan," which "can cause a sudden, temporary decrease in query performance as the number of new compilations increases." The script below finds the plan first and evicts only that one. It then shows how to inspect the cache for single-use plans, because a cache full of plans that never get reused is a different problem from a single bad plan and is not fixed by flushing.

Code Breakdown

The first query locates the plan for a given procedure or statement. The second evicts one plan by its handle. The third is the pool-scoped form, and the fourth summarizes the cache by object type so you can judge whether flushing is even the right move.

 1-- 1. Find the plan. The handle is transient: it stays constant only while the plan is in cache.
 2SELECT TOP (20)
 3       cp.plan_handle,
 4       cp.cacheobjtype,
 5       cp.objtype,
 6       cp.usecounts,
 7       cp.size_in_bytes,
 8       st.text
 9FROM   sys.dm_exec_cached_plans AS cp
10CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
11WHERE  st.text LIKE N'%usp_GetOrdersByCustomer%'
12  AND  st.text NOT LIKE N'%sys.dm_exec_cached_plans%'
13ORDER BY cp.usecounts DESC;
14
15-- 2. Evict that one plan. Replace the value with the plan_handle returned above.
16DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
17
18-- 3. Pool-scoped: look up the pool name, then clear only that pool's entries.
19SELECT pool_id, name
20FROM   sys.dm_resource_governor_resource_pools;
21
22DBCC FREEPROCCACHE ('default');
23
24-- 4. Cache profile by object type, with a single-use count.
25SELECT cp.objtype,
26       COUNT(*)                                          AS plan_count,
27       SUM(CAST(cp.size_in_bytes AS bigint)) / 1048576   AS cache_mb,
28       SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END) AS single_use
29FROM   sys.dm_exec_cached_plans AS cp
30GROUP BY cp.objtype
31ORDER BY cache_mb DESC;

Finding the plan with sys.dm_exec_cached_plans

sys.dm_exec_cached_plans returns a row for each query plan cached by SQL Server, and it can be used to find cached query plans, cached query text, the memory taken by cached plans, and the reuse count. The plan_handle column is the "identifier for the in-memory plan" and is "transient and remains constant only while the plan remains in the cache," so copy it and use it promptly. A handle copied an hour ago may point at nothing.

sys.dm_exec_sql_text is a table-valued function that takes the handle as an argument and returns the batch text, so it is called per row with CROSS APPLY. Its text column is NULL for encrypted objects, so an encrypted procedure will not match a LIKE filter. The second predicate excludes the search query itself, which otherwise appears in its own results because its own text contains the pattern.

Evicting a single plan

The plan-handle form removes exactly one plan and leaves the rest of the cache in place. The standard single-plan pattern does the same thing: query sys.dm_exec_cached_plans and sys.dm_exec_sql_text for the handle and then pass that value to DBCC FREEPROCCACHE "to remove only that plan from the plan cache." The sql_handle form works the same way, with the handle taken from a view such as sys.dm_exec_query_stats or sys.dm_exec_requests.

The command needs ALTER SERVER STATE permission on the server. Without WITH NO_INFOMSGS it returns "DBCC execution completed. If DBCC printed error messages, contact your system administrator."

Pool-scoped eviction

The pool_name argument is the name of a Resource Governor resource pool, and you obtain it by querying sys.dm_resource_governor_resource_pools. That view has a name column of type sysname, which is what the script selects. Pool-scoped eviction is useful when one workload's plans need to be refreshed and the others should stay warm.

The global form

DBCC FREEPROCCACHE with no argument clears everything. Beyond the recompilation cost already quoted, each cleared cachestore writes an informational message to the error log (the "cachestore flush" message), logged every five minutes while flushes continue. If you see that message and did not run the command, something else cleared the cache. These RECONFIGURE operations also clear the procedure cache: changes to max degree of parallelism, max server memory, min server memory, cost threshold for parallelism, and user options.

Profiling the cache before you flush

The last query groups the cache by objtype and counts plans whose usecounts is 1. usecounts is the number of times the cache object has been looked up, and it is "not incremented when parameterized queries find a plan in the cache." Treat a usecounts of 1 as a hint, not proof, of a plan that was never reused. The sum is cast to bigint because size_in_bytes is an int and a large cache can overflow an int total.

If single-use ad hoc plans dominate, the server option optimize for ad hoc workloads is built for that case. When it is set to 1, "the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan." The stub shows in sys.dm_exec_cached_plans with a cacheobjtype of Compiled Plan Stub. With the option enabled you cannot view execution plans for single-use queries, because only the stub is cached.

Key Benefits and Use Cases

  • Targeted relief for a regressed query — evicting one plan_handle makes the next execution compile a new plan while the rest of the cache stays warm.
  • No restart — the plan cache is cleared in place, with no service interruption.
  • Workload-scoped clearing — the pool form limits eviction to one Resource Governor pool.
  • Diagnosis and fix with the same views — sys.dm_exec_cached_plans and sys.dm_exec_sql_text find the plan, then the handle feeds the command.
  • Database-level alternative — from SQL Server 2016, ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE clears the plan cache for the current database, and its syntax also accepts an optional plan_handle.
  • Time to apply a lasting fix — eviction buys the minutes needed to pick a hint or a forced plan.

Performance Considerations

  • Recompilation burst after a global clear: every query compiles again on its next execution, so expect a temporary drop in performance until the cache refills.
  • Plan handles expire: a handle is valid only while the plan stays in cache, so a handle copied from an earlier session may no longer match anything.
  • Natively compiled procedures are different: DBCC FREEPROCCACHE does not clear execution statistics for natively compiled stored procedures, because the procedure cache holds nothing about them.
  • Encrypted text hides plans: sys.dm_exec_sql_text returns NULL text for encrypted objects, so text-based searches skip them.
  • Eviction does not change the cause: the next compile uses the next set of parameter values it sees, so the regression can come back.

Practical Tips

  • Evict the one handle first — locate it with the query above and pass it to DBCC FREEPROCCACHE; reserve the no-argument form for a development instance.
  • Mark an object for recompile instead — sys.sp_recompile drops the existing plan and forces a new one the next time the procedure, trigger or function runs. Given a table or view name, it marks every procedure, trigger and function that references it. It needs ALTER permission on the object. Proactive use is usually unnecessary because SQL Server recompiles automatically when advantageous.
  • Fix the statement, not the cache — the RECOMPILE query hint generates a temporary plan and discards it after the query runs, so it does not replace a plan stored in cache. The OPTIMIZE FOR hint tells the optimizer to compile with a particular value for a variable. Recompiling a procedure on every execution is "one of the less efficient ways" to deal with parameterization problems.
  • Check the version for built-in help — Parameter Sensitive Plan optimization, introduced in SQL Server 2022, attempts to mitigate parameter-dependent plan problems automatically. On earlier versions, the options are the WITH RECOMPILE option, a plan guide, or a Query Store hint.
  • Pin a good plan in Query Store — sp_query_store_force_plan takes a query_id and plan_id; the optimizer then tries to use that plan each time the query runs, and if forcing fails it optimizes normally. It applies to SQL Server 2016 and later.
  • Record the incident — if the same plan needs clearing twice, move to one of the lasting fixes above instead of scheduling a flush.

Conclusion

DBCC FREEPROCCACHE is the quick fix for one stale or badly shaped plan when you aim it at that plan. Find the handle in sys.dm_exec_cached_plans, evict only that entry, and then replace the stopgap with a hint, sys.sp_recompile, or a Query Store forced plan. For the rest of the family of commands, see the complete DBCC command reference.

References

Posts in this series