使用查询优化助手优化查询 (QTA)

  • 5 分钟

你决定在迁移之前使用查询存储来监视数据库性能，并将此数据与升级后的性能进行比较。 你计划使用查询优化助手 (QTA) 查找回归查询和提供改进建议。 本单元介绍使用查询存储和 QTA 维护查询性能的步骤。

由于数据库应用程序是支撑业务运营的关键系统，因此需要制定有关升级的行动计划和应变计划。 一次升级一个核心生产数据库。 确定升级后的数据库的测试计划，确保升级的每个数据库在重新投入生产时不存在任何问题，然后才升级任何其他数据库。

可以使用查询存储来持续监视查询的性能，同时将其用于 A/B 测试以衡量数据库升级等更改的影响。 QTA 在升级后提供自动支持，以根据查询存储中捕获的数据查找并修复退化的查询。

为使 QTA 正常运作，需要按顺序执行以下步骤。

重要

确保使用生产应用程序数据库或具有一个与生产数据库工作负载高度匹配的应用程序工作负载的数据库，以便查询存储可以在查询上收集真实指标信息。

  1. 将数据库迁移到 SQL Server 2022。
  2. 保持兼容级别不变，与以前的 SQL Server 版本一致。
  3. 在数据库上启用查询存储。
  4. 让查询存储基于足够的真实用户活动收集查询的基线指标信息。
  5. 将兼容性级别升级到 SQL Server 2022 (160)。
  6. 再次让查询存储基于足够的真实用户活动收集有关查询的数据。
  7. 使用 QTA 比较数据库兼容性级别更改前后的查询性能。 如果发现回归查询，则确认修复办法。

迁移数据库

准备好移动到 SQL Server 2022 时，请先将数据库迁移到新实例。 有多种方法可实现此迁移。 例如，可以使用简单的备份和还原，使用数据库镜像或使用大容量加载。 最合适的选择取决于当前环境的配置以及要从中迁移的 SQL Server 的版本。 Azure 数据迁移服务 (DMS) 是一个不错的解决方案，因为它支持从 SQL Server 2005 开始的数据库。

注意

Azure DMS 还支持将数据库迁移到 Azure SQL 托管实例。 使用适用于 Azure Data Studio 的 Azure SQL 迁移扩展开始。

保持兼容性级别不变

迁移数据库后，保持兼容性级别不变。 此步骤非常重要，因为需要使用当前数据库配置来度量基线。 在将兼容级别转变到 SQL Server 2014 (120) 或更高版本之前，SQL Server 使用旧版基数估算器。 SQL Server 2014 引入了升级的基数估算器，这不仅有利于大多数查询，而且很少产生负面性能影响。

启用查询存储

虽然数据库兼容级别与之前版本的相同，但可以对数据库启用查询存储，因为查询存储是服务器级别功能。 启用“查询存储”：

  1. 在 SQL Server Management Studio (SSMS) 中，右键单击数据库并选择“属性”。
  2. 在“数据库属性”窗口中，选择左窗格中的“查询存储”。
  3. 将“操作模式（请求的）”设置为“只读”或“读写”。
  4. 选择“确定”。

或可运行以下语句，在默认 READ WRITE 模式下启用查询存储：

ALTER DATABASE <database-name> SET QUERY_STORE = ON

让查询存储收集数据

将迁移的数据库重新投入生产，并切换与应用程序或报表之间的任何数据库连接。 数据库开始从生产应用程序接收查询。 允许查询存储运行足够长的时间，以收集数据库中的真实工作负载信息。

查询存储应捕获典型的业务活动周期，包括营业时间、夜间处理、维护时段和其他活动。 对于许多企业，一周的活动已足够，但对于某些企业，这一时长可能短了或长了。

许多企业都有主业务周期，因此也就会发生两周一结工资或月末处理这样的独特活动。 应了解数据库要经历的业务周期的时间点。 对于杂货店，每周库存到达和补货周期占据了大多数数据库活动。

可以通过浏览“查询存储”选项卡来查看收集的数据。 要查看该选项卡，可在 SSMS 的“对象资源管理器”中，展开数据库树以显示“查询存储”。 如果认为已收集足够数据，便可以安排升级了。

升级兼容级别

在对数据库进行任何更改之前，最好在工作时间之外（如果可能）备份数据库。 执行备份后，升级兼容性级别，如下所示：

  1. 右键单击 SSMS“对象资源管理器”中的数据库，然后选择“属性”。
  2. 在“数据库属性”窗口中，选择“选项”选项卡。
  3. 将兼容性级别更改为“SQL Server 2022 (160)”，然后选择“确定”。

或可运行以下语句：

ALTER DATABASE <database-name> SET COMPATIBILITY_LEVEL = 160

让查询存储继续收集数据

升级数据库并恢复应用程序后，查询存储将继续在后台运行以收集查询的指标信息。 由于查询优化器使用了新基数估算器，查询现在可能发生问题。

继续运行查询存储，使其能够在升级前收集同一时长的数据。 但查询回归可能会即刻显示，以便可以采取措施立即解决任何性能问题。

运行查询优化助手

运行 QTA 以解决任何回归查询。 配置 QTA：

  1. 右键单击 SSMS 对象资源管理器中的数据库，然后选择“任务”>“数据库升级”>“新数据库升级会话”。
  2. 在“查询优化助手向导”的“设置”屏幕上，在“要捕获的工作负载持续时间（天）”和“目标数据库兼容性级别”中输入相应信息。
  3. 选择“下一步”以配置“设置”和“优化”屏幕。
  4. 选择“完成”。

要监视 QTA，可右键单击数据库名称，选择“任务”>“数据库升级”>“监视会话”。 QTA 提供有关退化程度最高的查询的摘要报表，并将观察到的数据与基线数据进行比较。 然后可查看 QTA 建议的更改，以优化性能下降的查询。

总结

在数据库升级后使用 QTA 查找并修复因升级而回归的查询。 为了让 QTA 找到退化的查询，必须首先使用查询存储来创建基线，用于测量处于旧兼容级别的查询。

然后使用查询存储在升级之后收集指标，可将其与 QTA 结合使用，将新性能与基线进行比较。 查询存储在升级前后收集数据对 QTA 操作至关重要。

QTA 识别退化的查询时，它会进行试验以寻找用于改进性能的最佳操作。 然后便可应用这些操作。