Automatic tuning introduces Automatic plan correction and T-SQL management

Azure SQL Database automatic tuning, industry’s first truly auto-tuning database based on Artificial Intelligence, is now providing more flexibility and power with the global introduction of the following new features:

◈ Automatic plan correction
◈ Management via T-SQL
◈ Index created by automatic tuning flag

Automatic tuning is capable of seamlessly tuning hundreds of thousands of databases without affecting performance of the existing workloads. The solution has been globally available since 2016 and proven to enable performant and stable workloads while reducing resource consumption on Azure.

Automatic plan correction

Automatic plan correction, feature introduced in SQL Server 2017, is now making its way to Azure SQL Database as a tuning option Force Last Good Plan. This decision was made after a rigorous testing on hundreds of thousands of SQL Databases ensuring there is an overall positive performance gain for workloads running on Azure. This feature shines in cases of managing hundreds and thousands of databases and heavy workloads.

Automatic tuning feature continuously monitors SQL Database workloads and with the Automatic plan correction option Force Last Good Plan, it automatically tunes regressed query execution plans through enforcing the last plan with a better performance. Since the system automatically monitors the workload performance, in case of changing workloads, the system dynamically adjusts to force the best performing query execution plan.

The system automatically validates all tuning actions performed in order to ensure that each tuning action is resulting in a positive performance gain. In case of a performance degradation due to a tuning action, the system automatically learns and it promptly reverts such tuning recommendation. Tuning actions performed by automatic tuning can be viewed by users in the list of recent tuning recommendations through Azure Portal and T-SQL queries.

Manage automatic tuning via T-SQL

Recognizing the needs of a large community of professionals using T-SQL and scripting procedures to manage their databases, Auto tuning team has developed a new feature making it possible to enable, disable, configure, and view the current and historical tuning recommendations using T-SQL. This makes it possible to develop custom solutions managing auto tuning, including custom monitoring, alerting and reporting capabilities.

In the upcoming sections, this blog post outlines a few examples on how to use some of the T-SQL capabilities we have made available to you.

Viewing the current automatic tuning configuration via T-SQL

In order to view the current state of the automatic tuning options configured on an individual server, connect to an SQL Database using a tool such as SSMS and execute the following query to read the system view sys.database_automatic_tuning_options:

SELECT * FROM sys.database_automatic_tuning_options

Resulting output are values from the automatic tuning options system view, as shown in the following example:

In the column name it can clearly be seen that there are three Auto tuning options available:

◈ FORCE_LAST_GOOD_PLAN
◈ CREATE_INDEX, and
◈ DROP_INDEX.

Column desired_state indicates settings for an individual tuning option, with its description available in the column desired_state_desc. Possible values for desired_state are 0 = OFF and 1 = ON for custom settings, and 2 = DEFAULT for inheriting settings from the parent server or Azure platform defaults.

Values of the column desired_state_desc indicate if an individual automatic tuning option is set to ON, OFF, or inherited by DEFAULT (corresponding to their numerical values in the column desired_state). The column actual_state indicates if the automatic tuning option is actually working on a database with value 1 indicating it is, and with value 0 indicating it is not working.

Please note that although you might have one of the auto tuning options set to ON, the system might decide to temporarily disable automatic tuning if it deems necessary to protect the workload performance. It also could be that if the Query Store is not enabled on a database, or if it is in a read-only state, this will also render automatic tuning as temporarily disabled. In this case, view of the current state will indicate “Disabled by the system” and the value of the column actual_state will be 0.

The last part of the sys.database_automatic_tuning_options system view indicates in the columns reason and reason_desc if each of the individual automatic tuning options is configured through defaults from Azure, defaults from the parent server, or if it is custom configured. In case of inheriting Azure platform defaults, column reason will have value 2 and column reason_desc value AUTO_CONFIGURED. In case of inheriting parent server defaults, column reason will have value 1 and column reason_desc value INHERITED_FROM_SERVER. In case of a custom setting for an individual automatic tuning option, both columns reason and reason_desc will have the value NULL.

Enable automatic tuning via T-SQL

In order to enable automatic tuning on a single database with inheriting Azure configuration defaults, execute a query such as this one:

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO /* possible values AUTO, INHERIT and CUSTOM */

Possible values to enable automatic tuning are AUTO, INHERIT and CUSTOM. Setting the automatic tuning value to AUTO will apply Azure configuration defaults for automatic tuning. Using the value INHERIT will inherit the default configuration from the parent server. This is especially useful if you would like to customize automatic tuning configuration on a parent server, and have all the databases on such server INHERIT these custom settings.

Please note that in order for the inheritance to work, the three individual tuning options FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEX need to be set to DEFAULT. This is because one, or several of these individual tuning options could be custom configured and there could be a combination between DEFAULT, and custom forced ON or OFF settings in place.

