SQL Server 2017 Automatic Query Tuning

By: Rajendra Gupta   |   Updated: 2017-10-24   |   Comments (4)   |   Related: > SQL Server 2017

Problem

SQL Server 2017 includes an automatic tuning feature providing insight into potential query performance problems, recommend solutions, and automatically fixing identified problems. In this tip we will explore the automatic tuning feature and see the benefits.

Solution

When we submit a query to a SQL Server database, the query optimizer is used to generate the query execution plan based on the query processor tree and the statistics. The optimizer will generate and evaluate multiple execution plans based on the calculation done for the least amount of resources: CPU, IO and query execution time. SQL Server queries the statistics to estimate the data based on the estimated execution plan and performance of the query depends on the query plan. Some of the factors impacting selection of execution plans are:

• Out of date statistics
• Improper indexing
• Badly written queries
• Re-work to modify the queries

Plan change regression happens when the SQL Server database changes a plan for some T-SQL queries and the new plan has worse performance than the previous one. Sometimes we can see the plan regression issue in our environments.

SQL Server 2016 introduced the Query Store, which makes a big difference in assisting us in monitoring and understanding what is happening in our SQL Server environments. If we are running SQL Server 2016, and enable the Query Store, this feature captures the compile time and run time statistics of statements being executed. With this feature and the data being collected, we can find out if the execution plans have regressed due to any number of reasons.  With this information we are able to identify a previous execution plan that performs better and force any future statement executions to utilize the forced execution plan. With that in place we should then be looking at identifying what caused the regression and resolve the issue, so that we are then able to unforce the execution plan thus allowing the database engine to make the correct decisions for the statement being executed.

With the release of SQL Server 2017, this is now taken one step further and the database engine is looking at the information captured in the Query Store then making decisions for us when regression is detected.  At that point the database engine will automatically force the last good plan.

Automatic tuning in SQL Server 2017 enables the identification and correction of performance issues caused by SQL Server plan choice regression.  The database engine monitors the queries that are executed on the database and automatically improves the performance of the workload. The database engine has a built-in intelligence mechanism that can automatically tune and improve performance of your queries by dynamically adapting the database to the workload.

SQL Server 2017 provides two kinds of plan correction:

• Manual Plan Correction
• Automatic Plan Correction

SQL Server Manual Plan Correction

In this mode, users must periodically monitor the system and look for the queries that regressed.  If any plan regressed, the user should find a previous good plan and force it instead of the current one using the system stored procedure sp_query_store_force_plan. The user who forces the last known good plan should monitor performance of the query that is executed using the forced plan and verify that the forced plan works as expected. Depending on the results of monitoring and analysis, the plan should be forced or the user should find some other way to optimize the query. Manually forced plans should not be forced forever, because the Database Engine should be able to apply optimal plans. The user or DBA should eventually unforce the plan using the sp_query_store_unforce_plan system stored procedure and let the Database Engine find the optimal plan.

In SQL Server 2016, we can find plan choice regressions using Query Store system views. In SQL Server 2017, the database engine detects and shows potential plan choice regressions and the recommended actions that should be applied in the sys.dm_db_tuning_recommendations view. The view shows information about the problem, the importance of the issue, and details such as the identified query, the ID of the regressed plan, the ID of the plan that was used as baseline for comparison, and the Transact-SQL statement that can be executed to fix the problem.

Information returned by sys.dm_db_tuning_recommendations is updated when the database engine identifies a potential query performance regression and it is not persisted. Recommendations are kept only until SQL Server is restarted. Database Administrators should periodically take backup copies of the tuning recommendations if they want to keep it after the server is rebooted.

We can go through the details about the sys.dm_db_tuning_recommendations from the link mentioned in Next Steps section.

By default Automatic Plan correction is disabled. We can verify it using the script below:

Use WideWorldImporters 
GO

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;

For the demonstration purposes I will be using the WideWorldImporters sample database. First, we will clear the procedure cache along with the query store data. For this we can use the below query:

 --Clear procedure cache
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
--Clear the query store
ALTER DATABASE current SET QUERY_STORE CLEAR ALL;

Now let's execute the query in the database to set up the baseline. We have used "GO 150" to execute the code 150 times.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
from Sales.OrderLines
where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
GO 150

Create the below stored procedure and execute it to generate regression.

CREATE OR ALTER PROCEDURE [dbo].[regression]
AS BEGIN

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
BEGIN
       declare @packagetypeid int = 0;
       exec report @packagetypeid;
