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]
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:
|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.
Find more info about Automatic tuning features in SQL Server 2017 on Microsoft Doc site.