SQL Server 2017 Automatic Query Tuning

By:   |   Comments (4)   |   Related: > SQL Server 2017


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.


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 

SELECT name, desired_state_desc, actual_state_desc, reason_desc
FROM sys.database_automatic_tuning_options;
Verify SQL Server 2017 Automatic Plan correction is disabled

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
--Clear the query store

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]

       declare @packagetypeid int = 0;
       exec report @packagetypeid;


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;
get the tuning recommendations using the sys.dm_db_tuning_recommendations view

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.[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;

Detailed tuning recommendations using the sys.dm_db_tuning_recommendations view

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 2017 query execution time is 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:


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

Or we can use the below script as well:

Enable SQL Server 2017 automatic tuning

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
Validate that SQL Server 2017 automatic tuning is enabled

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,
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;
SQL Server 2017 automatic tuning recommendation from the original query

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:

Status Description
Active Recommendation is active and not yet applied. User can take recommendation script and execute it manually.
Verifying Recommendation is applied by Database Engine and internal verification process compares performance of the forced plan with the regressed plan. Success Recommendation is successfully applied.
Success Recommendation is successfully applied.
Reverted Recommendation is reverted because there are no significant performance gains.
Expired Recommendation has expired and cannot be applied anymore.

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

Reason Description
SchemaChanged Recommendation expired because the schema of a referenced table is changed.
StatisticsChanged Recommendation expired due to the statistic change on a referenced table
ForcingFailed Recommended plan cannot be forced on a query. Find the last_force_failure_reason in the sys.query_store_plan view to find the reason of the failure.
AutomaticTuningOptionDisabled FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. Enable FORCE_LAST_GOOD_PLAN option using ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) statement or force the plan manually using the script in [details] column. UnsupportedStatementType Plan cannot be forced on the query. Examples of unsupported queries are cursors and INSERT BULK statement.
UnsupportedStatementType Plan cannot be forced.
LastGoodPlanForced Recommendation applied.
AutomaticTuningOptionNotEnabled SQL Server Automatic Tuning is not enabled.
VerificationAborted FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. Enable FORCE_LAST_GOOD_PLAN option using ALTER DATABASE SET Verification process is aborted due to the restart or Query Store cleanup.
VerificationForcedQueryRecompile No significant performance improvement.
PlanForcedByUser User manually forced the plan using sp_query_store_force_plan (Transact-SQL) procedure..
PlanUnforcedByUser User manually unforced the plan using sp_query_store_unforce_plan (Transact-SQL) procedure.

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:

Verify SQL Server Query Store Data in SQL Server Management Studio

Here we can see that the better plan is forced.

See that a better query plan is forced in SQL Server 2017

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

SQL Server query plan causing the issue
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta 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

Comments For This Article

Thursday, August 2, 2018 - 8:22:56 AM - rajendra Back To Top (76944)

 In which query you got this error

Incorrect syntax near '$.queryId'.

Wednesday, August 1, 2018 - 10:13:55 PM - ob213 Back To Top (76934)

I am getting an error: 

Msg 102, Level 15, State 1, Line 214

Incorrect syntax near '$.queryId'.

Sunday, July 22, 2018 - 9:41:01 AM - Marios Philippopoulos Back To Top (76762)

I've been looking for an easy-to-do demo to try out this feature. After a quick glance, it seems this is it!

I will definitely give it a try.

Thank you for taking the time to write this article and for the in-depth explanation!

Marios Philippopoulos

Tuesday, December 19, 2017 - 3:51:06 PM - Eric Back To Top (74253)

Great write up.  I encountered one issue when running your examples...in your Regression stored proc you are calling "exec report @packagetypeid;"  however SQL can't find a stored procedure called "report" in  WideWorldImporters.   "The module 'regression' depends on the missing object 'report'. The module will still be created; however, it cannot run successfully until the object exists."


get free sql tips
agree to terms