主页 / dba / 问题 / 170729
Accepted
Professional Sounding Name
Asked: 2017-04-12 07:12:00 +0800 CST

SQL Server 2016 Bad Query Plan 每周锁定一次数据库

  • 772

每周一次，在过去的 5 周内，大约在一天中的同一时间（清晨，可能基于人们开始使用它时的用户活动），SQL Server 2016（AWS RDS，镜像）开始大量超时查询。

所有表上的 UPDATE STATISTICS 总是立即修复它。

第一次之后，我让它每晚（而不是每周）更新所有表上的所有统计信息，但它仍然发生（在更新统计信息运行后大约 8 小时，但不是每天运行）。

上次，我启用了查询存储，看看我是否可以找到它是哪个特定的查询/查询计划。我想我可以把它缩小到一个：

错误的查询计划

找到该查询后，我添加了一个推荐的索引，该索引从这个不常用的查询中丢失（但它确实触及了很多常用的表）。

错误的查询计划正在执行索引扫描（在只有 10k 行的表上）。其他以毫秒为单位返回的查询计划过去常常执行相同的扫描。最新的查询计划，在创建新索引后只会寻找。但即使没有该索引，在 99% 的情况下，它也会在几毫秒内返回，但每周，它需要超过 40 秒。

从 2012 年迁移到 SQL Server 2016 后，这种情况开始发生。

DBCC CHECKDB 不返回错误。

  1. 新索引会解决问题，使其不再选择糟糕的计划吗？
  2. 我应该“强制”现在行之有效的计划吗？
  3. 我如何确保这不会发生在另一个查询/计划中？
  4. 这是更大问题的征兆吗？

我刚刚添加的索引：

CREATE NONCLUSTERED INDEX idx_AppointmetnAttendee_AttendeeType
ON [dbo].[AppointmentAttendee] ([UserID],[AttendeeType])

