sql server 性能调优资源等待之 CXPACKET

jiawang · 发表于 2023-5-24 09:01:31

本帖最后由 jiawang 于 2023-5-24 09:29 编辑

　　
cxpacket
CXPACKET is one of the famous wait type that database administrators are experiencing. Before moving into the details of CXPACKET wait type, first let us discuss about the waits in SQL Server in brief.
CXPACKET是数据库管理员正在经历的著名的等待类型之一。
在进入CXPACKET等待类型的细节之前，首先让我们简要讨论一下SQL Server中的等待。
SQL Server is a mini operating system. When SQL Server is executing any task and if for any reason it has to wait for resources to execute the task, it will wait in a list until it gets the relevant resources. This list is called Waiter list or suspended list. This is not a queue as whenever that task is ready with required resources it will move to the runnable queue which means that it is ready to execute whenever the processor is free to execute. Depending on the type of the wait, there are more than 200 wait types. CXPACKET, WRITELOG, ASYNC_NETWORK_IO are the most common wait types. This is very brief discussion about waits. For any case where this is not clear, it will be better to get more details from other sources as this article is not intend to discuss about waits in detail.
SQL Server是一个小型操作系统。当SQL Server执行任何任务时，如果由于某种原因必须等待资源来执行任务，它将在列表中等待，直到获得相关资源为止。此列表称为服务员列表或暂挂列表。这不是队列，因为只要该任务准备好所需的资源，它将移至可运行队列，这意味着只要处理器有空执行，它就可以执行。根据等待的类型，有200多种等待类型。 CXPACKET，WRITELOG，ASYNC_NETWORK_IO是最常见的等待类型。

The CXPACKET wait type is the one of the most common wait type if not the most. According to the survey done at sqlskils.com, out of the waits that are occurring in the surveyed systems, more than 25% are CXPACKET wait types. That tells you how frequent CXPACKET wait type is.
CXPACKET等待类型是最常见的等待类型之一（如果不是最多的话）。根据sqlskils.com进行的调查，在被调查系统中发生的等待中，超过25％是CXPACKET等待类型。

CXPACKET occurs when a parallel operation is created for a task. The query processor may determine that a particular operation can be performed more efficiently by using multiple threads depending on the cost of the query. At the execution time, the query execution portion of the query processor decide what parallelism to use. For example, if you are executing a query which deals with a large portion of a table, multiple threads are required. This is a common phenomenon when queries running in a fact table in a data warehouse system as typical query in a fact table needs a large portion of the data to be extracted. After identifying the number of threads needed to execute the query, it will decide which portion to be executed by which thread. Ideally, load will be divided equally among the threads. This scenario is displayed in the following diagram:
为任务创建并行操作时，将发生CXPACKET。查询处理器可以根据查询的成本确定通过使用多个线程可以更有效地执行特定操作。在执行时，查询处理器的查询执行部分决定使用哪种并行性。例如，如果您正在执行处理表的大部分的查询，则需要多个线程。当在数据仓库系统中的事实表中运行的查询时，这是一种常见现象，因为事实表中的典型查询需要提取大部分数据。确定执行查询所需的线程数后，它将决定由哪个线程执行哪个部分。

理想情况下，负载将在线程之间平均分配。下图显示了这种情况：

登录/注册后可看大图

QQ图片20230524091917.png (28.63 KB, 下载次数: 212)

下载附件

2023-5-24 09:19 上传

Let us assume that blue part is the table which needs to be scanned. SQL Server has divided scanning operation to four parts and each part is assigned to a thread. As you can see, thread 1 is assigned to a part one of the table etc. There will be a control thread which will be overlooking the other executing threads. Control thread will be seen as CXPACKET while other threads are seen as executing or running.
让我们假设蓝色部分是需要扫描的表。 SQL Server将扫描操作分为四个部分，每个部分都分配给一个线程。如您所见，线程1被分配给表等的一部分。将有一个控制线程，它将忽略其他正在执行的线程。控制线程将被视为CXPACKET，而其他线程将被视为正在执行或正在运行。
There can be another scenario for the CXPACKET wait type. Though the entire load is divided among threads so that cost will be equal, there can be cases where one or more threads are still executing while the other threads have completed. Until the last thread is executed, other completed threads are on CXPACKET wait. Following diagram is to elaborate the given scenario:
CXPACKET等待类型可能还有另一种情况。尽管整个负载在线程之间分配，因此成本是相等的，但是在某些情况下，一个或多个线程仍在执行，而其他线程已完成。在执行最后一个线程之前，其他已完成的线程将处于CXPACKET等待状态。下图是详细说明给定方案：