END

GO

EXEC dbo.regression;

Now let's again run the first query, this time the query will perform slowly due to regression.

EXEC sp_executesql N'select avg([UnitPrice]*[Quantity])
                     from Sales.OrderLines
                     where PackageTypeID = @packagetypeid', N'@packagetypeid int', @packagetypeid = 7;
GO 100

Now as stated above, we can get the tuning recommendations using the sys.dm_db_tuning_recommendations view.

SELECT reason, score,
       script = JSON_VALUE(details, '$.implementationDetails.script')
FROM sys.dm_db_tuning_recommendations;

We can see in the output that it mentions the reason for tuning 'Average query CPU time changed from 2.19ms to 41.72ms' and also includes the script to force the plan using sp_query_store_force_plan.

If we want to get more details on this, we need to use the below query:

SELECT reason,type, score,
  script = JSON_VALUE(details, '$.implementationDetails.script'),
  planForceDetails.[query_id],
  planForceDetails.[new plan_id],
  planForceDetails.[recommended plan_id],
  estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)*(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
  error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
 FROM sys.dm_db_tuning_recommendations
     CROSS APPLY OPENJSON (Details, '$.planForceDetails')
                 WITH ( [query_id] int '$.queryId',
                        [new plan_id] int '$.regressedPlanId',
                        [recommended plan_id] int '$.recommendedPlanId',
                        regressedPlanErrorCount int,
                        recommendedPlanErrorCount int,
                        regressedPlanExecutionCount int,
                        regressedPlanCpuTimeAverage float,
                        recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float ) as planForceDetails;

We can see the recommended plan along with the percent gain. Now we can force the plan by running the query provided below:

exec sp_query_store_force_plan @query_id = 1, @plan_id = 1

We can see below the query execution time is now reduced.

SQL Server Automatic Tuning

In the above approach, we need to monitor and run the analysis manually. In this mode, the Database Engine can automatically switch to the last known good plan whenever plan choice regression is detected.

With this option, the Database Engine automatically detects any potential plan choice regression including the plan that should be used instead of the wrong plan. When the Database Engine applies the last known good plan, it automatically monitors the performance of the forced plan. If the forced plan is not better than the regressed plan, the new plan will be unforced and the Database Engine will compile a new plan. If the Database Engine verifies that the forced plan is better than regressed one, the forced plan will be retained until a recompile (for example, on next statistics or schema change) if it is better than the regressed plan.

To enable automatic tuning, we need to enable it as shown in the script below:

ALTER DATABASE DBNAME
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); 

Once we turn-on this option, the Database Engine will automatically force any recommendation where the estimated CPU gain is higher than 10 seconds, or the number of errors in the new plan is higher than the number of errors in the recommended plan, and verify that the forced plan is better than the current one.

Let's enable it on the WideWorldImporters database.

ALTER DATABASE wideworldimporters
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ); 

Or we can use the below script as well:

ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON);

Verify that actual state is ON:

SELECT name, actual_state_desc, status = IIF(desired_state_desc <> actual_state_desc, reason_desc, 'Status:OK')
FROM sys.database_automatic_tuning_options
WHERE name = 'FORCE_LAST_GOOD_PLAN';

Now we will perform the same test as performed above and see the recommended output:

SELECT reason, score,
       JSON_VALUE(state, '$.currentValue') state,
       JSON_VALUE(state, '$.reason') state_transition_reason,
       JSON_VALUE(details, '$.implementationDetails.script') script,
       planForceDetails.*
FROM sys.dm_db_tuning_recommendations
  CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            [new plan_id] int '$.regressedPlanId',
            [recommended plan_id] int '$.recommendedPlanId'
          ) as planForceDetails;

We can see in the output that the recommendations were automatically applied with state_transition_reason as LastGoodPlanForced.

Keep in mind that the currentValue data accessed from JSON and returned as the state column in the result set might have the following values:

The JSON document in state column contains the reason describing the current state. Values in the reason field might be:

Verify SQL Server Query Store Data in SQL Server Management Studio

In SQL Server Management Studio (SSMS), we can also review the Query Store / Top Resource Consuming Queries report as shown below:

Here we can see that the better plan is forced.

Below we can see the SQL Server query plan causing the issue.

Next Steps

• SQL Server 2017 officially launched on October 2, 2017. Check out the SQL Server 2017 Release notes.
• Learn about SQL Server 2017 - What's new in SQL Server 2017.
• Read more about SQL Server 2017 tips.

About the author

Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips