Plan Cache, Parameterization and Parameter Sniffing: Symptoms, Diagnosis and Fixes

Version note: The T-SQL (Transact-SQL, Microsoft's dialect of SQL (Structured Query Language)) examples in this post were run on SQL Server 2025 CU10 (17.0.5035.5, compatibility level 170), and the C# examples with .NET 10, Entity Framework Core (EF Core) 10.0.14, Microsoft.Data.SqlClient 7.1.1 and Dapper 2.1.89.

Guess First

The "my orders" list in the customer panel comes from a simple stored procedure:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderNumber, Status, CreatedAt, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY CreatedAt DESC;
END;

Customer 42 has 473 orders, customer 1 (the university library) has 200,000. We clear the plan cache and run the procedure first for 42, then for 1; then we do the same in reverse order. For this experiment I turned off PSP (Parameter Sensitive Plan) optimization, introduced with SQL Server 2022; we will turn it back on shortly. Guess the logical reads of the four executions:

/* A: 42 first, then 1 */
Table 'Orders'. Scan count 1, logical reads 1460
Table 'Orders'. Scan count 1, logical reads 612850
/* B: 1 first, then 42 */
Table 'Orders'. Scan count 11, logical reads 7709
Table 'Orders'. Scan count 11, logical reads 7709

Same procedure, same customer 1: 7,709 logical reads in one order, 612,850 in the other. The difference comes from the value the procedure was first executed with. This is the classic cause of the complaint "the query is sometimes slow, sometimes fast, and a server restart fixes it": parameter sniffing. In this post we examine how the plan cache works, the kinds of parameterization, how to diagnose parameter sniffing, its fixes and the price of each, SQL Server 2022's PSP optimization and 2025's OPPO (Optional Parameter Plan Optimization).

The Plan Cache: Ad Hoc, Prepared and Procedures

We saw it in post one: because compiling is expensive, SQL Server caches the plans it compiles and reuses them when the same query arrives. "The same query" means the text is identical byte for byte. I sent the same join with three different literals, then twice with sp_executesql, and looked at the database's plan cache:

SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId WHERE o.OrderId = 42;
/* the same with 43 and 44 */
SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 42;
SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 43;
EXEC sp_executesql N'SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers AS c
    ON c.CustomerId = o.CustomerId WHERE o.OrderId = @OrderId', N'@OrderId int', 42;  /* and 43 */

objtype  cacheobjtype  usecounts size_in_bytes text
-------- ------------- --------- ------------- ----------------------------------------------------------------------
Adhoc    Compiled Plan 1         73728         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         73728         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         73728         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         16384         SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 42;
Adhoc    Compiled Plan 1         16384         SELECT OrderNumber FROM dbo.Orders WHERE OrderId = 43;
Prepared Compiled Plan 2         49152         (@1 tinyint)SELECT [OrderNumber] FROM [dbo].[Orders] WHERE [OrderId]=@
Prepared Compiled Plan 2         73728         (@OrderId int)SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN

The join sent with literals became three separate ad hoc plans, 72 KB each, each used only once. The simple single-table query, however, was parameterized by SQL Server itself (simple parameterization): a prepared plan with (@1 tinyint) was created and both calls shared it; the ad hoc rows are only 16 KB "shell" entries. Simple parameterization applies only to very simple queries whose plan is safe regardless of the value; it does not kick in when it sees a join, an IN list or a subquery. A query explicitly parameterized with sp_executesql naturally gets a single plan. That is why EF Core and Dapper queries are plan-cache friendly: they always send values as parameters.

Forced Parameterization and Optimize for Ad Hoc Workloads

If you cannot change an old application that generates queries with literals, there are two database-level settings. The first is forced parameterization:

ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
/* the same three join queries, literals 42, 43, 44 */

objtype  cacheobjtype  usecounts size_in_bytes text
-------- ------------- --------- ------------- ----------------------------------------------------------------------
Adhoc    Compiled Plan 1         16384         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         16384         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Adhoc    Compiled Plan 1         16384         SELECT o.OrderNumber, c.Email FROM dbo.Orders AS o JOIN dbo.Customers
Prepared Compiled Plan 3         73728         (@0 int)select o . OrderNumber , c . Email from dbo . Orders as o join

Now the literals were turned into parameters and the three calls shared one plan. The price: because every literal becomes a parameter, more queries are exposed to parameter sniffing; and optimizations that depend on literals, such as filtered index and computed column matching, can be lost. The second setting stores ad hoc plans only as a small "stub" on first execution. At the server level it is known as optimize for ad hoc workloads; to stay off the shared server's settings, I used its database-level equivalent:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;
/* OrderId = 45 twice, OrderId = 46 once */

objtype cacheobjtype       usecounts size_in_bytes
------- ------------------ --------- -------------
Adhoc   Compiled Plan Stub 1         456
Adhoc   Compiled Plan      1         73728

The query that ran once (46) took 456 bytes in the cache instead of 72 KB; the query that arrived a second time (45) was promoted to a full plan. A cheap way to protect plan cache memory in workloads that generate one-off queries.

Parameter Sniffing: Why Does It Happen?

On the first execution of a stored procedure or sp_executesql, the optimizer looks at (sniffs) the parameter's current value and uses the histogram with that value. For customer 42 it estimated 487 rows and built a plan with a seek on IX_Orders_CustomerId plus key lookups: 1,460 logical reads for 473 rows, excellent. But the plan went into the plan cache, and when customer 1 came, the same plan ran: 200,000 key lookups, 612,850 logical reads. In reverse order, a parallel clustered index scan was built for customer 1, and customer 42 also scanned the whole table for 473 rows.

Sniffing is not a bad thing; in most queries it is the reason for the right plan. Compare it with variables: in post four, a DECLAREd variable left the optimizer only the average (10.2 rows). The problem is that when data is skewed, a single plan does not fit every value. Two signs are enough for a diagnosis: in the cached plan of the query, the value at compile time (ParameterCompiledValue) differs from the value at run time (ParameterRuntimeValue), and there is a gulf between the query's min_logical_reads and max_logical_reads in sys.dm_exec_query_stats.

Fixes and the Price of Each

I added different hints to the procedure's query, cleared the cache each time and ran it in the order 42, 1, 42 (PSP off):

SELECT OrderId, OrderNumber, Status, CreatedAt, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
ORDER BY CreatedAt DESC
OPTION (RECOMPILE);   /* or OPTIMIZE FOR (@CustomerId = 42), OPTIMIZE FOR UNKNOWN */

OPTION (RECOMPILE) gave the best plan for each value on every call; the price is compile CPU (Central Processing Unit) on every call and the plan not showing up in the cache. Expensive for a query that runs hundreds of times a second, and often the right fix for a search screen that runs a few times a minute. OPTIMIZE FOR pins the plan for a specific value: small customers always good, the big customer always bad. OPTIMIZE FOR UNKNOWN ignores the value and uses the density vector's average (10.2 rows); on our skewed data, still a disaster for customer 1. These two hints buy "consistency", not correctness. Another option on the application side is to route big customers to a separate procedure or a separate query; if you know your data, that is the most robust fix.

PSP Optimization: One Query, Several Plans

PSP optimization, introduced with SQL Server 2022 (compatibility level 160), was designed for exactly this problem and is on by default at level 160 and above. The last row of the table is its result: the same procedure, without hints, got the right plan on all three calls. We can see how from Query Store:

SELECT qv.query_variant_query_id, qv.parent_query_id, qv.dispatcher_plan_id, p.plan_id,
       SUBSTRING(qt.query_sql_text, CHARINDEX('option (PLAN PER VALUE', qt.query_sql_text), 200) AS variant_hint
FROM sys.query_store_query_variant AS qv
JOIN sys.query_store_query AS q ON q.query_id = qv.query_variant_query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id;

61 57 32 33 option (PLAN PER VALUE(ObjectID = 1653580929, QueryVariantID = 2, predicate_range([sqls_07].[dbo].[Orders].[CustomerId] = @CustomerId, 100.0, 100000.0)))
62 57 32 34 option (PLAN PER VALUE(ObjectID = 1653580929, QueryVariantID = 3, predicate_range([sqls_07].[dbo].[Orders].[CustomerId] = @CustomerId, 100.0, 100000.0)))

<ParameterSensitivePredicate LowBoundary="100" HighBoundary="100000">
  <StatisticsInfo Statistics="[IX_Orders_CustomerId]" Table="[Orders]" ... />

PSP created a dispatcher plan for the CustomerId = @CustomerId predicate, where it saw skew in the histogram. The dispatcher puts the value into one of three buckets based on the parameter's estimate in the histogram: below 100 rows, between 100 and 100,000, and above 100,000. Each bucket compiles its own plan (query variant) with the first value that arrives. Customer 42 (473 rows) fell into the second bucket, customer 1 (200,000 rows) into the third, and two separate plans were created. You also recognize this from the PLAN PER VALUE suffixes we saw in the plans in post three.

You also need to know its limits: PSP looks only at equality predicates and at most three predicates per query; sniffing continues inside a bucket (two customers with 120 and 90,000 rows in the same bucket still share one plan). Even so, it is a serious improvement that comes without code changes for OLTP (Online Transaction Processing) queries on skewed data.

OPPO: The @p IS NULL OR col = @p Pattern

The "catch-all" query often seen in search screens is a special kind of parameter sniffing:

CREATE OR ALTER PROCEDURE dbo.SearchOrders @OrderNumber varchar(20) = NULL, @CustomerId int = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderNumber, CustomerId, Status, TotalAmount
    FROM dbo.Orders
    WHERE (@OrderNumber IS NULL OR OrderNumber = @OrderNumber)
      AND (@CustomerId IS NULL OR CustomerId = @CustomerId);
END;

The problem runs deeper than sniffing: because @OrderNumber can be NULL, a plan that seeks on OrderNumber = @OrderNumber cannot be correct for every value, so with a single plan the optimizer plays safe and scans the index from start to end. SQL Server 2025's OPPO (on by default at compatibility level 170) uses PSP's infrastructure to create separate query variants depending on whether a parameter is NULL, folding @p IS NULL to a constant in each variant. I ran the same two calls with OPPO off and on:

EXEC dbo.SearchOrders @OrderNumber = 'BK-00424242';
EXEC dbo.SearchOrders @CustomerId = 42;

/* OPTIONAL_PARAMETER_OPTIMIZATION = OFF */
Table 'Orders'. Scan count 1, logical reads 3117
Table 'Orders'. Scan count 1, logical reads 3205
/* OPTIONAL_PARAMETER_OPTIMIZATION = ON */
Table 'Orders'. Scan count 0, logical reads 6
Table 'Orders'. Scan count 1, logical reads 3205

option (PLAN PER VALUE(ObjectID = 1685581043, QueryVariantID = 1, optional_predicate(@OrderNumber IS NULL),
        predicate_range([sqls_07].[dbo].[Orders].[CustomerId] = @CustomerId, 100.0, 100000.0)))

Searching by order number dropped from 3,117 to 6 logical reads: the variant has a real seek on OrderNumber. Searching by customer did not change. The variant text shows why: OPPO treated only @OrderNumber as an optional predicate, left the CustomerId condition to PSP's range bucket, and the @CustomerId IS NULL OR ... expression stayed in place; the plan scanned IX_Orders_CustomerId and evaluated the condition on every row. So OPPO may not separate every optional parameter; for queries with several optional parameters, the classic fixes (OPTION (RECOMPILE), or generating dynamic SQL with only the supplied parameters) may still be needed. We will cover safe dynamic SQL in the stored procedure post.

ORM Parameter Lengths and Plan Cache Bloat

The parameter definition is part of the plan cache key. nvarchar(21) and nvarchar(22) are different definitions and mean different plans. The AddWithValue of ActiveX Data Objects for .NET (ADO.NET) takes the length from the value itself:

string[] emails = ["customer7@example.com", "customer42@example.com", "customer4242@example.com"];

foreach (string email in emails)
{
    await using var cmd = new SqlCommand(
        "SELECT CustomerId FROM dbo.Customers WHERE Email = @email /* addwithvalue */", conn);
    cmd.Parameters.AddWithValue("@email", email);
    await cmd.ExecuteScalarAsync();
}

/* EF Core, HasMaxLength(200).IsUnicode(false) in the model */
foreach (string email in emails)
    await ctx.Customers.Where(c => c.Email == email).Select(c => c.CustomerId).SingleAsync();

1x (@email nvarchar(21)
1x (@email nvarchar(22)
1x (@email nvarchar(24)
3x (@email varchar(200)

Three emails, three separate plans with AddWithValue; and since they are nvarchar, the implicit conversion from post six comes on top. EF Core used the HasMaxLength(200) and IsUnicode(false) information in the model, sent varchar(200) on every call, and one plan was used three times. Without a length in the model, EF Core sends nvarchar(4000) or nvarchar(max); Dapper's rule is similar (post 13 of the Dapper series). In a real application, thousands of different lengths mean thousands of plans, which means both memory and compile CPU.

How SQL Server 2019/2022 differ: the plan cache, simple/forced parameterization and sniffing behave the same in all three versions. The database-level OPTIMIZE_FOR_AD_HOC_WORKLOADS has existed since 2019. PSP optimization arrived in 2022 with compatibility level 160; 2019 lacks it. OPPO exists only in 2025 at level 170. According to the edition table, PSP and OPPO are available in every edition, including Standard and Express.

In Production

Diagnose with Query Store. Query Store's "Regressed Queries" report shows that the same query runs at different costs with different plans and which plan arrived when; that is the topic of post eight. The gap between min_logical_reads and max_logical_reads in the plan cache is a quick sign too.

Be wary of the sentences "a restart fixed it" or "an index rebuild fixed it". Both evict the plan from the cache, and the next call compiles with a new value. The problem was not fixed; a lucky value just came first this time.

Do not clear the plan cache wholesale in production. Fix a single bad plan in a targeted way with DBCC FREEPROCCACHE (plan_handle) or by forcing a plan in Query Store.

Common Pitfalls

Mistaking parameter sniffing for server load. With a "the query is sometimes slow" complaint, first look at the value the cached plan was compiled with; CPU or disk load is not always the cause, sometimes it is the result.

"Fixing" it by copying the parameter into a variable. Assigning the parameter to a variable inside the procedure and using that in the query has the same effect as OPTIMIZE FOR UNKNOWN: the estimate falls to the average. On our data, that meant 600,350 logical reads for customer 1.

Adding OPTION (RECOMPILE) to every query. Right for rarely run queries; for frequently run queries, a fix that costs compile CPU and makes diagnosis harder because the plans never appear in the plan cache.

Not writing string lengths into the ORM (Object-Relational Mapping) model. A model missing length and Unicode information leads to both plan cache bloat and implicit conversion.

A Small Experiment

★☆☆ With PSP on, run the procedure for customer 60 (389 orders) and customer 300 (122 orders). Did both use the same variant? Did the number of variants in Query Store change?

★★☆ Read the min_logical_reads and max_logical_reads of the GetCustomerOrders query from sys.dm_exec_query_stats, and find ParameterCompiledValue in the XML (Extensible Markup Language) of the cached plan.

★★★ Turn SearchOrders into parameterized dynamic SQL with sp_executesql that adds a WHERE condition only for supplied parameters. Compare the logical reads of the same two calls and the plans created in the plan cache with the OPPO version.

Most of the diagnosis in this post relied on the plan cache, and the plan cache is volatile: it is cleared on restart, under memory pressure and by rebuilds. In the next post we open up the durable one: finding regressions with Query Store, forcing plans and hinting without changing code.