描述自动优化

  • 7 分钟

自动优化是一项监视和分析功能，可持续了解工作负荷并识别潜在问题和改进。

自动优化建议基于从查询存储收集的数据。 执行计划会因架构更改、索引修改或对导致统计信息更新的数据的更改，随时间推移而变化。 如果执行计划不再满足给定查询的需求，这一演变可能会导致查询性能下降。

此外，自动优化可根据性能指标收集和应用机器学习服务，以提供建议的改进，甚至可自行更正。

无论是在本地还是在云中，都可使用自动优化来确定由查询执行计划性能下降所导致的问题。 此外，在 Azure SQL 数据库中，可通过索引优化提高查询性能。 Azure SQL 数据库自动优化可以识别应添加到数据库中的索引或甚至应从数据库中删除的索引，以提高查询性能。

自动计划更正

借助查询存储数据，数据库引擎可以确定查询执行计划何时性能下降。 尽管你可通过用户界面手动识别下降的计划，但查询存储还提供自动通知你的选项。

用于回归计划更正的查询存储视图的屏幕截图。

在上面的示例中，你可以看到“计划 ID 1”上有一个复选标记，这意味着该计划已强制执行。 启用该功能后，数据库引擎会在以下情况下自动强制实施任何建议的查询执行计划：

  • 上一个计划的错误率高于建议的计划
  • 估计的 CPU 增益大于 10 秒
  • 强制计划的性能比上一个计划好

在执行 15 次查询后，计划将还原为上一个已知良好计划。

当强制执行计划自动发生时，数据库引擎将应用上一个已知良好计划，而且还将继续监视查询执行计划性能。 如果强制计划的性能没有上一个计划好，随后它会被取消强制执行，并强制编译一个新计划。 如果强制计划继续优于以前的不良计划，它将一直强制执行，直到发生重新编译的情况。

可以通过 T-SQL 查询启用自动计划更正，如下所示。 查询存储必须启用并且必须处于读/写模式，命令才能成功。 如果不满足这两个条件中的任何一个，ALTER 语句将失败。

ALTER DATABASE [WideWorldImporters] SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

可通过动态管理视图 (DMV) sys.dm_db_tuning_recommendations 查看自动优化建议，SQL Server 2017 或更高版本以及 Azure SQL 数据库解决方案中均提供了此视图。 此 DMV 提供如下所述的信息：提出建议的原因、建议类型和建议状态。 若要确认为数据库启用了自动优化，请检查视图 sys.database_automatic_tuning_options。

自动索引管理

Azure SQL 数据库可执行自动索引优化。 随着时间的推移，数据库将了解现有的工作负载，并提供有关添加或删除索引的建议，以提高性能。 与强制改进的查询计划一样，数据库可以配置为允许根据现有索引性能自动创建或删除索引，如下所示：

Azure SQL 数据库的自动优化选项的屏幕截图。

启用后，“性能建议”页将根据查询性能识别可创建或删除的索引。 请记住，此功能不适用于本地数据库，仅适用于 Azure SQL 数据库。

或者，使用以下查询查看数据库中已启用的自动优化功能：

SELECT name,
    desired_state_desc,
    actual_state_desc,
    reason_desc
FROM sys.database_automatic_tuning_options

创建新索引可能会消耗资源，并且创建索引的时间安排非常重要，应确保不会对工作负载产生负面影响。

Azure SQL 数据库将监视实现新索引所需的资源，以避免导致性能降低。 优化操作将推迟到提供可用资源，例如，现有工作负荷需要资源且不可用于创建索引的情况。

监视可确保采取的任何操作都不会损害性能。 如果删除了索引，并且查询性能明显下降，则最近删除的索引将会自动重新创建。