Automatic tuning

Automatic tuning identifies potential query performance problems, recommends solutions, and automatically fixes problems identified.

By default, automatic tuning is disabled and must be enabled. There are two automatic tuning features available:

• Automatic plan correction
• Automatic index management

Automatic plan correction

To take advantage of automatic plan correction, the Query Store must be enabled on your database. Automatic plan correction is made possible by constantly monitoring data that is stored by the Query Store.

Automatic plan correction is the process of identifying regression in your query execution plans. Plan regression occurs when the SQL Server Query Optimizer uses a new execution plan that performs worse than the previous plan. To identify plan regression, the Query Store captures compile time and runtime statistics of statements being executed.

The database engine uses the data captured by the Query Store to identify when plan regression occurs. More specifically, to identify plan regression and take necessary action, the database engine uses the sys.dm_db_tuning_recommendations view. This is the same view you use when manually determining which plans have experienced regressions and which plans to force.

When plan regression is noticed, the database engine will force the last known good plan.

The great news is that the database engine doesn't stop there; the database engine will monitor the performance of the forced plan and verify that the performance is better than the regressed plan. If the performance is not better, then the database engine will unforce the plan and compile a new query execution plan.

Enabling automatic plan correction

Automatic plan correction is disabled by default. The following code can be used to verify the status of automatic plan correction on your database:

SELECT name, desired_state_desc, actual_state_desc
FROM sys.database_automatic_tuning_options

The output is as follows:

Figure 1.19: Automatic plan correction is turned off

You enable automatic plan correction by using the following code:

ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON )

If you have not turned the Query Store on, then you will receive the following error:

Figure: 1.20: Error report if the Query Store is off

Automatically forced plans

The database engine uses two criteria to force query execution plans:

• Where the estimated CPU gain is higher than 10 seconds
• The number of errors in the recommended plan is lower than the number of errors in the new plan

Forcing execution plans improves performance where query execution plan regression has occurred, but this is a temporary solution, and these forced plans should not remain indefinitely. Therefore, automatically forced plans are removed under the following two conditions.

• Plans that are automatically forced by the database engine are not persisted between SQL Server restarts.
• Forced plans are retained until a recompile occurs, for example, a statistics update or schema change.

The following code can be used to verify the status of automatic tuning on the database:

SELECT name, desired_state_desc, actual_state_desc
FROM sys.database_automatic_tuning_options;

Figure 1.21: Verifying the status of automatic tuning on the database