First published on MSDN on Apr 24, 2017
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_idreasonscorescript
|
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.
