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等待状态。 下图是详细说明给定方案：
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进行时，有几种方法可以识别它。 查询计划中的第一个，如下所示：
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，如下查询所示：
Above query will return the following output:
上面的查询将返回以下输出：
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将扫描操作分为四个部分，每个部分都分配给一个线程。 如您所见，线程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, 下载次数: 149)
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, 下载次数: 146)
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, 下载次数: 142)
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来创建索引或删除错误的缓存查询计划。 如果索引到位，则很可能该索引统计信息已过期。 这样就可以开始更新统计信息了。 如果这样做没有帮助，则可能是索引碎片化了，需要重建索引。
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |