Why the Same Query Is Fast Then Suddenly Slow

With parameter sniffing, SQL Server uses a parameter value while compiling a reusable plan. That is normally helpful, but a plan chosen for one part of a skewed distribution can perform poorly for another.

Reuse Is the Starting Point

Compiling a plan costs work, so reuse is valuable. At compilation, SQL Server can use the current parameter value to estimate how many rows the request will find. Later executions can reuse that plan. The first value matters when it shapes a plan that survives in cache.

The troublesome case is parameter sensitivity. One value can match few rows while another matches many. A seek with lookups can be appropriate for one request and expensive for another. Don’t call all sniffing a bug because one reused plan made a poor choice.

SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name IN
    (N'PARAMETER_SNIFFING', N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION');

Prove the Difference

Capture the slow request’s parameter values and types. Compare its plan with a representative fast execution. Check compiled and runtime parameter information where available. Also check blocking and resource pressure, because a query can become slow without any change to its plan.

Query Store helps show when plans and runtime behavior changed. Keep the comparison within meaningful workload periods. A fast run against warm data and a slow run during a large load don’t isolate parameter sensitivity. You need a reproduction that changes the parameter while holding other important conditions steady.

SELECT TOP (20)
    query_hash, query_plan_hash,
    execution_count, creation_time, last_execution_time,
    min_elapsed_time / 1000.0 AS min_elapsed_ms,
    max_elapsed_time / 1000.0 AS max_elapsed_ms
FROM sys.dm_exec_query_stats
ORDER BY max_elapsed_time DESC;

A large range is a candidate, not proof. Cached statistics cover completed executions for the surviving plan. They don’t retain every parameter value. Use them to choose what to inspect, then gather evidence that connects the distribution to the plan choice.

Remedy One: Recompile the Statement

OPTION (RECOMPILE) lets a statement compile for its current values instead of reusing its previous plan. That can help when executions need substantially different strategies. The cost is repeated compilation. Evaluate that cost at the statement’s real execution frequency.

Prefer a targeted statement-level decision over recompiling everything without evidence. The following uses a built-in catalog as a harmless syntax example. It doesn’t demonstrate a measured parameter-sensitive workload. Replace it with the representative statement in your test environment.

DECLARE @type char(2) = 'U';
SELECT name, type_desc
FROM sys.objects
WHERE type = @type
OPTION (RECOMPILE);

Remedy Two: Choose a General Estimate

OPTIMIZE FOR UNKNOWN asks for an estimate that doesn’t depend on the current parameter value in the usual sniffed way. It can produce a steadier compromise. It can also produce a plan that is mediocre for important values. Predictable isn’t automatically acceptable.

DECLARE @type char(2) = 'U';
SELECT name, type_desc
FROM sys.objects
WHERE type = @type
OPTION (OPTIMIZE FOR UNKNOWN);

Test both common and uncommon values rather than checking only the request that first raised the complaint. A chosen literal with OPTIMIZE FOR is another deliberate policy, but it depends on that value remaining representative. Record why the selected estimate is appropriate.

Remedy Three: Separate Different Workloads

When requests represent meaningfully different shapes, separate statements or procedures can give each shape its own compilation path. Parameterized dynamic SQL can also express distinct predicate combinations. Keep values as parameters. Concatenating user input trades a tuning problem for a security problem.

Simply placing an IF around the same procedure isn’t a guarantee that compilation follows your intended separation. Test the resulting plans. Maintain the branching logic as business behavior changes. More code can be justified, but it becomes another thing the team must understand.

This approach is useful when the application knows something the optimizer cannot infer from one generic request. It also gives you separate evidence for each path. Don’t create dozens of branches merely to preserve a complicated query that should be simplified.

Remedy Four: Use Supported Plan Variants

SQL Server 2022 introduced Parameter Sensitive Plan optimization for eligible workloads under the required compatibility settings. It can maintain multiple variants for different parameter ranges. SQL Server 2025 extends the feature. Check eligibility and the actual dispatcher and variant plans rather than assuming every query qualifies.

This keeps reuse while allowing more than one strategy. It isn’t a promise that all skew or optional search patterns are solved. Read the documentation for your release and inspect why a query was or wasn’t considered. Query hints can also change eligibility.

A known good forced plan can provide temporary relief, but must still serve the values it receives. Keep that limitation visible. I choose a remedy only after testing the important parameter shapes and measuring the resulting workload, including compilation.

Parameter sniffing is not automatically a defect, it is a useful optimization that needs the right reuse boundary.

This post was rewritten from scratch in September 2026. The original, published on 2013-12-01, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.