In Focus

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 more 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 which is the results of machine learning processes being applied directly to data generated by Query Store.

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 which 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 then 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 making 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.

 

What Is Automatic Tuning In Azure SQL Database

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.

  1. ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM  
  2. 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.

What Is Automatic Tuning In Azure SQL Database

What Is Automatic Tuning In Azure SQL 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.

StateDescription
PendingApply recommendation command has been received and is scheduled for execution.
ExecutingThe recommendation is being applied.
VerifyingRecommendation was successfully applied, and the service is measuring the benefits.
SuccessRecommendation was successfully applied, and benefits have been measured.
ErrorAn 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.
RevertingThe recommendation was applied but has been deemed non-performant and is being automatically reverted.
RevertedThe 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.