First published on MSDN on Jul 18, 2017
SQL Server 2017 can automatically tune your queries by identifying and fixing
SQL plan change regressions
. SQL Server tracks last known good plans for each query, and if the plan for the query changes, last know good plan will be used if performance of the new plan is worse than the performance of the last know good plan. You can use XEvent sessions to identify when plan regressions are identified, when SQL Server forces last known good plan instead of the current plan. XEvents for monitoring automatic tuning are available since
SQL Server 2017 RC1
Automatic tuning process
detects regressed plans, but it will not apply last know good plan if the difference between performance of the regressed plan and last known good plan is not high enough.
Whenever automatic tuning detects that a plan has potentially regressed and compares performance of the current plan with the last known good plan, XEvent
You can identify recommendations that are not applied because the difference between the current and recommended plans are not high enough using the following XEvent session:
CREATE EVENT SESSION [APC - plans that are not corrected] ON SERVER
ADD EVENT qds.automatic_tuning_plan_regression_detection_check_completed(
ADD TARGET package0.event_file(SET filename=N'plans_that_are_not_corrected')
ALTER EVENT SESSION [APC - plans that are not corrected] ON SERVER STATE = start;
We have the following fields in the
means that SQL Server has identified possible plan change regression
means that regression is corrected,
is always 0 (
is first and only automatic tuning option in SQL Server 2017)
Another important field in this XEvent is
that represents an estimated number of microseconds that would be saved if recommended plan would be used instead of the regressed one. If this value is less than 10.000.000 (10 CPU seconds), the recommendation will probably not be applied.
Once the correction is applied and the last know good plan is forced, automatic tuning constantly monitors the forced plan and verifies that performance of the plan will not degrade. The forced plan should be reverted if performance degrade, and Query Optimizer should create a new plan.
SQL Server will periodically check are the performance of the forced plan regressed and decides should the forced plan be retained or we need a new plan. Whenever SQL Server completes verification of a forced plan, new
XEvent is fired. This Xevent has the following fields:
that will be set to 1 if SQL Server detects that forced plan regressed.
that will be set to 1 if SQL Server un-forces the plan and let Query Optimizer create a new plan.
If the difference between performance in two consecutive intervals are high enough, the plan will be unforced and the field [is_regression_corrected] will be set to 1. If the value of this field is 0, the difference is not high enough and the recommended plan will not be unforced.
You can find all plans that are unforced because they regressed using the following XEvent session:
CREATE EVENT SESSION [APC - Reverted plan corrections] ON SERVER
ADD EVENT qds.automatic_tuning_plan_regression_verification_check_completed(
ADD TARGET package0.event_file(SET filename=N'reverted_plan_corrections')
ALTER EVENT SESSION [APC - Reverted plan corrections] ON SERVER STATE = start;