Parameter Sensitive Plans
Overview
Parameter sensitive plan (PSP) optimization is on by default at compatibility level 160 in SQL Server 2022. On a parameterized statement whose predicate column is badly skewed, the optimizer stops compiling one plan for every value. It compiles a dispatcher instead: two boundaries in estimated rows, taken from the statistics, and a variant query with its own plan for each of the three ranges they make:
| Range | Calls whose predicate is estimated to match |
|---|---|
| low | fewer rows than the low boundary |
| middle | between the two boundaries |
| high | more rows than the high boundary |
It is the engine’s own answer to parameter sniffing. The catch is how Query Store records it: every variant is a query of its own, so one statement appears on every ranked page as three unrelated queries, and nothing else in the product can see it.
This page puts the pieces back together and asks four questions:
- Does the split buy anything? A call either side of a boundary should run a different plan.
- Do the ranges actually cost different amounts? If not, nobody can tell the split from no split.
- Is the plan still moving inside a range? That is the problem PSP exists to prevent, happening underneath it.
- Is the statement bigger than it looks? A statement in the top ten as one thing can be outside the top ten in every piece.
Where to find it
In the tree, under a database, Real Time → Query Store → Parameter Sensitive Plans.
The report needs SQL Server 2022 or newer. On older versions the page says so instead of loading.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2022 or newer | sys.query_store_query_variant arrived in 2022. |
| Compatibility level 160 | PSP optimization only happens at 160. |
PARAMETER_SENSITIVE_PLAN_OPTIMIZATION ON |
The database scoped configuration that switches it. ON by default. |
| Query Store on for the database | The variants and their statistics live there. |
VIEW DATABASE STATE |
To read the Query Store catalog views and table row counts. |
The toolbar
| Control | What it does |
|---|---|
| Duration / CPU / Reads | The measure statements are ranked by and the chart is drawn in. |
| 24 h / 3 d / 7 d | The window. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Reads everything again. |
Reading the chart
The verdict names the most important finding, in this order: a plan moving inside a range, a top ten statement hidden by being counted in pieces, splits that gain nothing, and splits doing their job.
| Tile | What it is |
|---|---|
| Split statements | Statements with a dispatcher that ran in the window, of how many Query Store holds. |
| Splits that pay | A different plan, and a measurably different cost, either side of a boundary. |
| Split for nothing | The same plan in every range, or different plans nobody can tell apart on cost. |
| Plans moving in a range | A range served by two plans at least twice apart in CPU per call. |
| Hidden by the split | In the top ten as one statement and outside it in every piece. |
| Share of the workload | The split statements’ share of the measure across everything Query Store recorded. |
The forest plot
One row per split statement, the costliest ten. On each row, one line per variant (low, middle, high) at that variant’s average per call as a multiple of the statement’s execution weighted average, with a 95% interval for that average.
- The solid center line is 1.0x, the statement’s own average.
- The axis is logarithmic and symmetric: half and double are the same distance either side.
- A filled blue square is a range clearly cheaper per call than the statement; a filled orange square is one clearly dearer. “Clearly” means the whole interval is on that side of 1.0x and the estimate is more than a tenth away from it.
- A hollow square is a range nobody can tell from 1.0x.
- The shaded band around the center line is within a tenth, too small to matter.
- A square’s area is how many calls stand behind it.
- An interval running past the end of the axis ends in an arrow.
- A range with fewer than three calls has no interval; it is drawn across the whole axis and never counts as a difference.
If every interval on a row crosses 1.0x, the split changed nothing anybody could measure.
On the right of each row, the finding in a few words. Hover over a row for every range’s boundaries, calls, cost per call and plans. Double-click a row for the statement.
Reading the grid
| Column | What it is |
|---|---|
| # | Rank by the measure, as one statement. |
| Object | The procedure or function, when the statement belongs to one. |
| Statement | The statement text the application sent, with the parameter list removed. |
| Finding | For example Split pays, a different plan and cost either side, Split, and the same plan either side, Different plans, and the ranges cost the same, Plans moving inside the middle range, 3.0x apart, Every call landed in one range. |
| Notes | Hidden rankings, a high boundary past the table’s row count, aborted calls, the statistics the boundaries came from, and forced variant plans. |
| Duration, CPU or Reads | The statement’s total of the measure in the window. |
| Of the workload | That total as a share of everything Query Store recorded. |
| Plans | A letter per plan shape in each range, low to high. The same letter twice is the same plan. |
| Calls low / mid / high | Regular executions in each range. |
| Split on | The skewed column, schema.table.column. |
| Boundaries (rows) | The low and high boundary in estimated rows, such as “100 / 100k”. |
| Rank as one / Best piece | Where the statement ranks with its variants added together, and the best rank any one variant reaches on its own. |
| Per call vs statement | Each range’s cost per call as a multiple of the statement’s average. |
The Notes column takes the width the others leave, so it grows with the window. The last columns (Split on, Boundaries (rows), Rank as one, Best piece and Per call vs statement) sit past the edge of a 1280 pixel screen; scroll right for them, or hover over a row, whose tooltip lists every column along with the full statement and notes.
Right-click for:
| Action | What it does |
|---|---|
| Explain this split | Every range with its boundaries, calls, cost and plans, in words. |
| Show the statement | The statement in the query window. |
| Copy the script that turns the split off | sp_query_store_set_hints with DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION on the statement’s own query, the matching sp_query_store_clear_hints, and a check, all commented out. Nothing is run. |
| Go to Plan Regressions | Plan history, where a variant’s plans can be compared. |
| Copy statement text | The statement, without the parameter list. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
A hint on the statement’s own query is inherited by every variant under it.
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_query_variant |
Which variant queries belong to which statement, and the dispatcher plan. |
sys.query_store_plan |
The dispatcher plan XML (boundaries, column, statistics) and each variant’s plans. |
sys.query_store_query_text |
The statement, and each variant’s text with its QueryVariantID and predicate_range. |
sys.query_store_runtime_stats, ..._interval |
Executions, duration, CPU, reads and their standard deviations over the window. |
sys.dm_db_partition_stats |
The row count of the table behind each split. |
sys.database_scoped_configurations, sys.databases |
Whether PSP is switched on, and the compatibility level. |
Things the query gets right
The dispatcher plan XML is parsed by the page, not with XQuery in the batch, so a plan too deep for the xml type costs nothing. When the dispatcher plan has left Query Store, the boundaries are read from a variant’s text instead.
The interval is pooled across intervals from Query Store’s per interval averages and standard deviations, and every average is weighted by executions.
Both rankings are computed over the whole window, not over the rows returned, so “hidden by the split” is real.
Only regular executions are measured. Aborted and failed calls are counted in the footer and the notes.
Messages you may see
… is still switching plans underneath its split. One range ran two plans at least twice apart in CPU per call, on at least 20 calls and a second in total.
… is #4 by duration, and no ranked page shows it in the top 10. The statement is big as one thing and every piece of it ranks lower.
N split statements gain nothing from being split. The same plan in every range, or ranges that cost the same.
This database cannot split a statement by parameter value. The compatibility level is below 160.
Parameter sensitive plan optimization is switched off on this database.
PARAMETER_SENSITIVE_PLAN_OPTIMIZATIONis OFF.
No statement was split by parameter value. Most statements never get a dispatcher. A SELECT that assigns to variables is never split.
Related reports
| Report | Why you would go there |
|---|---|
| Query Feedback | The other changes SQL Server 2022 makes to plans on its own. |
| Plan Regressions | A variant’s plans, to compare or force one on a single range. |
| Duration Spread | Statements with parameter sniffing that PSP did not split. |
| Automatic Tuning | Plans SQL Server forced back after a regression. |
Frequently asked questions
Which parameter values went to which range? Query Store does not keep parameter values, so no page can say. The boundaries and call counts are what is recorded.
A range is wide on the chart. Is that bad? No. A wide interval means few calls or calls that vary a lot. The middle range can span three orders of magnitude of rows, and a call that reads more because it matched more rows is the plan working.
My skewed query is not listed. The optimizer only splits some statements. A SELECT that assigns results to variables, for example, is never split, however skewed its column is.
Should I turn a split off? Only where the page shows it gains nothing and the fragmented Query Store history matters to you. The right-click menu copies the hint, commented out, for your change window.