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

A SQL Server query may run quickly most of the time but become unexpectedly slow for certain requests. The query text has not changed, the database is still online, and the application may not have been updated. Yet a request that normally finishes in milliseconds can suddenly take several seconds.

One possible cause is parameter sniffing.

SQL Server can use the parameter values supplied during query compilation to create an execution plan. That plan may work well for one set of values but perform poorly for another. When SQL Server reuses the same plan for different parameter values, the performance difference can become noticeable.

Parameter sniffing is not always a problem. It helps SQL Server avoid compiling a new execution plan for every execution. The problem occurs when a cached plan is unsuitable for the values used by later requests.

In this article, we will learn how parameter sniffing works, how to identify it, and how to choose a suitable fix without unnecessarily disabling execution plan reuse.

What Is Parameter Sniffing in SQL Server?

Consider a table containing millions of orders. Most customers have only a few orders, but a small number of large customers have hundreds of thousands.

An application might use a parameterized query to retrieve orders for a customer:

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;

When SQL Server compiles this stored procedure, it can use the current parameter value and available statistics to estimate how many rows the query will return.

Suppose the first execution requests a customer with only a few orders. SQL Server might choose an index seek followed by key lookups because it expects to retrieve only a small number of rows.

Later, another request uses a customer with hundreds of thousands of orders. The same plan may require many lookups and become inefficient. A scan or a different access strategy might have been more suitable for that parameter value.

This is one way parameter sniffing can cause performance problems.

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

The important point is that the best execution plan can depend on the values supplied to the query.

How SQL Server Uses Parameters and Execution Plans

SQL Server does not normally compile every parameterized query from scratch on every execution. It can cache and reuse execution plans to reduce compilation overhead.

A simplified sequence looks like this:

An application executes a parameterized query or stored procedure.
SQL Server checks whether a suitable execution plan is available.
If a plan must be compiled, SQL Server uses query information, statistics, and parameter values where applicable.
SQL Server stores the compiled plan in the plan cache when appropriate.
Later executions may reuse that plan with different parameter values.

Parameter sniffing occurs during compilation, but the performance problem usually becomes visible when the resulting plan is reused for parameter values with different data distributions.

This behavior is particularly relevant when a table has uneven data distribution, also known as data skew.

Step 1: Check Whether Parameter Sniffing Is the Real Problem

Do not assume that every slow query is caused by parameter sniffing. Missing indexes, outdated statistics, blocking, excessive logical reads, poor joins, and resource pressure can produce similar symptoms.

Start by comparing executions with different parameter values.

For the stored procedure above, run:

EXEC dbo.GetCustomerOrders
    @CustomerId = 101;

EXEC dbo.GetCustomerOrders
    @CustomerId = 9001;

Choose IDs that represent different data volumes in your database. The values shown here are examples, not assumptions about your actual data.

In SQL Server Management Studio, enable the actual execution plan and inspect the results. You can also use SET STATISTICS IO and SET STATISTICS TIME to compare logical reads and execution time.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

EXEC dbo.GetCustomerOrders
    @CustomerId = 101;

EXEC dbo.GetCustomerOrders
    @CustomerId = 9001;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Look for differences between estimated and actual row counts, unexpected scans, expensive key lookups, and large differences in logical reads.

A large gap between estimated and actual rows can indicate a cardinality-estimation problem, but it does not prove that parameter sniffing is the cause.

For a more reliable diagnosis, compare the actual execution plans and test both parameter values under controlled conditions. Query Store can also help identify plan changes and compare query performance over time.

Step 2: Understand the Difference Between a Good and a Bad Plan

SQL Server chooses an execution plan based on the estimated cost of available operations.

For a query that returns a small number of rows, an index seek may be efficient. For a query that returns a large portion of a table, scanning the relevant data may be cheaper than performing many individual lookups.

The following table illustrates the general trade-off.

Execution strategy	Often suitable when	Potential problem
Index seek	The query retrieves a small subset of rows	Repeated lookups can become expensive for many rows
Index scan	The query needs many rows from an index	Can read unnecessary pages for a selective query
Table or clustered index scan	A large portion of the table is required	Can be expensive when only a few rows are needed
Repeated key lookups	The number of matching rows is small	Many lookups can increase logical reads significantly

These are general patterns, not fixed rules. The best plan depends on the query, indexes, data distribution, statistics, and other workload conditions.

Before changing the query, verify that the required indexes exist and that the statistics used by SQL Server are suitable for the data.

Step 3: Use Query Store to Investigate Plan Changes

Query Store records query text, execution plans, and runtime statistics, depending on its configuration and retention settings.

It is useful when a query was fast earlier but has become slow, particularly when SQL Server has started using a different plan.

Check whether Query Store is enabled for the database and review the affected query's performance history.

You can also inspect cached execution plans using dynamic management views, but plan-cache information is temporary and can disappear when plans are evicted or the cache is cleared.

When investigating parameter sniffing, compare:

Execution plans used by the query.
Runtime duration and logical reads.
Estimated and actual row counts.
Parameter values used during testing, where available.
Statistics and indexes relevant to the query.

Avoid clearing the entire plan cache as a routine troubleshooting technique. It can force many unrelated queries to recompile and create additional load across the database.

Step 4: Fix Parameter Sniffing with the Least Disruptive Option

There is no single fix that works for every parameter-sniffing problem. Choose the solution based on the query's behavior and the application's requirements.

Option 1: Use OPTION (RECOMPILE)

