Automatic tuning is a database feature that provides insight into potential query performance problems, recommend solutions, and automatically fix identified problems.
Automatic tuning in SQL Server 2017, notifies you whenever a potential performance issue is detected, and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems. Automatic tuning in SQL Server 2017 enables you to identify and fix performance issues caused by SQL plan choice regressions.
What is plan choice regression?
SQL Server Database Engine may use different SQL plans to execute the Transact-SQL queries. Query plans depend on the statistics, indexes, and other factors. The optimal plan that should be used to execute some Transact-SQL query might be changed over time. In some cases, the new plan might not be better than the previous one, and the new plan might cause a performance regression.
In order to prevent unexpected performance issues, users must periodically monitor system and look for the queries that regressed. If any plan regressed, user should find some
previous good plan and force it instead of the current one using
sp_query_store_force_plan procedure. The best practice would be to force last known good plan because older plans might be invalid due to statistic or index changes.
The user who forces the last known good plan should monitor performance of the query that is executed using the forced plan and verify that forced plan works as expected. Depending on
the results of monitoring and analysis, plan should be forced or user should find some other way to optimize the query.
Manually forced plans should not be forced forever, because the Database Engine should be able to apply optimal plans. The user or DBA should eventually
unforce the plan using
sp_query_store_unforce_plan procedure, and let the Database Engine find the optimal plan.
SQL Server provides all necessary views and procedures required to monitor performance and fix problems in Query Store. However, continuous monitoring and fixing performance issues might be a tedious process.
Database Engine in SQL Server 2017 provides information about regressed plans and recommended corrective actions. Additionally, Database Engine enables you to fully automate this process and let Database Engine fix any problem found related to the plan changes.
How to detect plan choice regression?
In SQL Server 2017, the Database Engine detects and shows potential plan choice regressions and the recommended actions that should be applied in the sys.dm_db_tuning_recommendations (Transact-SQL) view. The view shows information about the problem, the importance of the issue, and details such as the identified query, the id of the regressed plan, the id of the plan that was used as baseline for comparison, and the Transact-SQL statement that can be executed to fix the problem.
||CPU time changed from 4 ms to 14 ms||3/17/2017||83||
||CPU time changed from 37 ms to 84 ms||3/16/2017||26||
Some columns from this view are described in the following list:
- Type of the recommended action -
- Description that contains information why Database Engine thinks that this plan change is a potential performance regression.
- Datetime when the potential regression is detected.
- Score of this recommendation.
- Details about the issues such as id of the detected plan, id of the regressed plan, id of the plan that should be forced to fix the issue, Transact-SQL script that might be applied to fix the issue, etc. Details are stored in JSON format.
Use the following query to obtain a script that fixes the issue and additional information about the estimated gain:
SELECT reason, score, script = JSON_VALUE(details, '$.implementationDetails.script'), planForceDetails.*, estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount) *(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000, error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO') FROM sys.dm_db_tuning_recommendations CROSS APPLY OPENJSON (Details, '$.planForceDetails') WITH ( [query_id] int '$.queryId', [current plan_id] int '$.regressedPlanId', [recommended plan_id] int '$.recommendedPlanId', regressedPlanErrorCount int, recommendedPlanErrorCount int, regressedPlanExecutionCount int, regressedPlanCpuTimeAverage float, recommendedPlanExecutionCount int, recommendedPlanCpuTimeAverage float ) as planForceDetails;
Here is the result set.
|reason||score||script||query_id||current plan_id||recommended plan_id||estimated_gain||error_prone|
|CPU time changed from 3 ms to 46 ms||36||EXEC sp_query_store_force_plan 12, 17;||12||28||17||11.59||0|
estimated\_gain represents the estimated number of seconds that would be saved if the recommended plan would be executed instead of the current plan. The recommended plan should be forced instead of the current plan if the gain is greater than 10 seconds. If there are more errors (for example, time-outs or aborted executions) in the current plan than in the recommended plan, the column
error\_prone would be set to the value
YES. Error prone plan is another reason why the recommended plan should be forced instead of the current one.
Automatic plan choice correction
In addition to detection, the Database Engine can automatically switch to the last known good plan whenever the regression is detected.
When the Database Engine applies a recommendation, it automatically monitors the performance of the forced plan. The forced plan will be retained until a recompile (for example, on next statistics or schema change) if it is better than the regressed plan. If the forced plan is not better than the regressed plan, the new plan will be unforced and the Database Engine will compile a new plan.
The user can enable automatic tuning per database and specify that last good plan should be forced whenever some plan change regression is detected. Automatic tuning is enabled using the following command:
ALTER DATABASE current SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
Once you turn-on this option, Database Engine will automatically force any recommendation where the gain is higher than 10 seconds, or the number of errors in the new plan is higher than the number of errors in the recommended plan, and verify that the forced plan is better than the current one.
The status of the automatic tuning option is shown in the following view:
SELECT name, desired_state_desc, actual_state_desc, reason_desc FROM sys.database_automatic_tuning_options;
Here is the result set.
FORCE_LAST_GOOD_PLAN option might be in
OFF state even if the user specified
ON. The option might be disabled if Query Store is disabled or in read-only mode. Column
gives information about the current state of automatic tuning option, and column
reason_desc gives information why is actual state different that desired state.
reason_desc column are shown in the following table:
||Option is disabled by system.|
||Query Store is turned off.|
||Query Store is in read-only mode.|
||Available only in Enterprise Edition of SQL Server.|
ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)