Clear Procedure Cache for One Database in SQL Server

You can clear procedure cache for one database with a single statement: ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE. It removes the cached plans of the database you’re connected to and leaves every other database alone. Older versions only offered a flush of the whole instance.

Why Clearing Is Not the Answer

A cached plan saves SQL Server the work of compiling a query again. Clearing the cache throws that work away. Every query then compiles on its next run, the CPU rises for a while, and the first runs are slower.

It’s still the right move in three cases. One is to measure how a procedure behaves from a cold start. Another is a plan built for unusual parameter values that hurts a database, where you want a fresh plan. A third is a test that needs a clean slate. In each case, clear as little as you can.

The Old Way and the New Way

Before SQL Server 2017, the options were blunt. DBCC FREEPROCCACHE with no arguments clears the plans of the whole instance, for every database. With a plan handle, it clears one plan. A third command, DBCC FLUSHPROCINDB, targets one database, but it’s undocumented, so don’t build anything on it.

The newer statement is documented and works at the database level. It runs in the context of the current database, so the USE statement before it matters.

Build the Demo

The demo creates two databases, ClearCacheDemo and ClearCacheOtherDemo. The first holds two small procedures, and the second holds one. Each procedure runs once, so each gets a cached plan. Run the scripts on a test server only.

IF DB_ID(N'ClearCacheDemo') IS NULL CREATE DATABASE ClearCacheDemo;
IF DB_ID(N'ClearCacheOtherDemo') IS NULL CREATE DATABASE ClearCacheOtherDemo;
GO
USE ClearCacheDemo;
GO
CREATE OR ALTER PROCEDURE dbo.ListMenu AS DECLARE @c int; SELECT @c = COUNT(*) FROM sys.objects WHERE type = 'U';
GO
CREATE OR ALTER PROCEDURE dbo.CountMenu AS DECLARE @c int; SELECT @c = COUNT(*) FROM sys.objects;
GO
USE ClearCacheOtherDemo;
GO
CREATE OR ALTER PROCEDURE dbo.ListOrders AS DECLARE @c int; SELECT @c = COUNT(*) FROM sys.objects WHERE type = 'U';
GO
USE ClearCacheDemo;
GO
EXEC dbo.ListMenu;
EXEC dbo.CountMenu;
EXEC ClearCacheOtherDemo.dbo.ListOrders;

Now count the cached procedure plans for the two databases. The view sys.dm_exec_procedure_stats has one row for each cached procedure plan.

USE master;
GO
SELECT DB_NAME(database_id) AS DatabaseName, COUNT(*) AS ProcedurePlans
FROM sys.dm_exec_procedure_stats
WHERE database_id IN (DB_ID(N'ClearCacheDemo'), DB_ID(N'ClearCacheOtherDemo'))
GROUP BY database_id
ORDER BY DatabaseName;

Remove One Plan

When a single procedure has the bad plan, remove only that plan. To clear procedure cache for one procedure, pass its plan handle. The statement accepts the plan handle at the end. The plan handle is a binary value. The statement can’t take it from a variable, so the script builds the statement as text and runs it.

USE ClearCacheDemo;
GO
DECLARE @plan varbinary(64) = (SELECT plan_handle FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID() AND object_id = OBJECT_ID(N'dbo.ListMenu'));
DECLARE @sql nvarchar(200) = N'ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ' + CONVERT(nvarchar(130), @plan, 1) + N';';
EXEC (@sql);

Look at the view again. Only the plan of dbo.ListMenu is gone.

USE master;
GO
SELECT DB_NAME(database_id) AS DatabaseName, OBJECT_NAME(object_id, database_id) AS ProcedureName
FROM sys.dm_exec_procedure_stats
WHERE database_id IN (DB_ID(N'ClearCacheDemo'), DB_ID(N'ClearCacheOtherDemo'))
ORDER BY DatabaseName, ProcedureName;

The test ran on SQL Server 2025, which accepts the plan handle. On a version that rejects it, use sp_recompile instead. It marks the procedure for a new plan on its next run.

Clear the Whole Database

Now clear everything in the first database. Run the statement inside that database.

USE ClearCacheDemo;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
USE master;
GO
SELECT DB_NAME(database_id) AS DatabaseName, COUNT(*) AS ProcedurePlans
FROM sys.dm_exec_procedure_stats
WHERE database_id IN (DB_ID(N'ClearCacheDemo'), DB_ID(N'ClearCacheOtherDemo'))
GROUP BY database_id
ORDER BY DatabaseName;

ClearCacheDemo has no plans left, and ClearCacheOtherDemo kept its one. That’s the point of the database-level command. The rest of the instance never noticed.

What Happens Next

The cache refills as queries run. The first run of a procedure compiles a new plan. The view then shows a fresh row with an execution count of 1. Run one of the cleared procedures to see it.

EXEC ClearCacheDemo.dbo.ListMenu;
SELECT OBJECT_NAME(object_id, database_id) AS ProcedureName, execution_count AS ExecutionCount
FROM sys.dm_exec_procedure_stats
WHERE database_id = DB_ID(N'ClearCacheDemo');

The compile happened on that run, and that is the cost of every clear. On a busy server, thousands of plans compile in the same few minutes.

Is Clearing Ever Safe?

You could argue that clearing a cache is a bad habit. In my work, I say no when a team asks for it. A cleared cache hides the symptom of a plan problem and doesn’t explain it. The same plan returns when the same parameter values arrive.

A fair use is a short, planned test on a non-production copy. On production, ask who owns the database first. Every busy query compiling at once raises the CPU for a while.

The statement also needs the ALTER ANY DATABASE SCOPED CONFIGURATION permission. A login that lacks it gets a permission error.

Before you clear, write down why. A clear that fixes a slow procedure points to a plan problem, such as parameter sniffing. Capture the bad plan first, with its parameter values. Then the real cause can be fixed after the clear hides it.

What to Remember

Clear procedure cache one database at a time, not for the whole instance. Remove a single plan when only one procedure is the problem. Prefer sp_recompile when you only want a new plan on the next run. Remove the demo databases when you finish.

USE master;
GO
ALTER DATABASE ClearCacheDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ClearCacheDemo;
ALTER DATABASE ClearCacheOtherDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ClearCacheOtherDemo;

A plan cache is not a mistake to erase, it is a memory to prune.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.