Parameter sniffing

In one line: SQL Server compiles a stored procedure’s plan for the parameter values of the first call, then reuses that plan for every value after it. When the data is skewed, the plan that suits one value is wrong for another, and the failure comes in two forms: a wrong plan shape and a wrong memory grant.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/parameter-sniffing on GitHub.

What you’ll see

  • A procedure that’s fast for most customers and painfully slow for one big one, or the other way round, depending on who called it first after a restart or recompile.
  • Wrong shape: a plan built for a few hundred rows (an index seek plus key lookups) reused for 500,000. In the demo that meant 1.5 million logical reads and a sort that spilled to tempdb.
  • Wrong grant: a plan built for the big customer reserves hundreds of MB of memory to return a handful of rows. Nothing is slow, so a duration-based monitor never notices, but other queries can end up waiting on RESOURCE_SEMAPHORE.

Why it happens

The optimizer estimates row counts from the parameter value it sees at compile time (the “sniffed” value, shown as ParameterCompiledValue in the plan). Both the plan shape and the memory grant come from that estimate. With uniform data any value gives about the same plan. With one customer holding half a million rows and the rest a few hundred, it doesn’t.

What to do

  • OPTION (RECOMPILE) on the statement gets the right shape and the right grant every time. You pay with a compile on every call, and nothing builds up for memory grant feedback to learn from.
  • Memory grant feedback (2019+ row mode, compatibility level 150) fixes the grant over repeated executions. In the demo it went from 1 MB to 250 MB through Yes: Adjusting to Yes: Stable. It never fixes the plan shape, which stayed identical on every run.
  • On SQL Server 2022, the learned grant outlives a recompile. Memory grant feedback is persisted in Query Store, so a freshly compiled plan can arrive carrying a grant learned from other parameter values. In the demo, the same recompile came back with 1 MB with persistence off and 250 MB with it on.
  • Parameter Sensitive Plan optimization (2022+) can cache more than one plan for different value ranges, but only for queries that qualify. On the tested run, this query did not, and PSP didn’t engage.

What doesn’t help

  • DBCC FREEPROCCACHE. It throws away every plan on the instance to reset one procedure. Use sp_recompile on the procedure instead.
  • Assuming a recompile resets everything. On 2022 with Query Store, the persisted grant comes straight back.

Check your server

-- Cached statements whose row counts and memory grants vary widely between executions.
SELECT TOP (20)
       qs.execution_count,
       qs.min_rows, qs.max_rows,
       qs.min_grant_kb, qs.max_grant_kb,
       qs.min_spills, qs.max_spills,
       SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
                 (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                       ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 1
  AND qs.max_rows > 100 * (qs.min_rows + 1)
ORDER BY qs.max_grant_kb - qs.min_grant_kb DESC;

See it happen

The parameter-sniffing demo runs six scenarios on WideWorldImporters:

  1. sniff small, run big
  2. sniff big, run small
  3. memory grant feedback
  4. RECOMPILE
  5. PSP
  6. feedback persisted across a recompile

It records the shape, grant and spills of every execution.

Related: memory grants sized by declared width · catch-all queries · slow in the app, fast in SSMS · SQL Server 2022 query processing

Measured on SQL Server 2025 (17.0.4055.5) against WideWorldImporters, plus partial checks on SQL Server 2022. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights