Parameter sniffing is SQL Server doing exactly what it’s supposed to do: compiling a query plan based on the actual parameter value passed in the first time it runs, then reusing that plan for later calls. That’s normally the right behavior, a plan built for the real value is usually better than a generic one. The problem shows up when the data behind a parameterized query is skewed, one value matches a handful of rows, another matches most of the table, and whichever value happened to compile the plan first determines how every later call performs, regardless of what it’s actually being asked to do.
This post reproduces it directly: a table with deliberately skewed data, a stored procedure, and the exact estimated-vs-actual row counts SQL Server produced.
The Setup
A table with two categories, wildly uneven in size, and an index that covers the query:
CREATE TABLE dbo.SniffTest (id INT IDENTITY PRIMARY KEY, cat INT NOT NULL, filler CHAR(200));
INSERT INTO dbo.SniffTest (cat, filler) -- cat = 1: 10,000 rows
SELECT TOP (10000) 1, 'x' FROM sys.all_objects a CROSS JOIN sys.all_objects b;
INSERT INTO dbo.SniffTest (cat, filler) -- cat = 2: 10 rows
SELECT TOP (10) 2, 'x' FROM sys.all_objects;
CREATE INDEX ix_sniff_cat ON dbo.SniffTest(cat) INCLUDE (filler);
GO
CREATE PROCEDURE dbo.GetByCategory @cat INT AS
BEGIN
SELECT COUNT(*) AS row_count FROM dbo.SniffTest WHERE cat = @cat;
END
GO
Step 1: Compile Against the Rare Value First
EXEC dbo.GetByCategory @cat = 2; -- 10 rows, compiles and caches the plan here
The count comes back as 10, correctly, and the plan SQL Server caches is built for a 10-row result.
Step 2: Call It Again With the Common Value
EXEC dbo.GetByCategory @cat = 1; -- actually 10,000 rows, but reuses the cached plan
Same procedure, same query text, but the actual row count coming back is 10,000, not 10. SQL Server doesn’t recompile just because the parameter changed, it reuses the plan compiled in Step 1. SET STATISTICS PROFILE ON shows exactly what that plan estimated versus what actually happened, measured here on SQL Server 2025 CU8:
The estimate is off by a factor of 1,000. In this specific example the index still covers the query either way, so the physical operator (an index seek) doesn’t change, only the row and cost estimate do, but that gap is exactly what drives a bad decision in a less trivial query: an under-costed operation gets a smaller memory grant than it needs, gets picked as the wrong side of a join, or doesn’t get the parallelism a correctly-estimated 10,000-row operation would have received. A query with a JOIN or ORDER BY layered on top of this same pattern is where a 1,000x cardinality miss turns into a real, visible slowdown, not just a wrong number in a query plan.
Step 3: Force a Fresh Plan Per Call
ALTER PROCEDURE dbo.GetByCategory @cat INT AS
BEGIN
SELECT COUNT(*) AS row_count FROM dbo.SniffTest WHERE cat = @cat OPTION (RECOMPILE);
END
With OPTION (RECOMPILE), the second call (@cat = 1) compiles its own plan instead of reusing the one from @cat = 2. The estimate this time: 10000.0, exact. OPTION (RECOMPILE) pays a real compilation cost on every single execution, that’s the trade, not a free fix, worth reaching for on a query that’s actually hurt by sniffing, not applied by default everywhere.
How to Actually Confirm This Is What’s Happening
Don’t guess from symptoms alone (“sometimes it’s fast, sometimes it’s slow” is consistent with several other problems too). Confirm the estimate mismatch directly:
SELECT ps.execution_count,
ps.total_worker_time / ps.execution_count / 1000.0 AS avg_cpu_ms,
qp.query_plan
FROM sys.dm_exec_procedure_stats ps
CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp
WHERE ps.database_id = DB_ID()
AND ps.object_id = OBJECT_ID('dbo.GetByCategory');
Run it in the database that owns the procedure. sys.dm_exec_procedure_stats is instance-wide while object_id is only unique inside a database, which is what the database_id filter is for, and reading the procedure’s own row rather than the statement’s is what keeps it returning something after Step 3: OPTION (RECOMPILE) stops the statement being cached, so sys.dm_exec_query_stats has nothing to show you. It returns nothing at all until the procedure has run at least once since it was last compiled, which is worth knowing before you conclude it is not the procedure you are after.
Open the plan and compare EstimateRows against the actual row count for the parameter value currently causing complaints. A large, consistent gap on a specific operator is the signature, not a one-off blip. Across the whole plan cache, Get Query Variance lists the queries whose slowest execution is at least 5x their fastest, which is the same signature at instance scale.
Common Causes and Fixes
- A shared procedure serving both a common-case and a rare-case caller. The classic shape: a scheduled job or batch process calls the rare-value path first thing after a service restart or plan cache clear, caching a plan every regular user then inherits.
OPTION (RECOMPILE)on the specific statement, orOPTION (OPTIMIZE FOR UNKNOWN)to get a distribution-average estimate instead of a sniffed one, both address this without recompiling the entire procedure on every call. - Highly skewed data with no good general-purpose plan. If one value genuinely needs a seek and another genuinely needs a scan, no single cached plan serves both well. This is a real case for
OPTION (RECOMPILE)on the affected statement specifically, accepting the per-call compile cost as the price of a correct plan every time. - Plan cache cleared at an inconvenient moment (a failover, a restart, an explicit
DBCC FREEPROCCACHE, or a large ad-hoc workload evicting the plan). Whatever value happens to run first after that recompiles the shared plan. Check Get Query Store Status to see whether Query Store is on for the database; if it is, Get Query Store Regressions and Forced Plans names thequery_idthat regressed against its own 7-day baseline and shows which plans are already forced. Both are read-only, the forcing itself issp_query_store_force_plan. - Stale statistics making every estimate wrong regardless of sniffing. Confirm statistics are current before concluding sniffing is the root cause, an outdated histogram produces bad estimates independent of which parameter compiled the plan.
Best Practices
- Confirm the estimate-vs-actual mismatch directly (
SET STATISTICS PROFILE ON, an actual execution plan, orsys.dm_exec_query_stats) before reaching forOPTION (RECOMPILE), don’t apply it as a reflex to every slow parameterized query. - Enable Query Store on instances where this recurs; it shows plan changes over time and lets you force a specific plan without touching the procedure’s code.
- Reserve
OPTION (RECOMPILE)for the specific statement that actually needs it, not the whole procedure, recompiling only what’s sensitive to the parameter keeps the compile cost as small as possible. - Re-test after any fix with the same skewed values that originally exposed the problem, a plan that looks fine for the common case can still be wrong for the rare one.
Related Scripts
- Get Query Performance Deep-Dive, for finding which cached plan and parameter combination is behind a slow procedure
- Get Query Store Status, whether Query Store is on and capturing, which plan forcing needs
- Get Query Store Regressions and Forced Plans, the query that regressed against its baseline and what is already forced
- Get Trace Flags and Resource Governor Configuration, to check whether trace flag 4136, which disables parameter sniffing instance-wide, is already set
- Table Variables vs Temp Tables: What the Optimizer Actually Sees, a related cardinality-estimate mismatch with a different root cause
- SQL Server High CPU, the walkthrough that decides working versus waiting before any setting is touched
- Performance and Troubleshooting (area)
Leave a Reply