使用查询优化助手升级数据库

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure Synapse Analytics 分析平台系统 (PDW)

从较低版本的 SQL Server 迁移到 SQL Server 2014 (12.x) 或更高版本，并将数据库兼容性级别升级到最新可用级别时，工作负载可能会面临性能回归风险。 从 SQL Server 2014 (12.x) 升级到任何较新版本时，出现此情况的可能性更小。

从 SQL Server 2014 (12.x) 开始，每个新版本的所有查询优化器更改均限制为最新的数据库兼容性级别，因此系统不会在升级后立即更改执行计划，而是在用户将 COMPATIBILITY_LEVEL 数据库选项更改为最新可用版本后更改。 有关 SQL Server 2014 (12.x) 中引入的查询优化器更改的详细信息，请参阅基数估算器。 要详细了解兼容性级别及其对升级的影响，请参阅兼容性级别和数据库引擎升级。

如果升级遵循以下建议工作流，那么将数据库兼容性级别提供的此限制功能与查询存储相结合，可在升级过程中拥有对查询性能很高的控制级别。 有关用于升级兼容性级别的建议工作流的详细信息，请参阅更改数据库兼容性模式和使用查询存储。

SQL Server 2017 (14.x) 引入了自动优化，进一步提升了对升级的控制，并且能够自动执行上述建议工作流中的最后一步。

从 SQL Server Management Studio v18 开始，新的查询优化助手 (QTA) 功能将在升级到更新的版本期间指导用户完成建议工作流以保持性能稳定，如查询存储使用方案的在升级到新版 SQL Server 期间保持性能稳定部分中所述。 不过，QTA 不会回滚到以前已知的优质计划，如建议的工作流的最后一步所示。 相反，QTA 会跟踪在查询存储“回归查询”视图中找到的任何回归，并遍历适用的优化器模型变体的可能排列，以便生成新的更优质计划。

重要

QTA 不会生成用户工作负载。 如果在应用程序未使用的环境中运行 QTA，请确保可以通过其他方式在目标 SQL Server 数据库引擎上执行代表性测试工作负载。

查询优化助手工作流

QTA 的起点假设从旧版本的 SQL Server 数据库（通过 CREATE DATABASE ... FOR ATTACH 或 RESTORE）移动到新版本的 SQL Server 数据库引擎，并且升级前的数据库兼容性级别不会立即更改。 QTA 将引导用户完成以下步骤：

1. 根据用户设置的工作负载持续时间（以天为单位）的建议设置来配置查询存储。 考虑与典型业务周期匹配的工作负载持续时间。
2. 请求启动所需的工作负载，以便查询存储可以收集工作负载数据的基线（若尚未提供）。
3. 升级到用户所选的目标数据库兼容性级别。
4. 请求收集第 2 次传递的工作负载数据，用于进行比较和回归检测。
5. 循环访问根据查询存储回归的查询视图找到的任何回归，通过收集有关适用优化器模型变体的可能排列的运行时统计信息进行试验，并测量结果。
6. 报告测量到的改进，并且可选择允许使用计划指南保留那些更改。

有关附加数据库的详细信息，请参阅数据库分离和附加。

请参阅下文，了解 QTA 如何使用上文所述的查询存储，只更改升级兼容性级别建议工作流的最后几个步骤。 QTA 专为选定的回归查询提供了优化选择，用于使用优化的执行计划新建改进状态；而不是提供选项，供用户选择是使用当前低效的执行计划，还是使用最后一个已知的优质执行计划。

QTA 优化内部搜索空间

QTA 仅面向可以从查询存储中执行的 SELECT 查询。 若编译参数是已知的，那么参数化查询符合条件。 此时，依赖于运行时构造（如临时表或表变量）的查询不符合条件。

QTA 面向 基数估计器 (CE) 版本更改带来的查询回归已知可能的模式。 例如，将数据库从 SQL Server 2012 (11.x) 和数据库兼容性级别 110 升级到 SQL Server 2017 (14.x) 和数据库兼容性级别 140 时，一些查询可能发生回归，因为它们是专为适用于 SQL Server 2012 (11.x) (CE 70) 中存在的 CE 版本而设计的。 这并不意味着从 CE 140 还原为 CE 70 是唯一选择。 只要较新版本中的某项特定更改引入了回归，就有可能提示查询只使用上一个 CE 版本中对特定查询更有效的相关部分，同时依然利用 CE 较新版本的所有其他改进。 还能使工作负载中未回归的其他查询获益于较新版 CE 的改进。

由 QTA 搜索到的 CE 模式如下：

• 依赖项与相关性：如果独立性假设为特定查询提供的评估更好，那么查询提示 USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') 使 SQL Server 在为了说明关联而对筛选器的 AND 谓词进行估算时使用最小选择性来生成执行计划。 有关详细信息，请参阅 USE HINT 查询提示和 CE 的版本。
• 简单包含与基础包含：如果不同的联接包含为特定查询提供了更好的估计，则查询提示 USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') 使 SQL Server 使用“简单包含”假设（而不是默认的“基础包含”假设）来生成执行计划。 有关详细信息，请参阅 USE HINT 查询提示和 CE 的版本。
• 多语句表值函数 (MSTVF) 固定基数猜测 - 100 行与 1 行：如果使用 TVF 默认固定估值 100 行与使用 TVF 固定估值 1 行（对应于 SQL Server 2008 R2 (10.50.x) 及早期版本的查询优化器 CE 模型下的默认值）相比，并不能带来更有效的计划，则使用查询提示 QUERYTRACEON 9488 生成执行计划。 有关 MSTVF 的详细信息，请参阅创建用户定义函数（数据库引擎）。

注意

万不得已时，如果狭窄范围的提示不能为符合条件的查询模式带来足够好的结果，也可考虑使用查询提示 USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') 生成执行计划，从而充分利用 CE 70。

重要

任何提示都会强制执行可能在将来的 SQL Server 更新中解决的特定行为。 我们建议仅在没有其他选择且打算每次新升级都重新访问提示的代码时，才应用提示。 强制行为可能导致工作负载无法受益于更新版 SQL Server 中引入的增强功能。

为数据库升级启动查询优化助手

QTA 是一种基于会话的功能，它将会话状态存储在首次创建会话的用户数据库的 msqta 架构中。 可在一段时间内在单个数据库上创建多个优化会话，但是任何给定的数据库只能存在一个活动会话。

创建数据库升级会话

1. 在 SQL Server Management Studio 中，打开对象资源管理器并连接到数据库引擎。

2. 对于计划升级数据库兼容性级别的数据库，右键单击数据库名称，依次选择“任务”、“数据库升级”和“新建数据库升级会话”。

3. 在 QTA 向导窗口中，配置会话需要两个步骤：

   1. 在“设置”窗口中，将查询存储配置为捕获相当于要分析和优化的一个完整业务周期的工作负载数据。

      • 以天为单位输入预期的工作负载持续时间（最短为 1 天）。 这将用于提出推荐的查询存储设置，以暂时允许收集完整基线。 要确保能够分析在更改数据库兼容性级别后找到的任何回归查询，捕获良好的基线至关重要。
      • 完成 QTA 工作流之后，设置用户数据库应处于的预期目标数据库兼容性级别。 完成后，选择“下一步”。

      

   2. 在“设置”窗口中，有两列显示目标数据库中查询存储的“当前”状态以及“推荐”设置。

      • 默认选择“推荐”设置，但选择“当前”列的单选按钮会接受当前设置，还可以微调当前的查询存储配置。
      • 建议的过时查询阈值设置的数值是预期工作负载持续时间（以天为单位）的两倍。 这是因为查询存储需要保存有关基线工作负载和数据库升级后的工作负载的信息。 完成后，选择“下一步”。

      

      重要

      建议的“最大大小”是适合于短时间工作负载的任意值。 但是，请记住，对于非常密集的工作负载（即可能生成许多不同的计划时），该值可能不足以保存关于基线和数据库升级后的工作负载的信息。 如果预料到会出现这种情况，请输入一个合适的较大值。

4. “优化”窗口结束会话配置，并引导完成打开并继续处理会话的后续步骤。 完成后，选择“完成”。

   

执行数据库升级工作流

1. 对于计划升级数据库兼容性级别的数据库，右键单击数据库名称，依次选择“任务”、“数据库升级”和“监视会话”。

2. “会话管理”页列出范围内数据库的当前和过去的会话。 选择所需会话，然后选择“详细信息”。

   注意

   如果当前会话不存在，请选择“刷新”按钮。

   列表包含以下信息：

   • 会话 ID
   • 会话名称：系统生成的名称，该名称由数据库名称以及创建会话的日期和时间组成。
   • 状态：会话状态（活动或关闭）。
   • 说明：系统生成的说明，包括用户所选的目标数据库兼容性级别以及业务周期工作负载天数。
   • 开始时间：创建会话的日期和时间。

   

   注意

   删除会话：删除为所选会话存储的任何数据。 然而，删除已关闭的会话不会删除之前部署的任何计划指南。 如果删除已部署计划指南的会话，则无法使用 QTA 进行回滚。 改为使用 sys.plan_guides 系统表搜索计划指南，然后使用 sp_control_plan_guide 手动删除。

3. 新会话的入口点是“数据收集”步骤。

   注意

   通过“会话”按钮可回到“会话管理”页面，而活动会话保持不变。

   此步骤有以下三个子步骤：

   1. 基线数据收集：要求用户运行代表性工作负载周期，以便查询可以收集基线。 完成此工作负载后，选中“工作负载运行已完成”并选择“下一步”。

      注意

      运行工作负载时，可关闭 QTA 窗口。 稍后回到仍处于活动状态的会话会从停止的那一步继续。

      

   2. 升级数据库：会提示需要将数据库兼容性级别升级到期望目标的权限。 若要继续进行下一个子步骤，请选择“是”。

      

      下一页确认已成功升级数据库兼容性级别。

      

   3. “观测到的数据收集”要求用户重新运行代表性工作负载周期，以便查询存储可收集用于搜索优化机会的比较基线。 执行工作负载时，使用“刷新”按钮持续更新回归的查询列表（若找到任何回归的查询）。 更改“要显示的查询数”值，以限制显示的查询数量。 列表顺序受“指标”（持续时间或 CpuTime）和“聚合”（默认使用平均值）。 还需选择要显示的查询数量。 完成此工作负载后，选中“工作负载运行已完成”并选择“下一步”。

      

      列表包含以下信息：

      • 查询 ID
      • 查询文本：可选择 ... 按钮展开的 Transact-SQL 语句。
      • 运行次数：显示为整个工作负载收集执行该查询的次数。
      • 基线指标：升级数据库兼容性之前，为收集基线数据选定的单位为毫秒的指标（持续时间或 CpuTime）。
      • 观测的指标：升级数据库兼容性之后，为收集数据选定的单位为毫秒的指标（持续时间或 CpuTime）。
      • 百分比变化：数据库兼容性升级状态前后所选指标的百分比变化。 负数表示查询的测量的回归数量。
      • 可优化：True 或 False，具体取决于查询是否有资格进行试验。

4. 查看分析可选择要试验的查询并查找优化机会。 “要显示的查询数量”值成为要试验的符合条件的查询的范围。 选中所需查询后，选择“下一步”开始试验。

   注意

   不能选择“可优化”为“False”查询进行试验。

   重要

   系统显示一个提示，告知在 QTA 进行到试验阶段后，不可能回到“查看分析”页面。
   如果在前进到试验阶段之前未选择所有符合条件的查询，需在稍后创建新会话并重复工作流。 这需要将数据库兼容性级别重置回之前的值。

   

5. 查看结果：可选择要将建议优化部署为计划指南的查询。

   列表包含以下信息：

   • 查询 ID
   • 查询文本：可选择 ... 按钮展开的 Transact-SQL 语句。
   • 状态：显示查询的当前试验状态。
   • 基线指标：为步骤 2 子步骤 3 中执行的查询选定的单位为毫秒的指标（持续时间或 CpuTime），表示升级数据库兼容性后的回归查询。
   • 观测的指标：为对试验后的查询进行很好的建议优化选定的单位为毫秒的指标（持续时间或 CpuTime），已进行足够好的建议优化。
   • 百分比变化：试验状态前后所选指标的百分比变化，表示执行建议的优化后测得的查询的改进量。
   • 查询选项：链接到改进查询执行指标的建议提示。
   • 可以部署：True 或 False，具体取决于是否能将建议的查询优化部署为计划指南。

   

6. 验证：显示之前为此会话选定的查询的部署状态。 此页的列表不同于上一页，“可以部署”列更改为了“可以回滚”。 此列可以为 True 或 False，具体取决于是否可以回滚部署的查询优化以及是否可以删除它的计划指南。

   

   如果之后需要对建议优化进行回滚，请选择相关查询，并选择“回滚”。 删除此查询计划指南并更新列表，以删除已回滚的查询。 请注意，在下图中，已删除查询 8。

   

   注意

   删除已关闭的会话不会删除之前部署的任何计划指南。 如果删除已部署计划指南的会话，则无法使用 QTA 进行回滚。 改为使用 sys.plan_guides 系统表搜索计划指南，然后使用 sp_control_plan_guide 手动删除。

权限

需要的成员资格为 db_owner 角色。

另请参阅

• 兼容性级别和数据库引擎升级
• 性能监视和优化工具
• 相关视图、函数和过程
• 更改数据库兼容性模式和使用 Query Store
• 跟踪标志
• USE HINT 查询提示
• 基数估计器
• 自动优化
• 使用 SQL Server 查询优化助手