自动优化
适用于：
SQL Server 2017 (14.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例
自动优化是一种数据库功能，提供对潜在查询性能问题的深入了解、提出建议解决方案并自动解决已标识的问题。
SQL Server 2017 (14.x) 中引入的自动优化功能会在每当检测到潜在性能问题时发出通知，并允许应用纠正措施，或允许数据库引擎自动修复性能问题。 通过自动优化 SQL Server，可以识别和修复由查询执行计划选择回归导致的性能问题。 Azure SQL 数据库中的自动优化功能还会创建必要的索引并删除未使用的索引。 有关查询执行计划的详细信息，请参阅执行计划。
SQL Server 数据库引擎可监视在数据库上执行的查询，并且可自动提高工作负荷的性能。 数据库引擎具有内置智能机制，此机制可根据工作符合动态调整数据库，从而自动优化和提高查询性能。 有两种自动优化功能可用：
自动计划更正：可识别有问题的查询执行计划，例如参数敏感度或参数探查问题，并通过在回归发生之前强制执行上一个已知良好的计划来修复与查询执行计划相关的性能问题。 适用范围：SQL Server（SQL Server 2017 (14.x) 及更高版本）、Azure SQL 数据库和 Azure SQL 托管实例
自动索引管理：标识应在数据库中添加的索引以及应删除的索引。 适用于： Azure SQL 数据库
为什么启用自动优化？
经典数据库管理中的三个主要任务是监视工作负荷、识别关键的 Transact-SQL 查询，以及识别应添加以提高性能的索引，或很少使用且可删除以提高性能的索引。 SQL Server 数据库引擎提供针对需要监视的查询和索引的详细见解。 然而，持续监视数据库是一项艰巨且乏味的任务，尤其是在处理多个数据库时。 可能无法高效管理大量数据库。 可考虑使用自动优化功能将某些监视和优化操作委派给数据库引擎，而不是手动监视和优化数据库。
自动优化的工作原理是什么？
自动优化是一种连续的监视和分析进程，可持续了解工作负荷的特性并识别潜在问题和改进措施。
此过程能发现哪些索引和计划可能提高工作负荷性能以及哪些索引会影响工作负荷，数据库可据此进行动态调整以适应工作负荷。 基于这些发现，自动优化将应用可提高工作负荷性能的优化操作。 此外，在实现任何更改后，自动优化会持续监视数据库的性能，以确保它在提高工作负荷的性能。 将自动还原未提高性能的任何操作。 此验证过程非常重要，可确保自动优化所做的任何更改都不会降低工作负荷总体性能。
自动计划更正
自动计划更正是一种自动优化功能，用于标识执行计划选择回归，并通过强制执行上一个已知良好的计划自动修复问题。 有关查询执行计划和查询优化器的详细信息，请参阅查询处理体系结构指南。
重要
自动计划更正根据数据库中启用的查询存储来跟踪工作负荷。
什么是执行计划选择回归？
SQL Server 数据库引擎可能使用不同的执行计划来执行 Transact-SQL 查询。 查询计划取决于统计信息、索引和其他因素。 应该用于执行 Transact-SQL 查询的最佳计划可能会随时间变化，具体取决于这些因素的变化。 在某些情况下，新计划可能不会比上一个计划好，而且新计划可能会导致性能回归，例如参数敏感度或参数探查相关问题。
每当注意到发生计划选择回归时，应找到以前的良好计划，并强制使用该计划而不是当前计划。 这可以通过使用 sp_query_store_force_plan 过程来完成。 SQL Server 2017 (14.x) 中的数据库引擎提供有关回归计划和建议纠正措施的信息。 此外，数据库引擎支持完全自动化此过程，并能够修复与计划更改相关的任何问题。
重要
在捕获基线后，应在数据库兼容性级别升级的范围内使用自动计划更正，以自动缓解工作负荷升级风险。 有关本用例的详细信息，请参阅在升级到新版 SQL Server 期间保持性能稳定性。
自动计划选择更正
每当检测到计划选择回归时，数据库引擎可自动切换到上一个已知良好的计划。
数据库引擎自动检测任何潜在计划选择回归，包括应使用的计划（而不是错误计划）。 由自动计划更正强制生成的执行计划将与上一个已知良好的计划相同或类似。 由于生成的计划可能与上一个已知良好的计划不同，因此强制计划的性能可能会有所不同。 在极少数情况下，性能差异可能很大，并且是负面的；在这种情况下，自动计划更正将自动停止尝试强制执行替换计划。
当数据库引擎在回归发生前应用上一个已知良好的计划时，它会自动监视强制计划的性能。 如果强制计划没有回归计划好，则取消强制执行新计划，数据库引擎会编译一个新计划。 如果数据库引擎证实强制计划优于回归计划，将保留强制计划。 它将保留到重新编译前（例如，在下一个统计信息更新或架构更改时）。 有关计划强制执行和可强制执行的计划类型的详细信息，请参阅计划强制执行限制。
注意
如果在验证计划强制执行操作之前重启 SQL Server 实例，则该计划将自动取消强制执行。 否则，在重启 SQL Server 时保留计划强制执行。
启用自动计划选择更正
可以针对每个数据库启用自动优化，并指定在每次检测到某些计划更改回归时强制使用最近一个良好计划。 使用以下命令启用自动优化：
ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
启用此选项后，数据库引擎会自动强制使用具有以下特征的任何建议：预计 CPU 性能提升超过 10 秒，或新计划中的错误数多于建议计划中的错误数，且经验证强制计划优于当前计划。
要在 Azure SQL 数据库和 Azure SQL 托管实例中启用自动优化，请参阅使用 Azure 门户在 Azure SQL 数据库中启用自动优化。
替代项 - 手动计划选择更正
如不使用自动优化，用户必须定期监视系统并查找已回归的查询。 如果任何计划已回归，用户应找到以前的良好计划，并使用 sp_query_store_force_plan 过程强制执行该计划，而不是当前计划。 最佳做法是强制执行上一个已知良好的计划，因为由于统计信息或索引更改，旧计划可能无效。 强制执行上一个已知良好计划的用户应监视使用强制计划执行的查询性能，并验证强制计划是否按预期工作。 根据监视和分析的结果，决定是应强制执行计划，还是用户应找到另一种方法（例如重写）来优化查询。 不应永久强制执行手动强制计划，因为数据库引擎应能够应用最佳计划。 用户或 DBA 最终应使用 sp_query_store_unforce_plan 过程取消强制执行计划，并让数据库引擎找到最佳计划。
提示
或者，使用“具有强制计划的查询”查询存储视图来查找和取消强制执行计划。
SQL Server 提供监视性能和修复查询存储中的问题所需的所有必要视图和过程。
在 SQL Server 2016 (13.x) 中，可以使用查询存储系统视图找到计划选择回归。 从 2017 SQL Server 2017 (14.x) 起，数据库引擎会检测并显示潜在计划选择回归以及应在 sys.dm_db_tuning_recommendations (transact-SQL) DMV 中应用的建议操作。 DMV 显示了问题及问题重要性的相关信息并详细显示了标识查询、回归计划的 ID、用作比较基线的计划 ID 以及可执行的 Transact-SQL 语句等来修复问题。
| type | description | datetime | score | 详细信息 | ... |
|---|---|---|---|---|---|
FORCE_LAST_GOOD_PLAN |
CPU 时间从 4 毫秒更改为 14 毫秒 | 2017 年 3 月 17 日 | 83 | queryId recommendedPlanId regressedPlanId T-SQL |
|
FORCE_LAST_GOOD_PLAN |
CPU 时间从 37 毫秒更改为 84 毫秒 | 2017 年 3 月 16 日 | 26 | queryId recommendedPlanId regressedPlanId T-SQL |
以下列表中描述了此视图中的某些列：
- 建议操作的类型
FORCE_LAST_GOOD_PLAN。 - 包含数据库引擎认为此计划更改是潜在性能回归的原因的说明。
- 检测到潜在回归的日期/时间。
- 此建议的评分。
- 有关检测计划 ID、回归计划 ID、应强制执行以修复问题的计划 ID、可能应用于修复问题的 Transact-SQL 脚本等问题的详细信息。详细信息存储为 JSON 格式。
使用以下查询获取修复问题的脚本以及有关预计好处的其他信息：
SELECT reason, score,
script = JSON_VALUE(details, '$.implementationDetails.script'),
planForceDetails.*,
estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
* (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
WITH ( [query_id] int '$.queryId',
regressedPlanId int '$.regressedPlanId',
recommendedPlanId int '$.recommendedPlanId',
regressedPlanErrorCount int,
recommendedPlanErrorCount int,
regressedPlanExecutionCount int,
regressedPlanCpuTimeAverage float,
recommendedPlanExecutionCount int,
recommendedPlanCpuTimeAverage float
) AS planForceDetails;
下面是结果集：
| reason | score | 脚本 | query_id | current plan_id | recommended plan_id | estimated_gain | error_prone |
|---|---|---|---|---|---|---|---|
| CPU 时间从 3 毫秒更改为 46 毫秒 | 36 | EXEC sp_query_store_force_plan 12, 17; | 12 | 28 | 17 | 11.59 | 0 |
estimated_gain 列表示使用建议计划而不是当前计划来执行查询时会节省的预计秒数。 如果节省时间大于 10 秒，应强制执行建议计划而不是当前计划。 例如，如果当前计划的错误（如超时或中止执行）比建议计划多，则 error_prone 列将设为 YES 值。 应强制执行建议计划而不是当前计划的另一个原因就是当前计划容易出错。
虽然数据库引擎提供了识别计划选择回归所需的所有信息，但持续监视和修复性能问题仍可能成为一个繁琐的过程。 自动优化大大简化了此过程。
注意
在重启数据库引擎后，sys.dm_db_tuning_recommendations DMV 中的数据不会持久保存。 使用 sys.dm_os_sys_info 中的 sqlserver_start_time 列查找上次数据库引擎启动时间。
自动索引管理
在 Azure SQL 数据库中进行索引管理很容易，因为 Azure SQL 数据库了解工作负荷并确保始终以最佳方式为数据编制索引。 正确的索引设计对于优化工作负荷性能至关重要，而自动索引管理也有助于优化索引。 自动索引管理可修复未正确编制索引的数据库中的性能问题，或者维护并改进现有数据库架构上的索引。 Azure SQL 数据库中的自动优化执行以下操作：
- 识别可提高从表中读取数据的 Transact-SQL 查询性能的索引。
- 识别冗余索引或较长时间内未使用并可删除的索引。 删除不必要的索引可提高更新表中数据的查询的性能。
为什么需要索引管理？
索引能加速某些从表中读取数据的查询；但会减慢更新数据的查询速度。 需要仔细分析何时创建索引以及需在索引中包含哪些列。 某些时间后可能不需要某些索引。 因此，需要定期识别并删除没有任何益处的索引。 如果忽略未使用的索引，将导致更新数据的查询性能降低，对读取数据的查询也毫无益处。 未使用的索引也会影响系统整体性能，因为其他更新会需要不必要的日志记录。
既要改进从表中读取数据的查询的性能，又要最大限度地减少对更新数据的查询的影响，要找出此类最佳索引可能需要持续进行复杂的分析。
Azure SQL 数据库使用内置智能和高级规则分析查询，可识别最适用于当前工作负荷的索引以及可能需要删除的索引。 对于可优化读取数据的查询的索引，Azure SQL 数据库可确保将此类索引数量降至最低，同时将对其他查询的影响降至最低。
自动索引管理
除检测之外，Azure SQL 数据库还可以自动应用已识别的建议。 如果发现该内置规则提高了数据库的性能，你可能会用 Azure SQL 数据库自动管理索引。
当 Azure SQL 数据库应用 CREATE INDEX 或 DROP INDEX 建议时，它会自动监视受索引影响的查询性能。 仅当受影响的查询性能得到提高时，才会保留新索引。 如果由于缺少索引导致某些查询运行速度较慢，则会自动重新创建已删除的索引。
自动索引管理注意事项
在 Azure SQL 数据库中创建必要索引所需的操作可能会消耗资源并暂时影响工作负荷性能。 为将创建索引对工作负荷性能的影响降至最低，Azure SQL 数据库可为任何索引管理操作找到适当的时段。 如果数据库需要资源才能执行工作负荷，优化操作将被推迟，并在数据库有足够可用于维护任务的未使用资源时重新启动。 验证操作是自动索引管理中的一项重要功能。 Azure SQL 数据库创建或删除索引后，监视进程将分析工作负荷的性能，验证该操作是否提高了总体性能。 如果未显著提高，操作将立即还原。 这样，Azure SQL 数据库便可确保自动优化操作不会对工作负荷性能产生负面影响。 自动优化创建的索引对在基础架构上进行的维护操作没有任何影响。 自动创建的索引不会阻止对架构进行更改（如删除或重命名列）。 相关表或列被删除时，Azure SQL 数据库自动创建的索引也将被立即删除。
可选 - 手动索引管理
如果没有自动索引管理，用户或 DBA 将需要手动查询 sys.dm_db_missing_index_details (Transact-SQL) 视图，或使用 Management Studio 中的性能仪表板报表查找可能提高性能的索引，使用此视图中提供的详细信息创建索引，并手动监视查询性能。 如要查找应删除的索引，用户应监视索引的操作使用情况统计信息，以查找很少使用的索引。
Azure SQL 数据库简化了此过程。 Azure SQL 数据库可分析工作负荷、识别可使用新索引加速执行的查询，以及识别未使用或重复的索引。 有关如何识别应更改索引的详细信息，请参阅在 Azure 门户中查找索引建议。
后续步骤
- Azure SQL 数据库与 Azure SQL 托管实例中的自动优化
- ALTER DATABASE SET AUTOMATIC_TUNING (Transact-SQL)
- sys.database_automatic_tuning_options (Transact-SQL)
- sys.dm_db_tuning_recommendations (Transact-SQL)
- sys.dm_db_missing_index_details (Transact-SQL)
- sp_query_store_force_plan (Transact-SQL)
- sys.query_store_plan_forcing_locations (Transact-SQL)
- sp_query_store_unforce_plan (Transact-SQL)
- sys.database_query_store_options (Transact-SQL)
- sys.dm_os_sys_info (Transact-SQL)
- JSON 函数
- 执行计划
- 监视和优化性能
- 性能监视和优化工具
- 相关视图、函数和过程
- 查询优化助手
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