SQL Server Parameter Sniffing: Diagnose and Fix It

A stored procedure runs in milliseconds most of the day and then takes seconds for no visible reason. Restarting the service or clearing the plan cache "fixes" it until it comes back. That pattern is usually parameter sniffing: SQL Server compiles a plan for the first parameter value it sees and reuses it for every later value, including ones that need a completely different plan.

Every number here comes from a lab on SQL Server 2022 CU27 (16.0.4295.3) in Docker, with generated data. It is not a client system.

The lab

An Orders table has 600,000 rows. Customer 1 owns 300,000 of them; every other customer owns about two. A nonclustered index on CustomerId does not include the columns the procedure aggregates:

CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId);

CREATE PROCEDURE dbo.CustomerSummary @CustomerId int AS
SELECT COUNT(*) AS Orders, SUM(Total) AS Revenue, MAX(CreatedAt) AS LastOrder
FROM dbo.Orders
WHERE CustomerId = @CustomerId;

At database compatibility level 150, the plan depends entirely on which customer runs first after the plan cache is cleared:

Neither plan is wrong for the value it was compiled for. Reusing it for the other value is the problem: the large customer pays 48 times more reads than necessary, and the small customer pays 2,000 times more.

Find it in Query Store

The signature is one plan with a very wide spread between its fastest and slowest executions. Query Store keeps minimum and maximum duration and reads per plan and interval:

SELECT q.query_id, p.plan_id,
       SUM(rs.count_executions)                          AS executions,
       CAST(MIN(rs.min_duration) / 1000.0 AS decimal(10,1)) AS min_ms,
       CAST(MAX(rs.max_duration) / 1000.0 AS decimal(10,1)) AS max_ms,
       MIN(rs.min_logical_io_reads)                      AS min_reads,
       MAX(rs.max_logical_io_reads)                      AS max_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE q.object_id = OBJECT_ID('dbo.CustomerSummary')
GROUP BY q.query_id, p.plan_id
ORDER BY max_ms DESC;

In the lab, the seek plan showed a minimum of 1.2 ms and 9 reads, and a maximum of 308.6 ms and 900,524 reads. A spread of five orders of magnitude on the same plan is not noise. To see the value a cached plan was compiled for, read ParameterCompiledValue from the plan XML (run with QUOTED_IDENTIFIER ON, or the XML methods fail):

SELECT x.p.value('@Column', 'sysname')                       AS parameter,
       x.p.value('@ParameterCompiledValue', 'nvarchar(100)')  AS compiled_value
FROM sys.dm_exec_procedure_stats AS ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('declare namespace sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
  //sp:ParameterList/sp:ColumnReference') AS x(p)
WHERE ps.object_id = OBJECT_ID('dbo.CustomerSummary');

Then compare the compiled value's row count with the values that run slowly. Check the column's skew directly: SELECT TOP (20) CustomerId, COUNT(*) FROM dbo.Orders GROUP BY CustomerId ORDER BY COUNT(*) DESC; To read the two actual plans node by node, save each as .sqlplan and open it in PlanReader, which flags where estimated and actual rows diverge.

Fixes, measured

Covering index: remove the reason for two plans

CREATE INDEX IX_Orders_CustomerId ON dbo.Orders (CustomerId)
  INCLUDE (Total, CreatedAt)
  WITH (DROP_EXISTING = ON, ONLINE = ON);  -- ONLINE needs an edition that supports it

The two plans existed because the seek needed a key lookup per row. With the aggregated columns in the index, a seek is cheap for both customers, and the result no longer depends on which value compiled first: 3–4 reads for the small customer and 1,155 for the large one, in either order. When a query has a narrow, stable column list, this is usually the most durable fix. It does not help when the large value genuinely needs a different join strategy.

Parameter Sensitive Plan optimization

At compatibility level 160, SQL Server 2022 can cache a dispatcher plan with several query variants for a skewed equality predicate. After switching the lab database to 160, Query Store showed a dispatcher plan and separate variants for the small and large cardinality ranges, and the reads were 9, 18,606 and 9 for small, large and small again. PSP currently works only with equality predicates and evaluates up to three predicates.

Verify rather than assume. In the lab, the first executions after switching compatibility level still read 900,524 pages for the large customer; the variants appeared on a later run. Check for variants with sys.query_store_query_variant and sys.query_store_plan.plan_type_desc, and use the query_with_parameter_sensitivity and parameter_sensitive_plan_optimization_skipped_reason Extended Events to see whether a query qualified. Raising the compatibility level also changes cardinality estimation and other behavior, so test it as an upgrade, not as a one-query fix.

OPTION (RECOMPILE)

Recompiling on every execution gives each call a plan for its own value. In the lab, that cost 2–3 ms of compile CPU per call. For a report that runs a few times an hour, that is a good trade. For a procedure called thousands of times a second, it moves the problem to CPU.

OPTIMIZE FOR UNKNOWN and plan forcing

OPTIMIZE FOR UNKNOWN plans for the average density of the column. With 150,000 customers averaging about four rows, the average looks like the small case, so the large customer still received the seek plan and read 900,524 pages. Forcing one plan through Query Store (sp_query_store_force_plan) gives predictability, but every value then pays for that plan: forcing the scan plan would make each small-customer call read 18,606 pages instead of 9.

Choosing and validating the fix

• Evidence first: confirm a single plan with a wide min/max spread, the compiled value, and the skew in the data. A slow query with one consistent plan is a different problem.
• Risk: indexes add write and storage cost; compatibility-level changes affect the whole database; RECOMPILE adds CPU per call; forcing hides future better plans.
• Rollback: drop or revert the index; set the previous compatibility level; remove the hint; sp_query_store_unforce_plan.
• Validation: compare Query Store min, max and average duration and reads for the statement over a comparable period, for both small and large parameter values.

The same failure exists in PostgreSQL as generic-plan regressions; see the PostgreSQL generic-plan lab. For more SQL Server diagnostics, including blocking chains and implicit conversions, see the SQL Server performance review.