Sql Server 2017 Auto Tuning
Azure SQL Database is an automatically managed data service that constantly monitors your queries and identifies the action that you can perform to improve performance of your workload. You can review recommendations and manually apply them, or let Azure SQL Database automatically apply corrective actions - this is known as automatic tuning mode.
In this course, SQL Server: Automatic Tuning in SQL Server 2017 and Azure SQL Database, you'll learn how Query Store has been upgraded to also collect wait statistics from queries, to allow you to identify performance bottlenecks at the query level and dig deeper to resolve them. May 16, 2017 This is the blog of the engineering team managing and developing the Database Engine for SQL Server and Azure SQL Database Automatic index management in Azure SQL database May 16, 2017 May 16, 2017 by Jovan Popovic (MSFT) // 1 Comments.
Automatic tuning can be enabled at the server or the database level through the Azure portal, REST API calls and T-SQL commands.
Note
For Managed Instance, the supported option FORCE_LAST_GOOD_PLAN can be configured through T-SQL only. Portal based configuration and automatic index tuning options described in this article do not apply to Managed Instance.
Note
Configuring Automatic tuning options through ARM (Azure Resource Manager) template is not supported at this time.
Enable automatic tuning on server
On the server level you can choose to inherit automatic tuning configuration from 'Azure Defaults' or not to inherit the configuration. Azure defaults are FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is enabled, and DROP_INDEX is disabled.
Important
As of March, 2020 changes to Azure defaults for automatic tuning will take effect as follows:
- New Azure defaults will be FORCE_LAST_GOOD_PLAN = enabled, CREATE_INDEX = disabled, and DROP_INDEX = disabled.
- Existing servers with no automatic tuning preferences configured will be automatically configured to INHERIT the new Azure defaults. This applies to all customers currently having server settings for automatic tuning in an undefined state.
- New servers created will automatically be configured to INHERIT the new Azure defaults (unlike earlier when automatic tuning configuration was in an undefined state upon new server creation).
Azure portal
To enable automatic tuning on Azure SQL Database logical server, navigate to the server in Azure portal and then select Automatic tuning in the menu.
Korg polysix vst crack. Note
Please note that DROP_INDEX option at this time is not compatible with applications using partition switching and index hints and should not be enabled in these cases. Dropping unused indexes is not supported for Premium and Business Critical service tiers.
Select the automatic tuning options you want to enable and select Apply.
Automatic tuning options on a server are applied to all databases on this server. By default, all databases inherit configuration from their parent server, but this can be overridden and specified for each database individually.
REST API
Find out more about using REST API to enable Automatic tuning on a server, see SQL Server Automatic tuning UPDATE and GET HTTP methods.
Enable automatic tuning on an individual database
The Azure SQL Database enables you to individually specify the automatic tuning configuration for each database. On the database level you can choose to inherit automatic tuning configuration from the parent server, 'Azure Defaults' or not to inherit the configuration. Azure Defaults are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is enabled, and DROP_INDEX is disabled.
Tip
The general recommendation is to manage the automatic tuning configuration at server level so the same configuration settings can be applied on every database automatically. Configure automatic tuning on an individual database only if you need that database to have different settings than others inheriting settings from the same server.
Azure portal
To enable automatic tuning on a single database, navigate to the database in Azure portal and select Automatic tuning.
Individual automatic tuning settings can be separately configured for each database. You can manually configure an individual automatic tuning option, or specify that an option inherits its settings from the server.
Please note that DROP_INDEX option at this time is not compatible with applications using partition switching and index hints and should not be enabled in these cases.
Once you have selected your desired configuration, click Apply.
Rest API
Find out more about using REST API to enable Automatic tuning on a single database, see SQL Database Automatic tuning UPDATE and GET HTTP methods.
Sql Server Express
T-SQL
To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:
Setting automatic tuning to AUTO will apply Azure Defaults. Setting it to INHERIT, automatic tuning configuration will be inherited from the parent server. Choosing CUSTOM, you will need to manually configure automatic tuning.
To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this one:
Setting the individual tuning option to ON, will override any setting that database inherited and enable the tuning option. Setting it to OFF, will also override any setting that database inherited and disable the tuning option. Automatic tuning option, for which DEFAULT is specified, will inherit the automatic tuning configuration from the server level settings.
Important
In case of active geo-replication, Automatic tuning needs to be configured on the primary database only. Automatically applied tuning actions, such are for example index create or delete will be automatically replicated to the read-only secondary. Attempting to enable Automatic tuning via T-SQL on the read-only secondary will result in a failure as having a different tuning configuration on the read-only secondary is unsupported.
Find our more abut T-SQL options to configure Automatic tuning, see ALTER DATABASE SET Options (Transact-SQL) for SQL Database server.
Disabled by the system
Automatic tuning is monitoring all the actions it takes on the database and in some cases it can determine that automatic tuning can't properly work on the database. In this situation, tuning option will be disabled by the system. In most cases this happens because Query Store is not enabled or it's in read-only state on a specific database.
Permissions
As automatic tuning is Azure feature, to use it you will need to use Azure's built-in RBAC roles. Using SQL Authentication only will not be sufficient to use the feature from Azure portal.
To use automatic tuning, the minimum required permission to grant to the user is Azure's built-in SQL DB contributor role. You can also consider using higher privilege roles such are SQL Server Contributor, Contributor and Owner.
Configure automatic tuning e-mail notifications
See automatic tuning e-mail notifications guide.
Next steps
- Read the Automatic tuning article to learn more about automatic tuning and how it can help you improve your performance.
- See Performance recommendations for an overview of Azure SQL Database performance recommendations.
- See Query Performance Insights to learn about viewing the performance impact of your top queries.
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.
| 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
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:
Example 2
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 VIEW SERVER STATE permission in SQL Server.
Requires the VIEW DATABASE STATE permission for the database in Azure SQL Database.
Sql Server 2017 Auto Tuning Free
See Also
Sql Server 2017 Auto Tuning Reviews
Automatic Tuning
sys.database_automatic_tuning_options (Transact-SQL)
sys.database_query_store_options (Transact-SQL)
JSON Support