sql cxpacket_对SQL Server中的CXPACKET等待类型进行故障排除

sql cxpacket

The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process. One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using parallel plan.

SQL Server CXPACKET等待类型是最被误解的等待状态之一。 CXPACKET术语来自C lass E x change Packet ，从本质上讲，这可以描述为在两个并行线程之间交换的数据行，它们是单个进程的一部分。 一个线程是“生产者线程”，另一个线程是“消费者线程”。 此等待类型与并行性直接相关，只要SQL Server使用并行计划执行查询，它就会在SQL Server中发生。

Generally speaking, the CXPACKET wait type is normal for SQL Server and it is an indicator that SQL Server is using a parallel plan in executing a query, which is generally faster comparing to a query executed in a serialized process. When the parallel plan is used, the query is executed in multiple threads and the query can continue only when all parallel threads are completed. This mean that query will be as fast as the slowest thread.

一般而言，CXPACKET等待类型对于SQL Server是正常的，它指示SQL Server在执行查询时使用并行计划，与在串行化过程中执行的查询相比，它通常更快。 使用并行计划时，查询将在多个线程中执行，并且仅当所有并行线程都完成后，查询才能继续。 这意味着查询将与最慢的线程一样快。

The below diagram will be used for better understanding of the SQL Server CXPACKET wait type and will help in its interpretation.

下图将用于更好地理解SQL Server CXPACKET等待类型，并将有助于其解释。

From this diagram we can see that whenever a parallel query execution can provide benefit to SQL Server, it will create multiple threads for that statement allowing for each parallel process to produce its own subset of data. Each thread can be processed by the separate physical or logical CPU. Communication between the producer and consumer thread is performed via the producer-consumer queue, which is actually a buffer. The query operator in charge of implementing this queue is called the Exchange operator.

从该图中我们可以看到，只要并行查询执行可以为SQL Server提供好处，它将为该语句创建多个线程，从而允许每个并行进程产生自己的数据子集。 每个线程都可以由单独的物理或逻辑CPU处理。 生产者线程和使用者线程之间的通信是通过生产者－消费者队列（实际上是一个缓冲区）执行的。 负责实现此队列的查询运算符称为Exchange运算符。

One or more producer threads will produce packets and send them to a buffer. That data will then be read from the buffer by the consumer threads. During this process, three different scenarios that can cause the excessive CXPACKET waits can be encountered:

一个或多个生产者线程将产生数据包并将其发送到缓冲区。 然后，使用者线程将从缓冲区读取该数据。 在此过程中，会遇到三种可能导致过多的CXPACKET等待的方案：

• The Consumer cannot read the packets because the buffer (queue) is empty – meaning that the Producer threads do not supply or supply slowly data into buffer. This mean that some Producer threads are working slowly due to waiting for a resource such as CPU, memory grants, I/O etc., or some Producer threads are simply blocked

  消费者无法读取数据包，因为缓冲区（队列）为空–这意味着生产者线程不会向缓冲区中缓慢提供数据或向缓冲区缓慢提供数据。 这意味着某些生产者线程由于等待诸如CPU，内存授予，I / O等资源而工作缓慢，或者某些生产者线程被简单地阻止了

• The producer threads cannot store the packets into a buffer as buffer is full. This mean that Consumer threads cannot process the data fast enough, causing a situation where the Producer threads must wait