Automatic Tuning – Azure SQL Database & Managed Instance

Azure, SQL Server / DBA Comments Off on Automatic Tuning – Azure SQL Database & Managed Instance

Azure SQL Database and Azure SQL Managed Instance both are Azure’s PaaS implementations whereby Microsoft takes care of all management tasks of underlying systems. Automatic Tuning is also a fully managed service to monitor the queries executed on a database and improves their performance.

Automatic Tuning = Automatic Plan Correction + Automatic Index Management

Automatic Plan Correction is something which has been a feature of SQL Server Database Engine since 2017 which analyzes the query plan of queries being executed, identifies the problematic plans and then fixes the performance issues by forcing last good known plan of the query. However, Azure SQL Database extends this to cover Automatic Index Management also.

It’s a continuous process whereby all the data captured about all the queries being executed on the databases is analyzed.

Automatic tuning process

And this is not a just simple reading of data and a plain analysis, Microsoft documentation says that this is driven by Artificial Intelligence to have positive impact on the performance side. All the identified optimizations are applied only when the resource utilization is low so as not to interfere with the currently running workloads.

How does automatic tuning work

System may suspend this temporarily if the resource consumption is high. You will see a message like “Disabled by the system” displayed on the portal in such case. This generally happens when Query Store gets turned into Read-only mode.

Let’s take a note of some key aspects of it –

  1. Automatic Plan Correction is controlled by “FORCE LAST GOOD PLAN” configuration
  2. Automatic Index Management have two options – CREATE INDEX and DROP INDEX
  3. This can be enabled via Azure portal or T-SQL for Azure SQL Database
  4. Managed Instance allows only T-SQL option to configure this
  5. Also, Managed Instance doesn’t support Automatic Index Management i.e. none of the CREATE INDEX or DROP INDEX statements are supported just like similar to a core database engine feature
  6. DROP INDEX identifies and drops redundant and duplicate indexes though not supported in Business Critical or Premium service tiers
  7. This can be configured at the database level or server level

If not configured, database inherits the configuration from server and if not configured at server level also, it inherits from Azure’s default which is as shown in the below snapshot –

Default Settings
As per Microsoft

You can set them up using T-SQL as follows – 

ALTER DATABASE current SET Automatic_Tuning = AUTO
-- OR
ALTER DATABASE current SET Automatic_Tuning = INHERIT

To configure each option separately – 

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
-- OR
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (CREATE_INDEX  = ON, DROP_INDEX = ON)
-- OR
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX  = ON, DROP_INDEX = ON)

You can query the following catalog views to verify the configurations – 

SELECT * FROM sys.database_automatic_tuning_mode
-- OR
SELECT * FROM sys.database_automatic_tuning_options

However, if you run these queries on SQL Server (Not Azure offerings), keep in mind that only automatic plan correction is supported so options corresponding to index maintenance won’t be available or visible.

To conclude this, note that all the data it analyzes is what is being captured by Query Store which is enabled on Azure SQL databases by default.