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
.
In this post, you will see how to create XEvent sessions that will track actions in
automatic tuning process
.
Plan change regressions that are not corrected
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
qds.automatic_tuning_plan_regression_detection_check_completed
is fired.
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(
WHERE ((([is_regression_detected]=(1))
AND ([is_regression_corrected]=(0)))
AND ([option_id]=(0))))
ADD TARGET package0.event_file(SET filename=N'plans_that_are_not_corrected')
WITH (STARTUP_STATE=ON);
GO
ALTER EVENT SESSION [APC - plans that are not corrected] ON SERVER STATE = start;
GO
We have the following fields in the
qds.automatic_tuning_plan_regression_detection_check_completed
XEvent:
-
is_regression_detected
means that SQL Server has identified possible plan change regression
-
is_regression_corrected
means that regression is corrected,
-
option_id
is always 0 (
FORCE_LAST_GOOD_PLAN
is first and only automatic tuning option in SQL Server 2017)
Another important field in this XEvent is
cpu_gain
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.
Reverted corrections
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
qds.automatic_tuning_plan_regression_detection_check_completed
XEvent is fired. This Xevent has the following fields:
-
[is_regression_detected]
that will be set to 1 if SQL Server detects that forced plan regressed.
-
[is_regression_corrected]
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(
WHERE ((([is_regression_detected]=(1))
AND ([is_regression_corrected]=(1)))
AND ([option_id]=(0))))
ADD TARGET package0.event_file(SET filename=N'reverted_plan_corrections')
WITH (STARTUP_STATE=ON);
GO
ALTER EVENT SESSION [APC - Reverted plan corrections] ON SERVER STATE = start;
GO
