Azure SQL 数据库中的自动优化Automatic tuning in Azure SQL Database

通过基于人工智能和机器学习的持续性能优化,Azure SQL 数据库自动优化可以提供最佳性能和稳定的工作负载。Azure SQL Database automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.

自动优化是一项完全托管的智能性能服务,它使用内置的智能来持续监视在数据库上执行的查询,并自动提高其性能。Automatic tuning is a fully managed intelligent performance service that uses built-in intelligence to continuously monitor queries executed on a database, and it automatically improves their performance. 这是通过动态调整数据库以适应不断变化的工作负载并应用优化建议来实现的。This is achieved through dynamically adapting database to the changing workloads and applying tuning recommendations. 自动优化通过人工智能向 Azure 上的所有数据库进行横向学习,并动态地提高其优化操作。Automatic tuning learns horizontally from all databases on Azure through AI and it dynamically improves its tuning actions. 在开启自动优化时,数据库运行时间越长,执行能力就越好。The longer a database runs with automatic tuning on, the better it performs.

Azure SQL 数据库自动优化可能是可用于提供稳定及最佳性能数据库工作负载的最重要的功能之一。Azure SQL Database automatic tuning might be one of the most important features that you can enable to provide stable and peak performing database workloads.

自动优化有何作用What can automatic tuning do for you

  • Azure SQL 数据库的自动性能优化Automated performance tuning of Azure SQL databases
  • 性能优化的自动验证Automated verification of performance gains
  • 自动回退和自我更正Automated rollback and self-correction
  • 优化历史记录Tuning history
  • 优化用于手动部署的 T-SQL 脚本Tuning action T-SQL scripts for manual deployments
  • 主动的工作负载性能监视Proactive workload performance monitoring
  • 数以十万计的数据库上的横向扩展功能Scale out capability on hundreds of thousands of databases
  • 对 DevOps 资源和总拥有成本的积极影响Positive impact to DevOps resources and the total cost of ownership

安全、可靠且久经考验Safe, Reliable, and Proven

应用于 Azure SQL 数据库中数据库的优化操作非常安全,可满足最紧张工作负载的性能需求。Tuning operations applied to databases in Azure SQL Database are fully safe for the performance of your most intense workloads. 系统精心设计,旨在不干扰用户工作负载。The system has been designed with care not to interfere with the user workloads. 仅在利用率较低的情况下才应用自动优化建议。Automated tuning recommendations are applied only at the times of a low utilization. 系统还可暂时禁用自动优化操作以保护工作负载性能。The system can also temporarily disable automatic tuning operations to protect the workload performance. 在此情况下,Azure 门户中会显示“由系统禁用”消息。In such case, "Disabled by the system" message will be shown in Azure portal. 自动优化对工作负载持以最高的资源优先级。Automatic tuning regards workloads with the highest resource priority.

自动优化机制非常成熟,已经在 Azure 上运行的数百个数据库中进行了完善。Automatic tuning mechanisms are mature and have been perfected on several million databases running on Azure. 应用的自动优化操作将会自动验证,确保工作负载性能得到显著改善。Automated tuning operations applied are verified automatically to ensure there is a positive improvement to the workload performance. 动态检测针对退化性能的建议并立即优化。Regressed performance recommendations are dynamically detected and promptly reverted. 通过优化所记录的历史记录,可清楚地跟踪对每个 Azure SQL 数据库进行的优化改进。Through the tuning history recorded, there exists a clear trace of tuning improvements made to each Azure SQL Database.

自动优化的工作原理

Azure SQL 数据库自动优化与 SQL Server 自动优化引擎共享其核心逻辑。Azure SQL Database automatic tuning is sharing its core logic with the SQL Server automatic tuning engine. 有关内置智能机制的其他技术信息,请参阅 SQL Server 自动优化For additional technical information on the built-in intelligence mechanism, see SQL Server automatic tuning.

启用自动优化Enable automatic tuning

你可以在 Azure 门户中为单一和共用数据库启用自动优化,也可以使用 ALTER DATABASE T-SQL 语句来启用。You can enable automatic tuning for single and pooled databases in the Azure portal or using the ALTER DATABASE T-SQL statement. 让我们使用 ALTER DATABASE T-SQL 语句来为托管实例部署中的实例数据库启用自动优化。You enable automatic tuning for instance databases in a managed instance deployment using the ALTER DATABASE T-SQL statement.

自动优化选项Automatic tuning options

Azure SQL 数据库中可用的自动优化选项包括:Automatic tuning options available in Azure SQL Database are:

