Plan Caching and Parameterization

October 18, 2026

Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

SQL Server compiles a plan the first time it sees a statement and keeps it in the plan cache to reuse. Whether a statement can reuse a plan depends on how it is sent. Too little reuse wastes CPU and memory on compilations; reuse of the wrong plan gives parameter sniffing problems.

How statements are sent, and what gets cached

Ad hoc SQL with literal values: a separate plan for every different value, unless SQL Server can parameterize it.
Simple (auto) parameterization: SQL Server replaces literals with parameters itself, but only for very simple statements (single table, no joins, no IN lists, no subqueries in most cases).
EXEC of a dynamic string: treated as ad hoc SQL, with the same reuse problems, and open to SQL injection if values are concatenated in.
sp_executesql with parameters: one plan reused for every value, and safe from injection.
Stored procedures: one plan per procedure (per statement inside it), reused for every call.

Seeing it in the cache

-- Literal values: one plan per value
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = 101 AND Amount > 0;
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = 102 AND Amount > 0;
GO

-- Parameterized: one plan reused
EXEC sp_executesql N'SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @c AND Amount > 0',
                   N'@c int', @c = 101;
EXEC sp_executesql N'SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @c AND Amount > 0',
                   N'@c int', @c = 102;
GO

SELECT cp.objtype, cp.usecounts, cp.size_in_bytes / 1024 AS size_kb, LEFT(st.text, 120) AS sql_text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE N'%FROM dbo.Orders WHERE CustomerID%' AND st.text NOT LIKE N'%dm_exec_cached_plans%'
ORDER BY cp.usecounts DESC;

Run it with the GO separators, as shown: an ad hoc plan’s text is the whole batch it was sent in. The literal batch shows as an Adhoc plan used once, and each new set of literal values would add another. The sp_executesql statement shows as one Prepared plan with usecounts 2.

Finding a cache full of single-use plans

SELECT objtype,
       COUNT(*) AS plans,
       SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plans,
       SUM(CAST(size_in_bytes AS bigint)) / 1024 / 1024 AS size_mb
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY size_mb DESC;

If most of the Adhoc space is single-use plans, turn on optimize for ad hoc workloads. SQL Server then stores only a small stub the first time a statement is seen, and the full plan only if it runs again. It is safe to turn on for almost every server:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1; RECONFIGURE;

For an application that sends many similar statements with literals that simple parameterization will not handle, forced parameterization at database level makes SQL Server parameterize almost all of them. It increases plan reuse and also increases the risk of parameter sniffing, so test it first:

ALTER DATABASE [YourDB] SET PARAMETERIZATION FORCED;
-- back to the default: ALTER DATABASE [YourDB] SET PARAMETERIZATION SIMPLE;

Literals, variables and parameters

The same query can get three different estimates depending on how the value is supplied:

A literal: SQL Server knows the value and uses the statistics histogram for it.
A parameter (stored procedure or sp_executesql): SQL Server uses the value from the first execution when it compiles (parameter sniffing) and reuses that plan for every later value.
A local variable: the value is not known at compile time, so SQL Server uses the average density from the statistics. This gives a middle-of-the-road estimate, often wrong for both very common and very rare values.

DECLARE @c int = 42;
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @c;                     -- average density estimate
SELECT COUNT(*) FROM dbo.Orders WHERE CustomerID = @c OPTION (RECOMPILE);  -- uses the actual value

On a 200,000-row test table where one customer had about a third of the orders, the local variable gave an estimate of 38 rows against 63,869 actual; with OPTION (RECOMPILE) the estimate was exact.

The life of a plan

A plan leaves the cache when memory is needed (the least used plans go first), when the statistics on a table it uses are updated, when the table or index definition changes, when the database is restored, detached or set offline, or when someone runs DBCC FREEPROCCACHE or ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE. A sudden change of plan after an index rebuild or a statistics update is usually a new plan being compiled with different parameter values, not the rebuild itself.

Removing one bad plan

Never clear the whole cache to fix one query. Remove just its plan:

SELECT cp.plan_handle, cp.usecounts, LEFT(st.text, 120) AS sql_text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE st.text LIKE N'%FROM dbo.Orders WHERE CustomerID = @c%' AND st.text NOT LIKE N'%dm_exec_cached_plans%';

-- then, with the plan_handle from above:
-- DBCC FREEPROCCACHE (0x06000500...);

For a stored procedure, sp_recompile N’dbo.ProcedureName’ marks it to be recompiled on its next run.

From my SQL Server DBA handbook.

Performance

Clive Roberts