dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted June 28, 2018

WEBINAR:
On-Demand

The California Consumer Privacy Act: What You Need to Know


Automatic Query Performance Tuning in SQL Server Database

By Anoop Kumar

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM