Monitoring databases for optimal query performance, creating and maintaining required indexes, and dropping rarely-used, unused or expensive indexes is a common database administration task. As administrators, we’ve all wished, at some point, that these tasks were simpler to handle.

SQL Server 2017 can now assist database administrators in performing some of these routine operations by identifying problematic query execution plans and fixing problems with the SQL plan performance. Automatic tuning starts with continuously monitoring the database and learning about the workload that it serves. Automatic tuning is based on Artificial Intelligence, which makes managing the performance of the system flexible.

SQL Server can use different strategies (or SQL plans) to execute a T-SQL query. SQL Server analyzes possible plans that can be used to execute a T-SQL query and chooses the optimal plan. The plans for most of the successfully executed queries are cached and reused when the same query is executed. The plan is retained in the cache until the SQL Database Engine decides to recompile the plan and find a new one (e.g. when statistics change, index is added or removed, etc.).

The most traditional way to troubleshoot performance issues is by measuring the wait statistics. These metrics are further classified into various categories. When SQL Server is executing and waiting for the resources, the corresponding entry is made in the system objects. We can query the system objects using DMV (Dynamic Management View) sys.dm_os_wait_stats. The nature of the DMV output is cumulative; it provides an aggregated value. It keeps adding the values at frequent intervals of time. The workaround for keeping track of values during each check or get a trend is by creating a repository and pull the data from the DMV and store it at frequent intervals of time for later querying. This gives the data for specific time period for performance analysis and troubleshooting the issues. This process is a little cumbersome; most administrators do not automate the process until they deep-dive into a particularly problematic situation. However, we can track the wait stats in the Query Store with a few simple steps. There are several options available to configure the Query Store. The wait stats are further grouped into wait categories. (There are over 900+ wait types available in SQL Server.) We can query the DMV sys.query_store_wait_stats to get the wait information. The wait categories are stored in the query store along with the date and the timestamp.

The Query Store feature was introduced in SQL Server 2016. The SQL Server Query Store allows storing the history of queries, multiple plans, run time statistics, etc., and provides an insight into the query plan and performance of the database. This feature allows us to find regressed queries more easily. If a plan is not optimal and if there were plans that performed better, then we can unforce the plan, and use the more-optimal plan. This can be done using the stored procedures, sp_query_store_force_plan and sp_query_store_unforce_plan.

Automatic plan correction is a new automatic tuning feature in SQL Server 2017 that identifies SQL query plans that are worse than the previous one, and fixes performance issues by applying the previous good plan instead of the regressed one. Doing so allows the database engine to identify opportunities where alterations to the execution plans might increase the performance of the system. By default, the Automatic Plan Correction feature is disabled.

Also, automatic tuning can actually apply any suggested alterations according to its predictions. Finally—and this is the really smart part—automatic tuning continues to monitor the performance of the system after a change is made, to ensure that the expected results are achieved. If the change is found to not actually increase performance (or worse, found to negatively impact performance) it reverts such tuning recommendations.

Automatic Plan Correction(APC) is an extension of sp_query_sotre_force_plan. Forcing a plan is an effort to identify the optimal plan. However, it’s a manual effort in the Query Store to manage the plans (forcing and un-forcing actions are to be taken) for better performance. APC on the other hand is automatic in nature and is available in SQL Server 2017 as well as in Azure SQL Database. It uses the query store telemetry data to select and recommend the optimal plan. This is the reason the Query Store is a prerequisite.

• Data collection happens in the Query Store
• The most optimal plan is selected using dm_db_tuning_recommendations
• Automatic Plan Correction takes place based on the data
• The last good plan is reverted to

Enable the Query Store using SSMS

1. Use Object Explorer to browse the database properties
2. Select the Query Store option

3. In the Operation Mode (Requested) , select Read Write

   

Enable the Query Store using T-SQL

Use the ALTER DATABASE to enable the query store.

Demonstration

We’ll use the WideWorldImporters database for the demonstration.

1. Restore the WWI database from the backup. You can download the database here

2. Clone the data to SalesOrderLines table from sales.OrderLines and Sales.Orders to SalesOrders table

   1 2 3

   SELECT * INTO SalesOrderLines from sales.OrderLines   SELECT * INTO SalesOrders from sales.Orders

3. Enable and set the values to configure the Query Store

   1 2 3 4 5 6 7 8 9 10

   ALTER DATABASE CURRENT SET QUERY_STORE = ON; GO ALTER DATABASE CURRENT SET QUERY_STORE (   OPERATION_MODE = READ_WRITE,   DATA_FLUSH_INTERVAL_SECONDS = 600,   MAX_STORAGE_SIZE_MB = 500,   INTERVAL_LENGTH_MINUTES = 30   ); GO

4. Clear the Procedure cache using the following T-SQL

   1	ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;

5. Clear the Query Store using the following T-SQL

   1	ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL;

6. Enable the Automatic Tuning option on the database

   1 2 3

   ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON ); GO

7. Verify the database settings and options

   1	SELECT * FROM sys.database_automatic_tuning_options

   
   

   

Every parameter and every small change in a query impacts it in some way or the other. Conversely, the performance of a query does not change if there’s no change in the configuration or the underlying data. When a query is executed, SQL Server Engine chooses what it thinks is the best plan, when executing. If a plan was compiled and cached, it can be reused from cache. In some situations, even if a certain plan was not the most optimal, but performed better than the current plan on similar parameters, the SQL Server Engine would use the old plan. This is called a plan regression.

Identifying and forcing the query plan to perform optimally is very complex and tedious job, if it is done manually, but in SQL Server 2017, the Automatic Tuning feature does it efficiently.

Let’s start the SQL workload by executing the following SQL 600-900 times

To view the Query Store performance dashboard, browse the database, explore the Query Store component and select the Top Resource Consuming Queries option

Introduce regression

In this case we clear the cache using ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE before I execute the query. SQL Server must recompile the query and generate the new query plan.

Start the SQL workload process

We can see in this case that that plan 1 is forced.

SQL Server 2017 provides a new system view, sys.dm_db_tuning_recommendations, which shows all the identified plan regressions.

The state column of the sys.dm_db_tuning_recommendations DMV indicates that the recommendation is applied and a validation is in progress, upon comparing the performance of the forced plan and regressed plan.

Let’s create the missing indexes named NCI_SalesOrderLines_PTID on salesorderlines table. The schema of the salesorderline object is thus modified.

Now, run the aforementioned SQL query to check the status

Expired in the state column indicates that the recommended plan has expired and is now invalid. The state column can have different values such as Active, Verifying, Success, Reverted and Expired.

Summary

SQL Server 2017 introduces an automatic tuning feature, which is like a safety net for you workload. It prevents performance degradation of query plans. More information on the automatic tuning feature in SQL Server 2017 is available here.

The SQL Server Engine can compare and correct bad plan choices, which also happen to be one of the prime reasons to push for the upgrade/migration of SQL Server to SQL Server 2017. When the SQL Server Engine encounters a plan performance regression, the last good plan is forced upon, thereby improving performance. Also, if the forced plan does no good to the performance, the query is recompiled.

One point to keep in mind is to not clear the procedure cache on a production system because it will affect all queries! Also, since the data is not persisted in the DMV, the process of capturing the necessary information and transferring it to a permanent table for analysis and reporting should be carried out. However, this can be automated.

Table of contents

• Author
• Recent Posts

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

• Stairway to SQL essentials - April 7, 2021
• A quick overview of database audit in SQL - January 28, 2021
• How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021