When moving SQL databases to Azure, let Azure do some work for you. As the name suggests, the Azure SQL Database Automatic Tuning feature provides automatic performance optimization for databases. Activate it, it's simple.
Once the data is available in Azure SQL Database, there is no more server maintenance necessary. Azure SQL Database is a relational database-as-a service that runs in the Microsoft Cloud. With the service there comes a bunch of benefits. One of them is the Automatic Tuning Mode. This enables stable workloads through continuous performance tuning utilizing Artificial Intelligence and applying corrective actions for databases in use. See more at Automatic tuning in Azure SQL Database
Automatic tuning needs to be manually enabled in the Azure portal, see Enable automatic tuning. The admin needs to set the inheritance to Azure defaults or on database server level or per database.
Currently the options covered in Automatic Tuning are: Create Index, Drop Index and Force good plan to use the last known good execution plan instead of the regressed plan. Administrators can set Automatic tuning to Azure defaults or on a server level as well. This screenshot shows the Don't inherit option selected with custom switches for the current SQL server.
The Azure defaults are set to
- FORCE_LAST_GOOD_PLAN enabled
- CREATE_INDEX enabled, and
- DROP_INDEX disabled.
The documentation informs about more details.
Automatic Tuning can even be done programmatically, see more at REST API. In a single database, the following TSQL command allows to set Auto Tuning as well:
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = DEFAULT, DROP_INDEX = OFF)
Usually, the recommendation is to use Azure defaults. The resulting settings look as here (or are set to Inherit):
The Recommendations inform about existing database issues, the Automatic Tuning would resolve. As you can see in this sample, there are quite some recommendations on this SQL Azure server.
Once enabled, each database shows the Automatic Tuning status configured. The recommendations should vanish after some time when the tuning has done its job.
This short article shall act as a reminder to check your Azure SQL Database for Automatic Tuning. For doing more performance optimization, check out Tuning performance in Azure SQL Database. When designing a new database for performance, see Optimize performance by using In-Memory technologies in SQL Database.
Enable Automatic Tuning to optimize your SQL databases performance!