Automatic Tuning Enabled By Default In Azure SQL Database Happens Today!
From January 15th (ie, this Monday) Automatic Tuning will be enabled by default and gradually rolled out to ALL Azure subscriptions. If you are the owner of a subscriber you would have received an email two weeks ago alerting you of this fact. However if you’re not, and this has not been communicated out to you, this may be something of a surprise.
With regards to how the rollout impacts you, this blog post states that “All servers that do not have automatic tuning explicitly configured will inherit Azure defaults, making automatic tuning enabled. Similarly, all databases that do not have automatic tuning explicitly configured will inherit the configuration from the parent server. All newly created databases by default will inherit the configuration from the parent server.” So depending on how you have your servers and databases configured will determine your impact. There’s a little more marketing speak on Microsoft Docs.
At any rate, Automatic Tuning is, according to the blurb on the Microsoft website, “Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems.”
There are two automatic tuning features -
Automatic Plan Correction – Fixes plan performance problems by resolving Plan Choice Regressions.
Automatic Index Management – Identifies Indexes to add/remove that will improve performance.
It’s important to note that whilst Automatic Tuning is available in SQL Server 2017, only Automatic Plan Correction is the only feature. I believe more will be added in the future…
So, Automatic Index Management is an interesting feature, especially from a deployment POV – If you have a database whose code in entirely within source control, having indexes created in production that don’t exist in source this can be problematic. Similarly, chances are your database across environments will have different workloads, so the automatic tuning can create different indexes, or not anticipate the indexes required in production.
We’ve spoken at length amongst ourselves and with clients with the best course of action to take, at least for the time being. And unless you have a really good feedback loop from production as to what changes have been made to the database which may need to be bought back in to source prior to a deployment. Without the visibility to see what has changed, you will always have to leave objects in the database even if they are no longer in source, which is a less than ideal situation.
Consider also that a new index may be a covering index, and whilst indexes no longer required will eventually be dropped, dropped indexes do not occur quickly – it’s not dropped within minutes but a lot longer, much longer! By being able to review the change and recognise ourselves that an old index is now redundant, we can drop that ourselves way before Automatic Tuning does.
To be clear, we don’t think the feature is a bad one. Look at it this way; it won’t make a bad database significantly less worse, it will help, but not really. However it won’t make a good database worse. Irrespective of the overall quality of the database, it poses problems in terms of schema management. In the near future we will further expand on how to manage the indexes changes.