Query Feedback
Overview
Every other page measures the workload. This one measures the optimizer’s opinion of it.
From SQL Server 2022 the engine watches its own plans and corrects them without being asked:
- Cardinality estimation (CE) feedback tries a different estimation model on a query whose row counts keep coming out wrong.
- Memory grant feedback resizes a grant that spilled to tempdb or reserved memory nothing used, and with persistence keeps what it learned in Query Store.
- Degree of parallelism (DOP) feedback lowers the parallelism of a repeating query whose extra workers are not earning their place.
Each correction is recorded in sys.query_store_plan_feedback, kept under review, and silently undone when the review says it made things worse.
The undoing is why this page exists. A correction that worked needs nobody. A correction the engine tried and took back is the engine saying it found something wrong with the query and could not fix it from where it stands, which makes it one of the best pointers to a query worth a person’s time. It appears on no other page.
The funnel
Feedback narrows through gates in a fixed order, and each stage is a subset of the one above it:
| Stage | What is still in it | What dropped out before it |
|---|---|---|
| Feedback recorded | Every feedback row. | |
| Made a change | Rows where the engine found something worth changing. | Nothing to change (NO_RECOMMENDATION, NO_FEEDBACK): the engine looked and left the plan alone. |
| Finished verification | Changes the engine has finished measuring. Memory grant feedback needs no verification. | Still being verified (PENDING_VALIDATION, IN_VALIDATION): not failed, just not decided. |
| Still in effect | Changes that survived and are applied today (VERIFICATION_PASSED, FEEDBACK_VALID). |
Regressed or undone (VERIFICATION_REGRESSED, ROLLEDBACK_BY_APRC, FEEDBACK_INVALID): the engine measured the change, found it no better, and restored the plan. |
The states are read by their exact names. A state a later build adds is counted as in effect, because calling something a regression takes positive evidence.
Which features are on
Whether a feature can record anything depends on two things, and the page reads both:
| Feature | Compatibility level | Database scoped configuration | Default in SQL Server 2022 |
|---|---|---|---|
| Cardinality estimation feedback | 160 | CE_FEEDBACK |
ON |
| Memory grant feedback persistence | 140 | MEMORY_GRANT_FEEDBACK_PERSISTENCE |
ON |
| Degree of parallelism feedback | 160 | DOP_FEEDBACK |
OFF |
The page uses is_value_default to tell a feature somebody switched off from one that ships off. DOP feedback is off by default in SQL Server 2022, and the page says so rather than warning about it. A feature set OFF when its default is ON gets a warning, because it means the engine will not revisit a plan it would have corrected.
The Features switched on tile, and the right-click menu, show every intelligent query processing setting with its level, its switch and whether it was changed from the default.
Where to find it
In the tree, under a database, Real Time → Query Store → Query Feedback.
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_plan_feedback arrived in 2022. |
| Query Store on for the database | Feedback is persisted in Query Store. |
| Compatibility level 160 for CE and DOP feedback | Below it those two record nothing. Memory grant feedback persistence works from 140. |
VIEW DATABASE STATE |
To read the Query Store catalog views and the database scoped configurations. |
The toolbar
| Control | What it does |
|---|---|
| Feedback rows / CPU of those queries | What the funnel measures: the number of feedback rows, or the CPU the queries behind them spent in the window. |
| 24 h / 3 d / 7 d | The window the CPU is measured over. The feedback rows themselves are not window scoped. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Reads everything again. |
By row count a handful of undone corrections can look like a rounding error; by the CPU of the queries they belong to, the same handful is often the largest thing on the page.
Reading the chart
The verdict names the most important thing, in this order: corrections the engine took back (red when the queries behind them spent 5% or more of the window’s CPU), a compatibility level below 160, a feedback feature switched off, corrections in effect, corrections still being verified, and finally a database where nothing needed correcting.
| Tile | What it is |
|---|---|
| Feedback recorded | Every feedback row, and how many queries they belong to. |
| Still in effect | Corrections applied today, and the share of the window’s CPU their queries spent. Click to filter the grid. |
| Being verified | Corrections the engine has not decided on. Click to filter. |
| Regressed or undone | Corrections taken back, with the costliest query named. Click to filter. |
| Features switched on | How many of the intelligent query processing settings are on at this compatibility level. Click for the full list. |
| Parameter sensitive plans | Variants and split statements from parameter sensitive plan optimization. Click to open Parameter Sensitive Plans. |
Under the tiles, the funnel. Each band is centered and as wide as its share of the top of the funnel; the silhouette never widens, even if the numbers do not narrow. The value is written inside the band when it fits. Between two bands, the colored wedges are what dropped out there, with the loss named and its share of the stage above. A stage nothing reached is drawn as a short bare mark rather than left blank.
Double-click a band for the costliest query still at that stage. Double-click a loss to filter the grid to the rows that dropped out there.
Reading the grid
Rows are sorted worst first: undone, being verified, in effect, in effect but not run in the window, nothing to change. Within a group, by CPU.
| Column | What it is |
|---|---|
| # | Rank in that order. |
| Feature | CE Feedback, Memory Grant Feedback or DOP Feedback. |
| Query | The statement text, with the parameter list removed. |
| Finding | For example Regressed, the engine undid it, Being verified, In effect, In effect, not run in the window, Looked at, nothing to change. |
| State | state_desc as SQL Server reports it. |
| Notes | The change in words (“grant raised by 20.6 MB”, “estimation model: Simple containment”), the Query Store hint CE feedback placed, whether the plan is forced, and whether the query has moved to another plan. |
| Executions | Regular executions of the query in the window, over all of its plans. |
| CPU | CPU of the query in the window, over all of its plans. |
| Of the window | That CPU as a share of all CPU Query Store recorded in the window. |
| Object | The procedure or function, when the statement belongs to one. |
| Query id | The Query Store query_id. |
| Created / Last change | When the feedback row was created and last updated, UTC. |
The Notes column takes the width the others leave, so it grows with the window. The last columns (Object, Query id, Created and Last change) 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 feedback | Everything the page knows about the row, in words. |
| Show the statement | The statement in the query window. |
| Go to Plan Regressions | The query’s plan history. |
| Copy query text | The statement, without the parameter list. |
| Show the feature settings | Every intelligent query processing setting, its level and its switch. |
| Copy the script that turns the feedback features on | ALTER DATABASE SCOPED CONFIGURATION statements, commented out. Nothing is run. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_store_plan_feedback |
Feature, state, feedback data, created and updated times. |
sys.query_store_plan, ..._query, ..._query_text |
The query each feedback row belongs to. |
sys.query_store_runtime_stats, ..._interval |
Executions and CPU over the window. |
sys.database_scoped_configurations |
Which features are switched on, and whether that is the default. |
sys.databases |
The compatibility level. |
sys.query_store_query_variant |
Parameter sensitive plan variant counts. |
sys.query_store_query_hints |
The hint CE feedback placed on a query. |
Things the query gets right
The CPU is the query’s, over all of its plans. A CE feedback correction that works recompiles the query onto a new plan and leaves the feedback row on the old plan, which then never runs again. Weighted by its own plan, the correction that worked would look like a query nobody calls.
A query with several feedback rows is counted once in every CPU total.
Only regular executions are measured, and the window ends at the last completed interval.
Messages you may see
The engine tried to correct … and took the correction back. A correction regressed or was rolled back. The query has a problem feedback cannot fix.
This database cannot use cardinality estimation or DOP feedback at compatibility level N. Both need level 160.
Cardinality estimation feedback is switched off on this database. A feature that ships ON has been set OFF in the database scoped configuration.
The engine has found nothing on this database worth correcting. Feedback is only recorded for a query that runs repeatedly and misses repeatedly.
Related reports
| Report | Why you would go there |
|---|---|
| Parameter Sensitive Plans | The other way SQL Server 2022 changes plans on its own. |
| Automatic Tuning | Plans SQL Server forced back after a regression, and plans forced by hand. |
| Plan Regressions | A query’s plan history. |
| Duration Spread | Whether a query behaves differently from run to run. |
Frequently asked questions
Most rows say “Looked at, nothing to change”. Is that a problem? No. CE feedback records a row when it analyzes a query, and most analyses conclude the estimate is fine or cannot be improved.
Why is a query in the grid that has not run for weeks? Feedback lives in Query Store until its plan is cleaned out. It is shown with no CPU, as In effect, not run in the window.
DOP feedback is off. Should I turn it on? It is off by default in SQL Server 2022. The right-click menu copies the statement, commented out, if you decide to.