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.
