SQL Server DBCC FREEPROCCACHE: Clear the Plan Cache Safely

A stored procedure that returned in 20 milliseconds yesterday is timing out today, and nothing in the code changed. The data did not grow meaningfully, the indexes are intact, and statistics look current — yet the query is suddenly scanning a million rows it used to seek. The usual culprit is a cached execution plan compiled for the wrong parameter value, and DBCC FREEPROCCACHE is the scalpel that removes it. The danger is using it as a sledgehammer.

Purpose and Overview

Why does a query that ran in milliseconds yesterday suddenly take thirty seconds today, with no code change and no new data? The answer is usually parameter sniffing. The first time a parameterized query runs, SQL Server compiles a plan optimized for that parameter value and caches it for reuse. If that first value was atypical — a customer with three orders rather than three million — the cached plan is shaped for the rare case and performs terribly for everyone who follows.

DBCC FREEPROCCACHE evicts compiled plans from the plan cache, forcing a fresh compile on the next execution. It comes in four scopes, and the difference between them is the difference between a targeted fix and a self-inflicted outage. You can clear the entire cache, a single plan by its plan_handle, all plans for a given sql_handle, or every plan associated with a Resource Governor resource pool. The narrow forms solve a problem; the global form usually creates a bigger one.

Clearing the cache is also rarely the right long-term answer. It treats the symptom — a bad plan is in memory now — without preventing the next bad plan. The closing sections point at the durable fixes (Query Store plan forcing, OPTION (RECOMPILE), sp_recompile) that stop the regression from recurring, but when you need relief in the moment, targeted eviction is the fastest path.

Code Breakdown

The script demonstrates all four scopes. In practice you almost always want form (b) or (c), never (a) on a production instance.

 1-- (a) GLOBAL: clears EVERY plan on the instance. DANGEROUS in production.
 2DBCC FREEPROCCACHE;
 3
 4-- (c) Find the offending plan first, then evict just that one.
 5SELECT TOP (20)
 6       cp.plan_handle,
 7       cp.usecounts,
 8       cp.size_in_bytes,
 9       st.text
10FROM   sys.dm_exec_cached_plans AS cp
11CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
12WHERE  st.text LIKE N'%usp_GetOrdersByCustomer%'
13ORDER BY cp.usecounts DESC;
14
15-- (b) Evict a single plan by the plan_handle returned above.
16DBCC FREEPROCCACHE (0x06000100A27E0C00...);   -- paste the real plan_handle
17
18-- (d) Pool-scoped: clear only plans tied to a Resource Governor pool.
19DBCC FREEPROCCACHE ('PoolName');

The global form (and why to avoid it)

DBCC FREEPROCCACHE with no argument drops every cached plan instance-wide. The next execution of every query then has to compile from scratch, producing a recompile storm: a CPU spike, a burst of latch contention on the cache, and slow responses across every application until the cache repopulates. On a busy OLTP server this is indistinguishable from a brief outage. It belongs in development, not production.

Finding the plan with sys.dm_exec_cached_plans

The query in form (c) joins sys.dm_exec_cached_plans to sys.dm_exec_sql_text with CROSS APPLY. CROSS APPLY is required here rather than a normal join because sys.dm_exec_sql_text is a table-valued function that takes the plan_handle as an argument and returns one row per handle — you are invoking it per row of the outer DMV. Filtering on the statement text isolates the specific procedure's plans and their reuse counts.

Plan-handle eviction

Form (b) passes a single plan_handle to FREEPROCCACHE, removing exactly one plan and leaving the rest of the cache warm. The next run of that one query recompiles — ideally sniffing a representative parameter this time — while nothing else on the instance is disturbed. This is the form to reach for during an incident.

Single-use plan bloat

Before reaching for any eviction, check why the cache is under pressure. A plan cache dominated by ad hoc queries that each ran exactly once wastes memory that could hold reusable plans, and flushing it repeatedly only masks the cause:

1SELECT
2    cp.objtype,
3    COUNT(*)                       AS plan_count,
4    SUM(cp.size_in_bytes) / 1048576 AS cache_mb,
5    SUM(CASE WHEN cp.usecounts = 1 THEN 1 ELSE 0 END) AS single_use
6FROM sys.dm_exec_cached_plans AS cp
7GROUP BY cp.objtype
8ORDER BY cache_mb DESC;

If single_use dominates the Adhoc row, the durable fix is the optimize for ad hoc workloads server setting, which caches a small stub on first execution and only stores the full plan on reuse — not a recurring FREEPROCCACHE.

Targeted Eviction and safer alternatives

Even a single-plan eviction is a temporary fix: the next atypical first-execution can cache another bad plan. The sql_handle-scoped approach lets you clear every plan generated from one batch or procedure when several variants are cached, while leaving the rest of the instance untouched. But the better long-term answers stop the bad plan from returning at all: OPTION (RECOMPILE) on the offending statement (compile every time, no caching, at a small CPU cost), sp_recompile 'dbo.usp_Name' (mark the object to recompile on next use), OPTIMIZE FOR hints (pin compilation to a representative value), and — best of all on modern versions — Query Store, which lets you force a known-good plan so the optimizer cannot drift back to the bad one even across restarts.

Key Benefits and Use Cases

• Immediate relief from a bad plan — evicting the cached plan lets a regressed query recompile and recover in seconds.
• Surgical scope — plan_handle and pool-scoped forms fix one problem without touching the rest of the cache.
• No restart required — you resolve a parameter-sniffing incident without bouncing the instance.
• Diagnostic pairing — combined with sys.dm_exec_cached_plans, it both identifies and clears the offending plan.
• Pool isolation — the Resource Governor form contains the impact to a single workload group.
• Bridge to a permanent fix — buys time while you implement Query Store forcing or a hint.

Performance Considerations

• Global clear = recompile storm: an unscoped FREEPROCCACHE forces every query instance-wide to recompile, spiking CPU sharply.
• Cold-cache latency: immediately after eviction, affected queries pay full compilation cost before steady state returns.
• All sessions feel it: the global form is not isolated to your session or database — it hits the entire instance.
• Prefer single-plan eviction: clearing one plan_handle keeps the rest of the cache hot and the blast radius tiny.
• Query Store is non-destructive: forcing a plan via Query Store fixes the regression without throwing away the cache at all.

Practical Tips

• Diagnose before clearing — confirm the plan is actually the problem via sys.dm_exec_cached_plans rather than clearing blindly.
• Watch for single-use plan bloat — a cache full of usecounts = 1 ad hoc plans is better solved with optimize for ad hoc workloads than repeated flushing.
• Test in non-production — never run a global FREEPROCCACHE against a busy server to "see if it helps."
• Log when and why you cleared — record the incident so a recurring regression gets a permanent fix, not a nightly flush.
• Graduate to Query Store — if you are clearing the same plan repeatedly, force a good one and stop firefighting.

Conclusion

DBCC FREEPROCCACHE is the right tool for an urgent parameter-sniffing regression — provided you scope it to the one plan causing pain rather than nuking the entire cache. Use it to buy time, then make the fix permanent with Query Store or a recompile strategy. For the rest of the toolkit, see the complete DBCC command reference.

References

• DBCC FREEPROCCACHE (Transact-SQL) — the official reference for every scope of the command.
• sys.dm_exec_cached_plans (Transact-SQL) — the DMV used to locate a plan_handle before eviction.
• SQL Server First Responder Kit — sp_BlitzCache for deep plan-cache analysis and bad-plan hunting.