If a SQL Server query is fast for some parameter values and slow for others, parameter sniffing may be involved—but a slow run by itself does not prove it. SQL Server normally uses parameter values during compilation to choose a plan it can reuse. Trouble arises when that plan performs poorly for inputs with a materially different data distribution. OPTION (RECOMPILE) can make the optimizer compile for the current execution, but it also adds compilation work, so it is best considered for a demonstrated, specific problem rather than applied by default.
What is SQL Server parameter sniffing?
When SQL Server compiles a parameterized statement, it can use the parameter values available at compilation to estimate how many rows a query will process and choose an execution plan. SQL Server can then reuse that plan for later executions. This is normal plan reuse, not inherently a fault.
As an Amazon Associate I earn from qualifying purchases.
It becomes a parameter-sensitive performance problem when the plan selected using one set of values is reused for different values whose data distribution or expected row counts call for a substantially different plan. For example, one value might match a small number of rows while another matches a large share of a table. A plan well suited to one case may be inefficient for the other.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Repair Windows errors before they cause bigger problemsFix Now →Why is a query slow for some parameter values but fast for others?
Parameter sensitivity is one possible explanation, but slowness alone is not enough to identify it. Compare executions using representative values and look for a repeatable relationship between the input values and runtime or resource use. Inspect actual execution plans and Query Store history when available. The relevant question is whether the values that perform poorly are being served by a plan that is a poor fit for those executions.
#1 Best Overall
Microsoft describes targeted removal of a plan from the cache as a diagnostic indication: if the query improves after recompilation, parameter sensitivity may be involved. That is evidence to investigate, not a lasting remedy. Avoid clearing the whole plan cache casually; doing so forces plans to be compiled again and can cause one-time longer execution times. Where appropriate, use the specific plan handle rather than flushing the cache globally. Microsoft’s parameter-sensitive plan troubleshooting guidance explains this diagnostic approach.
What should you check before adding a hint?
- Verify the pattern. Compare representative parameter values, execution behavior, actual plans, and Query Store records where available. Do not treat a general report of slowness as proof of parameter sniffing.
- Check statistics and indexes. Review whether statistics reflect the current data distribution and whether statistics or index maintenance is needed. Microsoft recommends this before evaluating Query Store hints. Query Store hints guidance
- Confirm engine version and database compatibility level. SQL Server 2022 (16.x) Parameter Sensitive Plan (PSP) optimization requires compatibility level 160 in the documented guidance and is on by default starting at that level. Confirm the actual version and compatibility level for the database; Azure SQL products have their own applicable feature scope. If PSP is eligible, test the compatibility change and inspect Query Store for dispatcher and query variant plans. Microsoft’s PSP optimization documentation
- Compare interventions with realistic inputs. Test across the values and execution frequency the workload actually sees. A fix that helps one value can make another worse, and compilation itself consumes resources.
When should you use OPTION (RECOMPILE)?
Consider OPTION (RECOMPILE) when investigation shows that one statement’s reusable plan is a poor fit across parameter values and compiling for each execution is likely to save more execution cost than it adds in compilation work. With the hint, SQL Server compiles that statement using the current execution’s parameter values rather than relying on a reused plan.
Prefer statement-level recompilation when one statement is the problem. Recompiling an entire stored procedure on every execution is a broader intervention and should not be the default response. Estimate the trade-off using the query’s call frequency, compilation cost, and the distribution of values it receives—not just a single fast or slow run. A query-level RECOMPILE hint also prevents PSP from operating on that query, so weigh it against PSP where both are relevant. Microsoft’s query hint reference
How do the main alternatives compare?
| Option | How it behaves | Scope and trade-off |
|---|---|---|
OPTION (RECOMPILE) |
Compiles the statement using the current execution’s parameter values. | Statement scope; adds compilation work on each execution. Can prevent PSP for that query. |
| PSP optimization | For eligible parameter-sensitive queries, supports multiple active plans rather than relying on one plan for every parameter case. | Requires SQL Server 2022 (16.x) or applicable Azure SQL offering and, in the documented SQL Server guidance, compatibility level 160. Check dispatcher and variant plans in Query Store. |
OPTIMIZE FOR (@parameter = value) |
Optimizes using a chosen representative value. | Can suit a workload with a known representative case, but that choice may not suit other values or remain representative as data changes. |
OPTIMIZE FOR UNKNOWN |
Uses density-vector average estimates rather than optimizing for the current sniffed value. | May provide a more general plan, but an average estimate is not necessarily suitable for strongly skewed inputs. |
| Disable parameter sniffing | Uses a more generic planning approach instead of plans tailored to sniffed parameter values. | Can trade a parameter-specific plan for a generic one; disabling sniffing can also disable PSP for associated workloads or contexts. |
| Targeted plan-cache eviction | Causes the affected plan to be compiled again on a subsequent execution. | Useful as a targeted diagnostic or temporary action, not a durable solution to recurring parameter sensitivity. |
| Query Store hint | Applies supported plan behavior through Query Store without changing application query text. | Availability and constraints depend on the exact SQL Server or Azure product and version. Microsoft notes that Query Store RECOMPILE hints are not supported when database parameterization is forced. |
These choices are not universally interchangeable. Judge them by plan quality across skewed values, compilation cost and execution frequency, scope, whether application changes are possible, version and PSP eligibility, and how easily the change can be monitored, removed, and retested.
Rank #3
How should you manage a Query Store hint?
A Query Store hint can be useful when changing application code is not practical and the target environment supports the desired hint. Treat it as a scoped intervention: test it before production use, check whether the hint is being applied, and plan how to remove or revise it. Microsoft advises revisiting hints as data volume or distribution changes and during database migrations; a previously suitable hint can become stale. Consult the product-specific guidance for support and restrictions, including the forced-parameterization limitation for Query Store RECOMPILE hints. Query Store hints guidance
Does SQL Server recompile automatically?
Yes. SQL Server can recompile statements for engine-related reasons, including changes to cardinality estimates after statistics updates. That is different from forcing recompilation proactively on every execution. Microsoft’s sp_recompile reference notes that automatic recompilation can occur; a forced recompilation strategy is not usually necessary simply because a query has a cached plan. Microsoft’s sp_recompile reference
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problems
