次の方法で共有

Automatic plan correction in SQL Server 2017

  • [アーティクル]

Automatic plan correction is a new automatic tuning feature in SQL Server 2017 that identifies SQL query plans that are worse than previous one, and fix performance issues by applying previous good plan instead of the regressed one.

When you need a plan correction?

SQL Server can use different strategies (or SQL plans) to execute a T-SQL query. SQL Server will analyze possible plans that can be used to execute a T-SQL query and choose the optimal plan. The plans for most of the successfully executed queries are cached and reused when the same query is executed. The plan is retained in the cache until SQL Database engine decides to recompile the plan and find a new one (e.g. when statistics change, index is added or removed, etc.)

In some cases, new plan that is chosen might not be better than the previous plans. This is rare situation and might happen if an optimal plan is not included in a list of plans that will be considered as potential candidates. In other cases, SQL Server might choose the best plan for the current T-SQL query, but the selected plan might not be optimal if the same T-SQL queries is executed with different parameter values. In that case, SQL Server might reuse the plan that is compiled and cached after first execution even if the plan is not optimal for the other executions. These problems are known as plan regressions.

If you identify that previous plan had better performance, you might explicitly force SQL Server to use the plan that you identified as optimal for some specific query using sp_force_plan procedure:

 EXEC sp_force_plan @query_id = 1196, @plan_id = 1804

This process might be very complex and tedious if it is done manually, but SQL Server 2017 enables you to fully automate it.

How to identify plans that should be corrected?

SQL Server 2017 provides a new system view called sys.dm_db_tuning_recommendations that shows all identified plan regressions. You can select data from this view, find the difference between last known good plan and regressed plan, and the script that you can execute to manually fix regression.

Automatic plan correction

As a next step, you can let SQL Server 2017 to automatically correct any plan that regressed.

 ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )

This statement will turn-on automatic tuning in the current database and instruct SQL Server to force last good plan if it identifies plan performance regression while the current plan is executed.

SQL Server 2017 will continuously monitor and analyze plan performance, identify new plans that have worse performance than the previous ones, and force last know good plan as a corrective action.

SQL Server 2017 will keep monitoring performance of the forced plan and if it does not help, query will be recompiled.

At the end, SQL Server 2017 will release the forced plan because query optimizer component should find optimal plan in the future.

Summary

Automatic plan correction is the automatic tuning in SQL Server 2017 that represents a safety net for your workload that will prevent degradation in query plans. You can find more information about the automatic tuning in SQL Server 2017 here.

Comments

  • Anonymous
    May 22, 2017
    The comment has been removed
  • Anonymous
    June 22, 2017
    Hi, Jovan,This feature seems to be very good, however I was unable to reproduce it in CTP 2.1, my fault. I was able to reproduce a plan regression, dropping an index. The sys.dm_db_tuning_recommendations DMV registers the regression, however it registers "Reason:AutomaticTunningOptionNotEnabled" even when the Automatic Tunning Option is enabled.Is it because I dropped the index and the old plan became impossible, or there is another reason for this problem ?Could you publish a sample script illustrating this automatic regression ?Another question: If I manually force a plan that appears in sys.dm_db_tuning_recommendations DMV and in the future unforce it, a record is keeped in this DMV with the state "Reverted". If, in the future again, another plan needs to be forced for the same query, it's not recommended any more by this DMV, because the old "Reverted" state is keeped. Would we need to periodially clean the records with 'Reverted' state from this DMV ? How ?Thank you in advance !