Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
What is Automatic Tuning in SQL Server?
Automatic Tuning is announced with SQL Server 2017.
Automatic Tuning is a database feature that identifies potential performance problems, makes recommendations for solving these problems, or solves these problems automatically.
Isn’t that exciting? You may be wondering whether SQL Server will perform all the performance operations by itself with this feature. Let me get to the point without you waiting any longer.
Automatic Tuning Types
There are two types of Automatic Tuning.
- Automatic Plan Correction
- Automatic Index Management
Automatic Plan Correction(Available for SQL Server 2017 and Azure SQL Database)
It solves performance plan-related performance problems by identifying problematic execution plans. In some cases, SQL Server creates a new execution plan for queries and query may run with worse performance than before. In such cases, if automatic plan correction is enabled, the latest execution plan, which is known to work well, is used instead of the newly created poor performance query plan. It can be enabled on the database as follows.
1 | ALTER DATABASE database_name SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); |
You can manually set an execution plan with QueryStore. You can find detailed information about Query Store in the article “What is Query Store in SQL Server“.
Automatic Index Management(Available for Azure SQL Database)
It specifies the indexes that should be created and removed.
It determines the index requirement needed in databases on Azure and automatically creates missing indexes and deletes unused indexes.
You can enable automatic tuning on a database basis with the default settings of azure with the following query.
1 | ALTER DATABASE database_name SET AUTOMATIC_TUNING = AUTO |
If you want to set options on the current database manually you can use the below script.
1 | ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = DEFAULT, DROP_INDEX = OFF) |
As most people thought, I thought that SQL Server may create unneeded indexes if we enable this feature. So normally when we look at the execution plan in the queries, we know that SQL Server recommends that we put 20 columns in the include column of the index as the missing index. Such situations often reduce rather than improve performance. Therefore, a good database administrator generally does not create every index recommended by SQL Server.
Microsoft thought about that. After creating the index that it thinks its missing, it continues to monitor the system and deletes the index it created if necessary. Or, it continues to monitor the system after deleting the index that SQL Server thinks its not being used and if the performance is badly affected, it recreate the index.
For more information about Indexes, I recommend you to read the article “Index Concept and Performance Effect On SQL Server“.
