skip to content

In SQL Server, a stored procedure is fast for one customer and slow for another; how do you confirm parameter sniffing and choose a fix?

level: seniorimportance: must knowfreq 74%

answer

  1. one cached plan, two very different customers
  2. compiled value versus runtime value
  3. RECOMPILE, OPTIMIZE FOR, forcing, PSP
  4. dispatcher plan and query variants

basics

~20 s

Compare the value the cached plan was compiled for with the slow customer's value and its actual rows. Then fix per statement: OPTION (RECOMPILE), OPTIMIZE FOR, a forced Query Store plan or hint, or PSP optimization from SQL Server 2022.

solid answer

~40 s

I first prove it is sniffing: the plan's parameter list shows the value it was compiled for, and running the slow customer's value with `OPTION (RECOMPILE)` shows a different plan that is fast. Query Store or `sys.dm_exec_query_stats` (`min_rows`/`max_rows`, elapsed times) shows the same statement swinging between outcomes. Then I pick the narrowest fix. `OPTION (RECOMPILE)` on the one statement gives every call its own plan at a compile cost per execution. `OPTIMIZE FOR (@CustomerId = <value>)` pins a plan to a representative value, and `OPTIMIZE FOR UNKNOWN` plans for average selectivity. Without a code change I can force a known good plan with `sys.sp_query_store_force_plan` or attach a hint with `sys.sp_query_store_set_hints` (2022). From SQL Server 2022 at compatibility level 160, Parameter Sensitive Plan optimization can keep several plans for one equality predicate automatically.

code

sql · 10 lines
sql
CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.OrderId, o.OrderDate, o.Total
    FROM dbo.Orders AS o
    WHERE o.CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;

go deeper

for a junior

Recall that SQL Server builds a cached plan from the first parameter values it sees and reuses it for later calls.

for a middle

Explain how to read the compiled value in a plan and what OPTION (RECOMPILE) and OPTIMIZE FOR each change.

for a senior

Prove sniffing with plans and Query Store history, then choose between statement hints, forcing, Query Store hints and PSP by call rate and risk.

for a principal

Decide team rules for when hints in code are acceptable versus Query Store fixes, and who reviews forced plans after schema changes.

