What Is Automatic Tuning In Azure SQL Database

Microsoft has long given us DBA tools and suggestions to help tune databases, to fix queries with indexes, and many times they were of questionable merit. We have all seen the dreaded dta_index names in a database and rolled our eyes knowing that sometimes these indexes are not what is needed. While the Database Tuning Advisor has gotten better over time it is still not a tool most senior DBAs use. Microsoft made big steps in helping DBAs with performance with the introduction of Query Store in both Azure SQL Database and SQL Server 2016. Query Store collects execution and run-time performance information. Microsoft has taken an additional leap with the introduction of Automatic Tuning. Automatic Tuning is an intelligent performance tuning service that is the result of machine learning processes being applied directly to data generated by Query Store.

By continuously monitoring queries, Automatic Tuning can quickly and intelligently improve their performance. Since it is based on machine learning, it adapts to changing workloads and therefore is better at index recommendations than previously. You can enable it to Create Indexes, Drop Indexes, and Force the Last Good Plan on the database level (which is the feature that is available in SQL Server 2017), so it’s not an all-or-nothing feature. I prefer not to have things automatically done, so given that you can set index creation or plan correction to allow you to manually apply recommendations using the portal it is a great feature.

According to Microsoft, there is a benefit to having it automatically make changes. They state: “The benefits of letting the system autonomously apply to tune recommendations for you is that in such case it automatically validates there exists a positive gain to the workload performance, or otherwise if a regression is detected it will automatically revert the tuning recommendation.” When manually applying suggestions the reversal mechanism is not available.

To enable Automatic Tuning.

• Log in to the Azure portal.
• Go to your SQL database and click on it.
• On the menu to the left, choose Automatic Tuning.

Here, you can toggle on and off each option separately. When I first started using it, I tended not to let it drop the indexes. Now that I am more familiar with it, I realize it only drops those it created and now based on AI it knows if they are useful or not. The fact that Automatic Tuning was developed and tested on millions of different real-world workloads in Azure makes this a very promising feature for me.

If you choose not to use the GUI, you can enable these using T-SQL as well.

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM;
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = DEFAULT, DROP_INDEX = OFF);

The force_last_good_plan flag will work in SQL Server 2017 as well.

This is what recommendations look like in Azure (I took an image from MSDN as I didn’t have any good examples to show you from my own environment). It keeps a very nice history, so you can follow the changes over time. To manually run the recommendations, you can click on any one of them and then click View Script then run it against your database.

Recommendations sometimes are not applied right away as Azure makes sure it does not interfere with the workload and may hold them. You will see several “states” of recommendations. This is a big win for me.

State	Description
Pending	The apply recommendation command has been received and is scheduled for execution.
Executing	The recommendation is being applied.
Verifying	A recommendation was successfully applied, and the service is measuring the benefits.
Success	A 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 possibly 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.

So far, I think Microsoft is on the right track with this. I look forward to seeing what else they come up with.