Parameter sniffing: when one plan doesn't fit all

-- performance tuning · Ronald de Groot

The classic phone call: "that procedure was always fast, and since this morning it takes minutes — and we changed nothing." Nine times out of ten this is parameter sniffing. Not a bug, but a design choice in SQL Server that usually works out well — and sometimes disastrously.

What happens

On the first execution of a stored procedure (or parameterised query), the optimizer "sniffs" the parameter values and builds the best plan for those. That plan goes into the plan cache and is reused for all subsequent calls — regardless of their parameter values.

That's usually exactly what you want: compilation is expensive, so why rebuild the plan on every call? It goes wrong when the data distribution is skewed:

CREATE PROCEDURE dbo.GetOrders @CustomerID int
AS
SELECT OrderID, OrderDate, TotalAmount
FROM   dbo.Orders
WHERE  CustomerID = @CustomerID;

-- Customer 42: 12 orders      → ideal: Index Seek + Key Lookup
-- Customer 1001: 4.2 million  → ideal: Clustered Index Scan

If the plan is compiled for customer 42, that same seek-with-lookup performs millions of lookups for customer 1001 — minutes instead of milliseconds. And the other way around too: a scan plan compiled for customer 1001 reads the whole table to find customer 42's 12 rows.

How to recognise it

• Erratic behaviour: the same procedure is fast or slow depending on... nothing you can point at. It often "heals" spontaneously after a failover, restart or index rebuild — anything that clears the plan cache.
• Estimated vs. actual rows in the actual plan differ wildly (see my article on reading execution plans) — yet the estimate was correct for the sniffed value. You'll find that value in the plan XML under ParameterCompiledValue.
• Query Store makes it visible: one query with multiple plans, with wildly different durations per plan. The plan-over-time chart tells you exactly when the bad plan entered the cache.

Five fixes — and when each one fits

1. OPTION (RECOMPILE): always the best plan, always compiling

SELECT ... FROM dbo.Orders
WHERE  CustomerID = @CustomerID
OPTION (RECOMPILE);

Every execution gets a fresh plan for the real values. Perfect for reporting queries that run a few times per minute; too expensive for an OLTP query that comes by a thousand times per second — you'd pay compilation CPU continuously.

2. OPTIMIZE FOR: deliberately pick one scenario

OPTION (OPTIMIZE FOR (@CustomerID = 1001));   -- plan for the big customer
-- or:
OPTION (OPTIMIZE FOR UNKNOWN);                -- plan for the "average"

OPTIMIZE FOR UNKNOWN ignores the sniffing and uses the average density from the statistics. That makes performance predictable — not necessarily optimal. Choose this when a stable middle-ground plan beats gambling.

3. Rewrite: split the scenarios

The clean structural fix for genuinely skewed data: route big and small customers to separate (sub)procedures, so each gets its own cached plan. More code, but both paths keep their ideal plan permanently.

4. Query Store: force the good plan

Since SQL Server 2016 often the fastest production fix: point at the well-performing plan in Query Store and click Force Plan (or sp_query_store_force_plan). From SQL 2022, Automatic Plan Correction even does this automatically on a demonstrable regression.

5. The emergency brake: evict one plan

-- Remove only this procedure's plan from the cache:
EXEC sp_recompile 'dbo.GetOrders';

Not a structural fix — it solves nothing for next time — but at 3 a.m. it's perfectly good first aid. Never run DBCC FREEPROCCACHE without a plan handle on a busy server: everything recompiles.

What I do in practice

1. Confirm the diagnosis in Query Store: multiple plans, strongly varying durations, a deviating ParameterCompiledValue.
2. Acute pain? Force the plan (or sp_recompile) to relieve production.
3. Then choose structurally: RECOMPILE for low-frequency reporting, OPTIMIZE FOR or a rewrite for high-frequency OLTP paths.
4. And check the indexes: sometimes the real cause is that a better index should simply exist for the "big" scenario — then the difference between the plans disappears by itself.

A procedure behaving unpredictably and you can't crack it? Send me the plan — I'm happy to take a look.