Using the value CUSTOM, you will need to manually custom configure each of the automatic tuning options available.

Custom configuring automatic tuning options via T-SQL

Available options in Auto tuning that could be custom configured, independent of Azure platform and parent server defaults, are FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEXES. These options can be custom configured through executing a query such as this one:

ALTER DATABASE current
SET AUTOMATIC_TUNING (
FORCE_LAST_GOOD_PLAN = [ON | OFF | DEFAULT],
CREATE_INDEX = [ON | OFF | DEFAULT],
DROP_INDEX = [ON | OFF | DEFAULT])
)

Possible values to set one of the three available options are ON, OFF and DEFAULT. Setting an individual tuning option to ON will custom configure it to be explicitly turned on, while setting it to OFF will custom configure it to be explicitly turned off. Setting an individual tuning option to DEFAULT will make such option inherit default value from the Azure platform or the parent server, depending on the setting of the AUTOMATIC_TUNING being set to AUTO or INHERIT, as described above.

If you are using Azure defaults, please note that current Azure defaults for Auto tuning options are to have the FORCE_LAST_GOOD_PLAN and CREATE_INDEX turned ON, while we are having DROP_INDEX option turned OFF by default. We have made this decision as DROP_INDEX option when turned ON drops unused or duplicated user created indexes. With this our aim was to protect user defined indexes and let users explicitly choose if they would like automatic tuning to manage dropping indexes as well.

If you would like to use the DROP_INDEX tuning option, please set the DROP_INDEX option to ON through executing the following query:

ALTER DATABASE current SET AUTOMATIC_TUNING (DROP_INDEX = ON) /* Possible values DEFAULT, ON, OFF */

The resulting output will denote in the columns desired_state and desired_state_desc that the DROP_INDEX option has been set to ON:

Reverting back automatic tuning from custom to inheriting defaults via T-SQL

Please note that once you set the AUTOMATIC_TUNING to the CUSTOM setting in order to custom configure each of the three automatic tuning options (FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEX) manually (ON or OFF), in order to revert back to the default inheritance, you will need to:

◈ set AUTOMATIC_TUNING back to AUTO or INHERIT, and also
◈ for each tuning option FORCE_LAST_GOOD_PLAN, CREATE_INDEX and DROP_INDEX for which you need to have inheritance set back to DEFAULT.

This is because automatic tuning always respects decisions users explicitly took while customizing options and it never overrides them. In such case, you will first need to set the preference to inherit the values from Azure or the parent server, followed by setting each of the three available tuning options to DEFAULT through executing the following query:

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO /* Possible values AUTO for Azure defaults and INHERIT for server defaults */
ALTER DATABASE current SET AUTOMATIC_TUNING (
FORCE_LAST_GOOD_PLAN = DEFAULT,
CREATE_INDEX = DEFAULT,
DROP_INDEX = DEFAULT
)

The result of executing the above query will be set all three automatic tuning options to inherit defaults from Azure, with the following output:

View tuning recommendations and history via T-SQL

In order to view the history of recent automatic tuning recommendations, you can retrieve this information from the system view sys.dm_db_tuning_recommendations through executing the following query:

SELECT * FROM sys.dm_db_tuning_recommendations 

Output from this view provides detailed information on the current state of automatic tuning:

To highlight some of the values available in this view, we will start with the type and the reason as why a tuning recommendation was made. The column type indicates a type of the tuning recommendation made. The column reason indicates identified reason as why a particular tuning recommendation was made.

Columns valid_since and last_refresh indicate a timespan when a tuning recommendation was made and the time until the system has considered such recommendation as beneficial to the workload performance.

The column state provides a JSON document with details of automatically applied recommendation with a wealth of information related to index management and query execution plans.

If a tuning recommendation can be executed automatically, the column is_executable_action will be populate with a bit value 1. Auto tuning recommendations flagged with a bit 1 in the column is_revertable_action denote tuning recommendations that can be automatically reverted by the system if required. The column execute_action_start_time provides a timestamp when a tuning recommendation was applied.

System created indexes column

Automatic tuning team has also added a new column auto_created in the system view (sys.indexes) that contains a bit indicating if an index was created by the automatic tuning. This is now making it possible to clearly distinct between the system and user created indexes on a database.

The column auto_created accepts a bit value of 0 and 1, whereas 0 indicates that an index was created by user, and 1 indicates that the index was created by automatic tuning.

With this flag, customers can differentiate between user and automatic tuning created indexes. This is because automatic tuning created indexes behave differently than user created indexes. When automatic tuning index is created over a column that user wants to drop, automatic tuning index will move out of the way. If this was not the case, user index would prevent this operation.