Automatic Tuning
Overview
Every other page measures the database as the optimizer left it. This one is about the places where somebody has overruled the optimizer:
- A plan SQL Server forced back on its own, because a plan change made a query slower.
- A plan SQL Server wants to force and is not allowed to.
- A plan a person forced by hand, possibly a long time ago, and never looked at again.
For each of them the page answers one question: did forcing help?
Automatic plan correction, and what the edition has to do with it
From SQL Server 2017 the engine watches Query Store for a query whose plan changed and got worse. When it finds one, it works out which earlier plan was better and records a recommendation in sys.dm_db_tuning_recommendations, with the CPU it measured on both plans.
What happens next depends on one database setting, FORCE_LAST_GOOD_PLAN, and on the edition:
| Situation | What SQL Server does |
|---|---|
| Enterprise or Developer edition, setting ON | Forces the earlier plan itself, keeps measuring, and takes the force back if it did not help. |
| Enterprise or Developer edition, setting OFF | Records the recommendation and does nothing. The analysis is done and sitting unused. |
| Standard, Web or Express edition | Automatic plan correction is an Enterprise edition feature and the setting cannot be turned on. Any recommendation still carries the script to apply it by hand. |
Developer edition has every Enterprise feature and is licensed for development and test only, so a test server can show automatic plan correction working while the Standard edition production server cannot use it. The page names the edition and the setting in words, on the verdict or under the chart.
The recommendations are held in memory, so a restart of the instance clears them. The footer says when the instance last started.
Forced plans
Forcing a plan is a decision that stops being reviewed the moment it is made. It keeps applying through schema changes, statistics updates and upgrades. Two things go wrong with it:
- A force that is failing. The plan can no longer be produced, for example because an index it used was dropped. The engine quietly goes back to choosing a plan for itself while the database still reports the plan as forced.
force_failure_countis the only thing that says otherwise. - A force that succeeded and was wrong. The pinned plan is now slower than the plans it displaced, usually because the data changed shape after the decision was made.
Where to find it
In the tree, under a database, Real Time → Query Store → Automatic Tuning.
The report needs SQL Server 2017 or newer. On SQL Server 2016 the page says so instead of loading.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2017 or newer | sys.dm_db_tuning_recommendations and sys.database_automatic_tuning_options arrived in 2017. |
| Query Store on for the database | Automatic tuning reads Query Store, and the forced plans live there. |
| Enterprise or Developer edition to turn automatic plan correction on | Standard edition can read the page but cannot let the engine act. |
VIEW DATABASE STATE |
To read the Query Store catalog views and the recommendations. |
VIEW SERVER STATE (optional) |
Only for the instance start time in the footer. |
The toolbar
| Control | What it does |
|---|---|
| Everything / Recommendations / Forced plans | Which rows the chart and the grid show. |
| 24 h / 3 d / 7 d | The window Query Store measures forced plans over. The recommendations are what SQL Server holds now, whatever 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
At the top, a verdict names the most important thing on the page, in this order: a forced plan that is failing, a forced plan slower than the alternatives, recommendations nobody has allowed, corrections that were undone, a correction still being verified, corrections that worked, and forced plans that are holding.
Five tiles sit under the verdict:
| Tile | What it is |
|---|---|
| Automatic plan correction | FORCE_LAST_GOOD_PLAN as the database reports it: ON, OFF, or not available in this edition. |
| Waiting to be applied | Active recommendations. Amber when SQL Server is not allowed to act on them. Click to filter the grid. |
| Applied by SQL Server | Recommendations the engine applied on its own. Click to filter. |
| Reverted | Corrections the engine took back because they did not help. Click to filter. |
| Forced plans | Plans forced in Query Store, and how many are failing. Red when any are failing. Click to filter. |
Click a filtering tile again to clear the filter.
The slope chart
Each line is one query. The left end is the average CPU per execution of the plan that was displaced; the right end is the plan that is forced or recommended.
- A line that falls (green) is a plan that is cheaper forced.
- A line that rises (red) is a plan that costs more forced.
- A grey line changed by less than 1%.
The query name and the first value are on the left, the second value and the change (“6.2x faster”, “30% slower”) on the right. When labels would overlap they are pushed apart and a dotted leader points back to the end of the line. When the values span 25 times or more the axis is logarithmic and the heading says log scale.
Two sources of measurement are drawn, and the tooltip says which one a line uses:
- SQL Server’s own measurement, from the recommendation, taken over the executions that made it decide. This is preferred whenever it exists.
- Query Store over the window, for a forced plan: the forced plan’s runs against the same query’s other plans. It is only drawn when both sides ran at least 10 times in the window.
A forced plan whose query ran nothing else in the window has nothing to compare against and is not drawn, which is what a healthy forced plan usually looks like. The chart draws at most 14 lines; the grid has every row.
Reading the grid
| Column | What it is |
|---|---|
| # | The order the page ranks rows in: worst finding first, then the CPU the change is worth. |
| Subject | The procedure or function, otherwise the statement, otherwise the query id. |
| Finding | What the page concluded, for example Forced, but failing to be applied, Forced plan is slower than the alternatives, Found, and not allowed to act, Applied, still being measured, Applied, and it worked, Forced and holding. |
| State | The recommendation state SQL Server reports: Active, Verifying, Success, Reverted, Expired. “Forced” for a plan forced by hand. |
| Notes | The failure reason in words, who measured the pair, whether the recommended plan is still in Query Store, CPU saved or added, the state reason, and when a correction was applied or reverted. |
| Change | The difference in words, sorted as a percentage. |
| Displaced plan CPU | Average CPU per execution of the plan that was, or would be, replaced. |
| Forced or recommended CPU | Average CPU per execution of the plan that is, or would be, forced. |
| Score | SQL Server’s estimate, 0 to 100, of what acting on the recommendation is worth. |
| Executions | The executions behind the measurement. |
| Action | “Force plan 22869 in place of plan 22826” for a recommendation, or “Plan 24 is forced by hand” for a forced plan, with the number of plans the query has. |
| Query | The Query Store query_id. |
| Recorded since | When the recommendation was recorded, or when a forced plan was first compiled, which is the earliest it can have been forced. |
The Notes column takes the width the others leave, so it grows with the window. The last columns (Score, Executions, Action, Query and Recorded since) 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 notes.
Double-click a row to see the statement with the explanation beside it. Right-click for:
| Action | What it does |
|---|---|
| Explain this row | Everything the page knows about the row, in words. |
| Show the statement | The statement in the query window. |
| Copy the force plan script | For a recommendation: sp_query_store_force_plan, the matching unforce, and a check of force_failure_count, all commented out. |
| Copy the unforce plan script | For a forced plan: sp_query_store_unforce_plan and the same check. |
| Go to Plan Regressions | The query’s plan history. |
| Copy query text | The statement, without the parameter list. |
| Copy the automatic plan correction script | Only on Enterprise or Developer edition with the setting off: ALTER DATABASE ... SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON), commented out. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Nothing on this page forces, unforces or changes a setting. Every script goes to the clipboard.
Where the data comes from
| Source | What it gives |
|---|---|
sys.database_automatic_tuning_options |
Whether FORCE_LAST_GOOD_PLAN is on, and why. |
sys.dm_db_tuning_recommendations |
The recommendations, their state, and the JSON details with both plans’ CPU. |
SERVERPROPERTY('Edition'), SERVERPROPERTY('EngineEdition') |
Whether automatic plan correction is available. |
sys.query_store_plan |
Forced plans, force_failure_count, last_force_failure_reason_desc, and plan_forcing_type_desc (AUTO or MANUAL) on SQL Server 2019 and newer. |
sys.query_store_runtime_stats, ..._interval |
Executions and CPU per plan over the window. |
sys.query_store_query, ..._query_text |
The statement and the object it belongs to. |
sys.dm_os_sys_info |
The instance start time, when the login can read it. |
Things the query gets right
CPU in the recommendation is microseconds. A recommendation whose reason reads “0.59ms to 0.1ms” carries 594 and 96 in its details.
A plan recompiled under a force can get its own plan id. When a forced plan recompiles into a plan with a different hash, Query Store records it as a new plan that is not marked forced, and every run made under the force lands there. The page counts any plan of the same query marked UsePlan="1" on the forced side. Measured by plan id alone, a forced plan that was seven times dearer than the plan it displaced looked thousands of times cheaper.
Only regular executions are measured. Aborted and failed executions are counted in the footer.
The window ends at the last completed interval, and every average is weighted by executions.
The JSON is read by the page, not by the query, so a database left at a compatibility level below 130 still loads.
Messages you may see
A plan is forced on … and is not being applied.
force_failure_countis above zero. Fix what the plan depends on, or unforce it.
The plan forced on … is now slower than the alternatives. Over the window the forced plan averaged at least 25% more CPU than the query’s other plans, with at least 10 runs on each side.
SQL Server found N plan regressions and has not acted on them. Amber when at least one would cut CPU per execution by 25% or more; blue when all are small.
SQL Server forced a plan for … and then undid it. The correction did not help, so the engine reverted it.
Nothing on this database is being overruled. No recommendation and no forced plan.
Related reports
| Report | Why you would go there |
|---|---|
| Plan Regressions | The plan history of a query, and forcing a plan you chose yourself. |
| Parameter Sensitive Plans | When no single plan is right because the parameter values differ. |
| Query Feedback | The other changes the optimizer makes on its own. |
| Duration Spread | Whether a query behaves differently from run to run. |
| Workload Change | Whether the database costs more than it did. |
Frequently asked questions
The recommendations disappeared. They are held in memory and a restart of the instance clears them. The engine records new ones as it finds new regressions.
Why is a forced plan in the grid but not on the chart? The query ran on no other plan, or fewer than 10 times on one of the two sides, in the window. There is nothing to compare it with.
Can I turn automatic plan correction on from this page? No. Right-click copies the ALTER DATABASE statement, commented out, for you to run in a change window.
The recommendation is on Standard edition. What can I do with it? Copy the force plan script and apply it by hand, then review it like any other forced plan.