在 Azure 门户中启用自动优化以监视查询并提高工作负载性能Enable automatic tuning in the Azure portal to monitor queries and improve workload performance

适用于:是 Azure SQL 数据库 是Azure SQL 托管实例 APPLIES TO: yesAzure SQL Database yesAzure SQL Managed Instance

Azure SQL 数据库是自动托管的数据服务,可持续监视查询并识别可为改善工作负载性能执行的操作。Azure SQL Database automatically manages data services that constantly monitor your queries and identifies the action that you can perform to improve performance of your workload. 可以查看建议并手动应用这些建议,或者让 Azure SQL 数据库自动应用纠正措施 - 这称为自动优化模式You can review recommendations and manually apply them, or let Azure SQL Database automatically apply corrective actions - this is known as automatic tuning mode.

可通过以下方法在服务器或数据库级别启用自动优化:Automatic tuning can be enabled at the server or the database level through:

备注

对于 Azure SQL 托管实例,支持的选项 FORCE_LAST_GOOD_PLAN 只能通过 T-SQL 进行配置。For Azure SQL Managed Instance, the supported option FORCE_LAST_GOOD_PLAN can only be configured through T-SQL only. 本文中所述的基于 Azure 门户的配置和自动索引优化选项不适用于 Azure SQL 托管实例。The Azure portal based configuration and automatic index tuning options described in this article do not apply to Azure SQL Managed Instance.

备注

目前不支持通过 ARM(Azure 资源管理器)模板配置自动优化选项。Configuring automatic tuning options through the ARM (Azure Resource Manager) template is not supported at this time.

在服务器上启用自动优化Enable automatic tuning on server

在服务器级别上,可选择从“Azure 默认值”继承自动优化配置,或选择不继承配置。On the server level you can choose to inherit automatic tuning configuration from "Azure Defaults" or not to inherit the configuration. Azure 默认设置为启用 FORCE_LAST_GOOD_PLAN,禁用 CREATE_INDEX 并禁用 DROP_INDEX。Azure defaults are FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is disabled, and DROP_INDEX is disabled.

重要

从 2020 年 3 月开始,用于自动优化的新 Azure 默认设置如下所示:As of March, 2020 new Azure defaults for automatic tuning are as follows:

  • 启用 FORCE_LAST_GOOD_PLAN,禁用 CREATE_INDEX,禁用 DROP_INDEX。FORCE_LAST_GOOD_PLAN = enabled, CREATE_INDEX = disabled, and DROP_INDEX = disabled.
  • 未配置自动优化首选项的现有服务器将自动配置为继承 Azure 默认设置。Existing servers with no automatic tuning preferences configured are automatically configured to INHERIT the Azure defaults. 这适用于当前有用于自动优化的服务器设置处于未定义状态的所有客户。This applies to all customers currently having server settings for automatic tuning in an undefined state.
  • 创建的新服务器将自动配置为继承 Azure 默认设置(与之前创建新服务器时自动优化配置处于未定义状态时不同)。New servers created will automatically be configured to INHERIT the Azure defaults (unlike earlier when automatic tuning configuration was in an undefined state upon new server creation).

Azure 门户Azure portal

若要在 Azure SQL 数据库中的服务器上启用自动优化,请在 Azure 门户中导航到该服务器,然后在菜单中选择“自动优化”。To enable automatic tuning on a server in Azure SQL Database, navigate to the server in the Azure portal and then select Automatic tuning in the menu.

服务器

备注

请注意,此时 DROP_INDEX 选项与使用分区切换和索引提示的应用程序不兼容,并且不应在这些情况下打开。Please note that the 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 APIREST API

了解有关使用 REST API 在服务器上启用自动优化的详细信息,请参阅服务器自动优化 UPDATE 和 GET HTTP 方法To find out more about using a REST API to enable automatic tuning on a server, see Server automatic tuning UPDATE and GET HTTP methods.

对单个数据库启用自动优化Enable automatic tuning on an individual database

