When the same SQL Server statement is fast for some parameter values and slow for others, the cause may be parameter sensitivity: SQL Server compiled a cached plan for one value and later reused it for values with a very different number or distribution of matching rows. Compare representative executions before changing anything; one slow run is not enough to diagnose parameter sniffing. On SQL Server 2022 (16.x) and later, first check whether Parameter Sensitive Plan (PSP) optimization can handle the query at database compatibility level 160.
What parameter sniffing is—and when it becomes a problem
During compilation, SQL Server can use parameter values to estimate how many rows a statement will process and choose an execution plan. Reusing a cached plan is normal and often avoids unnecessary compilation. The trouble arises when the plan that suits the compile-time value performs poorly for later values because the data distribution is uneven. That performance condition is more precisely called parameter sensitivity; a parameter-sensitive plan (PSP) is a plan affected by it. Microsoft describes parameter sensitivity as a possible query-performance bottleneck in its overview of detectable query performance bottlenecks.
Do not assume every slow statement has this cause. Blocking, I/O pressure, stale statistics, inadequate indexes, and broader resource pressure can also make a query slow. A plan change after recompilation can be a useful clue, but it does not replace checking the query’s history and competing explanations. Microsoft’s SQL Server high-CPU troubleshooting guidance discusses parameter-sensitive plans and ways to investigate them.
Diagnose the statement before choosing a fix
- Identify the exact statement and its performance history. Use Query Store, when available, to compare runtime history and plans for the statement. Record the SQL Server version and build, the database compatibility level, the actual SQL text, and representative parameter values. Query Store can help surface plan and performance changes; see Microsoft’s Query Store Hints documentation.
- Compare materially different inputs. Include values that return very different row counts or access differently distributed data. For each, compare actual and estimated rows in the execution plan, and assess whether the chosen access path and join strategy suit that execution. The relevant signal is a repeatable mismatch across inputs, not simply a high duration on one call.
- Check other likely causes. Review statistics and indexes, and investigate blocking, I/O, and resource pressure before adding a hint. Statistics or index maintenance may address a problem that otherwise looks like a plan-selection issue; Microsoft includes these considerations in its Query Store Hints guidance.
- Check version and compatibility before selecting a remedy. These queries show the current server version string and the compatibility level for the connected database:
SELECT @@VERSION; SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();Do not assume a SQL Server upgrade also changed a database’s compatibility level.
- Use cache removal only as a targeted diagnostic. Recompiling after removal of one identified cached plan can help test whether a different compile-time value changes the plan and performance. Microsoft notes that clearing cached plans can indicate a parameter-sensitive problem, but clearing the entire cache removes all compiled plans. Prefer a known plan handle or SQL handle when you understand the immediate compile impact; do not treat broad cache clearing as a permanent repair. See Microsoft’s high-CPU troubleshooting guidance.
Check whether SQL Server can use PSP optimization
Parameter Sensitive Plan optimization is available in SQL Server 2022 (16.x) and later, as well as Azure SQL Database and Azure SQL Managed Instance. For SQL Server 2022, the database must use compatibility level 160. PSP is on by default starting at that compatibility level and can maintain multiple active plans for eligible parameterized queries, rather than forcing one cached plan to serve every qualifying value range. Microsoft’s database-scoped configuration reference documents the applicability and settings; its Query Store guidance recommends Query Store for insight into PSP behavior.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
Check that the specific database is at the required compatibility level and that the query is eligible before adding a workaround. If parameter sniffing is disabled through trace flag 4136, the database-scoped PARAMETER_SNIFFING = OFF setting, or the DISABLE_PARAMETER_SNIFFING query hint, PSP is disabled for the affected workload or context. These settings therefore have a trade-off: suppressing sniffing may change plan behavior broadly while also removing PSP as an option. See Microsoft’s configuration documentation.
Compare the main remedies
| Option | When it fits | Trade-off and scope |
|---|---|---|
| PSP optimization | SQL Server 2022 (16.x)+ at compatibility level 160, or supported Azure SQL services; query must be eligible. | Engine-managed multiple plans can serve qualifying parameter ranges. Not available in contexts where parameter sniffing has been disabled. Microsoft configuration reference. |
Statement-level OPTION (RECOMPILE) |
One identified statement needs a plan optimized using its current parameter values, and the execution benefit justifies compiling it each time. | Adds compilation CPU on each execution; keep the scope narrow where practical. Recompiling an entire procedure repeatedly is less efficient than statement-level alternatives. Microsoft troubleshooting guidance; sp_recompile reference. |
OPTIMIZE FOR (@p = value) |
A known value is representative of the dominant or business-important workload. | Targets a chosen estimate, not every value. Values unlike the chosen one may still receive a poor plan. Microsoft troubleshooting guidance. |
OPTIMIZE FOR (@p UNKNOWN) |
No single value represents the workload and a broader compromise plan is worth evaluating. | Uses an average-density estimate rather than the sniffed value; it is not guaranteed to be optimal for any particular execution. Microsoft troubleshooting guidance. |
| Disable parameter sniffing narrowly | A targeted query needs a plan that does not depend on sniffed parameter values and other options do not suit the workload. | Changes optimizer behavior for the affected query or scope; broader database/server settings can affect unrelated queries. It also disables PSP for affected contexts on SQL Server 2022. Microsoft troubleshooting guidance; configuration reference. |
| Query Store hint | You need a query-level hint without changing application SQL, and the hint is justified by workload testing. | Overrides the optimizer’s normal behavior for all executions of that query; revisit it as data distributions change. With forced parameterization, Query Store’s RECOMPILE hint is not supported and is ignored, while other valid specified hints may still apply. Query Store Hints; best practices. |
| Targeted cached-plan removal | A temporary diagnostic or short-term step is needed while a durable query or configuration change is prepared. | Forces a new compilation on a later execution, but does not prevent the same sensitivity from recurring. Whole-cache removal rebuilds plans for unrelated queries too and can temporarily increase their duration. Microsoft troubleshooting guidance. |
Apply a fix at the narrowest useful scope
Prefer PSP when the deployment and query qualify
For supported SQL Server 2022+ deployments, verify compatibility level 160 and investigate eligibility before overriding optimizer behavior. Keep Query Store available where feasible to review query performance and plan behavior. Newly created SQL Server 2022 databases have Query Store enabled by default, but do not assume it is enabled for an older database or an upgraded configuration. Microsoft documents the version and configuration details in its configuration reference and Query Store documentation.
Recompile only the statement that needs value-specific plans
When distinct parameter values need distinct plans and PSP is unavailable or insufficient, statement-level OPTION (RECOMPILE) lets the optimizer use current parameter values at execution time. Measure the execution improvement against compilation CPU and overall throughput, especially for a frequently executed statement. Avoid repeatedly recompiling an entire stored procedure if a statement-level change can address the problem. Microsoft explains recompilation trade-offs in its CPU troubleshooting guidance.
sp_recompile is not the same as a recurring fix: it marks procedures, triggers, or functions that act on a specified table for recompilation on their next execution. SQL Server also recompiles automatically in some circumstances, including relevant underlying changes or statistics changes. Use the procedure deliberately rather than applying it blindly; see Microsoft’s sys.sp_recompile reference.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Use an optimization target only when it matches the workload
For a statement where one value is a defensible proxy for the important workload, OPTIMIZE FOR (@CustomerId = 42) illustrates the syntax; substitute the actual parameter and a value justified by observed usage. Test it against the full range of meaningful inputs, because a value-specific estimate can still be poor for materially different values. If no value is representative, OPTIMIZE FOR (@CustomerId UNKNOWN) asks SQL Server to use average density instead. That may be a workable compromise, not a guarantee of fast execution. Microsoft documents these choices in its troubleshooting guidance.
Keep disabling sniffing and Query Store hints controlled
Microsoft documents the query-level USE HINT ('DISABLE_PARAMETER_SNIFFING') option as well as database-scoped and server-level ways to affect sniffing. Prefer the narrowest scope that addresses the identified query; a broad setting can change plans for unrelated workload and can make PSP unavailable in affected SQL Server 2022 contexts. See Microsoft’s troubleshooting guidance and configuration reference.
Query Store hints let DBAs apply query-level hints without changing application code, but they override normal optimizer behavior for all executions of that query. Before applying one, address statistics and index maintenance where feasible, test the change against application load, verify that the hint was accepted and applied, and reassess it after migrations or meaningful data-distribution changes. With forced parameterization, Query Store’s RECOMPILE hint is unsupported; the engine ignores that hint while applying other valid hints if specified. Microsoft’s Query Store Hints documentation and best-practices guide cover these controls.
Treat cache removal as temporary
Removing a specific known-bad cached plan can prompt a fresh compilation and is useful as a targeted diagnostic or bridge to a durable fix. Do not clear the whole cache as a routine repair: it discards compiled plans beyond the query under investigation, and rebuilding them can increase duration once as they are compiled again. Microsoft describes that impact in its high-CPU troubleshooting guide.
Quick Recap
Best Value
Validate the change across the workload
- Compare latency, CPU, and plan behavior for representative parameter ranges, not just the input that originally exposed the problem.
- For recompilation, confirm that the execution benefit justifies added compile CPU at expected call volume.
- For a fixed optimization target or hint, check whether less common but valid inputs regress and whether the selected plan remains appropriate as data changes.
- For Query Store hints, verify application and retain a path to remove or revise the hint if workload behavior changes.
- After changing compatibility or other database configuration, verify the resulting behavior on the affected database and query rather than assuming the setting alone resolves the regression.
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.
