Sep 27, 2017 Perhaps though the most compelling enhancement in SQL Server 2017 is Automatic Tuning. Parameter Sniffing got you down? Parameter Sniffing got you down? Automatic Tuning uses Query Store to detect query plan regressions and automatically forces a. Sep 15, 2018 Automatic tuning is the new features which is came up in Microsoft SQL Server 2017. I have demonstrated the key functionality and configuration of Automatic plan correction in this video.
- Sql Server 2017 Updates
- Sql Server 2017 Auto Tuning Reviews
- Index Tuning Sql Server
- Performance Tuning Sql Server 2012
- Sql Server 2017 Download
Sql Server 2017 Updates
-->SQL Server 2017 includes an automatic tuning feature providing insight into potential query performance problems, recommend solutions, and automatically fixing identified problems. In this tip we will explore the automatic tuning feature and see the benefits. Sep 20, 2019 Hello, This past summer we migrated to OS 2019 and SQL 2017. I have the automatic tuning feature enabled for the DB in question and all has been fantastic until this morning.
Returns detailed information about tuning recommendations.
In Azure SQL Database, dynamic management views cannot expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out.
Sql Server 2017 Auto Tuning Reviews
Column name | Data type | Description |
---|---|---|
name | nvarchar(4000) | Unique name of recommendation. |
type | nvarchar(4000) | The name of the automatic tuning option that produced the recommendation, for example, FORCE_LAST_GOOD_PLAN |
reason | nvarchar(4000) | Reason why this recommendation was provided. |
valid_since | datetime2 | The first time this recommendation was generated. |
last_refresh | datetime2 | The last time this recommendation was generated. |
state | nvarchar(4000) | JSON document that describes the state of the recommendation. Following fields are available: - currentValue - current state of the recommendation.- reason - constant that describes why the recommendation is in the current state. |
is_executable_action | bit | 1 = The recommendation can be executed against the database via Transact-SQL script. 0 = The recommendation cannot be executed against the database (for example: information only or reverted recommendation) |
is_revertable_action | bit | 1 = The recommendation can be automatically monitored and reverted by Database engine. 0 = The recommendation cannot be automatically monitored and reverted. Most 'executable' actions will be 'revertable'. |
execute_action_start_time | datetime2 | Date the recommendation is applied. |
execute_action_duration | time | Duration of the execute action. |
execute_action_initiated_by | nvarchar(4000) | User = User manually forced plan in the recommendation. System = System automatically applied recommendation. |
execute_action_initiated_time | datetime2 | Date the recommendation was applied. |
revert_action_start_time | datetime2 | Date the recommendation was reverted. |
revert_action_duration | time | Duration of the revert action. |
revert_action_initiated_by | nvarchar(4000) | User = User manually unforced recommended plan. System = System automatically reverted recommendation. |
revert_action_initiated_time | datetime2 | Date the recommendation was reverted. |
score | int | Estimated value/impact for this recommendation on the 0-100 scale (the larger the better) |
details | nvarchar(max) | JSON document that contains more details about the recommendation. Following fields are available:planForceDetails - queryId - query_id of the regressed query.- regressedPlanId - plan_id of the regressed plan.- regressedPlanExecutionCount - Number of executions of the query with regressed plan before the regression is detected.- regressedPlanAbortedCount - Number of detected errors during the execution of the regressed plan.- regressedPlanCpuTimeAverage - Average CPU time (in micro seconds) consumed by the regressed query before the regression is detected.- regressedPlanCpuTimeStddev - Standard deviation of CPU time consumed by the regressed query before the regression is detected.- recommendedPlanId - plan_id of the plan that should be forced.- recommendedPlanExecutionCount - Number of executions of the query with the plan that should be forced before the regression is detected.- recommendedPlanAbortedCount - Number of detected errors during the execution of the plan that should be forced.- recommendedPlanCpuTimeAverage - Average CPU time (in micro seconds) consumed by the query executed with the plan that should be forced (calculated before the regression is detected).- recommendedPlanCpuTimeStddev Standard deviation of CPU time consumed by the regressed query before the regression is detected.implementationDetails - method - The method that should be used to correct the regression. Value is always TSql .- script - Transact-SQL script that should be executed to force the recommended plan. |
Remarks
Index Tuning Sql Server
Information returned by
sys.dm_db_tuning_recommendations
is updated when database engine identifies potential query performance regression, and is not persisted. Recommendations are kept only until SQL Server is restarted. Database administrators should periodically make backup copies of the tuning recommendation if they want to keep it after server recycling.currentValue
field in the state
column might have the following values:Status | Description |
---|---|
Active | Recommendation is active and not yet applied. User can take recommendation script and execute it manually. |
Verifying | Recommendation is applied by Database Engine and internal verification process compares performance of the forced plan with the regressed plan. |
Success | Recommendation is successfully applied. |
Reverted | Recommendation is reverted because there are no significant performance gains. |
Expired | Recommendation has expired and cannot be applied anymore. |
JSON document in
state
column contains the reason that describes why is the recommendation in the current state. Values in the reason field might be:Reason | Description |
---|---|
SchemaChanged | Recommendation expired because the schema of a referenced table is changed. New recommendation will be created if a new query plan regression is detected on the new schema. |
StatisticsChanged | Recommendation expired due to the statistic change on a referenced table. New recommendation will be created if a new query plan regression is detected based on new statistics. |
ForcingFailed | Recommended plan cannot be forced on a query. Find the last_force_failure_reason in the sys.query_store_plan view to find the reason of the failure. |
AutomaticTuningOptionDisabled | FORCE_LAST_GOOD_PLAN option is disabled by the user during verification process. Enable FORCE_LAST_GOOD_PLAN option using ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL) statement or force the plan manually using the script in [details] column. |
UnsupportedStatementType | Plan cannot be forced on the query. Examples of unsupported queries are cursors and INSERT BULK statement. |
LastGoodPlanForced | Recommendation is successfully applied. |
AutomaticTuningOptionNotEnabled | Database Engine identified potential performance regression, but the FORCE_LAST_GOOD_PLAN option is not enabled - see ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL). Apply recommendation manually or enable FORCE_LAST_GOOD_PLAN option. |
VerificationAborted | Verification process is aborted due to the restart or Query Store cleanup. |
VerificationForcedQueryRecompile | Query is recompiled because there is no significant performance improvement. |
PlanForcedByUser | User manually forced the plan using sp_query_store_force_plan (Transact-SQL) procedure. Database engine will not apply the recommendation if user explicitly decided to force some plan. |
PlanUnforcedByUser | User manually unforced the plan using sp_query_store_unforce_plan (Transact-SQL) procedure. Since the user explicitly reverted the recommended plan, database engine will keep using the current plan and generate a new recommendation if some plan regression occurs in future. |
Statistic in the details column do not show runtime plan statistics (for example, current CPU time). The recommendation details are taken at the time of regression detection and describe why Database Engine identified performance regression. Use
regressedPlanId
and recommendedPlanId
to query Query Store catalog views to find exact runtime plan statistics.Examples of using tuning recommendations information
Example 1
The following gets the generated Transact-SQL script that forces a good plan for any given query:
Performance Tuning Sql Server 2012
Example 2
Sql Server 2017 Download
The following gets the generated Transact-SQL script that forces a good plan for any given query and additional information about the estimated gain:
Example 3
The following gets the generated Transact-SQL script that forces a good plan for any given query and additional information that includes the query text and the query plans stored in Query Store:
For more information about JSON functions that can be used to query values in the recommendation view, see JSON Support in Database Engine.
Permissions
Requires
Requires the
VIEW SERVER STATE
permission in SQL Server.Requires the
VIEW DATABASE STATE
permission for the database in Azure SQL Database.See Also
Automatic Tuning
sys.database_automatic_tuning_options (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
JSON Support
sys.database_automatic_tuning_options (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
JSON Support