登录/注册后可看大图

QQ图片20230524092627.png (29.96 KB, 下载次数: 209)

下载附件

2023-5-24 09:26 上传

CXPACKET scenario is equivalent to a scenario where you have given lots of current notes to count to a bank cashier. Since one cashier cannot count all the notes alone, he will distribute it among his fellow workers. In case, one cashier is still counting while the others have completed the counting, still all the cashiers have to wait until the last count is known.
CXPACKET方案等同于您已将大量当前票据分配给银行出纳员的方案。由于一个收银员不能单独计算所有钞票，因此将其分配给他的同工。万一一个收银员仍在点票，而其他收银员已完成点票，则所有收银员仍必须等到知道最后一次点票。
When a CXPACKET is going on, there are couple of ways to identify it. First one from the query plan, as shown below:
当CXPACKET进行时，有几种方法可以识别它。查询计划中的第一个，如下所示：

登录/注册后可看大图

QQ图片20230524092719.png (31.52 KB, 下载次数: 217)

下载附件

2023-5-24 09:27 上传

In the query plan, there is a yellow colored circle in the controls, which denotes that query is on parallelism.
在查询计划中，控件中有一个黄色圆圈，表示该查询处于并行状态。
Another way of identifying the CXPACKET wait type is by querying sys.dm_os_waiting_tasks DMV as shown in below query:
标识CXPACKET等待类型的另一种方法是查询sys.dm_os_waiting_tasks DMV，如下查询所示：

SELECT
owt.session_id,
owt.exec_context_id,
owt.wait_duration_ms,
er.command,
owt.wait_type,
owt.blocking_session_id
FROM
sys.dm_os_waiting_tasks owt
INNER JOIN sys.dm_exec_sessions es ON
owt.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
WHERE es.is_user_process = 1 --and es.session_id = 400
ORDER BY owt.session_id, owt.exec_context_id

Above query will return the following output:
上面的查询将返回以下输出：

登录/注册后可看大图

QQ图片20230524092909.png (55.22 KB, 下载次数: 216)

下载附件

2023-5-24 09:29 上传

In this query, you can see the CXPACKET wait type and other information like the duration for the wait type. In the displayed scenario wait duration is 1713 milliseconds. The Blocking session id column gives you an understanding about which session is blocking the current session. In the CXPACKET scenario, no other external session is blocking the current session but the same session itself.
在此查询中，您可以看到CXPACKET等待类型以及其他信息，例如等待类型的持续时间。在显示的方案中，等待时间为1713毫秒。阻止会话ID列使您了解哪个会话正在阻止当前会话。在CXPACKET方案中，没有其他外部会话阻止当前会话，而是同一会话本身。
After analyzing both scenarios, it is clear that CXPACKET wait type occurs because of the parallelism. Next question is, how we can avoid the CXPACKET wait type. As said previously, sometimes parallelism is not something you can avoid. In a case of data warehouse fact table example, most of the time you need it to just execute and no intervention is required. If the CXPACKET is occurring because of a table scan, most likely that table doesn’t have a correct index or incorrect query plan. Either you can create an index or remove the bad cached query plan by executing sp_recompile for the stored procedure. If the index in place, most likely that index statistics are out of date. So you can start update statistics. If that won’t help, then it might be that index has fragmented and index rebuild is required.
在分析了两种情况之后，很明显，由于并行性，发生了CXPACKET等待类型。下一个问题是，如何避免CXPACKET等待类型。如前所述，有时无法避免并行性。以数据仓库事实表为例，大多数情况下，您只需要执行它就可以，不需要干预。如果CXPACKET是由于表扫描而发生的，则很可能该表没有正确的索引或错误的查询计划。您可以通过为存储过程执行sp_recompile来创建索引或删除错误的缓存查询计划。如果索引到位，则很可能该索引统计信息已过期。这样就可以开始更新统计信息了。如果这样做没有帮助，则可能是索引碎片化了，需要重建索引。

帐号		自动登录	找回密码
密码			注册

sql server 性能调优 资源等待之 CXPACKET

sql server 性能调优资源等待之 CXPACKET