CREATE NONCLUSTERED INDEX [idx_appointment_start] ON [dbo].[Appointment]
(
    [ProjectID] ASC,
    [Start] ASC
)
INCLUDE (   [ID],
    [AllDay],
    [End],
    [Location],
    [Notes],
    [Title],
    [CreatedByID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

完整的查询文本：

https://pastebin.com/Z5szPBfu（LINQ 生成，我可以/应该能够优化选择的列，但应该与这个问题无关）

sql-server execution-plan

1 个回答

  1. Best Answer

    我将以与您提出问题不同的顺序回答您的问题。

    4. 这是更大问题的征兆吗？

    SQL Server 2016 中的新基数估计器可能会导致该问题。SQL Server 2012 使用旧版 CE，您在该版本上没有遇到问题。新的基数估计器对您的数据做出不同的假设，并且可以为相同的 SQL 生成不同的查询计划。根据您的查询和数据，您可能会在使用旧版 CE 的某些查询时体验到更好的性能。因此，您的数据模型的某些部分可能不是新 CE 的最佳匹配。没关系，但您现在可能需要解决新的 CE。

    即使每天更新统计信息，我也会担心查询性能不一致。需要注意的重要一点是，收集所有表的统计信息将有效地清除缓存中的所有查询计划，因此您可能会遇到统计信息问题，或者可能与参数嗅探有关。如果没有关于数据模型、数据更改率、统计更新策略、如何调用代码等的大量信息，很难做出决定。SQL Server 2016 确实提供了一些用于参数嗅探的数据库级别设置，这可能会有所帮助，但这可能会影响您的整个应用程序，而不仅仅是一个有问题的查询。

    我将抛出一个可能导致这种行为的示例场景。你说：

    一些用户可能有 1 条权限记录，有的最多 20k。

    假设您收集了所有表的统计信息，这些表清除了所有查询计划。根据上面提到的因素，如果当天的第一个查询是针对只有 1 条权限记录的用户，那么 SQL Server 可能会缓存一个计划，该计划适用于有 1 条记录的用户，但适用于有 20k 条记录的用户。如果一天中的第一个查询是针对具有 20k 条记录的用户，那么您可能会为 20k 条记录制定一个好的计划。当代码针对具有 1 条记录的用户运行时，它可能不是最佳查询，但仍可能在毫秒内完成。这听起来确实像参数嗅探。它解释了为什么您并不总是看到问题，或者为什么有时需要几个小时才能出现。

    1. 新索引能否解决问题，使其不再选择糟糕的计划？

    我认为您添加的索引之一将防止出现问题，因为通过索引访问所需数据将比对表执行聚集索引扫描更便宜，尤其是当扫描无法提前终止时。让我们放大查询计划的错误部分：

    错误的查询计划

    [Permission]SQL Server 估计在和上的连接只会返回一行[Project]。对于外部输入中的每一行，它将对[Appointment]. 将从该表中扫描所有行，但只有与过滤匹配的行[Start]才会返回给连接运算符。在连接运算符中，结果会进一步减少。

    如果确实只有一行发送到连接的外部输入，则上述查询计划可能没问题。但是，如果连接的基数估计错误并且我们得到 1000 行，那么 SQL Server 将对[Appointment]. 查询计划的性能对估计问题非常敏感。

    不再获得该查询计划的最直接方法是针对该[Appointment]表创建一个覆盖索引。像索引之类的东西[ProjectId]，[Start]应该这样做。看起来这正是[idx_appointment_start]您为解决该问题而创建的索引。阻止 SQL Server 选择查询计划的另一种方法是修复连接上的基数估计[Permission]和[Project]。执行此操作的典型方法包括更改代码、更新统计信息、使用旧版 CE、创建多列统计信息、为 SQL Server 提供有关局部变量的更多信息（例如带有RECOMPILE提示）或将这些行具体化到临时表中。当您需要毫秒级别的响应时间或必须通过 ORM 编写代码时，其中许多技术都不是一个好方法。

    您创建的索引[AppointmentAttendee]不是解决问题的直接方法。但是，您将获得有关索引的多列统计信息，这些统计信息可能会阻止错误的查询计划。[AppointmentAttendee]

    3. 我如何确保这不会发生在另一个查询/计划中？

    我理解你为什么要问这个问题，但这是一个非常广泛的问题。我唯一的建议是尝试更好地了解查询计划不稳定的根本原因，验证您是否为您的工作负载创建了正确的索引，并仔细测试和监控您的工作负载。微软对如何处理 SQL Server 2016 中的新 CE 导致的查询计划回归有一些一般性建议：

    将查询处理器升级到最新版本代码的推荐工作流程是：

    1. 在不更改数据库兼容性级别的情况下将数据库升级到 SQL Server 2016（保持在之前的级别）

    2. 在数据库上启用查询存储。有关启用和使用查询存储的更多信息，请参阅使用查询存储监控性能。

    3. 等待足够的时间来收集工作负载的代表性数据。

    4. 将数据库的兼容级别更改为130

    5. 使用 SQL Server Management Studio，评估兼容性级别更改后特定查询是否存在性能回归

    6. 对于存在回归的情况，在查询存储中强制执行先前的计划。

    7. 如果存在无法强制执行的查询计划或性能仍然不足，请考虑将兼容性级别恢复为之前的设置，然后联系 Microsoft 客户支持。

    我并不是说您需要降级到 SQL Server 2012 并重新开始，但所描述的一般技术可能对您有用。

    2.我应该“强制”现在行之有效的计划吗？

    这完全取决于你。如果您认为您的查询计划适用于所有可能的输入参数，对查询存储的功能感到满意，并且希望通过强制执行查询计划让您高枕无忧，那么就去做吧。毕竟，强制执行具有回归的查询计划是 Microsoft 推荐的 SQL Server 2016 升级策略的一部分。

    • 17

相关问题