自动优化选项Automatic tuning option 单一数据库和共用数据库支持Single database and pooled database support 实例数据库支持Instance database support
创建索引 - 标识可提高工作负载性能的索引,创建索引,并自动验证查询性能是否有所提高。CREATE INDEX - Identifies indexes that may improve performance of your workload, creates indexes, and automatically verifies that performance of queries has improved. Yes No
删除索引 - 每日识别冗余和重复的索引,但不包括唯一索引和长时间(>90 天)未使用的索引。DROP INDEX - Identifies redundant and duplicate indexes daily, except for unique indexes, and indexes that were not used for a long time (>90 days). 请注意,此选项与使用分区切换和索引提示的应用程序不兼容。Please note that this option is not compatible with applications using partition switching and index hints. 高级和业务关键服务层不支持删除未使用的索引。Dropping unused indexes is not supported for Premium and Business Critical service tiers. Yes No
强制执行上一卓越计划(自动更正计划)- 标识使用执行计划的 SQL 查询(该执行计划速度慢于上一卓越计划),并标识使用上一已知卓越计划的查询而不是回归计划。FORCE LAST GOOD PLAN (automatic plan correction) - Identifies SQL queries using execution plan that is slower than the previous good plan, and queries using the last known good plan instead of the regressed plan. Yes Yes

单一数据库和共用数据库的自动优化Automatic tuning for single and pooled databases

单一数据库和共用数据库的自动优化使用 CREATE INDEXDROP INDEXFORCE LAST GOOD PLAN 数据库顾问建议来优化你的数据库性能。Automatic tuning for single and pooled databases uses the CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN database advisor recommendations to optimize your database performance. 有关详细信息,请参阅 Azure 门户中的PowerShell 中的和 REST API 中的数据库顾问建议。For more information, see Database advisor recommendations in the Azure portal, in PowerShell, and in the REST API.

可以使用 Azure 门户手动应用优化建议,也可以让“自动优化”自主为你应用优化建议。You can either manually apply tuning recommendations using the Azure portal or you can let automatic tuning autonomously apply tuning recommendations for you. 让系统自主为你应用优化建议的好处是,它会自动验证对工作负荷性能是否有正向提升,如果检测不到显著的性能改进,它会自动还原优化建议。The benefits of letting the system autonomously apply tuning recommendations for you is that it automatically validates there exists a positive gain to the workload performance, and if there is no significant performance improvement detected, it will automatically revert the tuning recommendation. 请注意,按照设计,如果受优化建议影响的查询不是频繁执行,则验证阶段可能要花费长达 72 小时。Please note that in case of queries affected by tuning recommendations that are not executed frequently, the validation phase can take up to 72 hrs by design.

如果通过 T-SQL 应用优化建议,则自动性能验证和反转机制不可用。In case you are applying tuning recommendations through T-SQL, the automatic performance validation, and reversal mechanisms are not available. 以这种方式应用的建议将保持活动状态,并在 24-48 小时显示在优化建议列表中。Recommendations applied in such way will remain active and shown in the list of tuning recommendations for 24-48 hrs. 将在 24-48 小时内保持活动状态并显示在建议列表中。before the system automatically withdraws them. 如果你想要更快地删除建议,可以通过 Azure 门户放弃它。If you would like to remove a recommendation sooner, you can discard it from Azure portal.

每个数据库都可以独立启用或禁用自动优化选项,也可以在 SQL 数据库服务器上配置这些选项,并将其应用于从服务器继承设置的每个数据库。Automatic tuning options can be independently enabled or disabled per database, or they can be configured on SQL Database servers and applied on every database that inherits settings from the server. SQL 数据库服务器可继承 Azure 默认值,用于自动调整设置。SQL Database servers can inherit Azure defaults for automatic tuning settings. 目前 Azure 默认值设为启用 FORCE_LAST_GOOD_PLAN 和 CREATE_INDEX,禁用 DROP_INDEX。Azure defaults at this time are set to FORCE_LAST_GOOD_PLAN is enabled, CREATE_INDEX is enabled, and DROP_INDEX is disabled.

Important

从 2020 年 3 月开始,对用于自动优化的 Azure 默认设置的更改将生效,如下所示:As of March, 2020 changes to Azure defaults for automatic tuning will take effect as follows:

  • 新的 Azure 默认设置为 FORCE_LAST_GOOD_PLAN = enabled、CREATE_INDEX = disabled 和 DROP_INDEX = disabled。New Azure defaults will be FORCE_LAST_GOOD_PLAN = enabled, CREATE_INDEX = disabled, and DROP_INDEX = disabled.
  • 未配置自动优化首选项的现有服务器将自动配置为继承新的 Azure 默认设置。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.
  • 创建的新服务器将自动配置为继承新的 Azure 默认设置(与之前创建新服务器时自动优化配置处于未定义状态时不同)。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).

要配置自动优化,建议在服务器上配置自动优化选项并继承属于父级服务器的数据库设置,因为这会简化对大量数据库的自动优化选项的管理。Configuring automatic tuning options on a server and inheriting settings for databases belonging to the parent server is a recommended method for configuring automatic tuning as it simplifies management of automatic tuning options for a large number of databases.

实例数据库的自动优化Automatic tuning for instance databases

托管实例部署中的实例数据库的自动优化仅支持“强制使用上个良好计划” 。Automatic tuning for instance databases in a managed instance deployment only supports FORCE LAST GOOD PLAN. 有关通过 T-SQL 配置自动优化选项的详细信息,请参阅自动优化引入了自动计划更正自动计划更正For more information about configuring automatic tuning options through T-SQL, see Automatic tuning introduces automatic plan correction and Automatic plan correction.

后续步骤Next steps