As you might guess, automatic tuning is the process where by the last step from the preceding example (in that case performed by you) is automatically done by SQL Server. To see automatic tuning in action, you will again execute the code from the previous subsection, but with an additional database setting. Ensure that the Discard results after execution option is turned on in your SSMS and execute the following code:

USE WideWorldImporters;
GO
ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR;
ALTER DATABASE WideWorldImporters SET QUERY_STORE = OFF;
GO
ALTER DATABASE WideWorldImporters 
SET QUERY_STORE = ON 
( 
OPERATION_MODE = READ_WRITE, 
INTERVAL_LENGTH_MINUTES = 1 
); 
GO
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON); 
GO
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 110;
GO
SET NOCOUNT ON;
SELECT *
FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE o.SalespersonPersonID IN (0,897);
GO 1000
ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 140;
GO
SET NOCOUNT ON;
SELECT *
FROM Sales.Orders o
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE o.SalespersonPersonID IN (0,897);
GO 1000

In the preceding code, you ensure that Query Store is turned on and empty and then execute the same query under the same circumstances as in the previous subsection. The only difference compared to the previous case is the FORCE_LAST_GOOD_PLAN option. It is turned on to instruct SQL Server to automatically enforce an old plan, whenever a regression plan is detected.

After executing the code, you can re-check the queries with forced plans report. The following screenshot shows that plan forcing is done automatically:

As you can see, this time you did not need to perform any action: SQL Server silently identified and fixed the problem with the plan regression. That sounds perfect and very promising for SQL Server performance troubleshooting. However, before you turn this feature on, you have to be sure that you want to enforce the old plan whenever SQL Server detects a plan regression. I have to confess that this code example did not always work when I tried it. As you can see in the code, I had to execute the query at least 1,000 times to get desired results. And even this did not work every time. Thus, you should not expect that every regression will be automatically fixed. On the other hand, in the offline mode, each execution was successful and ended with appropriate recommendations.

As a rule of thumb—if the old execution plan was stable (CPU time was relatively constant for different calls and different parameters) and suddenly (or after an upgrade or a patch) a new plan is created, and is significantly slower, you'll want to force the new plan. If you had several plans in the past, or the execution or CPU time varies with the same plan from execution to execution, it is better to monitor the query execution, analyze it, and then eventually force the old plan, instead of letting SQL Server force it automatically.

In my experience, collected during the book preparation and usage in our test and production systems, Query Store is a great troubleshooting and monitoring tool and I would highly recommend to you to use it. However, the Automatic tuning feature does not seem to be stable and does not work always as expected. Therefore, for dealing with regressed queries, I would not recommend this option automatically, I would rather refer to the results of the sys.dm_db_tuning_recommendations dynamic management view and decide about potential tuning actions manually.