Automatic tuning in Azure SQL Database
Azure SQL Database is a fully managed data service that monitors the queries that are executed on the database and automatically improves performance of the database workload. Azure SQL Database has a built-in Automatic tuning intelligence mechanism that can automatically tune and improve performance of your queries by dynamically adapting the database to your workload. Automatic tuning in Azure SQL Database might be one of the most important features that you can enable on Azure SQL Database to optimize performance of your queries.
Automatic tuning options
Automatic tuning options available in Azure SQL Database are:
- CREATE INDEX that identifies the indexes that may improve performance of your workload, creates the indexes, and verifies that they improve performance of the queries.
- DROP INDEX that identifies redundant and duplicate indexes, and indexes that were not used in the long period of time.
- PLAN REGRESSION CORRECTION that identifies SQL queries that are using execution plan that are slower than previous good plan, and uses the last known good plan instead of the regressed plan.
Azure SQL Database identifies CREATE INDEX, DROP INDEX, and PLAN REGRESSION CORRECTION recommendations that can optimize your database and shows them in Azure portal. Find more information about identification of indexes that should be changed at Find index recommendations in Azure portal. You can either manually apply recommendations using the portal or you can let Azure SQL Database to automatically apply recommendations, monitor workload after the change, and verify that the recommendation improved the performance of your workload.
Automatic tuning options can be independently turned on or off per database, or they can be configured on logical server and applied on every database that inherits settings from the server. Configuring Automatic tuning options on the server and inheriting settings on the databases in the server is recommended method for configuring automatic tuning because it simplifies management of automatic tuning options on a large number of databases.
See this article for the steps to enable automatic tuning using the Azure portal.
- To enable automatic tuning in Azure SQL Database and let automatic tuning feature fully manage your workload, see Enable automatic tuning.
- To use manual tuning, you can review Tuning recommendations in Azure portal and manually apply the ones that improve performance of your queries.
- Read more about built-in intelligence that tunes the Azure SQL Database.
- Read more about Automatic tuning in Azure SQL Database and SQL Server 2017.