Quick wins for a faster PC:
Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
Parameter sniffing is normal SQL Server optimization behavior. It becomes a performance problem when a plan compiled for one parameter value is reused for values with very different numbers of matching rows. This walkthrough uses explicitly illustrative row counts—not measured performance results—and shows how to diagnose the issue and choose a fix on SQL Server 2025.
What is parameter sniffing in SQL Server?
When SQL Server compiles or recompiles a parameterized query, it can use the parameter values available at that time to estimate how many rows will match and choose an execution plan. A cached plan may then be reused for later executions with different values. This applies to stored procedures, parameterized sp_executesql statements, and prepared queries. Microsoft describes the behavior in its Query Processing Architecture Guide.
The optimizer is trying to choose a plan for the data it expects to process. If a column has a nonuniform distribution, one parameter might match a handful of rows while another matches most of a table. A plan that works well for one group can be a poor fit for the other. That mismatch—not the act of examining parameter values—is the problem commonly called a parameter-sniffing problem.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsDifferent access strategies can suit different row counts. For example, a seek with lookups may be sensible for a small result, while processing a large fraction of a table may favor a scan. Those are possibilities, not guaranteed outcomes: indexes, selected columns, statistics, cost estimates, and other query details affect the plan SQL Server chooses.
#1 Best Overall
What the illustrative numbers do—and do not—show
The example below defines a one-million-row table in which TenantId = 7 matches 900,000 rows and TenantId = 42 matches 10. These are teaching inputs for a deliberately skewed distribution. They are not published SQL Server statistics, a measured benchmark, or evidence that either value will produce a particular plan, duration, CPU use, or logical-read count on your system.
To learn what your system does, record its SQL Server build, database compatibility level, schema, indexes, statistics state, and cache conditions. Then capture actual plans and runtime measurements for both parameter groups. The sample only provides a reproducible starting shape for that investigation.
Build a small skewed test case
Run this in a disposable test database. Six cross-joined sets of ten digits generate at least one million row numbers; the remaining rows are assigned to a third tenant so the two values under study have the stated counts. The index is intentionally noncovering for the query, so the optimizer has alternatives to consider, but no particular plan shape is promised.
CREATE TABLE dbo.TenantRows
(
RowId bigint NOT NULL PRIMARY KEY,
TenantId int NOT NULL,
Payload char(100) NOT NULL
);
WITH Digits(n) AS
(
SELECT n
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(n)
),
Numbers AS
(
SELECT TOP (1000000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM Digits AS d1
CROSS JOIN Digits AS d2
CROSS JOIN Digits AS d3
CROSS JOIN Digits AS d4
CROSS JOIN Digits AS d5
CROSS JOIN Digits AS d6
)
INSERT dbo.TenantRows (RowId, TenantId, Payload)
SELECT rn,
CASE
WHEN rn <= 900000 THEN 7
WHEN rn <= 900010 THEN 42
ELSE 99
END,
REPLICATE('x', 100)
FROM Numbers;
CREATE INDEX IX_TenantRows_TenantId
ON dbo.TenantRows (TenantId);
UPDATE STATISTICS dbo.TenantRows;
GO
CREATE OR ALTER PROCEDURE dbo.GetTenantRows
@TenantId int
AS
BEGIN
SELECT RowId, Payload
FROM dbo.TenantRows
WHERE TenantId = @TenantId;
END;
GOCheck the generated distribution rather than assuming the setup succeeded:
SELECT TenantId, COUNT_BIG(*) AS MatchingRows
FROM dbo.TenantRows
GROUP BY TenantId
ORDER BY TenantId;Record the database compatibility level and engine build alongside your results. SQL Server 2025 is version 17.x; compatibility level 170 is the level for its newest documented PSP improvements. Compatibility level 160 is the documented baseline for PSP introduced in SQL Server 2022. A database’s compatibility level is set separately from the installed engine version.
Compare parameter values in both compilation orders
The order matters because the first execution after compilation can influence the reusable plan. In the isolated test database, enable actual execution plans in your client and turn on runtime reporting:
SET STATISTICS IO, TIME ON;
EXEC dbo.GetTenantRows @TenantId = 42;
EXEC dbo.GetTenantRows @TenantId = 7;
EXEC sys.sp_recompile N'dbo.GetTenantRows';
EXEC dbo.GetTenantRows @TenantId = 7;
EXEC dbo.GetTenantRows @TenantId = 42;
SET STATISTICS IO, TIME OFF;sp_recompile marks the procedure for recompilation at its next execution, allowing the second pair to start with the opposite parameter value. This is a scoped test technique, not a reason to clear the entire production plan cache. Run controlled trials, record the conditions, and avoid interpreting a single run as a workload-wide result.
What to record for each execution
- Which value ran first after compilation, and whether the procedure was recompiled.
- Estimated versus actual rows at the relevant plan operators.
- Actual plan shape, including access method and any lookup or join behavior.
- Logical reads, CPU time, and elapsed time from the runtime output.
- Compilation and recompilation behavior, using Query Store or other appropriate monitoring for the environment.
Compare the same measures across the small and large matching sets. A large estimated-versus-actual row discrepancy or a strong difference between compilation orders can help explain inconsistent performance. The row counts alone do not prove parameter sniffing: stale or inadequate statistics, query design, blocking, memory pressure, and other causes can also contribute to poor execution.
Check Query Store and SQL Server 2025 PSP
Query Store preserves query, plan, and runtime-statistics history, which can help distinguish a one-off slow execution from a recurring plan problem. Use that history alongside actual plans and parameter groups rather than treating one cached plan as the whole story. Microsoft’s guidance on troubleshooting high CPU usage includes Query Store as part of investigating query performance.
Rank #4
Parameter Sensitive Plan optimization (PSP) can retain a dispatcher plan and multiple query variants for eligible parameter-sensitive queries. The dispatcher routes executions to variants based on cardinality ranges, documented as low, medium, and high. Variants can have their own plans in cache and Query Store. PSP was introduced in SQL Server 2022 at compatibility level 160; SQL Server 2025’s additions are not the feature’s first appearance.
What changes at compatibility level 170
Microsoft documents SQL Server 2025 PSP improvements at compatibility level 170, including support for DML statements such as DELETE, INSERT, MERGE, and UPDATE; expanded tempdb support; and additional consideration for multiple eligible predicates on the same table. The query_with_parameter_sensitivity extended event also has changes to its reported fields at this level. PSP currently supports equality predicates, and eligibility is not automatic for every query. See Microsoft’s PSP documentation for requirements and details.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Verify that PSP actually engaged
- Confirm the database compatibility level and that the query has a supported, eligible shape.
- Inspect plans and Query Store for a dispatcher and query variants, rather than assuming that compatibility level alone guarantees variants.
- If PSP does not engage, investigate documented eligibility or skipped-reason evidence. Do not label a single cached plan as a PSP variant.
- Repeat the parameter-group comparison with PSP enabled and report what the environment actually produced.
Test a move to the latest compatibility level and complete appropriate statistics maintenance before reaching for a Query Store hint. A compatibility-level change can affect more than one query, so validate it against representative workload behavior.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How do I fix parameter sniffing?
There is no universal best fix. Start by confirming the diagnosis, then compare options against the full set of parameter groups. A change that improves one tenant or execution can make another worse; include latency, CPU, logical reads, compilation overhead, plan stability, and reversibility in the decision.
| Approach | What it does | Consider it when | Trade-off or check |
|---|---|---|---|
| PSP optimization | Keeps a dispatcher and multiple plans for eligible parameter-sensitive queries. | The query is eligible and its value ranges benefit from separate variants. | Verify compatibility, eligibility, and actual variants. SQL Server 2025 improvements require level 170; earlier PSP support began at level 160. |
OPTION (RECOMPILE) |
Compiles using current parameter or local-variable values for that execution. | Input values vary substantially and extra compile work is acceptable. | Compilation consumes CPU, and the query does not use PSP while this hint applies. |
OPTIMIZE FOR (@TenantId = value) |
Optimizes for a chosen value rather than specializing for each execution’s value. | A known representative value gives a useful compromise for the actual workload. | Requires workload knowledge; a changing distribution can make the selected value unrepresentative. |
OPTIMIZE FOR UNKNOWN or disabled sniffing |
Uses a general estimate instead of specializing to the current parameter value. | A more generic plan is sufficiently robust across observed values. | A generic estimate can still be wrong at both extremes. Test each important parameter group. |
| Query Store hint | Applies a query-level hint to a captured query without editing application SQL. | A targeted operational change is needed before application code can be updated. | Can affect all executions of that query and should be reevaluated as workload or data changes. |
| Plan forcing or targeted cache removal | Forces a known plan or removes a particular cached plan so SQL Server compiles again. | A carefully scoped temporary operational response or diagnostic is appropriate. | A fresh compile may reproduce the issue. Broad cache clearing creates recompilation work; follow up with root-cause analysis. |
When recompilation is the right trade
For a query whose inputs vary widely, recompiling can let the optimizer tailor each plan to the current value. Apply OPTION (RECOMPILE) to the narrowest appropriate statement, then compare the reduction in execution cost with the added compilation work. It is not a free switch: a high-frequency query may spend substantial CPU compiling.
When a representative or generic estimate helps
OPTIMIZE FOR (@TenantId = 42) asks the optimizer to plan for a chosen value; the example value here is only illustrative. Use that approach only when workload evidence supports a representative target and the compromise remains acceptable for other values. OPTIMIZE FOR UNKNOWN, or disabling parameter sniffing, avoids specializing to the current value but may produce a plan that fits neither extreme particularly well.
Use Query Store hints narrowly
Query Store hints can apply a query-level hint to a captured query_id without changing application SQL. For example, after identifying and validating the correct query, an administrator can apply a recompile hint with sys.sp_query_store_set_hints:
EXEC sys.sp_query_store_set_hints
@query_id = 39,
@query_hints = N'OPTION(RECOMPILE)';The query ID shown is an example only; substitute the ID captured in your own Query Store. Clear a hint with sys.sp_query_store_clear_hints for that query ID. Microsoft says Query Store hints persist across restarts and failovers. Since a hint can override optimizer choices and affect every execution of the query, monitor its effects and review it after data-distribution or workload changes. See Microsoft’s Query Store Hints documentation and best practices.
Choose a fix from evidence, not from the symptom alone
- Establish the scope. Identify the affected query, execution patterns, parameter-value groups, database compatibility level, and current plan history.
- Check the estimates. Compare estimated and actual rows, review statistics and indexes, and capture runtime measures for representative values.
- Check PSP. At the relevant compatibility level, confirm whether a dispatcher and variants exist; investigate eligibility or skip evidence if they do not.
- Test one mitigation at a time. Keep the workload and measurement conditions comparable, and include both high- and low-cardinality inputs.
- Validate operational effects. Review compile cost, plan stability, latency, CPU, and logical reads; load-test sensitive systems and retain a clear rollback path.
A cache removal or recompile can change which value compiles first, so it may alter symptoms without explaining the underlying mismatch. Prefer narrowly scoped actions over a global cache clear, and treat a new compile as a diagnostic step rather than a durable fix by itself.
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.
