优化迁移后的查询

  • 5 分钟

假设你是律所的数据库管理员。 周末，你已将一些数据库迁移到 SQL Server 2019，包括客户关系管理 (CRM) 数据库。 星期一一大早你就在办公室，预计销售团队会在一两个小时内到达，然后拨打和接听客户电话。 销售团队依赖于 CRM 数据库，为了使他们能够有效地与客户沟通，应用程序必须立即响应。

迁移数据库后，你将兼容性级别保留为先前的设置。 CRM 应用程序连接到新服务器。 你希望在更改兼容性级别之前，针对典型的星期一工作负载，在新系统中测量查询性能。 在升级数据库时，维护查询的性能至关重要。

你需要一个可以自动测量工作负载和性能的流程，而不是等待销售团队发现 CRM 应用程序运行缓慢才测量。 此流程应确保可在更改兼容性级别后修复任何慢查询。

查询存储

“查询存储”功能在 SQL Server 2016 中启动，以持续收集有关查询的信息。 查询存储收集的信息可用于对正在执行的查询进行故障排除，并且适用于本地和云安装。 缓存的查询计划存储在过程缓存中，但 SQL Server 仅存储最近的执行计划。

由于架构发生更改或者添加或删除索引，查询的执行计划很可能将随着时间的推移而发生变化。 此外，内存压力可能会导致计划从计划缓存中被逐出。

默认情况下，在创建数据库或将数据库迁移到 SQL Server 2017 或 SQL Server 2019 时，不会启用查询存储。 但是，在更新已迁移数据库的数据库兼容性级别之前，可以启用查询存储功能。 通过右键单击“对象资源管理器”中的数据库，选择“属性”，然后选择“查询存储”页，启用查询存储。 在“操作模式(请求)”选择列表中，选择“关闭”、“只读”或“读写”。 还可以使用 Transact-SQL 启用查询存储。 要为名为“CustomerServices”的数据库启用查询存储，需要运行以下命令：

ALTER DATABASE CustomerServices SET QUERY_STORE = ON

最初编译查询时，查询文本和第一个计划会传递到查询存储。 如果重新编译查询，则会更新查询存储中的计划。 在创建新计划后，查询存储将添加该计划并将其与前面的计划以及执行统计信息一起保存。

执行查询时，查询存储会保存运行时统计信息。 在编译或重新编译期间，SQL Server 会检查查询存储中是否存在应应用于查询的计划。 如果强制计划与过程缓存中的计划不同，则会重新编译该查询。

更改兼容级别之前，数据库不会向最新的查询优化器更改公开。 可以收集典型的工作负载，并在现有兼容性级别上创建用于度量的基线。 收集足够多的查询信息以获得性能基线后，将数据库兼容性级别更改为服务器版本。

若要创建足够的基线，请确保从典型的业务活动期收集足够多的数据。 当你移动到当前服务器兼容性级别时，工作负载将向最新的查询优化器公开。 查询性能可能会提高，因为新的查询优化器应该会生成更好的计划。

有时 SQL Server 会使用不太理想的查询计划，因此执行速度会突然变慢且没有明显的原因。 可以通过在查询存储中强制执行上一个计划来解决此问题。 使用查询存储与使用 USE PLAN 查询提示类似，但不需要更改用户应用程序。

移动兼容性级别后，查询存储继续提供查询支持以维持最佳性能。 它对回归的查询、总体资源消耗、资源消耗最大的一些查询、具有强制执行计划的查询、具有高差异性的查询和跟踪的查询进行测量和审核。

自动查询优化

SQL Server 中的基数估算器 (CE) 的角色用于估算查询中返回的行数。 查询优化器使用 CE 的结果来生成执行计划。 SQL Server 2014 之前，基数估算器基本保持不变。 SQL Server 2014 的发布标志着对 CE 算法进行了彻底的修改，目的是改进估算并提供最快的查询结果。 但是，数据库的兼容性级别决定使用哪个 CE。

尽管查询性能应在 SQL Server 2014 及更高版本中大幅改进，但具有更新兼容性级别的已迁移数据库的性能可能会降低。 SQL Server 2017 中引入了“自动查询优化”。 将它与查询存储结合使用，以查找并自动修复回归查询。 在先前的兼容性级别迁移数据库并捕获数据库的工作负载后，应升级兼容性级别并继续运行查询存储。

自动优化使用查询存储中的数据，来查找由于在更改兼容性级别后基数估算器发生更改而开始回归的查询。 对回归查询进行自动优化试验以提高性能，使你能够创建已进行改进的计划指南。

如何在 SQL Server 2019 中优化查询性能

在你的律所示例中，由于已将数据库服务器升级到 SQL Server 2019，因此可以同时使用查询存储和自动优化来优化性能，并确保兼容性级别的更改不会导致回归。 建议采用以下过程：

  1. 将数据库服务器升级到 SQL Server 2019，但不更改兼容性级别。
  2. 启用“查询存储”。
  3. 允许查询存储在正常工作时间内收集性能数据。
  4. 升级数据库兼容性级别。
  5. 检查查询存储是否已找到回归。 如果已找到，可以强制上次已知正常的查询计划返回到较高性能级别。

查询存储自动执行监视查询性能的过程。 这是在后台运行的有用工具，可确保数据库尽可能快地持续传递结果。 查询优化助手 (QTA) 与查询存储结合使用，使用查询存储中的数据在升级后的数据库中查找回归的查询。

迁移数据库并将兼容性级别升级到 SQL Server 2014 或更高版本后，将使用新的基数估算器来推测查询返回的行数。 使用 QTA 查找并修复通过切换到较新 CE 回归的查询。