主页 / dba / 问题 / 126095
Accepted
matskm
Asked: 2016-01-14 02:59:38 +0800 CST

如何减少巨大的 CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) 等待时间？

  • 772

问题

自今年年初以来，由于我们系统中的 SQL 超时，我们一直在经历严重的用户中断。

有问题的 SQL-Server 实例在工作时间具有非常高的 CPU 使用率（所有 16 个内核上始终高于 90%）。

我们还注意到非常高的等待时间：CXPACKET 和 LATCH_EX 的组合约占所有等待的 97%。这在 CXPACKET 和 LATCH_EX 之间分配了大约 50/50。

占 LATCH_EX 绝大多数 (>95%) 的非缓冲锁存等待是 ACCESS_METHODS_DATASET_PARENT。

这表明问题与并行性有关。

等待时间规模的一个例子是：

CXPACKET : 332,301,799 ms
LATCH_EX : 267,955,752 ms
PAGEIOLATCH_SH : 2,955,160 ms

这是 1 月 11 日 08:00-16:24 之间的时间段。

正在考虑的选项

1) 将 MAXDOP 从 0 更改为 4 到 8 之间的值

2）将并行度的成本阈值从50修改为更高的数字

关于如何缓解我们所看到的非常高的 CPU 负载并减少超时的建议非常受欢迎，特别是建议的行动方案是否明智，以及将 MAXDOP 和并行性的成本阈值更改为哪些数字。

背景资料

  • SQL-Server 2008 R2 在 AMD Opteron 6180 SE 上运行，其中 16 个内核分配给此 SQL-Server 实例。

  • 工作负载类型：在工作时间内同时连接大约 800 个连接；混合了一些 OLAP 的大多数 OLTP 类型的工作负载。

  • Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) ... Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1).内存在 24 个内核之间约为 128 Gigs。此实例有 16 个内核可用

sql-server-2008-r2 parallelism

3 个回答

  1. Best Answer

    混合了一些 OLAP 的大多数 OLTP 类型的工作负载

    这是问题所在，而不是 CXPACKET。并行是一种症状，而不是原因。您的“某些”OLAP 工作负载正在执行触发并行性的扫描，这会级联到交换等待时间、可能的缓冲池污染和可能的阻塞（OLTP 工作负载阻塞在 OLAP 扫描之后）。

    如果 OLAP 工作负载很好理解并且绝对关键，那么您可以考虑为其添加占用者覆盖索引。但这是一场艰苦的战斗。我更愿意看到具有破坏性扫描的 OLAP 工作负载转移到一个专用的盒子上。较新的版本 (SQL Server 2014) 具有可读的辅助数据库和列存储，它们都非常擅长服务于分析/ad-hoc/OLAP 工作负载。

    对于 SQL Server 2008 R2，我会考虑日志传送或复制（尽管我认为没有一个是“完美的”）。

    短期：你有一个性能问题，你需要适当地分析它。阅读如何分析 SQL Server 性能。识别造成最大损害的一个或多个查询（请参阅识别问题查询。只有在您识别出实际问题之后，才能推荐解决方案。

    注意：LATCH_EXonACCESS_METHODS_DATASET_PARENT根本与 IO 无关。它与并行性严格相关，并行扫描“子”线程必须在父线程上获取的锁存器才能为该子线程分配扫描范围。对它的争用表明并行性效率低下（做的“功课”比实际有用的工作多）。分区会加剧这种症状，特别是未对齐的分区（因为每个分区都设置了父/子数据集）。糟糕的基数估计（过时的统计数据？）也可能是罪魁祸首，在不必要的时候进行并行处理。我的所有建议都是一样的：确定实际的问题查询。

    • 7

  2. 如前所述，CXPACKET 通常是一种误导性的等待类型，因为高值并不总是坏的，而且这些高值通常只是指标而不是真正的问题。

    因此，检查伴随 CXPACKED 的其他等待类型将是故障排除的良好起点。建议检查以下内容：

    • 检查并行成本阈值 (CTFP) 并确保使用的值适合您的系统

    • 检查 CXPACKET 是否带有 LATCH_XX（可能带有 PAGEIOLATCH_XX，如您的情况，或 SOS_SCHEDULER_YIELD）。如果是这种情况，则应降低 MAXDOP 值以适合您的硬件

    • 检查 CXPACKET 是否带有 LCK_M_XX（通常带有 IO_COMPLETION 和 ASYNC_IO_COMPLETION）。如果是这种情况，那么并行性就不是瓶颈。您需要对这些等待统计信息进行故障排除，以找到问题的根本原因和解决方案

    最后，阅读SQL Server 文章中的 CXPACKET 等待类型疑难解答以获取有关此内容的更多深入信息，并检查哪些现实世界场景是最常见的场景。

    • 4

  3. 正如 Remus 所说，您遇到了性能问题，可能是由于混合的工作负载和调整不佳的 sql 语句。以我的经验，最好的解决方法是对 sql 查询使用性能/调整方法。如果这是不可能的，或者如果您必须管理紧急情况，您可以降低 MAXDOP 参数的最高值限制。要找到更好的值，您可以使用 maxdop 查询 int 对您报告的事件（特别是 LATCH_EX）执行前 N 个 sql 查询：

    SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)

    您可以从 MAXDOP=8 开始并降低该值，直到执行时间减少或不变......（是的，执行时间可以减少或保持不变）。当您找到最佳值时，将其设置在实例级别。根据我的经验，这种方法可以提供帮助，但请记住：这是一种解决方法，而不是解决您的性能问题的方法。

    • 1

相关问题