Configure database auto-tuning
In this we will learn how to configure database auto-tuning with enabling automatic tuning in the Azure portal to monitor queries and improve workload performance.
Azure SQL Database automatically manages data services that constantly monitor your queries and identifies the action that you can perform to improve performance of your workload. However, you can review recommendations and manually apply them, or let Azure SQL Database automatically apply corrective actions – this is known as automatic tuning mode.
Automatic tuning can be enabled at the server or the database level through:
- Firstly, the Azure portal
- Secondly, REST API calls
- Lastly, T-SQL commands
Enable automatic tuning on server
On the server level you can choose to inherit automatic tuning configuration from “Azure Defaults” or not to inherit the configuration. Azure defaults are FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is disabled, and DROP_INDEX is disabled.
Azure portal
To enable automatic tuning on a server in Azure SQL Database. Firstly, navigate to the server in the Azure portal and then select Automatic tuning in the menu.
After that, select the automatic tuning options you want to enable and select Apply.
However, automatic tuning options on a server are applied to all databases on this server. By default, all databases inherit configuration from their parent server, but this can be overridden and specified for each database individually.
Enable automatic tuning on an individual database
Azure SQL Database enables you to individually specify the automatic tuning configuration for each database. Further, on the database level you can choose to inherit automatic tuning configuration from the parent server, “Azure Defaults” or not to inherit the configuration. Azure Defaults are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is disabled, and DROP_INDEX is disabled.
Azure portal
To enable automatic tuning on a single database. Then, navigate to the database in the Azure portal and select Automatic tuning.
Individual automatic tuning settings can be separately configured for each database. You can manually configure an individual automatic tuning option, or specify that an option inherits its settings from the server. However, once you have selected your desired configuration, click Apply.
T-SQL
To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:
SQL
ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM
Setting automatic tuning to AUTO will apply Azure Defaults. Setting it to INHERIT, automatic tuning configuration will be inherited from the parent server. Choosing CUSTOM, you will need to manually configure automatic tuning.
Next, to configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this one:
SQL
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = OFF)
Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option. Setting it to OFF will also override any setting that database inherited and disable the tuning option. Automatic tuning option, for which DEFAULT is specified, will inherit the automatic tuning configuration from the server level settings.
Disabled by the system
Automatic tuning is monitoring all the actions it takes on the database and in some cases it can determine that automatic tuning can’t properly work on the database. In this situation, the tuning option will be disabled by the system. In most cases this happens because Query Store is not enabled or it’s in read-only state on a specific database.
Permissions
As automatic tuning is an Azure feature, to use it you will need to use Azure’s built-in roles. Using SQL Authentication only will not be sufficient to use the feature from the Azure portal.
However, to use automatic tuning, the minimum required permission to grant to the user is Azure’s built-in SQL Database contributor role. You can also consider using higher privilege roles such as SQL Server Contributor, SQL Managed Instance Contributor, Contributor, and Owner.
Reference: Microsoft Documentation