Narcis Miclaus

6 min read

SQL Server, from the ground up Lesson 27 / 40

Parameter sniffing: the bug, the feature, the fix

Why the query is fast in SSMS and slow in the app. OPTIMIZE FOR, RECOMPILE, Query Store plan forcing, and the recent SQL Server features that make this less painful.

Your support engineer files a ticket: “The ‘find customer by email’ endpoint is suddenly timing out for some users but not others.”

You run the underlying query in SSMS. Instant. You give up trying to reproduce it locally. The engineer gives up and restarts the app server. The issue goes away for three days, then comes back.

This is parameter sniffing. Today’s lesson is the one that solves it.

The mechanism, one paragraph

SQL Server caches execution plans for parameterized queries. The first time the query runs, the optimizer builds a plan using the specific parameter values it sees. That plan is then reused for every subsequent call, regardless of the parameter values. When the first call happened to pick “an extreme” value — a customer with 20,000 orders, say — the plan is optimized for that case. A later call with a normal customer reuses the extreme-case plan, and it’s wildly wrong.

That’s the bug. It’s also called a “feature” because for 80% of queries, sniffing produces a great plan and saves the optimization cost. For the other 20%, it’s the reason you can’t reproduce a prod issue in dev.

The canonical demo

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId;
END;

-- First call: a "small" customer
EXEC Sales.GetOrdersForCustomer @CustomerId = 123;
-- Plan: nested loop, key lookup. Great for ~5 rows.

-- Later call: a "giant" customer with 50,000 orders
EXEC Sales.GetOrdersForCustomer @CustomerId = 42;
-- Reuses the nested-loop plan. Does 50,000 key lookups. Times out.

-- Another SSMS user runs the query directly
SELECT * FROM Sales.Orders WHERE CustomerId = 42;
-- Fresh compile. Optimizer sees @CustomerId = 42, builds a better plan (scan + filter).
-- Fast.

This is why “it’s fast in SSMS and slow in the app” — fresh SSMS run gets a fresh plan; app is reusing yesterday’s cached plan.

Fixes, from least to most invasive

Fix 1: OPTION (RECOMPILE)

Force a fresh plan for every call:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);
  • Pros: always uses the best plan for the current parameter.
  • Cons: compile cost on every call. For a stored proc called thousands of times per minute, that’s CPU you can’t afford.

Use RECOMPILE on queries where parameter skew is large and call volume is low. Reports, ad-hoc dashboards, admin tools — perfect fit. The hot order-lookup endpoint called 5,000 times a minute — not the place.

Fix 2: OPTIMIZE FOR UNKNOWN

Tell the optimizer to use average density instead of sniffed values:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);
  • Pros: no per-call compile cost. Plan is generic.
  • Cons: generic plan is mediocre for both small and large cases. You trade the “great for one, terrible for other” problem for “mediocre for both.”

Useful when the parameter distribution is uniform and no value is an outlier. Otherwise mostly a historical workaround.

Fix 3: OPTIMIZE FOR (@p = 'some value')

Force a specific value at plan-compile time:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1));
-- Assumes CustomerId 1 is "representative of the typical case"
  • Pros: deterministic plan, optimized for your chosen value.
  • Cons: maintenance burden. What’s “typical” can drift over time.

Used when you know a specific value produces the shape of plan you want for the majority of calls.

Fix 4: parameter masking (the old hack)

Assign the parameter to a local variable inside the proc:

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    DECLARE @c INT = @CustomerId;    -- local variable breaks sniffing
    SELECT * FROM Sales.Orders WHERE CustomerId = @c;
END;

The optimizer can’t “sniff” a local variable (its value isn’t known at compile time), so it uses average density. Effectively the same as OPTIMIZE FOR UNKNOWN.

This trick is everywhere in old production code. Works, ugly. Prefer the explicit OPTIMIZE FOR UNKNOWN hint.

Fix 5: Query Store plan forcing (the modern one)

Since SQL Server 2016, Query Store tracks every query, every plan, every runtime metric. If you find a plan that works well, you can pin it: all future calls of the same query use that plan regardless of sniffing.

We’ll cover Query Store properly in lesson 28. Teaser:

-- Force the plan identified as plan_id 42 for query 19
EXEC sp_query_store_force_plan @query_id = 19, @plan_id = 42;

This is the production-safe way to say “this plan is good, use it until I say otherwise.” No code changes, no hints. Reversible with sp_query_store_unforce_plan.

Fix 6: branched logic (the nuclear option)

When parameters fall into clearly different ranges (“small customers” vs “giant customers”), you can manually branch:

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerId = @CustomerId AND OrderCount > 10000)
        EXEC Sales.GetOrdersForLargeCustomer @CustomerId;
    ELSE
        EXEC Sales.GetOrdersForSmallCustomer @CustomerId;
END;

Two different procs, each with its own cached plan tuned to its case. Ugly. Used only when the other fixes aren’t enough.

Parameter Sensitive Plan optimization (SQL Server 2022+)

SQL Server 2022 introduced Parameter Sensitive Plan (PSP) optimization — the optimizer keeps multiple plans for the same query, chosen based on the parameter value. Fixes the bug at the engine level.

Enabled automatically on databases with compatibility level 160 (SQL 2022) or higher. The engine detects skew-sensitive parameters and caches up to three plans per query. The right one is picked at runtime based on the parameter’s “bucket” (small, medium, large).

If you’re on SQL Server 2022 with compat 160, most of the parameter-sniffing problems that plagued 2012-2019 just… go away. Not all — it’s a heuristic, not magic — but a significant fraction.

Check if PSP is doing anything for your queries:

SELECT TOP (20) query_id, query_text_id, plan_id, count_executions
FROM sys.query_store_plan
WHERE is_plan_for_psp = 1
ORDER BY count_executions DESC;

Recognizing the symptom in the wild

“Parameter sniffing” is the answer when:

  • The same query is slow at seemingly random times.
  • It’s fast when run from SSMS, slow from the application.
  • Restarting the SQL Server service (flushing the plan cache) temporarily fixes it.
  • Running DBCC FREEPROCCACHE or sp_recompile temporarily fixes it.
  • Adding OPTION (RECOMPILE) makes it consistent.

If any two of those are true, it’s almost certainly parameter sniffing.

Run this on your own machine

USE Runehold;
GO

-- Set up a skewed dataset: one customer with 10,000 orders, rest with 5-10
INSERT INTO Sales.Orders (CustomerId, OrderDate, Total, CountryCode, VatRate)
SELECT TOP (10000) 1, DATEADD(DAY, -v.n, GETDATE()), 49.99, 'NL', 0.2100
FROM (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
      FROM sys.all_objects a CROSS JOIN sys.all_objects b) AS v;

-- Build a simple procedure
CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderDate, Total
    FROM Sales.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;
GO

-- Clear the plan cache to start fresh
DBCC FREEPROCCACHE;

-- First call: CustomerId 1 (the big one). Plan optimized for 10,000 rows.
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

-- Second call: CustomerId 2 (small). Reuses the big plan. May be slow or fine.
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;

-- Clear again
DBCC FREEPROCCACHE;

-- Now first-call the small customer. Different plan compiled.
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

-- Fix with RECOMPILE
CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderDate, Total
    FROM Sales.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC
    OPTION (RECOMPILE);
END;

EXEC dbo.GetOrdersForCustomer @CustomerId = 1;  -- plan A
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;  -- plan B (fresh)

Watch the execution plans for each call. Compare. Observe how the “wrong” plan behaves when reused.

Next lesson: Query Store — the time machine for your query performance. How to detect a regression, find the plan that was good, and force it.

Search