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

14 March,2016 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to understand CXPACKET wait type resource description in Sys.dm_os_waiting_tasks


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer