Share via

Monitoring automatic tuning actions using XEvents

  • Article

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

Comments

  • Anonymous
    July 26, 2017
    i have found that ([option_id] = 0 ,in both cases when regression is detected but not corrected or when i set " ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); "the plan is auto corrected but [option_id] always is set to 0 ,however above it statesthat it is always "1'
  • Anonymous
    October 03, 2017
    Thanks for the article!There is a typo in Reverted corrections section:" Whenever SQL Server completes verification of a forced plan, new qds.automatic_tuning_plan_regression_detection_check_completed XEvent is fired."should be: Whenever SQL Server completes verification of a forced plan, new qds.automatic_tuning_plan_regression_verification_check_completed XEvent is fired.