您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

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. 在开启自动优化时,Azure SQL 数据库运行时间越长,执行能力就越好。The longer an Azure SQL 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 Azure SQL databases 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.

使用自动优化Use Automatic tuning

需要在订阅上启用自动优化。Automatic tuning needs to be enabled on your subscription. 若要使用 Azure 门户启用自动优化,请参阅启用自动优化To enable automatic tuning using Azure portal, see Enable automatic tuning.

自动优化可通过自动应用优化建议(包括性能提升的自动验证)自主操作。Automatic tuning can operate autonomously through automatically applying tuning recommendations, including automated verification of performance gains.

要获取更多掌控力,可关闭“自动应用优化建议”,还可通过 Azure 门户手动应用优化建议。For more control, automatic application of tuning recommendations can be turned off, and tuning recommendations can be manually applied through Azure portal. 也可使用该解决方案仅查看自动优化建议,并通过所选脚本和工具手动应用。It is also possible to use the solution to view automated tuning recommendations only and manually apply them through scripts and tools of your choice.

若要了解自动优化工作原理的概述以及典型使用方案,请观看嵌入视频:For an overview of how automatic tuning works and for typical usage scenarios, see the embedded video:

自动优化选项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 at this time the option is not compatible with applications using partition switching and index hints. 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

自动优化确定可以优化数据库性能的“创建索引”、“删除索引”和“强制执行上一个卓越计划”建议,在 Azure 门户中显示它们,并通过 T-SQLREST API 公开它们。Automatic tuning identifies CREATE INDEX, DROP INDEX, and FORCE LAST GOOD PLAN recommendations that can optimize your database performance and shows them in Azure portal, and exposes them through T-SQL and REST API. 若要详细了解如何强制执行最后一个良好计划和通过 T-sql 配置自动优化选项, 请参阅自动优化引入自动计划更正To learn more about FORCE LAST GOOD PLAN and configuring automatic tuning options through T-SQL, see Automatic tuning introduces automatic plan correction.

可以使用门户手动应用优化建议,也可以让“自动优化”自主为你应用优化建议。You can either manually apply tuning recommendations using the 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.

如果是手动应用优化建议,则自动性能验证和回退机制不可用。In case you are manually applying tuning recommendations, the automatic performance validation, and reversal mechanisms are not available. 此外,手动应用的建议在系统自动撤消它们之前,In addition, manually applied recommendations will remain active and shown in the list of recommendations for 24-48 hrs. 将在 24-48 小时内保持活动状态并显示在建议列表中。before the system automatically withdraws them. 如果你想要更快地删除建议,可以手动放弃它。If you would like to remove a recommendation sooner, you can manually discard it.

每个数据库都可以独立启用或禁用自动优化选项,也可以在 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.

要配置自动优化,建议在服务器上配置自动优化选项并继承属于父级服务器的数据库设置,因为这会简化对大量数据库的自动优化选项的管理。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.

后续步骤Next steps