Azure SQL Automatic Tuning

June 19, 2023

The days of manually tuning database performance are over, maybe.

I encountered the Automatic tuning feature of Azure SQL for the first time the other day and was immediately impressed. Previously, you would need to spend days to weeks understanding how indexing works, then analyzing the queries used by an application before even attempting to optimize an application's performance. After deciding on the index that you want to create, and then actually creating it, you would spend days monitoring the application's performance to ensure that the index is helping and not hurting your situation.

Automatic tuning saves you all of this overhead. It actively analyzes your application's least performant queries and creates indexes for you. It also monitors the performance of these indexes so that you don't have to. If the index is not helping, it will remove it. If it is helping, it will keep it. It will also drop indexes that are not being used. Depending on the application, this could be a huge time saver and cost reducer.

Some Considerations

  • Automatic tuning is comprised of three primary features: CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN. Each can be enabled individually.
  • If a column is added to an index without your knowledge, you will not be able to drop that column without first dropping the index. This may cause issues when running migrations using an ORM such as Entity Framework.
  • If you have an application where you wish to maintain more control over the indexes, you could consider enabling automatic tuning on your Development and/or QA environments, but not on your Production environment. This would allow you to see what indexes automatic tuning recommends and the outcome of its validation of those indexes. You could then manually create the indexes you wish to keep and utilize in Production using migrations and manually remove the automatically created indexes from the non-production environments. The caveat here is that your non-production environments would need to have similar workloads to your Production environment in order for the index recommendations to be on point, so an environment where regular end-to-end or performance testing is performed that mimics actual, real-world use cases would be ideal. You would also want to regularly review the recommendations being made by Azure SQL in your Production database to ensure there isn't anything there that would have a high impact which isn't being seen in your non-production environments.

Conclusion

Query analysis and index creation is one of the primary jobs of a database administrator and often also falls to application developers, who spend time communicating and coordinating with those DBAs. Automatic tuning can eliminate the overhead of analysis, implementation, and validation of indexes to a high degree. The automatic tuning feature has been available in some form since 2016, so it is quite mature at this point. Microsoft claims it to be "safe, reliable, and proven." My suggestion is to give it a try!

Additional Reading


Written by Tom