Automatic Query Performance Tuning in SQL Server Database

Introduction

Microsoft has introduced many new features to improve query performance in SQL Server. For example, SQL Server  2017 and Azure SQL Database have Query Store, Adapting Query Processing, Automatic Tuning of SQL Query, etc. to improve the query performance.

In previous articles, we’ve learned how Query Store and Adapting Query Processing help SQL Server users to improve query execution performance.

In this article we’ll discuss Automatic Tuning, a new database feature that helps improve SQL Server query performance automatically.

In the past, SQL Server Database Administrators (DBAs) spent a lot of time in investigating the root cause of query performance issues. DBAs always try to find the peak workload on SQL Server, which SQL queries are consuming the resources, and which set of queries are really hitting the performance? Once DBAs identify the real bottleneck of the system, then they work on possible resolutions to the issue and try to improve the query performance. This entire process used to take hours or days, or sometime weeks based on the size of the problem and possible resolutions to implement.

Microsoft tried to optimize this process and help SQL Server DBAs to reduce their efforts and time in finding the real bottlenecks and provide recommendations to resolve the issues or resolve the issues automatically using a new database feature called Automatic Tuning.

Automatic Tuning

Automatic Tuning is a new database feature introduced in SQL Server 2017 and Azure SQL Database. The Database Engine monitors the executed queries on the database and notifies the DBA whenever a potential performance issue is detected. This query performance issue can be caused by SQL Plan Choice Regression and SQL Server Database Engine lets DBAs take corrective action or if Database Engine is configured to take action to fix the problem, Database Engine fixes the problem automatically.

In Automatic Tuning, the Database Engine continuously monitors the workload on the database. Automatic Tuning enables the Database Engine to identify which indexes and query plans can improve the performance of the database workload and which can affect the workload negatively. After identifying the improvement, Automatic Tuning applies the tuning recommendations to improve the performance. Automatic Tuning not only applies the changes automatically, it also verifies that the changes should improve the performance and if it doesn’t, all the changes will be reverted back automatically. This verification process is an important feature to ensure that all the changes applied by Automatic Tuning will not reduce the performance.    

Automatic Tuning has the following two features available to improve query performance:

Automatic Plan Correction: This feature is available with both versions of SQL Server; on-premise SQL Server (SQL Server 2017) and the cloud version of SQL Server (Azure SQL Database). This feature identifies the issue in the query execution and fixes the SQL Query plan.

Automatic Index Management: This feature is available only on the cloud version of SQL Server (Azure SQL Database) and is not available in on-premise SQL Server. This feature helps in identifying the required indexes to be added or removed in the database.  

How Automatic Plan Correction Works

Automatic Tuning has a two step process to identify a query performance issue and once identified, take action to fix the issue. These two steps are as follows:

  1. SQL Plan Choice Regression: SQL Server Database Engine provides information about regressed plans and recommends corrective actions. Additionally, Database Engine enables you to automate this process and the Database Engine fixes the problem by itself, based on the query plan changes. To execute a SQL Query, Database Engine may use a query plan, which may not be an optimal query plan to execute compared to the previously executed query plans and might cause a performance regression. There is a possibility that the query plan might be changed over time due to a change in indexes, statistics and other factors, causing a new query plan to be developed,  which might not be better than the previous one. 

    Once SQL Server Database Engine identifies the plan regression then it will take corrective action to fix the problem. The next step is Automatic Plan Choice Correction.

  2. Automatic Plan Choice Correction: This is the step where the Database Engine applies the last known good plan on the detection of Plan Choice Regression. Automatic Tuning automatically detects the Plan Choice Regression plan and the plan which should be forced instead of the regressed plan. As we highlighted earlier, Automatic Tuning monitors the performance after forcing the plan and if it detects that the forced plan is not performing well, it reverts the changes automatically. Otherwise, the newly forced plan will be available until the plan recompiles on the occasion of statistics or schema change.

Automatic Index Management

As we know, Automatic Index Management is only available on cloud database (Azure SQL Database). This feature helps in identifying that database tables have been optimally indexed and give a quick response to the queries during the execution. Automatic Index Management either notifies about detection of Indexes to be added or removed in the database tables or takes the required action automatically.

How Automatic Index Management Works

Index design is very important for getting good query performance, and Automatic Index Management can help in optimizing table indexes to get the same in two ways by using in-built intelligence and advanced rules that analyze the queries throughout the execution:

  1. Detect new indexes that could improve the query performance .
  2. Detect indexes that have not been used in a long time or redundant indexes to be removed. The removal of indexes could help in data update operations.

In addition to detection, Automatic Index Management can automatically apply identified recommendations in Azure SQL Database tables. 

Enable Automatic Tuning using T-SQL

There are multiple ways to enable Automatic Tuning at the database level. We are showing the T-SQL method to enable Automatic Tuning.

Enable Automatic Plan Correction

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Enable Automatic Index Management

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = DEFAULT, DROP_INDEX = OFF);

Setting Options

Individual tuning option set to ON will override any setting that the database inherited and enable the tuning option.

Individual tuning option set to OFF will override any setting that the database inherited and disable the tuning option.

Individual tuning option set to DEFAULT will inherit the configuration from the database level automatic tuning setting.

Summary

Automatic tuning is a database feature that provides insight into possible query performance problems, recommends solutions, and provides options to fix identified problems either manually or automatically.

See all articles by Anoop Kumar

Anoop Kumar
Anoop Kumar
Anoop has 15+ years of IT experience, mostly in design and development of Enterprise Data warehouse and Business Intelligence solutions. Currently, Anoop is working on various Big Data and NoSQL based solution implementations. Anoop has written many online technical articles on Big Data, Hadoop, SQL Server and SSIS. On an education front, he has a Post Graduate degree in Computer Science from Birla Institute of Technology, Mesra, India. Disclaimer : I help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles