合 MSSQL 2017的自动优化
Tags： SQL Server整理自官网查询存储SQL Server 2017自动优化
简介
自动优化是一种数据库功能，提供对潜在查询性能问题的深入了解、提出建议解决方案并自动解决已标识的问题。
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 时保留计划强制执行。
启用自动计划选择更正
可以针对每个数据库启用自动优化，并指定在每次检测到某些计划更改回归时强制使用最近一个良好计划。 使用以下命令启用自动优化：
1 2 | 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 格式。
使用以下查询获取修复问题的脚本以及有关预计好处的其他信息：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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 值。 应强制执行建议计划而不是当前计划的另一个原因就是当前计划容易出错。
虽然数据库引擎提供了识别计划选择回归所需的所有信息，但持续监视和修复性能问题仍可能成为一个繁琐的过程。 自动优化大大简化了此过程。
