First published on MSDN on May 17, 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
.
