Azure SQL Database Automatic Tuning

Microsoft has recently announced an incredible new feature – automatic tuning in Azure SQL Database. To be honest, I am thoroughly impressed with this feature because Microsoft engineers have sophisticatedly used artificial intelligence in SQL Azure performance tuning. The aim is to monitor Azure SQL database and send these observations to the built-in intelligence service that generates some recommendations. They can be applied at offpeak times. This feature has also simplified the work of database administrators; they don’t have to worry about SQL Azure database performance now.

There is a key point on SQL Azure automatic tuning in MSDN documentation “Automatic tuning learns horizontally from all databases on Azure through Artificial Intelligence and it dynamically improves its tuning actions”. It means that the artificial intelligence algorithm learns huge experience from different SQL Azure databases. For this reason, these recommendations will be reliable. The automatic tuning feature can also roll back recommendations and correct itself.

Another interesting option about this feature is notifications. Microsoft is increasing the confidence of this feature by saying that it is implemented in some companies. Maybe, this approach can be a bit utopic but in the future artificial intelligence will get a lot of responsibilities of database administrators. Therefore, the number of tasks for database administrators will be reduced.

To test this feature, I created two tables on Azure SQL and populated them with some data. After I generated this test data, I executed many queries which needed the same non-clustered indexes. When I examined the estimated query execution plan, I found that it suggested a non-clustered index. Approximately, after 30 hours, Azure SQL automatic tuning generated a recommendation to create an index. For this waiting time, Microsoft notes that “Azure SQL Database needs to monitor activities at least for a day in order to identify some recommendations”.  This tuning recommendation index is the same as the missing index. When the automatic tuning tried to execute the script, there was an error because of limited disk space.

/*
Missing Index Details from SQLQuery1.sql - xxxx.database.windows.net.Adventureworks (esat.erkec)
The Query Processor estimates that implementing the following index could improve the query cost by 99.6269%.
*/

/*
USE [Adventureworks]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Salesd] ([SalesOrderID])

GO
*/

Automatic tuning options are as follows:

• Create Index that identifies the automatic index creation which may affect performance.
• Drop Index that identifies unused or redundant indexes.
• Force Last Plan Good that defines SQL query plans that are worse than the previous ones. This feature refers to automatic plan correction in SQL Server 2017.

How to enable Azure SQL automatic tuning

Azure Portal or T-SQL queries allow enabling automatic tuning options. This feature can enable automatic tuning at server or database levels at the same time. The database level can inherit these options from the server level.

Now, we will connect to Azure Portal and find SQL Servers.

Then, find automatic tuning options.

On this screenshot, we can change and apply automatic tuning options at the server level and in the default setting. All databases are inherited from the server level. However, we can also change this option at the database level.

The Current State option defines the current status of the automatic tuning option. Microsoft offers to manage this feature at the server level because its settings can be applied to all the databases.

Now, we will look at the automatic tuning options at the database level. We can find these options on SQL databases.

As you can see, we can set automatic tuning options for an individual database and change inherit options.

The above screenshot identifies the following options:

• Force Plan state is “ON” and inherited from the server level whose state is “ON”.
• Create Index state is “OFF” and inherited from server level. Its state is “OFF”.
• Drop Index state is “ON” and this option is set only for this database. The server level is not important for this setting.

We can enable automatic tuning via T-SQL:

ALTER DATABASE current SET AUTOMATIC_TUNING 
(FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = DEFAULT, DROP_INDEX)

At this step, we will look at the automatic tuning recommendations located below the SQL Server Menu.

Additionally, automatic tuning recommendations can be found below the database menu.

Tuning recommendations have different statuses:

• Pending: Apply recommendation command has been received and is scheduled for execution.
• Executing: The recommendation is being applied.
• Verifying: Recommendation was successfully applied and the service is measuring the benefits.
• Success: Recommendation was successfully applied and benefits have been measured.
• Error: An error occurred during the process of applying the recommendation. This can be a transient issue or a schema change to the table and the script is no longer valid.
• Reverting: The recommendation was applied, but has been deemed non-performant and is being automatically reverted.
• Reverted: The recommendation was reverted.

When automatic tuning creates a recommendation that will be located below the Recommendations tab, the status will be pending. When the tuning recommendation status is executing, progress, success or error, it will be shown under the Tuning history tab.  At the same time,  sys.dm_db_tuning_recommendations can return information about automatic tuning recommendation.

SELECT name, 
    JSON_VALUE(state, '$.currentValue') as script,
    JSON_VALUE(details, '$.implementationDetails.script') as script,
		
      details.* 
FROM sys.dm_db_tuning_recommendations
    CROSS APPLY OPENJSON(details, '$.createIndexDetails')
                WITH (  indexName VARCHAR(100) '$.indexName',
                        indexType VARCHAR(100) '$.indexType',
                        table_name VARCHAR(100) '$.table') as details

When we click Recommendation, we can find some details on tuning recommendation.

We can get a lot of useful information on tuning recommendation and generate a script of this feature.

The details the script will return are as follows:

• Impact defines a priority level of tuning recommendation.
• Disk space needed defines consumption of storage.
• Index type defines which type of index it will create.

Conclusions

In my opinion, SQL Azure automatic tuning is a new generation feature because Microsoft is starting to use artificial intelligence algorithms in tuning recommendation. At the same time, this feature can lead to the following questions:

• When should I have this tuning recommendation?
• Is this tuning recommendation is useful? If it is not useful, when should I roll back this tuning recommendation?

As a result, SQL Azure automatic tuning offers a futuristic approach.

References

Automatic tuning in Azure SQL Database

Tuning performance in Azure SQL Database

Artificial Intelligence tunes Azure SQL Databases