One procedure picks a bad plan and the whole application slows. Removing a bad plan from cache can give the next execution a fresh compile, but clearing every cached plan is an expensive way to help one statement. Identify the exact handle first.
Confirm a Bad Plan From Cache Is the Real Problem
A slow execution is not automatically a bad plan from cache. Check blocking, waits, row counts, and parameter values. Query Store can show whether the same query used a faster plan earlier. If it did, compare the two plans and the data change between them. I save the plan XML and the runtime numbers before removing anything. Once a cached plan is gone, its DMV row is gone too.
The quick command DBCC FREEPROCCACHE without an argument flushes the plan cache across the instance. That causes unrelated queries to compile again and can create a CPU spike. It also destroys the evidence you wanted to inspect. What is the smallest thing you need to recompile?
Find the Procedure's Handle
For a stored procedure that has finished executing and remains cached, sys.dm_exec_procedure_stats exposes its plan_handle, cached time, execution count, and recent elapsed time. Run the query in the affected database and replace the procedure name. A procedure can have more than one cached plan under different SET options or contexts, so inspect every returned row.
SELECT ps.plan_handle, ps.cached_time,
ps.last_execution_time, ps.execution_count,
ps.last_elapsed_time, ps.total_elapsed_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id = DB_ID()
AND ps.object_id = OBJECT_ID(N'dbo.YourProcedure')
ORDER BY ps.last_execution_time DESC;The handle is transient. It identifies a cached plan only while that plan remains in memory. If the query returns no row, the procedure can have been evicted, not yet executed, or be in a different database. A natively compiled procedure has special handle behavior. Use Query Store for durable plan history and the cache DMV for the current target.
Remove a Bad Plan From Cache by Handle
Copy the handle from the row you inspected and use it in DBCC FREEPROCCACHE with parentheses. The example below uses a placeholder and a guard so it cannot silently run without a real handle. As written, it stops with error 50001 until you paste a real handle. Run it in a controlled window because the next execution will compile again. A plan handle can represent a batch or procedure with multiple statements, so this is narrower than an instance flush but not necessarily a single statement.
DECLARE @plan_handle varbinary(64) = 0x0;
IF @plan_handle IS NULL OR DATALENGTH(@plan_handle) <= 1
THROW 50001, 'Replace the placeholder plan handle first.', 1;
DBCC FREEPROCCACHE (@plan_handle) WITH NO_INFOMSGS;The guard does not verify that the chosen handle belongs to the intended procedure. Re-run the inventory immediately before clearing and match the database, object, and cached time. I also record who requested the action and why. This is a temporary intervention. If the same inputs produce the same poor estimate, the bad plan can return on the next compile.
Compare sp_recompile
sp_recompile marks a stored procedure for recompilation at its next execution. It is useful when you know the module but do not need to fetch a plan handle. It does not immediately execute the procedure and test the result. Recompiling a table or view can affect related plans more broadly, so target the procedure when possible.
EXEC sys.sp_recompile N'dbo.YourProcedure';This call changes metadata state and leads to future compilation work. Treat it as a scoped operational action, not as a permanent tuning strategy. If a deployment changes the procedure or its indexes, the next compile can naturally produce a new plan. Recompiling after every slow call hides the root cause and spends CPU on repeated compilation.
Know the Database-Wide Choice
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE clears cached plans for the current database. It is less broad than an instance-wide flush, but much broader than one handle. Use it when a database-wide change makes many plans suspect, not for one procedure. The database context matters, so verify DB_NAME() before the command.
SELECT DB_NAME() AS current_database;
ALTER DATABASE SCOPED CONFIGURATION
CLEAR PROCEDURE_CACHE;Do not paste that block into production as a routine fix. It can trigger a compile surge for the whole database. If you need to compare compile behavior in a test copy, do so in a quiet window and watch CPU. The hierarchy is simple: one handle, one procedure marked for recompile, one database cache, and finally the whole instance. Choose by actual scope.
Stop the Bad Plan From Returning to Cache
A fresh plan can be bad for the next parameter value. Check statistics freshness, data skew, implicit conversions, index changes, and parameter-sensitive behavior. If Query Store has a stable good plan, a temporary force can protect the workload while you correct the underlying issue. A plan force needs its own verification and review date. It can fail after a schema change or become wrong when data moves.
I compare representative small and large parameter values before calling the problem fixed. Record elapsed time, CPU, logical reads, and row estimates for both. If one cached plan cannot serve both shapes, consider a targeted query rewrite, parameter sensitive plan features, or a narrowly placed recompile hint. An index change can solve an access-path problem, but an index created only for one unlucky parameter can slow writes all day.
Check the Cache After the Bad Plan Is Gone
After the intervention, run the affected procedure under the same parameters and SET options as the application. Check that a new cached time and plan appear, then compare Query Store runtime intervals. One fast call is not enough. Watch several executions and both common parameter shapes. If the bad plan returns, preserve the new evidence before another flush.
Compare the new plan handle with the old one, and check whether the same statement received a different plan ID in Query Store.
A cache clear has no memory of why the old plan was poor. Your investigation supplies that memory. Keep the plan IDs, handle, affected query, and measured result in the incident note. The next DBA should know whether the action was a bridge to a fix or merely a restart of the same cycle.
Related reading on this blog: Cleanup Plan Cache For a Single Database and Parameter Sniffing and Bad Plan.
Clearing one plan is not a lasting repair, it is a fresh chance after the cause is addressed.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
