What is parameter sniffing in SQL Server?

Parameter sniffing in Microsoft SQL Server is a query optimization behavior where SQL Server generates an execution plan based on the first parameter values it sees, then reuses that same plan for later executions — even if later parameter values are very different.

Sometimes this improves performance. Sometimes it causes severe slowdowns.

Why it happens

SQL Server caches execution plans to avoid recompiling queries repeatedly.

Suppose you have a stored procedure:

CREATE PROCEDURE GetOrders
    @CustomerID INT
AS
BEGIN
    SELECT *
    FROM Orders
    WHERE CustomerID = @CustomerID
END

When executed the first time:

EXEC GetOrders @CustomerID = 1

SQL Server:

• "Sniffs" the parameter value (1)
• Estimates row counts
• Builds what it thinks is the best execution plan
• Caches that plan

Later:

EXEC GetOrders @CustomerID = 999999

SQL Server may reuse the old plan even if:

• Customer 1 has 2 rows
• Customer 999999 has 2 million rows
• The cached plan may now be inefficient.

Typical problem scenario

Imagine:

• Small parameter values → few rows
• Large parameter values → many rows

SQL Server may choose:

• Index seek for small datasets
• Table scan/hash join for large datasets

If the "wrong" plan gets reused:

• CPU spikes
• Long-running queries
• Blocking
• Timeouts

Example

Small result set:

EXEC GetOrders @Status = 'Pending'

Might produce:

• Index seek
• Fast nested loop join

Large result set:

EXEC GetOrders @Status = 'Archived'

Could need:

• Scan
• Hash join

If the "Pending" plan gets reused for "Archived", performance may collapse.

Signs of parameter sniffing

Common symptoms:

• Query sometimes fast, sometimes extremely slow
• Clearing plan cache temporarily fixes issue
• Same query behaves differently for different parameters
• Performance changes after server restart/deployment

Ways to fix or mitigate it

1. OPTION (RECOMPILE)

Forces SQL Server to create a fresh plan each execution.

SELECT *
FROM Orders
WHERE CustomerID = @CustomerID
OPTION (RECOMPILE)

Pros:

• Best plan per execution

Cons:

• Extra compilation overhead

2. Local variables

Breaks parameter sniffing by hiding actual values.

DECLARE @LocalCustomerID INT
SET @LocalCustomerID = @CustomerID

SELECT *
FROM Orders
WHERE CustomerID = @LocalCustomerID

This causes SQL Server to use generic estimates instead.

Pros:

• More stable plans

Cons:

• Sometimes less optimal overall

3. OPTIMIZE FOR

Tell SQL Server which parameter value to optimize for.

OPTION (OPTIMIZE FOR (@CustomerID = 100))

Or:

OPTION (OPTIMIZE FOR UNKNOWN)

Useful when you know typical workloads.

4. Query Store / Forced Plans

Modern SQL Server versions support:

• Query Store
• Forced execution plans
• This can stabilize performance.

5. Rewrite the query

Sometimes:

• Splitting procedures
• Dynamic SQL
• Different query branches
• works better for skewed data distributions.

Example:

IF @CustomerID < 1000
BEGIN
    -- optimized query for small sets
END
ELSE
BEGIN
    -- optimized query for large sets
END

Simple mental model

Parameter sniffing is basically:

"SQL Server assumes future executions will behave like the first execution."

That assumption is sometimes wrong.

Important note

Parameter sniffing itself is not a bug.

It's actually a performance optimization:

• Reusing plans saves CPU
• Avoids recompilation

The issue only appears when:

• Data distribution is uneven (data skew)
• Different parameter values need different plans

In newer SQL Server versions

Recent versions of SQL Server include improvements like:

• Adaptive query processing
• Parameter Sensitive Plan (PSP) optimization
• Better cardinality estimators

These reduce parameter sniffing problems automatically in many cases.