适用于：
Microsoft Fabric 中的 SQL Server 2017 （14.x） 及更高版本 Azure SQL 数据库Azure SQL 托管实例SQL 数据库
自动优化是一项数据库功能，可深入了解潜在的查询性能问题、推荐解决方案，以及自动修复已识别的问题。
SQL Server 2017（14.x）中引入的自动优化会在检测到潜在性能问题时通知你，并允许应用纠正措施，或者让数据库引擎自动修复性能问题。 自动优化 SQL Server 可识别和修复 查询执行计划选择回归导致的性能问题。 Microsoft Fabric 中的 Azure SQL 数据库和 SQL 数据库中的自动优化还会创建必要的索引并删除未使用的索引。 有关查询执行计划的详细信息，请参阅 执行计划。
SQL Server 数据库引擎监视在数据库上执行的查询，并自动提高工作负荷的性能。 数据库引擎具有一种内置智能机制，可以通过动态将数据库适应工作负荷来自动优化和提高查询的性能。 有两个可用的自动优化功能：
自动计划更正 可识别有问题的查询执行计划，例如 参数敏感性或参数嗅探 问题，并通过在回归发生之前强制使用最后已知的有效计划来修复与查询执行计划相关的性能问题。 适用于：SQL Server（从 SQL Server 2017（14.x）开始）、Microsoft Fabric 中的 Azure SQL 数据库和 SQL 数据库，以及 Azure SQL 托管实例
自动索引管理 标识应在数据库中添加的索引，以及应删除的索引。 适用于：Azure SQL 数据库和 Microsoft Fabric 中的 SQL 数据库
Note
在本文中，Azure SQL 数据库的功能和行为也适用于 Microsoft Fabric 中的 SQL 数据库。
为什么自动优化？
经典数据库管理中的三个主要任务是监视工作负荷、识别关键 Transact-SQL 查询，以及确定应添加以提高性能的索引，或很少使用且可删除的索引以提高性能。 SQL Server 数据库引擎提供有关需要监视的查询和索引的详细见解。 但是，不断监视数据库是一项艰巨而繁琐的任务，尤其是在处理许多数据库时。 管理大量数据库可能无法高效执行。 可以考虑使用自动优化功能将某些监视和优化作委派给数据库引擎，而不是手动监视和优化数据库。
自动优化的工作原理是什么？
自动优化是一个持续监视和分析过程，它不断了解工作负荷的特征，并确定潜在的问题和改进。
此过程使数据库能够通过查找哪些索引和计划来提高工作负荷的性能以及哪些索引会影响工作负荷，从而动态适应工作负荷。 基于这些发现，自动优化将应用可提高工作负荷性能的优化操作。 此外，在实现任何更改后，自动优化会持续监视数据库的性能，以确保它在提高工作负荷的性能。 将自动还原未提高性能的任何操作。 此验证过程是一项关键功能，可确保自动优化所做的任何更改不会降低工作负荷的整体性能。
自动计划更正
自动计划更正是一种自动优化功能，用于标识 执行计划选择回归 ，并通过强制最后一个已知良好的计划自动修复问题。 有关查询执行计划和查询优化器的详细信息，请参阅 查询处理体系结构指南。
Important
自动计划更正取决于数据库中启用了查询存储功能，以进行工作负载跟踪。
什么是执行计划选择回归？
SQL Server 数据库引擎可以使用不同的执行计划来执行 Transact-SQL 查询。 查询计划取决于统计信息、索引和其他因素。 应用于执行 Transact-SQL 查询的最佳计划可能会随着时间推移而变化，具体取决于这些因素的变化。 在某些情况下，新计划可能不优于前一个计划，新计划可能会导致性能回归，例如 参数敏感度或参数探查 相关问题。
每当注意到计划选择回归发生时，应找到以前的良好计划，并强制使用它而不是当前计划。 这可以通过使用 sp_query_store_force_plan 过程来完成。 SQL Server 2017（14.x）中的数据库引擎提供有关回归计划和建议的纠正措施的信息。 此外，使用数据库引擎可以完全自动化此过程，并允许数据库引擎修复与计划更改相关的任何问题。
Important
在捕获基线后，应在数据库兼容性级别升级的范围内使用自动计划更正，以自动缓解工作负荷升级风险。 有关此用例的详细信息，请参阅 在升级到较新的 SQL Server 期间保持性能稳定性。
自动计划选择更正
每当检测到计划选择回归时，数据库引擎都可以自动切换到最后一个已知良好的计划。
数据库引擎会自动检测任何潜在的计划选择回归，包括应使用的计划，而不是错误的计划。 由自动计划更正强制生成的执行计划将与上一个已知良好计划相同或类似。 由于生成的计划可能与上一个已知良好的计划不同，因此强制计划的性能可能会有所不同。 在极少数情况下，性能差异可能很大且为负;在这种情况下，自动计划更正将自动停止尝试强制替换计划。
当数据库引擎在回归发生之前应用最后一个已知良好的计划时，它会自动监视强制计划的性能。 如果强制计划不优于回归计划，则将取消新计划的强制状态，数据库引擎将编译一个新计划。 如果数据库引擎验证强制计划是否优于回归计划，则会保留强制计划。 它将保留，直到重新编译（例如，在下一次统计信息更新或架构更改时）。 有关计划强制和可强制的计划类型的详细信息，请参阅 计划强制限制。
Note
如果在验证计划强制执行之前重启 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 过程取消执行计划，并让数据库引擎找到最佳计划。
Tip
或者，使用带有强制计划的查询存储视图来查找和取消强制计划。
SQL Server 提供监视性能和修复查询存储中问题所需的所有必要视图和过程。
在 SQL Server 2016（13.x）中，可以使用查询存储系统视图找到计划选择回归。 从 SQL Server 2017（14.x）开始，数据库引擎会检测并显示潜在的计划选择回归问题，并在 sys.dm_db_tuning_recommendations（Transact-SQL） DMV 中应用建议操作。 DMV 显示有关问题的信息、问题的重要性、标识的查询详细信息、退化计划的 ID、用于比较的基线计划 ID，以及可以执行以修复问题的 Transact-SQL 语句。
| 类型 | description | 日期/时间 | 分数 | details | ... |
|---|---|---|---|---|---|
FORCE_LAST_GOOD_PLAN |
CPU 时间从 4 毫秒更改为 14 毫秒 | 3/17/2017 | 83 |
queryId
recommendedPlanId
regressedPlanId
T-SQL
|
|
FORCE_LAST_GOOD_PLAN |
CPU 时间从 37 毫秒更改为 84 毫秒 | 3/16/2017 | 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 | 分数 | 脚本 | query_id | 当前计划_id | 推荐的plan_id | 预估增益 | 易出错 |
|---|---|---|---|---|---|---|---|
| CPU 时间从 3 毫秒更改为 46 毫秒 | 36 | EXEC sp_query_store_force_plan 12， 17; | 12 | 28 | 17 | 11.59 | 0 |
该列 estimated_gain 代表如果建议的计划用于查询执行而不是当前计划，则估计可以节省的秒数。 如果增益大于 10 秒，建议的计划应该替代当前计划。 如果当前计划中的错误（例如超时或中止执行）多于建议的计划，则列 error_prone 将设置为值 YES。 建议的计划应当被强制执行而不是当前计划，因为另一个原因是当前计划容易出错。
尽管数据库引擎提供了识别计划选择回归所需的所有信息，但持续监视和修复性能问题可能会成为一个繁琐的过程。 自动优化使此过程更容易。
Note
重启数据库引擎后，DMV 中的数据 sys.dm_db_tuning_recommendations 不会持久保存。 使用 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 函数
- 执行计划
- 监视和优化性能
- 性能监视和优化工具
- 相关视图、函数和过程
- 查询优化助手