mohamed Tayel

Posted on Jul 13

SQL Server Parameter Sniffing: Detecting Unstable Execution Plans and Choosing the Right Fix

#sql #sqlserver

A stored procedure may execute in milliseconds for one parameter value and take several seconds for another, even though the SQL statement itself has not changed.

One of the most common causes of this behavior in SQL Server is parameter sniffing.

Parameter sniffing is not always a defect. It is a normal SQL Server optimization behavior that often improves performance by allowing the Query Optimizer to create an execution plan based on the parameter values supplied during compilation.

The problem appears when different parameter values require significantly different execution strategies.

For example:

One customer may have five orders.
Another customer may have five million orders.
One execution may benefit from an Index Seek and Nested Loops.
Another may require a scan, parallelism, and a Hash Join.

If SQL Server compiles and caches one plan, then reuses it for both workloads, one of those executions may perform poorly.

This article explains how parameter sniffing works, how to reproduce it, how to identify it using Query Store and execution plans, and how to choose the most appropriate solution.

What Is Parameter Sniffing?

When SQL Server compiles a parameterized query or stored procedure, it examines the parameter values supplied during that compilation.

It uses those values to estimate:

How many rows will be returned
Which indexes should be used
Which join algorithms are appropriate
Whether parallel execution is beneficial
How much memory the query requires
The most efficient order for accessing tables

This behavior is known as parameter sniffing.

Consider the following stored procedure:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

The first time SQL Server executes this procedure, it may compile an execution plan using the supplied @CustomerId.

That plan is then stored in the plan cache and may be reused for future executions.

For example:

EXEC dbo.GetCustomerOrders
    @CustomerId = 10;
GO

If customer 10 has only five orders, SQL Server may choose:

Index Seek
    ↓
Key Lookup
    ↓
Nested Loops

That may be an excellent plan for a small number of rows.

Later, the same procedure may be executed for a customer with millions of orders:

EXEC dbo.GetCustomerOrders
    @CustomerId = 5000;
GO

SQL Server may reuse the plan created for customer 10.

The Index Seek and repeated Key Lookups that worked well for five rows may become extremely expensive for millions of rows.

The query has not changed.

The data has not necessarily changed.

The parameter value changed, but SQL Server reused the same cached plan.

Why Parameter Sniffing Is Usually Helpful

Parameter sniffing exists for a good reason.

Without it, SQL Server would have less information when estimating the number of rows returned by a query.

Suppose a table contains the following distribution:

Customer	Number of Orders
Customer 10	5
Customer 20	12
Customer 30	42
Customer 5000	5,000,000

If SQL Server compiles the procedure for customer 10, it can estimate that only a small number of rows will be returned.

That information may allow it to select an efficient Index Seek.

If the same procedure is normally executed for customers with small order volumes, the cached plan will perform well most of the time.

Parameter sniffing becomes a problem when:

Data distribution is highly skewed.
Different parameter values return very different numbers of rows.
The first compiled parameter is not representative of the normal workload.
A cached plan remains in use for a long period.
The query requires different join or index strategies for different parameter groups.
Statistics do not accurately represent the current data distribution.
A plan is compiled during an unusual workload condition.

The issue is therefore not parameter sniffing itself.

The issue is using one execution plan for workloads that require different plans.

Creating a Parameter Sniffing Demo

The following example creates a simplified orders table with heavily skewed data.

Create the Table

DROP TABLE IF EXISTS dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
    OrderId BIGINT IDENTITY(1,1) NOT NULL,
    CustomerId INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED (OrderId)
);
GO

Create an index on CustomerId:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId)
INCLUDE
(
    OrderDate,
    TotalAmount
);
GO

Insert Small Customers

The following script creates many customers with a relatively small number of orders:

WITH Numbers AS
(
    SELECT TOP (100000)
        ROW_NUMBER() OVER
        (
            ORDER BY
                (SELECT NULL)
        ) AS NumberValue
    FROM sys.all_objects AS A
    CROSS JOIN sys.all_objects AS B
)
INSERT INTO dbo.Orders
(
    CustomerId,
    OrderDate,
    TotalAmount
)
SELECT
    ((NumberValue - 1) % 1000) + 1,
    DATEADD
    (
        DAY,
        -(NumberValue % 365),
        SYSUTCDATETIME()
    ),
    CAST
    (
        10 + (NumberValue % 500)
        AS DECIMAL(18,2)
    )