## Why one customer is slow When SQL Server compiles a parameterized statement, including a statement inside a stored procedure, it **sniffs** the parameter values of that first call and builds a plan for them. The plan is cached and reused for later calls with any value. If `CustomerId = 17` owns a few hundred rows and `CustomerId = 3` owns millions, a plan built for 17 (an index seek plus lookups) is a disaster for 3, and a plan built for 3 (a scan with a hash join) is wasteful for 17. Which customer suffers depends on which value compiled the plan after the last recompile, eviction or restart. The general theory of plan reuse and sniffing is covered in the material on plan caching; here the job is SQL Server's evidence and fixes. ## Confirming it in SQL Server 1. Get the cached plan and read its parameter list, which records the **compiled value** for each parameter. In an actual plan the runtime value appears beside it. 2. Run the slow customer's call with `OPTION (RECOMPILE)` added to the statement in a test copy. If it gets a different plan and runs fast, the plan, not the data or the server, is the problem. 3. Look at the statement's history. `sys.dm_exec_query_stats` keeps `min_rows`, `max_rows` and elapsed-time columns per cached plan, and Query Store shows whether the same `query_id` has had several plans with very different durations. 4. Check the estimate against actual rows for the slow value. An estimate that matches the compiled customer and not this one is the signature. Evicting the single plan with `DBCC FREEPROCCACHE (plan_handle)` is a mitigation, not a fix: the next call sniffs whatever value comes first, and the problem returns. ## The fixes and what they cost | Fix | What it does | Cost or risk | | --- | --- | --- | | `OPTION (RECOMPILE)` on the statement | A fresh, temporary plan for each execution using the current values | Compile CPU on every call; the plan is not reused | | `OPTIMIZE FOR (@p = literal)` | One cached plan built for a value you choose | Customers unlike that value still get a poor plan | | `OPTIMIZE FOR UNKNOWN` | One plan built from average selectivity across all values | Stable but rarely ideal for the extremes | | Query Store plan forcing | `sys.sp_query_store_force_plan` pins a known good plan without a code change | Forcing can fail, and a forced plan hides later improvements | | Query Store hints (SQL Server 2022) | `sys.sp_query_store_set_hints` attaches, for example, `OPTION (RECOMPILE)` to a `query_id` | Same trade-off as the hint itself | | PSP optimization (SQL Server 2022, level 160) | Several cached plans for one statement, chosen by runtime cardinality | Equality predicates only; more plans in the cache and Query Store | Choose the narrowest one that fits. A statement that runs a few times a minute can afford `RECOMPILE`; one that runs thousands of times a second usually cannot. A procedure-wide `WITH RECOMPILE` is the procedure-syntax lesson's subject and is coarser than the statement hint. ## Parameter Sensitive Plan optimization From **SQL Server 2022** at **compatibility level 160**, the optimizer can look at the histograms during the first compilation and pick the most at-risk parameterized predicates, up to three. For an eligible statement it caches a **dispatcher plan**. At run time the dispatcher evaluates each selected predicate's cardinality for the actual parameter values, places it in one of three ranges (think low, medium, high), and routes the call to a **query variant** with its own plan. - It works only with **equality** predicates such as `CustomerId = @CustomerId`. - Variants show up in the plan XML with a `PLAN PER VALUE` hint and in Query Store through `sys.query_store_query_variant`. - Turn it off per database with `ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF`, or per query with the `DISABLE_PARAMETER_SENSITIVE_PLAN` hint. - Disabling parameter sniffing (trace flag 4136, the `PARAMETER_SNIFFING` configuration or `USE HINT('DISABLE_PARAMETER_SNIFFING')`) also disables PSP. - **SQL Server 2025** at compatibility level 170 extends it to `INSERT`, `UPDATE`, `DELETE` and `MERGE` and handles several eligible predicates on one table better. PSP is not a guarantee. A skew that sits on a range predicate, or on two columns that only together are selective, still needs one of the manual fixes. ## When forcing goes wrong A plan forced through Query Store is a request, not a guarantee. On recompilation the optimizer tries to produce the forced plan; if it cannot, for example because an index the plan uses was dropped, it optimizes normally and `sys.query_store_plan` increments `force_failure_count` and records `last_force_failure_reason_desc` (such as `NO_INDEX`). A forced plan that keeps failing is silently not protecting you, so review forced plans after schema changes.

  • Why not put OPTION (RECOMPILE) on every statement and stop worrying about sniffing?
    Every execution then pays a full compile in CPU, which on a statement called thousands of times a second can cost more than the bad plan did. Use it where calls are infrequent relative to their cost, and prefer PSP, `OPTIMIZE FOR` or forcing for hot statements.
  • PSP optimization is enabled at level 160, yet the slow customer still gets the wrong plan. Why might that be?
    PSP only considers equality predicates, at most three per statement, and picks them from histogram skew at compile time. The skew may sit on a range predicate, an optional filter or a column combination it cannot see. Parameter sniffing may also be disabled for that workload, which disables PSP too. Fall back to a statement hint or Query Store forcing.

A tailor who cuts one pattern from the first customer's measurements and then sells that pattern to everyone. It fits the first customer; the rest get it whether it fits or not. OPTION (RECOMPILE) measures every customer, OPTIMIZE FOR UNKNOWN cuts for the average body, and PSP optimization keeps a small, a medium and a large pattern and picks one by the customer's size.

  • Says OPTION (RECOMPILE) is free because compilation is cheap.
  • Clears the whole plan cache and calls the problem fixed.
  • Thinks PSP optimization handles range predicates as well as equality.
  • Believes a forced Query Store plan is always used exactly as captured.
  • Thinks OPTIMIZE FOR UNKNOWN gives each customer its own best plan.

Assumes SQL Server 2025; PSP optimization needs SQL Server 2022 at level 160, and level 170 in 2025 extends it to data-modification statements.