When a query's parameter values produce very different execution requirements, recompiling the statement can allow SQL Server to optimize it for the current execution.

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;

The OPTION (RECOMPILE) hint tells SQL Server to compile a fresh plan for this statement rather than reusing its cached statement plan.

This can improve performance when parameter values vary substantially, but compilation consumes CPU resources. It may be a poor choice for frequently executed queries where plan reuse is otherwise effective.

Use it for the affected statement rather than adding it everywhere without measurement.

Option 2: Use OPTIMIZE FOR UNKNOWN

Another option is to tell SQL Server to optimize the query without relying on the current parameter value for the usual parameter-sniffing estimate.

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

This can produce a plan based on average or density-based estimates rather than optimizing specifically for the current parameter value.

It may help when the plan created for one specific parameter is unsuitable for the broader workload.

However, a plan based on average estimates may still be poor when the data distribution is highly skewed. Test representative parameter values before using this option in production.

Option 3: Use a Targeted Plan Hint

If investigation shows that a particular execution plan performs well for the workload, SQL Server provides ways to influence plan selection.

For example, a query-level hint can force a known plan in supported scenarios. Query Store also supports plan forcing for eligible queries.

Plan forcing should be used carefully. Data distributions, indexes, statistics, and database versions can change, making a previously good plan less suitable.

Monitor the query after applying the hint and keep a documented rollback plan.

Option 4: Review Indexes and Statistics

Sometimes the apparent parameter-sniffing problem is caused or made worse by poor statistics or missing indexes.

For example, if SQL Server estimates that a customer has 20 orders but the query actually returns 200,000, the optimizer may select an unsuitable plan.

Check whether the relevant statistics are current and whether the query has an appropriate index.

An index on CustomerId may help the example query, but the correct index design depends on the selected columns, ordering requirements, table size, and write workload.

Do not create indexes blindly. Additional indexes consume storage and can increase the cost of inserts, updates, and deletes.

Step 5: Check Whether SQL Server 2022 or Later Features Help

SQL Server's Intelligent Query Processing features include Parameter Sensitive Plan optimization in supported versions and configurations.

Parameter Sensitive Plan optimization can allow SQL Server to maintain multiple plan variants for a parameterized query when different parameter ranges require different plans.

This is useful for eligible queries with significant parameter sensitivity. It is not a guarantee that every parameter-sniffing problem will disappear.

Check the SQL Server version, database compatibility level, relevant configuration, and Query Store information before relying on this feature. Test the actual workload to determine whether SQL Server is generating and using appropriate plan variants.

For applications on older SQL Server versions, targeted query tuning or recompilation may still be necessary.

Common Mistakes to Avoid

Disabling parameter sniffing for the entire database

A database-wide change can affect many queries that benefit from parameter sniffing. Apply broad configuration changes only when workload-wide evidence supports them.

Assuming OPTION (RECOMPILE) is always faster

Recompilation can improve plan quality but increases compilation overhead. Measure both execution and compilation costs.

Forcing a plan without monitoring it

A forced plan may become inefficient as data and workload patterns change. Monitor query performance and review the hint periodically.

Clearing the plan cache to solve one slow query

This can cause unrelated queries to recompile and temporarily increase CPU usage. Use targeted plan management and investigate the affected query directly.

Ignoring statistics and indexing

A plan problem can be caused by poor cardinality estimates or an unsuitable index design. Address the underlying issue rather than immediately applying a query hint.

Troubleshooting Parameter Sniffing

Symptom	What to investigate
Query performance changes significantly by parameter	Compare actual execution plans and logical reads
Estimated row count differs greatly from actual rows	Review statistics, data skew, and cardinality estimates
Query became slow after a plan change	Review Query Store plan history
Recompilation improves performance but increases CPU	Measure compilation overhead and execution frequency
A forced plan works initially but becomes slower later	Review data growth, statistics, and workload changes

Best Practices for Production Databases

Capture actual execution plans for representative parameter values.
Compare logical reads and runtime statistics instead of relying only on elapsed time.
Use Query Store to investigate performance changes over time.
Check indexes and statistics before applying query hints.
Apply OPTION (RECOMPILE) only when its benefits justify the compilation cost.
Test OPTIMIZE FOR UNKNOWN against the actual distribution of parameter values.
Avoid database-wide changes when a query-level solution is sufficient.
Monitor forced plans and review them as the workload changes.
Test fixes with both common and unusual parameter values.
Document the reason for each hint and how to reverse it if performance deteriorates.

Advantages and Disadvantages of Parameter Sniffing

Advantages

Allows SQL Server to reuse execution plans.
Can reduce repeated compilation overhead.
Can produce efficient plans when parameter values and data distribution are representative.
Helps the optimizer use information available during compilation.

Disadvantages

A cached plan may be inefficient for later parameter values.
Skewed data distributions can create large performance differences.
Diagnosing the problem requires comparing plans and runtime behavior.
Some fixes, such as frequent recompilation, introduce their own overhead.

Summary

Parameter sniffing is a normal SQL Server optimization behavior that becomes a problem when a cached execution plan does not suit later parameter values.

To diagnose it, compare execution plans and logical reads for different inputs, inspect Query Store history, and check statistics and indexes. Then choose a targeted fix, such as recompilation, OPTIMIZE FOR UNKNOWN, plan management, or an appropriate indexing change.

The goal is not to eliminate parameter sniffing everywhere. It is to ensure that the affected query performs reliably across the parameter values your application actually uses.