Azure SQL 数据库支持为每个数据库单独指定自动优化配置。Azure SQL Database enables you to individually specify the automatic tuning configuration for each database. 在数据库级别中,可选择从“Azure 默认值”继承自动优化配置,或选择不继承配置。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 默认设置为启用 FORCE_LAST_GOOD_PLAN,禁用 CREATE_INDEX 并禁用 DROP_INDEX。Azure Defaults are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is disabled, and DROP_INDEX is disabled.

提示

常规建议是在服务器级别管理自动优化配置,以便为每个数据库自动应用相同的配置设置。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 门户Azure portal

若要对单个数据库启用自动优化,请在 Azure 门户中导航到该数据库,然后选择“自动优化” 。To enable automatic tuning on a single database, navigate to the database in the 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.

数据库

请注意,此时 DROP_INDEX 选项与使用分区切换和索引提示的应用程序不兼容,并且不应在这些情况下启用。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 APIRest API

了解有关使用 REST API 在单个数据库上启用自动优化的详细信息,请参阅 Azure SQL 数据库自动优化 UPDATE 和 GET HTTP 方法To find out more about using a REST API to enable automatic tuning on a single database, see Azure SQL Database automatic tuning UPDATE and GET HTTP methods.

T-SQLT-SQL

要通过 T-SQL 在单个数据库上启用自动优化,请连接至数据库,并执行以下查询:To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:

ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO | INHERIT | CUSTOM

将自动优化设置为 AUTO 时,会应用 Azure 默认值。Setting automatic tuning to AUTO will apply Azure Defaults. 将其设置为 INHERIT 时,会从父服务器继承自动优化配置。Setting it to INHERIT, automatic tuning configuration will be inherited from the parent server. 选择 CUSTOM 时,需手动配置自动优化。Choosing CUSTOM, you will need to manually configure automatic tuning.

要通过 T-SQL 配置单个自动优化选项,请连接至数据库并执行如下所示的查询:To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this one:

ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = ON, DROP_INDEX = OFF)

将单个自动优化选项设置为 ON 时,数据库所继承的任何设置都将被替代,并会启用优化选项。Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option. 将其设置为 OFF 时,数据库所继承的任何设置亦将被替代,并会禁用优化选项。Setting it to OFF will also override any setting that database inherited and disable the tuning option. 自动优化选项(指定为 DEFAULT)将从服务器级别设置中继承自动优化配置。Automatic tuning option, for which DEFAULT is specified, will inherit the automatic tuning configuration from the server level settings.

重要

对于活动异地复制,只需在主数据库上配置自动优化。In the case of active geo-replication, Automatic tuning needs to be configured on the primary database only. 自动应用的优化操作(例如索引创建或删除)将自动复制到只读辅助数据库。Automatically applied tuning actions, such as for example index create or delete will be automatically replicated to the read-only secondary. 尝试在只读辅助数据库上通过 T-SQL 启用自动优化将导致失败,因为不支持在只读辅助数据库上使用不同的优化配置。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.

了解有关用于配置自动优化的 T-SQL 选项的详细信息,请参阅 ALTER DATABASE SET 选项 (Transact-SQL)To find out more abut T-SQL options to configure automatic tuning, see ALTER DATABASE SET Options (Transact-SQL).

已被系统禁用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, the 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

由于自动优化是 Azure 功能,因此若要使用它,需使用 Azure 的内置 RBAC 角色。As automatic tuning is an Azure feature, to use it you will need to use Azure's built-in RBAC roles. 若要使用 Azure 门户中的功能,仅使用 SQL 身份验证还不够。Using SQL Authentication only will not be sufficient to use the feature from the Azure portal.

若要使用自动优化,必须为用户授予权限,最低必需权限是 Azure 的内置 SQL 数据库参与者角色。To use automatic tuning, the minimum required permission to grant to the user is Azure's built-in SQL Database contributor role. 也可考虑使用特权较高的角色,例如 SQL Server 参与者、SQL 托管实例参与者、参与者和所有者。You can also consider using higher privilege roles such as SQL Server Contributor, SQL Managed Instance Contributor, Contributor, and Owner.

后续步骤Next steps