FROM Numbers;
GO

Insert a High-Volume Customer

Now create a customer with a much larger number of orders:

WITH Numbers AS
(
    SELECT TOP (1000000)
        ROW_NUMBER() OVER
        (
            ORDER BY
                (SELECT NULL)
        ) AS NumberValue
    FROM sys.all_objects AS A
    CROSS JOIN sys.all_objects AS B
)
INSERT INTO dbo.Orders
(
    CustomerId,
    OrderDate,
    TotalAmount
)
SELECT
    5000,
    DATEADD
    (
        MINUTE,
        -NumberValue,
        SYSUTCDATETIME()
    ),
    CAST
    (
        50 + (NumberValue % 1000)
        AS DECIMAL(18,2)
    )
FROM Numbers;
GO

Update statistics after loading the data:

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;
GO

Check the data distribution:

SELECT
    CustomerId,
    COUNT_BIG(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerId
ORDER BY OrderCount DESC;
GO

The result should show that customer 5000 has significantly more orders than the other customers.

Create the Stored Procedure

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

Enable performance statistics before testing:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

In SQL Server Management Studio, also enable:

Include Actual Execution Plan

Test 1: Compile the Plan for a Small Customer

Clear the procedure plan from the cache:

EXEC sys.sp_recompile
    N'dbo.GetCustomerOrders';
GO

Execute the procedure for a small customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 10;
GO

SQL Server may choose a plan based on the assumption that only a small number of rows will be returned.

Possible operators include:

Nonclustered Index Seek
Key Lookup
Nested Loops
Serial execution

This plan may be extremely efficient for customer 10.

Now execute the same procedure for the high-volume customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 5000;
GO

SQL Server may reuse the plan compiled for customer 10.

The execution may now require:

A very high number of logical reads
Repeated lookups
Increased CPU time
Longer duration
More expensive row-by-row processing

Test 2: Compile the Plan for the Large Customer

Recompile the stored procedure:

EXEC sys.sp_recompile
    N'dbo.GetCustomerOrders';
GO

Execute it first for the large customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 5000;
GO

SQL Server may now choose a different plan.

Possible characteristics include:

Index Scan or Clustered Index Scan
Parallel execution
Larger memory grants
Different join or access strategies

Now execute the same procedure for a small customer:

EXEC dbo.GetCustomerOrders
    @CustomerId = 10;
GO

The plan compiled for the high-volume customer may be inefficient for a customer that returns only a few rows.

A scan that is reasonable for one million rows may be unnecessary for five rows.

This demonstrates the central problem:

The optimal execution plan depends on the parameter value.

How to Confirm Parameter Sniffing

A slow stored procedure is not automatically a parameter sniffing problem.

You should collect evidence before applying a fix.

Common indicators include:

The same procedure is sometimes fast and sometimes slow.
Performance changes after recompilation.
Performance changes after restarting SQL Server.
Different parameter values produce very different row counts.
The cached plan contains a parameter value that is not representative.
Estimated and actual row counts differ significantly.
Query Store shows multiple plans for the same query.
The execution plan is efficient for one value but inefficient for another.
Clearing or recompiling the plan temporarily resolves the problem.

Inspecting the Compiled Parameter Value

The actual execution plan may show both:

The parameter value used during compilation
The parameter value used during the current execution

In the execution plan properties, you may find information similar to:

Parameter List

Parameter Compiled Value: 10
Parameter Runtime Value: 5000

This is an important clue.

It means the current execution is using a plan compiled for a different parameter value.

However, this alone does not prove that parameter sniffing caused the slowdown.

You must also verify that the plan is inappropriate for the current value.

Checking the Cached Execution Plan

You can inspect cached plans using dynamic management views.

SELECT
    DB_NAME(QueryText.dbid) AS DatabaseName,
    QueryStats.execution_count,
    QueryStats.total_elapsed_time,
    QueryStats.total_worker_time,
    QueryStats.total_logical_reads,
    QueryStats.last_execution_time,
    QueryText.text AS QueryText,
    QueryPlan.query_plan
FROM sys.dm_exec_query_stats AS QueryStats
CROSS APPLY
    sys.dm_exec_sql_text
    (
        QueryStats.sql_handle
    ) AS QueryText
CROSS APPLY
    sys.dm_exec_query_plan
    (
        QueryStats.plan_handle
    ) AS QueryPlan
WHERE QueryText.text LIKE
    N'%GetCustomerOrders%'
ORDER BY
    QueryStats.last_execution_time DESC;
GO

This query can help you review:

Execution count
Total elapsed time
CPU time
Logical reads
Cached plan XML
Last execution time

Keep in mind that plan-cache data is temporary.

It may disappear after:

SQL Server restarts
Plan recompilation
Memory pressure
Plan eviction
Database changes
Manual cache clearing

For historical analysis, Query Store is usually more reliable.

Detecting Parameter-Sensitive Behavior with Query Store

Query Store records:

Query text
Query plans
Runtime statistics
Execution history
Plan changes
Performance over time

This makes it especially valuable for identifying unstable queries.

The following query lists plans captured for a query:

DECLARE @QueryId BIGINT = 123;

SELECT
    QueryPlan.query_id,
    QueryPlan.plan_id,
    QueryPlan.is_forced_plan,
    QueryPlan.count_compiles,
    QueryPlan.first_execution_time,
    QueryPlan.last_execution_time,
    QueryPlan.force_failure_count,
    QueryPlan.last_force_failure_reason_desc,
    QueryPlan.query_plan
FROM sys.query_store_plan AS QueryPlan
WHERE QueryPlan.query_id = @QueryId
ORDER BY QueryPlan.plan_id;
GO

The following query compares plan runtime statistics:

DECLARE @QueryId BIGINT = 123;

SELECT
    QueryPlan.plan_id,
    QueryPlan.is_forced_plan,
    SUM(RuntimeStats.count_executions)
        AS ExecutionCount,
    CAST
    (
        SUM
        (
            RuntimeStats.avg_duration
            * RuntimeStats.count_executions
        )
        / NULLIF
        (
            SUM(RuntimeStats.count_executions),
            0
        )
        AS DECIMAL(18,2)
    ) AS WeightedAverageDurationMicroseconds,
    MAX(RuntimeStats.max_duration)
        AS MaximumDurationMicroseconds,
    CAST
    (
        SUM
        (
            RuntimeStats.avg_cpu_time
            * RuntimeStats.count_executions
        )
        / NULLIF
        (
            SUM(RuntimeStats.count_executions),
            0
        )
        AS DECIMAL(18,2)
    ) AS WeightedAverageCpuMicroseconds,
    CAST
    (
        SUM
        (
            RuntimeStats.avg_logical_io_reads
            * RuntimeStats.count_executions
        )
        / NULLIF
        (
            SUM(RuntimeStats.count_executions),
            0
        )
        AS DECIMAL(18,2)
    ) AS WeightedAverageLogicalReads,
    MAX(RuntimeStats.last_execution_time)
        AS LastExecutionTime
FROM sys.query_store_plan AS QueryPlan
INNER JOIN sys.query_store_runtime_stats AS RuntimeStats
    ON QueryPlan.plan_id = RuntimeStats.plan_id
WHERE QueryPlan.query_id = @QueryId
GROUP BY
    QueryPlan.plan_id,
    QueryPlan.is_forced_plan
ORDER BY
    WeightedAverageDurationMicroseconds;
GO

A parameter-sensitive query may show:

Multiple plans
Large performance differences between plans
Highly variable duration
High maximum duration despite an acceptable average
Different CPU and logical-read profiles

Why Estimated Rows Matter

Execution-plan selection depends heavily on cardinality estimation.

Consider the following values:

Estimated Rows: 10
Actual Rows:    1,000,000

SQL Server selected its plan expecting ten rows.

The actual execution returned one million rows.

This difference can cause SQL Server to choose:

Nested Loops instead of Hash Join
Repeated Key Lookups
A small memory grant
Serial execution
An inefficient join order
Operators that spill to tempdb

The reverse problem can also occur:

Estimated Rows: 1,000,000
Actual Rows:    10

SQL Server may request excessive memory, use parallelism unnecessarily, or scan a large portion of an index for a tiny result set.

Parameter sniffing can therefore affect much more than index selection.

It can influence the entire execution strategy.

Common Solutions for Parameter Sniffing

There is no single solution that is correct for every parameter sniffing problem.

The correct choice depends on:

Query frequency
Data distribution
Compilation cost
Number of parameter patterns
Performance requirements
SQL Server version
Ability to change application code
Acceptable operational risk

The following sections describe the most common approaches.

Solution 1: OPTION (RECOMPILE)

OPTION (RECOMPILE) tells SQL Server to compile a new plan each time the query runs.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;
GO

Each execution is optimized for its current parameter value.

Advantages

Uses the current parameter value.
Avoids reuse of an unsuitable cached plan.
Often effective for highly skewed data.
Simple to implement.
Useful for infrequently executed reporting queries.

Disadvantages

Adds compilation CPU cost.
The plan is not reused.
May be unsuitable for very frequently executed queries.
Reduces the usefulness of some cached-plan statistics.
Does not fix indexing, statistics, or query-design problems.

Good Use Cases

OPTION (RECOMPILE) is often appropriate when:

The query runs relatively infrequently.
Parameter values produce very different result sizes.
Compilation cost is low compared with execution cost.
Accurate row estimates are important.
Query duration is much greater than compile duration.

Solution 2: WITH RECOMPILE

A stored procedure can also be created with WITH RECOMPILE.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
WITH RECOMPILE
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

This recompiles the entire procedure for every execution.

This is broader than adding OPTION (RECOMPILE) to one statement.

For procedures containing multiple queries, statement-level recompilation is often more targeted and less expensive.

Solution 3: OPTIMIZE FOR a Specific Value

You can instruct SQL Server to optimize the query for a specific parameter value.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION
    (
        OPTIMIZE FOR
        (
            @CustomerId = 10
        )
    );
END;
GO

Advantages

Creates predictable plan behavior.
May stabilize a workload with one representative parameter.
Avoids compilation on every execution.

Disadvantages

The selected value may become unrepresentative.
Data distribution can change.
One plan may still be unsuitable for other parameter values.
Requires ongoing review.

Good Use Cases

This option may be appropriate when:

One parameter pattern represents most executions.
A known value creates a safe and stable plan.
Occasional slower executions are acceptable.
The data distribution is relatively stable.

Solution 4: OPTIMIZE FOR UNKNOWN

OPTIMIZE FOR UNKNOWN tells SQL Server not to optimize for the supplied parameter value.

Instead, it uses more generalized statistical estimates.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION
    (
        OPTIMIZE FOR UNKNOWN
    );
END;
GO

Advantages

Reduces dependency on the first compiled parameter.
Can provide more predictable performance.
Avoids per-execution recompilation.
Useful when no single parameter is representative.

Disadvantages

Produces a generic plan.
The generic plan may not be optimal for any parameter group.
Can still perform poorly with heavily skewed data.
May reduce performance for both small and large values.

Good Use Cases

This can be useful when:

Most parameter values have similar selectivity.
A stable average plan is acceptable.
The first parameter is frequently unrepresentative.
Consistency is more important than the fastest possible execution.

Solution 5: Use Local Variables

A common workaround is to assign the parameter to a local variable.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LocalCustomerId INT;

    SET @LocalCustomerId = @CustomerId;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @LocalCustomerId;
END;
GO

This can prevent SQL Server from using the original parameter value during optimization.

However, it often produces behavior similar to OPTIMIZE FOR UNKNOWN.

Advantages

Simple to implement.
May reduce instability caused by an unusual compiled value.

Disadvantages

Hides useful information from the optimizer.
May produce inaccurate estimates.
Often results in a generic plan.
Can create a different performance problem.
Usually not the preferred long-term solution.

Local variables should not be used automatically as a standard fix.

They should be tested against realistic workloads.

Solution 6: Dynamic SQL

Dynamic SQL can create separate cached plans for different query shapes or conditions.

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SqlCommand NVARCHAR(MAX);

    SET @SqlCommand =
        N'
        SELECT
            OrderId,
            CustomerId,
            OrderDate,
            TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId;
        ';

    EXEC sys.sp_executesql
        @SqlCommand,
        N'@CustomerId INT',
        @CustomerId = @CustomerId;
END;
GO

Simply converting a query to dynamic SQL does not automatically solve parameter sniffing.

However, dynamic SQL is useful when you intentionally generate different query forms for different parameter patterns.

For example:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerId = 5000
    BEGIN
        SELECT
            OrderId,
            CustomerId,
            OrderDate,
            TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId
        OPTION (RECOMPILE);

        RETURN;
    END;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

Different branches can produce separate cached statements and execution plans.

Advantages

Allows different strategies for different parameter groups.
Useful for optional search filters.
Can avoid one-size-fits-all plans.
Supports targeted query generation.

Disadvantages

Adds code complexity.
Requires careful parameterization.
Poor implementation may create SQL injection risk.
Can create excessive plan-cache entries.
Requires more testing and maintenance.

Always use sp_executesql and parameters rather than concatenating untrusted values into SQL strings.

Solution 7: Separate Procedures or Query Branches

When the workload naturally contains distinct parameter groups, separate procedures or branches may be clearer.

For example:

CREATE OR ALTER PROCEDURE dbo.GetSmallCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

CREATE OR ALTER PROCEDURE dbo.GetLargeCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;
GO

Application or procedure logic can route requests to the appropriate path.

Advantages

Clear separation of workload patterns.
Allows independent tuning.
Easier to test each workload type.
Supports different indexes, hints, or strategies.

Disadvantages

Increases code and maintenance.
Requires reliable classification logic.
Classification thresholds may change as data grows.
Adds application or procedural complexity.

Solution 8: Query Store Plan Forcing

If Query Store contains a stable historical plan, it can be forced.

EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id = 456;
GO

Advantages

Fast production stabilization.
Does not require application-code changes.
Easy to reverse.
Uses a previously captured execution plan.

Disadvantages

One forced plan may not suit all parameters.
It may hide the parameter-sensitive nature of the query.
The plan may become unsuitable after data growth.
Plan forcing may fail after schema or index changes.
It is often a mitigation rather than a permanent fix.

Plan forcing is appropriate when one plan is consistently acceptable across the workload.

It is risky when small and large parameters genuinely require different plans.

Solution 9: Query Store Hints

Query Store hints can apply supported query hints without modifying application code.

For example, a Query Store hint can apply OPTION (RECOMPILE):

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints = N'OPTION(RECOMPILE)';
GO

Or apply OPTIMIZE FOR UNKNOWN:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints =
        N'OPTION(OPTIMIZE FOR UNKNOWN)';
GO

To remove the hint:

EXEC sys.sp_query_store_clear_hints
    @query_id = 123;
GO

Advantages

Does not require changing application code.
Easy to apply and reverse.
Useful during production incidents.
Can support controlled testing.

Disadvantages

Still requires careful monitoring.
Not every query hint is supported.
Can be forgotten if not documented.
May become inappropriate after workload changes.

Solution 10: Parameter Sensitive Plan Optimization

Modern SQL Server versions can support Parameter Sensitive Plan Optimization, commonly referred to as PSP optimization.

Instead of using one plan for every parameter value, SQL Server may maintain multiple plan variants for different parameter ranges.

Conceptually, SQL Server can create:

One plan for low-selectivity values
Another plan for medium-selectivity values
Another plan for high-selectivity values

For example:

Customer with 5 orders
    → Small-result plan

Customer with 50,000 orders
    → Medium-result plan

Customer with 5,000,000 orders
    → Large-result plan

This directly addresses the one-plan-fits-all limitation.

Dispatcher Plan

PSP optimization can use a dispatcher expression to determine which plan variant should be selected for the current parameter value.

The dispatcher routes the query to the appropriate plan based on parameter boundaries derived from statistics.

Advantages

Supports multiple plans for one parameterized query.
Reduces the need for manual recompilation.
Improves performance for skewed data.
Preserves plan reuse.
Handles different parameter groups automatically.

Considerations

PSP optimization is not a replacement for:

Accurate statistics
Appropriate indexing
Good query design
Query Store monitoring
Workload testing

It may also not apply to every query or every parameter-sensitive scenario.

Why Clearing the Plan Cache Is Not a Real Fix

During troubleshooting, administrators may run commands such as:

DBCC FREEPROCCACHE;
GO

This may temporarily improve performance because SQL Server compiles a new plan.

However, clearing the entire plan cache is not a good production solution.

It affects every cached plan on the SQL Server instance and may cause:

A compilation storm
Increased CPU usage
Temporary performance degradation
Loss of useful cached plans
Unpredictable workload behavior

Even clearing one procedure plan is only a troubleshooting step:

EXEC sys.sp_recompile
    N'dbo.GetCustomerOrders';
GO

The new plan may again be compiled for an unrepresentative parameter.

Recompilation proves that plan selection affects performance, but it does not necessarily resolve the underlying design problem.

Why Updating Statistics May Help

Parameter sniffing issues are often made worse by outdated or sampled statistics.

Update the relevant statistics:

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;
GO

Or update one statistic:

UPDATE STATISTICS dbo.Orders
    IX_Orders_CustomerId
WITH FULLSCAN;
GO

Improved statistics can help SQL Server better understand data distribution.

However, updating statistics may also cause recompilation.

The newly compiled plan could be better or worse depending on the next parameter value.

Statistics updates are therefore important, but they are not always a complete solution for highly skewed workloads.

Indexing and Parameter Sniffing

An indexing problem can look like a parameter sniffing problem.

For example, this index:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId
ON dbo.Orders (CustomerId);
GO

may require Key Lookups for:

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId;

For five rows, the Key Lookup may be inexpensive.

For one million rows, it may be extremely costly.

A covering index may help:

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
ON dbo.Orders (CustomerId)
INCLUDE
(
    OrderDate,
    TotalAmount
);
GO

However, indexes have costs:

Additional storage
Increased write overhead
More maintenance
Longer index-rebuild operations
Potential plan changes

The correct solution may involve both plan management and index improvement.

Parameter Sniffing and Optional Search Parameters

Search procedures with optional filters are especially vulnerable to unstable plans.

Consider:

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerId INT = NULL,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE
        (
            @CustomerId IS NULL
            OR CustomerId = @CustomerId
        )
        AND
        (
            @StartDate IS NULL
            OR OrderDate >= @StartDate
        )
        AND
        (
            @EndDate IS NULL
            OR OrderDate < DATEADD
            (
                DAY,
                1,
                @EndDate
            )
        );
END;
GO

Different parameter combinations may require completely different plans.

Examples include:

EXEC dbo.SearchOrders
    @CustomerId = 10;

EXEC dbo.SearchOrders
    @StartDate = '2026-01-01',
    @EndDate = '2026-12-31';

EXEC dbo.SearchOrders;

The first execution may require a selective Index Seek.

The second may require a date-range scan.

The third may return the entire table.

One cached plan is unlikely to be optimal for all combinations.

Dynamic SQL or separate query branches are often more appropriate for this design.

How to Choose the Correct Fix

Use the following guidance as a starting point.

Scenario	Possible Approach
Query runs infrequently and parameters vary greatly	`OPTION (RECOMPILE)`
One parameter value represents most executions	`OPTIMIZE FOR`
Generic stable performance is acceptable	`OPTIMIZE FOR UNKNOWN`
Distinct parameter groups require different logic	Separate branches or procedures
Optional search filters create many query shapes	Parameterized dynamic SQL
Application code cannot be changed	Query Store hints
A stable historical plan works for all values	Query Store plan forcing
Modern SQL Server with eligible parameter-sensitive query	PSP optimization
Estimates are wrong because of stale statistics	Update statistics
Key Lookups or scans are inherently expensive	Review indexes and query design

The correct choice should be based on measured performance rather than preference.

Recommended Investigation Process

A disciplined parameter sniffing investigation should follow these steps.

1. Confirm the Performance Variation

Collect:

Duration
CPU time
Logical reads
Physical reads
Execution count
Parameter values
Execution times

Do not assume parameter sniffing based only on one slow execution.

2. Compare Parameter Values

Identify whether slow and fast executions use different parameter patterns.

Determine whether those parameters return significantly different row counts.

3. Review the Execution Plan

Check:

Compiled parameter value
Runtime parameter value
Estimated rows
Actual rows
Index access
Join types
Memory grant
Parallelism
Spills
Key Lookups

4. Test Recompilation

Recompile the query or procedure and test different parameter values.

If performance changes depending on which parameter is used during compilation, parameter sensitivity is likely.

5. Review Query Store

Check whether the query has:

Multiple plans
Plan regressions
Unstable duration
Different CPU profiles
Different logical-read profiles

6. Test Candidate Solutions

Test solutions using representative values:

Small-result parameter
Medium-result parameter
Large-result parameter
Common parameter
Rare parameter
Peak concurrency

7. Measure the Overall Workload

Do not optimize one execution while making the wider workload worse.

Review:

Compilation CPU
Throughput
Blocking
Memory usage
Plan-cache usage
tempdb impact
Concurrent executions

8. Document the Decision

Record:

Affected procedure or query
Root cause
Parameter patterns
Baseline measurements
Selected solution
Deployment date
Rollback method
Review date

Production Checklist

Before applying a parameter sniffing fix:

Confirm that performance varies by parameter value.
Capture actual execution plans.
Review compiled and runtime parameter values.
Compare estimated and actual rows.
Verify statistics freshness.
Review relevant indexes.
Test several representative parameters.
Check Query Store history.
Measure compilation cost.
Define rollback steps.

After applying the fix:

Monitor duration and CPU time.
Monitor logical reads.
Check Query Store for new regressions.
Review execution count and plan usage.
Watch for compilation overhead.
Check memory grants and tempdb spills.
Validate application timeout rates.
Review the solution after data growth or deployment changes.

Common Mistakes

Mistake 1: Assuming Every Slow Procedure Has Parameter Sniffing

Slow performance may instead be caused by:

Missing indexes
Blocking
Outdated statistics
Storage latency
Memory pressure
Poor query design
Implicit conversions
tempdb contention

Parameter sniffing should be proven, not guessed.

Mistake 2: Clearing the Entire Plan Cache

This can cause server-wide instability and does not prevent the issue from returning.

Mistake 3: Forcing the Fastest-Looking Plan

A plan that is fast for one parameter may be poor for another.

Stability across representative values matters more than one successful test.

Mistake 4: Using Local Variables Automatically

Local variables may hide useful parameter information and create generic estimates.

Mistake 5: Ignoring Execution Count

A plan executed once should not automatically be preferred over a plan with thousands of stable executions.

Mistake 6: Treating Plan Forcing as Permanent

Forced plans require monitoring and periodic review.

Mistake 7: Testing with Only One Parameter

Parameter-sensitive queries must be tested using multiple data distributions.

Final Example: A Controlled Branching Strategy

Suppose customer 5000 represents a special high-volume account.

The procedure can route that workload separately:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;

    IF @CustomerId = 5000
    BEGIN
        SELECT
            OrderId,
            CustomerId,
            OrderDate,
            TotalAmount
        FROM dbo.Orders
        WHERE CustomerId = @CustomerId
        OPTION (RECOMPILE);

        RETURN;
    END;

    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;
GO

This is only an example.

In a real system, the routing decision should not normally depend on one hard-coded customer.

A better design may classify workloads based on:

Customer category
Estimated data volume
Tenant size
Date-range size
Business rules
Configuration data

The main idea is that clearly different workloads may deserve clearly different execution strategies.

Conclusion

Parameter sniffing is a normal SQL Server behavior that becomes problematic when one cached execution plan is reused across parameter values with significantly different data distributions.

The typical pattern is: