What is Automatic Tuning in Azure SQL Database

, 2018-08-28

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.

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 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 tuning 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 indexes, now that I am more familiar with it I realize it only drops those it created and now based on AI knows if they are useful or not. The fact that Automatic Tuning was developed and tested over 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 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 workload and may hold them. You will see several “states” of recommendations. This is a big win for me.

State Description
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 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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads