Context
A parameterized SQL Server query can reuse a cached execution plan, which usually saves compilation work. When data distribution is uneven, a plan suitable for a small customer may perform poorly for a very large customer. This is parameter sensitivity. Demonstrate the relationship between values, estimates, and plans before applying a hint or changing cache behavior.
Prerequisites
Record engine version, database compatibility level, Query Store state, query identity, relevant statistics, and sanitized examples of both fast and slow parameter groups. Protect customer identifiers in diagnostic records. Use a representative test copy for execution-plan experiments. Current Microsoft documentation describes Parameter Sensitive Plan optimization from SQL Server 2022 onward, with additional SQL Server 2025 behavior at compatibility level 170; eligibility still matters.
Compare the evidence
- Establish that the fast and slow cases share the intended query shape and database context. Compare duration, resource use, returned rows, blocking, and time of execution. Similar text alone does not prove parameter sensitivity. A blocked request or a different workload period can explain variation without any change in the useful execution plan.
- Inspect Query Store history and available execution plans for representative parameter groups. Compare estimated row counts, join choices, access paths, and memory behavior. If actual execution information is needed, capture it in a controlled environment with reviewed overhead. Keep enough data to compare the workload rather than preserving only one convenient slow example.
- Review data skew and statistics before selecting a remedy. A stale estimate and a genuinely large result set need different treatment. Check engine and compatibility prerequisites for PSP, then determine whether the statement is eligible and whether dispatcher and variant plans are present. A feature being available does not mean every parameterized statement will receive multiple plans.
- Test the smallest appropriate correction against the full representative set. Options can include a query or index redesign, a targeted statistics correction, or a documented plan-management mechanism. Review version-specific PSP capabilities before changing compatibility. Avoid clearing the entire plan cache as a routine experiment; it disrupts unrelated workloads and may only change which parameter compiles first.
Diagnostic table
| Evidence | Interpretation | Next check |
|---|---|---|
| Same plan, very different row populations | Sensitivity is plausible | Estimates across value groups |
| Slow case mostly waits on locks | Another bottleneck dominates | Blocking owner and transaction |
| PSP available, no variants | Query may be ineligible or configured differently | Version and eligibility rules |
| One forced plan helps one group only | Remedy may shift the regression | Full representative workload |
Illustrative scenario
A support report retrieves orders for either a small tenant or a tenant with years of high activity. A plan compiled for the small case causes excessive work in the large case. The team compares both groups in a test copy and reviews eligible plan variants on its installed release. It validates any correction against both groups, so a faster large report does not silently degrade the ordinary support workflow.
Correct and verify
Compare resource use and response behavior across meaningful values under representative concurrency. Confirm that the change preserves results and that its compilation or maintenance cost is acceptable. Retain a tested rollback for hints, plan forcing, or compatibility changes. Do not label ordinary parameter sniffing defective when the evidence instead points to missing statistics or unexpectedly large requested work.
Operating procedure
Monitor regressions by query family and parameter population with sensitive values protected. Keep Query Store retention and capture settings appropriate to incident investigation. Recheck the workload after major distribution changes or SQL Server upgrades, documenting which plan mechanism actually handled the statement.
Explore Clouvenix server performance for implementation and operational handover.