SQL Server Parameter Sniffing: Diagnose It Before You Fix It

A stored procedure runs in 80 milliseconds for one customer and 40 seconds for another. Nothing changed in the code, the server is not overloaded, and rerunning the procedure sometimes makes the problem disappear. This is a classic sign of a parameter-sensitive plan, often called parameter sniffing.

Parameter sniffing is not automatically a defect. When SQL Server compiles a parameterized query, it uses the current parameter values to estimate row counts and choose a plan. Reusing that plan saves compilation work and is usually beneficial. The problem appears when one plan cannot serve very different data distributions well.

Why one plan can fail

Imagine an orders table where most customers have fewer than 100 rows, while one marketplace customer has 8 million. A plan compiled for a small customer may favor index seeks and nested loops. The same plan can become painfully slow for the large customer. A plan compiled for the large customer may scan and hash, wasting work for everyone else.

Skewed data, optional filters, stale statistics, and correlated columns make this more likely.

Reproduce the behavior safely

Start with a representative procedure:

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

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

Test a selective and a nonselective value in a nonproduction environment. Capture the actual execution plan and runtime statistics for both. If performance depends on which value compiled the cached plan, you have strong evidence of parameter sensitivity.

Do not clear the entire production plan cache. That creates a compilation spike and affects unrelated workloads.

A practical diagnostic workflow

1. Use Query Store to find plans with large duration, CPU, or logical-read variation.
2. Compare actual and estimated row counts at the first major join or lookup.
3. Record the parameter values used during compilation and execution.
4. Check the relevant statistics histogram and last update time.
5. Look for implicit conversions, non-sargable predicates, and missing indexes before blaming parameter sniffing.
6. Confirm that the fast and slow executions use the same query and plan identity.

The key question is not "Is the plan bad?" It is "Is this plan good for one important data shape and bad for another?"

Choose the smallest reliable fix

Begin with fundamentals. Update inaccurate statistics and create an index only when the access pattern justifies it. If the query has genuinely different shapes, separating those shapes is often clearer than forcing one compromise plan.

On SQL Server 2022 and later, Parameter Sensitive Plan optimization can keep multiple plan variants for eligible equality predicates. Verify that the database compatibility level and query shape allow it, then confirm the variants in Query Store.

For a narrow emergency stabilization, a Query Store hint or forced plan can buy time, but monitor it because data changes can make yesterday's plan wrong.

Use OPTION (RECOMPILE) when compilation cost is small and each execution needs a plan tailored to its values. Apply it to the smallest statement possible, not automatically to an entire workload.

Use OPTIMIZE FOR only when you have a stable, representative value or deliberately want an average plan. Dynamic SQL with sp_executesql can be effective for optional search predicates because it creates plans for distinct query shapes while preserving parameterization.

Avoid these common mistakes

• Do not disable parameter sniffing globally to solve one query.
• Do not add every suggested index without measuring write and storage costs.
• Do not force a plan without an owner, monitoring, and a removal condition.
• Do not assume every intermittent slowdown is parameter sniffing.

Production checklist

Capture a baseline, preserve the original plan, test with representative parameter groups, measure CPU and logical reads, deploy the narrowest change, and watch Query Store after release. Define a rollback before deployment.

Parameter sniffing is best treated as a data-distribution problem, not a mysterious cache failure. Prove the competing data shapes, then choose a solution that matches them. If you want a second opinion on a difficult SQL Server plan, use the question form below and include the procedure, representative parameters, and an anonymized execution plan.