How to understand CXPACKET wait type resource description in Sys.dm_os_waiting_tasks

Question : When checking  on waitin tasks through the Sys.dm_os_waiting_tasks view , I noticed in the resource description column some references which are unfamiliar to me . The wait type was a cxpacket and the resource description was similar to:

exchangeEvent id=Pipe5677568b0 WaitType=e_waitPipeNewRow nodeId=2

How do I parse these details to make sense?

Answer: The resource description column is a "description of the resource that is being consumed". For different types of wait types there are different nomenclatures. For the wait type cxpacket , the breakdown meanings are:

exchangeEvent id={Port|Pipe}<hex-address> WaitType=<exchange-wait-type> nodeId=<exchange-node-id>

Believe it or not , this is useful information paraticuarly wen it comes to troubleshooting  a performance isue.

In the example you've given , let's breakdown the details

e_waitPipeNewRow means the thread acting as the Producer i.e method sending the packets through to the exchange, is waiting to pass the packet.
node = 2 this refers to exchange operator in query plans. Node =2 suggests the subtree underneath is being executed.

It would be great if it were easy from this information you could deduce the scenario, but in reality a few different things could be going on. As a first step you need to identify whether the consumer thread is waiting for a packet but there is nothing for it to consumer , or a thread wants to pass a packet but the cxpacket buffer is saturated. Once you've established the basic pattern, you can begin  trapping other information such as the Execution plan and check for any query optimisation opportunities

Read More

SQL Server - Waitstats CXPACKET wait type and how to reduce it ...

CPU 100% - Check for Parallel Queries - SQL Server DBA

SQL Server – More processor speed or cpu cores

Memory grants and execution plans - SQL Server DBA