SQLServer CXPACKET等待事件

• 一个或多个生产者线程将生成数据包并将其发送到缓冲区，然后这些数据会由消费者线程从缓冲区读取。在此过程中，可能会遇到三种导致过多 CXPACKET 等待的情况：
  消费者无法读取数据包，因为缓冲区（队列）为空——这意味着生产者线程没有向缓冲区提供数据或* 提供数据的速度很慢。这意味着一些生产者线程由于等待资源（如 CPU、内存授权、I/O 等）而工作缓慢，或者某些生产者线程只是被阻塞了。
• 生产者线程无法将数据包存储到缓冲区，因为缓冲区已满。这意味着消费者线程无法足够快地处理数据，导致生产者线程必须等待将数据存储到缓冲区，一旦缓冲区满了。
• 对于小查询，过度的并行处理会导致创建并行计划和并行执行的成本高于串行计划，执行速度也更慢。
• 在并行线程之间，数据包的不均衡分配可能导致某些线程的工作完成得比其他线程快，然后它们必须等待其他数据包完成工作

因此，让我们深入了解 SQL Server 的 CXPACKET 等待类型，以更详细地理解这个过程。让我们考虑在使用并行计划执行查询时的理想场景。

并行成本阈值（Cost Threshold for Parallelism，CTFP）的值以秒为单位，这意味着对于每个 SQL Server 估计运行时间超过 5 秒的查询，将创建一个并行计划。这个默认值是在上世纪九十年代设定的，当时使用的是单核计算机、慢速硬盘和内存，而对于现代计算机来说，这个值显然并不理想。在那个时代，执行 5 秒的查询，在现代机器上可能只需几毫秒。一般来说，以秒为单位估计查询执行时间并不是一个好方法，因为查询的成本实际上取决于 CPU、内存、I/O 等因素，SQL Server 并不知道 CPU 的速度、可用的核心/CPU 数量以及使用的 HDD/SSD 的速度。
为了防止不必要的并行性，可以增加 CTFP 数值，通常认为最小值应设为 25。最近的分析表明，现代计算机的最佳最小值应该是 50。然而，找到适当的 CTFP 数值并对其进行微调以获得最佳性能，必须通过分析查询计划和可用资源来确定具体系统的最佳 CTFP 配置。有关如何正确确定 CTFP 值的良好资源是《计划缓存》中的“调整并行成本阈值”。
因此，只有在上述资源耗尽且 CXPACKET 等待时间仍然很大时，才应该考虑调整最大并行度（Maximum Degree of Parallelism，MAXDOP）。MAXDOP 数值代表 SQL Server 将用于并行查询执行的 CPU 核心数量。MAXDOP 的默认设置为 0，意味着将使用所有 CPU 核心进行处理。对于现代计算机，拥有 8、12、32、64 或更多核心，不建议让单个查询占用所有核心。
当高 CXPACKET 值伴随有 LATCH_XX 以及 PAGEIOLATCH_XX 或 SOS_SCHEDULER_YIELD 时，表明慢/低效的并行性本身是性能问题的根本原因。在这种情况下，如果 LATCH_XX 等待是 ACCESS_METHODS_DATASET_PARENT 或 ACCESS_METHODS_SCAN_RANGE_GENERATOR 类，那么并行级别很可能是瓶颈，也是查询性能问题的真正根源。这是需要降低 MAXDOP 的典型例子。
对于那些希望了解如何为 Intel、AMD 和/或虚拟机正确设置 MAXDOP 的人，这里有一篇很好的文章，内容是配置 max degree of parallelism（服务器配置选项）
上述所有描述的目标都是为了让大查询能够并行执行，因为它们可以从中显著受益，并确保小查询以序列化方式运行，因为这是对小查询来说更高效的方式。
另一个可能导致 SQL Server 中出现较高 CXPACKET 等待类型值的场景是数据在线程之间分布不均。这种情况下，CXPACKET 本身并不是问题，而是 CXPACKET 值表明存在其他问题。在这种情况下，排查应集中在其他潜在问题上，以更好地理解这个场景是如何导致 CXPACKET 值偏高的。以下图示将用于说明这一场景。

在这个特定场景中，可以看到线程 1 和线程 2 已经执行并完成了处理，所以它们现在在等待其他线程完成执行。正如在此特定案例中所示，线程 3 和线程 5 仍在运行。这种类型的线程等待称为 CXPACKET 等待。由于每个线程要处理的数据分布不均，有时 CXPACKET 等待类型的值可能会显著升高。大部分负担可能会集中在一两个线程上，而不是所有五个线程平分，如我们例子中所示，因此完成所需的时间会更长。在这种情况下，CXPACKET 等待再一次表明存在问题，虽然问题并不在于并行处理本身，而是由于外部资源导致线程间数据分布不均。应当通过调查问题源，例如不当的索引或过时的统计信息等原因，来排查问题。
也有可能是线程需要等待某些外部资源，最常见的情况是：

• 当线程需要与另一个数据库或应用程序共享 I/O 资源时，这会导致处理速度变慢，需要更多时间来完成工作。
• 一个执行时间较长的大型并行查询，不同的线程需要访问存储在不同物理或逻辑存储设备上的不同数据库，这些存储设备的速度可能不同。
• 一些并行线程所需的资源被同时执行的临时查询阻塞。

这也是一个CXPACKET等待类型只是一个问题指示的例子。在这种情况下，建议查看相关的等待类型，如LCK_M_XX或PAGEIOLATCH_XX，以及经常伴随前两者的IO_COMPLETION和ASYNC_IO_COMPLETION等待。诊断和排查这些等待类型，而不是专注于CXPACKET，将有助于解决通过高CXPACKET等待类型值标示出的并行问题的根本原因。
总结一下，以下是诊断高CXPACKET等待统计值的原因时推荐的步骤（在对SQL Server做出任何仓促反应和更改之前）：

• 不要将 MAXDOP 设置为 1，因为这从来都不是解决方案。
• 调查查询和 CXPACKET 的历史记录，了解并确定它是否只是发生过一两次，因为这可能是系统正常运行时的一个例外。
• 检查查询中使用的表的索引和统计信息，确保它们是最新的。
• 检查并确保并行度阈值（CTFP）设置的值适合你的系统。
• 检查 CXPACKET 是否伴随有 LATCH_XX（可能还伴随有 PAGEIOLATCH_XX 或 SOS_SCHEDULER_YIELD）。如果是这种情况，那么应降低 MAXDOP 值以适应你的硬件。
• 检查 CXPACKET 是否伴随有 LCK_M_XX（通常伴随 IO_COMPLETION 和 ASYNC_IO_COMPLETION）。如果是这种情况，并行处理并不是瓶颈。应排查这些等待类型，找到问题的根本原因和解决方案。