How to find query plan choice regressions with SQL Server 2017 CTP2

SQL Server 2017 in CTP2.0  version automatically identifies potential performance regressions in SQL queries, enables you to easily find potential issues, and gives you information what should you do to fix the issue.

In CTP2.0 version is added new system view sys.dm_db_tuning_recommendations that returns recommendations that you can apply to fix potential problems in your database. This view contains all identified potential performance issues in SQL queries that are caused by the SQL plan changes, and the correction scripts that you can apply. Every row in this view contains one recommendation that you can apply to fix the issue. Some of the information that are shown in this view are:

• Id of the query, plan that caused regression, and the plan that that might be used instead of this plan.
• Reason that describes what kind of regression is detected (e.g. CPU time for the query is changed from 17ms to 189ms)
• T-SQL script that can be used to force the plan.
• Information about the current plan, and previous plan that had better performance.

Since some of the information are formatted as JSON documents, you can use the following query to parse details in recommendations and return information about the query, identified problem, T-SQL script that you can apply, etc.

 SELECT planForceDetails.query_id, reason, score,
       JSON_VALUE(details, '$.implementationDetails.script') script,
       planForceDetails.[new plan_id], planForceDetails.[recommended plan_id]
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 '$.forcedPlanId'
                 ) as planForceDetails;

 

This query returns something like the following results:

query_id	reason	score	script
17	Average query CPU time changed from 3.55ms to 127.78ms	80	exec sp_query_store_force_plan @query_id = 17, @plan_id = 41
1804	Average query CPU time changed from 13.5ms to 27.78ms	23	exec sp_query_store_force_plan @query_id = 1804, @plan_id = 17
1304	Average query CPU time changed from 8.4ms to 207.8ms	41	exec sp_query_store_force_plan @query_id = 1304, @plan_id = 41

If you look at this results, you will be able to find queries that are slower, see what is the difference in CPU time, and execute the script in the [script] column and fix the problem. Score column is internal score that estimates the importance of the recommendation, and you can use this column to sort recommendations when you review them.

When you execute the script returned by the query (e.g. exec sp_query_store_force_plan @query_id = 1804, @plan_id = 17) the plan will be forced until you clean Query Store data, or manually unforce it using sp_query_store_unforce_plan procedure.

Dynamic Management View sys.dm_db_tuning_recommendations will not monitor T-SQL queries with the plans that are forced by user, cursor plans, BULK INSERT, and the queries with OPTION RECOMPILE. If you force the plan, Database engine will assume that you will monitor the query, and if you use OPTION RECOMPILE database engine might generate too many recommendations because the plan would change on every execution.

This scenario is designed for users who want to have full control over the plan execution and just need some need help to automatically identify potentially regressed queries.

You can also let Database engine to automatically force plans whenever the regression is detected, manage forced plans, verify that forced plan perform better, and revert it if there are no significant benefits. Find more info about this in Automatic tuning features in SQL Server 2017 on Microsoft Doc site.

Comments

• Anonymous
  April 24, 2017
  The comment has been removed
  • Anonymous
    April 25, 2017
    Hi Mohamed,Thanks for your comments. I have updated the post so I hope that I have clarified your questions. To summarize:1. This scenario is designed for people who want to have full control and monitor forced plans, and this feature just helps them to identify issues that they need to investigate.2. Plan forcing is the same as in SQL Server 2016 - plans are forced until you clear query store or unforce plan. In this scenario, it is expected that the user who forced the plan needs to monitor and unforce plan if needed.3. We are not reporting issues on the forced plans. Once the plan is forced, the previous plans might not be recently executed so there is a lower probability that comparison between forced plan and the plan that was executed a long time ago would be valid. This is current assumption and it might change depending on the feedback.I will add more posts where I will describe other scenario where database engine completely manages forced plan.Thanks,Jovan
    • Anonymous
      April 25, 2017
      The comment has been removed
      • Anonymous
        April 25, 2017
        The comment has been removed