The new SQL Server 2017 sys.dm_db_tuning_recommendations system dynamic management view returns detailed information about tuning recommendations for queries which execution details are captured by Query Store. To see this feature in action, you will repeat the scenario from the previous section, executing a query in two different compatibility levels, but this time you'll let SQL Server automatically identify the regression and suggest the fix.

Open your SSMS and turn on the Discard results after execution option. You need to choose the Query Options menu item, click on the Results node in the left pane tree view, and check the Discard results after execution checkbox. This option will prevent SSMS from writing output in the results pane and speed up the overall execution in this exercise. After you have set this option, run the following code in the WideWorldImporters database:

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 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

The first few statements ensure that Query Store is enabled and empty for the sample database. After that, the code looks the same as at the beginning of the chapter, and the same thing happened. The second execution is slower because the old execution plan for that query is better than the new one. Instead of searching for regression in Query Store reports, this time you will query the new sys.dm_db_tuning_recommendations dynamic management view:

SELECT * FROM sys.dm_db_tuning_recommendations;

The output produced by this query is shown as follows:

Maybe the most interesting detail here is the value in the reason column. You can see the sentence Average query CPU time changed from 0.03 ms to 10.2 ms, which describes the reason why this entry is shown. Since you know the nature of the queries that you ran, you know that the first time is related to the average CPU time for the query where the compatibility level was 110, while the second refers to the execution under the latest compatibility mode. From the CPU usage point of view, the regression is significant; the second query uses 340 times more CPU time!

The figure does not show the other attributes in detail, but you can check them in SSMS. In addition to the reason column, there are two more interesting columns: state and details. Both of them contain JSON data. Here is the content of the state column:

{"currentValue":"Active","reason":"AutomaticTuningOptionNotEnabled"}

You can see two JSON keys: currentValue and reason, which say more about the current recommendation. The value Active means that the recommendation is active, but not applied. You can find a full list and a description of all keys and values at https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql.

The details column is also JSON, but has more data. Here is the content, but formatted with the JSON formatter used in Chapter 5, JSON Support in SQL Server:

   {
   "planForceDetails":{
      "queryId":1,
      "regressedPlanId":2,
      "regressedPlanExecutionCount":18,
      "regressedPlanErrorCount":0,
      "regressedPlanCpuTimeAverage":1.019883333333333e+004,
      "regressedPlanCpuTimeStddev":2.017456808018999e+003,
      "recommendedPlanId":1,
      "recommendedPlanExecutionCount":998,
      "recommendedPlanErrorCount":0,
      "recommendedPlanCpuTimeAverage":2.860120240480962e+001,
      "recommendedPlanCpuTimeStddev":2.651439807288578e+001
   },
   "implementationDetails":{
      "method":"TSql",
      "script":"exec sp_query_store_force_plan @query_id = 1, @plan_id = 1"
    }
}

Here are more details showing the query_id and plan_id of the regressed query, average CPU time for both plans, and so on. You can also see the recommended command that can solve the regression by forcing the old plan. Since the data in these two columns is JSON data, to get the most important information from the view you need to parse JSON data. Here is a query that returns the most important details from the sys.dm_db_tuning_recommendations view:

SELECT 
    reason, 
    score,
    details.[query_id],
    details.[regressed_plan_id],
    details.[recommended_plan_id],
    JSON_VALUE(details, '$.implementationDetails.script') AS command
 FROM sys.dm_db_tuning_recommendations
    CROSS APPLY OPENJSON (details, '$.planForceDetails')
        WITH ( 
                query_id INT '$.queryId',
                regressed_plan_id INT '$.regressedPlanId',
                recommended_plan_id INT '$.recommendedPlanId'
              ) AS details;

This query produces the output shown in the following screenshot:

You can see query_id, reason, and command, which you need to execute to fix the query. Execute the following command to fix the plan:

EXEC sp_query_store_force_plan @query_id = 1, @plan_id = 1;
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);

To confirm that the old plan is enforced for that query, you can check the Queries With Forced Plan report as shown in the following screenshot:

You can see that the old plan is forced, and also used when you executed the query under the latest compatibility level.