SQL Server Parameter Sniffing: Why Queries Become Slow and How to Fix Them

Have you ever seen a SQL Server query run quickly in one situation but become very slow when the application passes a different parameter? The query has not changed, the database is still running, and yet the response time is completely different.

One possible reason is parameter sniffing.

SQL Server can use the parameter values supplied during compilation to choose an execution plan. That plan may work well for some values but perform poorly for others. When SQL Server reuses the cached plan, a query that was previously fast can become slow for a different parameter value.

Parameter sniffing is not always a problem. In fact, it often helps SQL Server choose efficient execution plans. The problem occurs when the selected plan does not perform well for the range of values the application uses.

In this article, we will understand why parameter sniffing happens, how to identify it, and which solutions are worth considering before changing your queries.

What Is Parameter Sniffing in SQL Server?

Suppose you have an Orders table containing millions of records. Most customers have only a few orders, but a small number of customers have thousands.

Your application runs this query:

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;

When SQL Server compiles the stored procedure, it can use the supplied @CustomerId value to estimate how many rows the query will return.

Based on that estimate, SQL Server chooses an execution plan. It may use an index seek with lookups, an index scan, or another strategy depending on the available indexes, statistics, and estimated costs.

The selected plan can then be cached and reused for later executions.

Imagine the first execution uses a customer with only three orders. SQL Server chooses a plan that works well for a small result set. Later, the application requests a customer with 50,000 orders. The same plan might now perform poorly.

This is one example of parameter sniffing.

The reverse can also happen. A plan compiled for a customer with many orders might be inefficient for customers with only a few records.

The important point is that the best execution plan can depend on the parameter value.

Why Parameter Sniffing Happens

Several factors can contribute to parameter-sensitive query performance.

1. Data Is Not Distributed Evenly

Some customers may have very few orders, while others have many. A query that returns ten rows for one parameter might return thousands for another.

When SQL Server estimates the number of rows incorrectly for a later execution, the cached plan may no longer be a good choice.

2. SQL Server Reuses Execution Plans

Compiling a query has a cost, so SQL Server can reuse execution plans from its plan cache.

Plan reuse is usually beneficial. However, a cached plan is not necessarily optimal for every possible parameter value.

3. Index Choices Depend on Estimated Rows

An index seek may work well when a query returns a small number of rows. A scan may be more efficient when a large portion of a table is required.

The optimizer considers several factors when choosing a plan. If the estimates are unsuitable for the current parameter, the chosen access method or join strategy may also be unsuitable.

4. Statistics May Not Represent the Data Well

SQL Server uses statistics to estimate row counts and selectivity.

Outdated statistics, uneven data distributions, and complex predicates can affect these estimates. However, updating statistics does not automatically solve every parameter sniffing problem.

How to Identify Parameter Sniffing

Before changing the query, confirm that parameter-sensitive plan reuse is actually causing the problem.

Step 1: Compare Execution Times for Different Parameters

Run the stored procedure with parameter values that return different numbers of rows.

EXEC dbo.GetCustomerOrders @CustomerId = 10;

EXEC dbo.GetCustomerOrders @CustomerId = 5000;

These values are illustrative. Use real parameter values from your application or test data.

Compare execution time, logical reads, CPU usage, and the number of rows returned.

If performance changes significantly between parameter values, investigate further. Different execution times alone do not prove parameter sniffing because blocking, storage latency, concurrent workloads, and other factors can produce similar symptoms.

Step 2: Review the Actual Execution Plan

In SQL Server Management Studio, enable the actual execution plan and run the query.

Look for differences between estimated and actual row counts. For example, an operator might estimate that a query returns a few rows but actually process thousands.

Pay attention to:

• Index seeks and scans.

• Key lookups.

• Join algorithms.

• Memory grants and spills.

• Large differences between estimated and actual rows.

A large estimation error is a useful clue, but it does not independently prove that parameter sniffing is responsible.

Step 3: Check Query Store

Query Store records query performance information and execution plans when it is enabled and configured appropriately.

It can help you compare plans, identify performance changes, and determine whether a query became slower after a different plan was introduced.

For example, you can inspect query statistics with a query like this:

SELECT TOP (20)
    q.query_id,
    p.plan_id,
    rs.count_executions,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads
FROM sys.query_store_query AS q
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

This example provides a starting point for investigation. Query Store runtime statistics are stored across intervals, so a more detailed analysis should account for interval boundaries, execution counts, and the relevant time range.

If the same query has multiple plans with noticeably different performance, investigate why those plans were selected and which parameter values were involved.

How to Fix Parameter Sniffing

There is no single fix that works for every query. The right solution depends on how much parameter values affect the execution plan and how frequently the query runs.

Solution 1: Use OPTION (RECOMPILE)

SQL Server can compile a statement again for each execution using OPTION (RECOMPILE).

CREATE OR ALTER PROCEDURE dbo.GetCustomerOrders
    @CustomerId INT
AS
BEGIN
    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;

