Parameter sniffing explained with a real-world pattern

Parameter sniffing is one of the most common “it was fine yesterday” performance stories on SQL Server.

What is happening?

When a procedure or parameterised query compiles, SQL Server peeks at the first (or sniffed) parameter values and builds a plan optimised for those values.

That plan is reused for later executions. If data distribution is skewed, the “good for yesterday’s parameter” plan can be disastrous for today’s.

Classic symptoms:

• Same procedure: 50ms for customer A, 45 seconds for customer B
• Intermittent slowness that “fixes itself” after a reboot or stats update
• Plan in cache shows seeks that become scans (or vice versa) for other values
• OPTION (RECOMPILE) “fixes” it but CPU climbs under load

A simplified example

Imagine Orders with:

• Most customers: dozens of rows
• A few wholesale accounts: millions of rows

CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

If the first compile sniffs a tiny customer, you may get a nested loop + seek plan. When a huge customer arrives, that plan can thrash. Sniff a huge customer first, and small customers may get a heavyweight plan.

Fix options (choose deliberately)

1. Optimal for highly variable plans: recompile carefully

OPTION (RECOMPILE)

• Pros: plan matches current parameters
• Cons: compile CPU; can hurt very high-frequency calls

Use when correctness of plan shape matters more than compile cost.

2. OPTIMIZE FOR / OPTIMIZE FOR UNKNOWN

Useful when you know a typical value, or want density-based guesses:

OPTION (OPTIMIZE FOR (@CustomerId UNKNOWN))

3. Query Store plan forcing

When you have a known good plan, Query Store can stabilise it after regressions — excellent for production control.

4. Design changes

Sometimes the real fix is structural:

• Separate procedures for “hot” paths
• Dynamic SQL with careful parameterisation (know the risks)
• Better indexing for the selective predicates you actually use
• Avoid one mega-procedure that serves every reporting shape

5. Stats and data quality

Skewed histograms, stale stats after bulk loads, and ascending keys all amplify sniffing pain. Fix the stats story alongside the plan story.

Investigation checklist

1. Capture the slow parameter values
2. Compare actual plans for fast vs slow parameters
3. Check Query Store runtime stats by query/plan
4. Confirm whether the plan was reused across wildly different cardinalities
5. Apply the least invasive fix that meets SLA

How we help

Sniffing issues are perfect for a short, focused engagement: reproduce, measure, stabilise, document. If your app team is stuck in RECOMPILE-everything mode, we can help you find a cleaner long-term approach.

Talk to Allay Data Solutions.