This allows the optimizer to build a plan using the current parameter value rather than reusing the statement's previous cached plan.

It can help when parameter values have very different data distributions.

However, recompilation adds CPU overhead. For a frequently executed query, compiling on every call may cost more than it saves.

Use it selectively and measure the results.

Solution 2: Use OPTIMIZE FOR

The OPTIMIZE FOR hint allows you to influence the parameter value used for optimization.

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

In this example, SQL Server optimizes the query as though the parameter value were 10.

This can help when a representative value produces a plan that performs well for the application's typical workload.

The risk is that the selected value may not represent future requests. If the data distribution or workload changes, the plan may become less suitable.

Use this hint only after testing a representative range of parameter values.

Solution 3: Use OPTIMIZE FOR UNKNOWN

Another option is to tell SQL Server not to optimize for a specific runtime parameter value.

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

This approach uses average selectivity assumptions from statistics rather than optimizing for a particular parameter value.

It can produce a more balanced plan when no single parameter value represents the workload well.

However, an average estimate can also be poor when the data distribution is highly uneven. Test it against both common and unusual parameter values before using it in production.

Solution 4: Consider Parameter Sensitive Plan Optimization

SQL Server includes Parameter Sensitive Plan (PSP) optimization in SQL Server 2022 and later, when the relevant compatibility level and feature requirements are met.

PSP optimization can allow SQL Server to maintain multiple plan variants for eligible parameter-sensitive queries instead of relying on one plan for every parameter value.

This can reduce the need for manual hints in supported scenarios.

However, not every query qualifies for PSP optimization, and its behavior depends on the query, database compatibility level, and SQL Server configuration. Check the actual execution plans and Query Store before assuming that PSP is handling a particular query.

Solution 5: Review Indexes and Statistics

Sometimes the problem is not only parameter sniffing. Missing indexes, outdated statistics, or inefficient query design can make a query slow regardless of which plan is selected.

Review existing indexes and the query's access pattern before adding new indexes.

You can update statistics when there is evidence that the statistics are outdated:

UPDATE STATISTICS dbo.Orders;

This updates statistics for the table, but it is not a universal solution. Statistics updates can cause plan changes, and unnecessary updates add maintenance overhead.

Use an appropriate statistics maintenance strategy and verify that the resulting estimates and execution plans improve.

Comparing Parameter Sniffing Solutions

Common Mistakes to Avoid

Clearing the Entire Plan Cache

Clearing the plan cache may temporarily change query behavior, but it affects unrelated queries and can cause additional compilation work.

Investigate the affected query and its plans instead of treating a global cache clear as a permanent solution.

Adding Indexes Without Checking the Execution Plan

More indexes do not automatically improve performance. They consume storage and add work to insert, update, and delete operations.

Use actual execution plans and workload evidence to determine whether an index is needed.

Applying RECOMPILE to Every Query

Recompilation can be useful for parameter-sensitive statements, but applying it everywhere wastes CPU resources.

Measure the cost of compilation and execution before adopting this approach.

Testing Only One Parameter Value

A fix that works for a small customer may fail for a customer with thousands of orders.

Test representative values across the data distribution, including common values and unusually large result sets.

Best Practices for Production Applications

1. Capture actual execution plans before changing query hints.

2. Compare logical reads, CPU time, duration, and row estimates.

3. Use Query Store to investigate plan changes and performance regressions.

4. Test parameter values that return very different numbers of rows.

5. Review statistics and indexes before forcing a particular execution strategy.

6. Apply recompilation or optimization hints only when the evidence supports them.

7. Evaluate PSP optimization when using a compatible SQL Server version.

8. Monitor query performance after deployment to confirm that the fix works under real workloads.

9. Keep a rollback plan when changing indexes, statistics maintenance, or query hints.

10. Avoid assuming that every slow parameterized query has a parameter sniffing problem.

Advantages and Disadvantages of Parameter Sniffing

Advantages

• Allows SQL Server to optimize a query using actual parameter values during compilation.

• Helps reuse execution plans and avoid unnecessary compilation.

• Can produce efficient plans when the compiled parameter represents the workload well.

Disadvantages

• A cached plan may perform poorly for parameters with different data distributions.

• Performance can vary significantly between executions.

• Diagnosing the problem may require execution plans, Query Store analysis, and workload testing.

• Some fixes introduce trade-offs, such as recompilation overhead or less accurate average estimates.

Summary

Parameter sniffing is a normal part of SQL Server's query optimization process. It becomes a problem when a cached execution plan performs poorly for other parameter values.

To diagnose it, compare different parameter values, review actual execution plans, and use Query Store to investigate plan changes. Then choose a solution based on the evidence.

OPTION (RECOMPILE), OPTIMIZE FOR, OPTIMIZE FOR UNKNOWN, PSP optimization, and improvements to indexes or statistics can all help in the right circumstances.

The most reliable approach is to understand the data distribution and test the query with representative values before making changes. This helps improve performance without introducing unnecessary compilation overhead or creating new problems for other queries.