In this post I will go through the SQL Server Wait Type CXPACKET, what is this wait type a symptom of, and how you could go about diagnosing and optimizing the CXPACKET wait type.

CXPACKET generally appears when SQL Server is parallelizing a particular transaction, if certain parts of the transaction has finished before others, these tasks will sometimes have to wait for the other tasks to finish in order to proceed forward with the next stage of the transaction, the wait type these transaction will exhibit will be the CXPACKET, until all dependent tasks have completed and Execution Contexts are merged.

In order to analyse the WAIT TYPE your transactions are exhibiting, you could refer to the sys.os_waiting_tasks Dynamic Management View (DMV), which has a “Wait_Type” column.

SQL Server generally does a great job in deciding when to parallelise a certain transaction and when not to, there are two ways you could control and override SQL Server’s default behavior with regard to query parallelism:

• cost threshold for parallelism Option: This can be configured through the “Facets” menu, or T-SQL (sp_configure). This controls the relative threshold cost of a query after which SQL Server decides to employ transactional parallelism
• MAXDOP Query Hint: This query hint tells SQL Server what is the maximum degree of parallelism to employ when running this query

It is normal for some queries to experience short bursts of CXPACKET wait type, particularly large queries that incur heavy costs on SQL Server’s query processing engine; this is because SQL Server cannot guarantee 100% that all sub-tasks of a particular transaction will finish at the exact same time, no matter how well the execution plan is generated, and so no need to freak out over low bursts of CXPACKET waits.

The problem becomes more complex when you start noticing large amount of CXPACKET waits.

Optimizing SQL Server CXPACKET Wait Type

CXPACKET is actually a symptom of some other problem with one of the sub-tasks of the transaction you are running, if you see a large amount of CXPACKET waits, then you must have one or more sub-task in that transaction experiencing some other Wait Type, which is causing the rest of the sub-tasks to wait until they are complete.

You can look at the OS Waiting Tasks DMV (with your session_id of the troublesome transaction), to investigate what other Wait Type this transaction is experiencing

The image below shows a sample for one of the transactions in a database I am managing.

In the example above, it can be seen that the actual problem was caused by an ASYNC_NETWORK_IO, which is the reason every other Execution Context (or Sub-Task) is being blocked. By identifying the actual cause of the CXPACKET wait type, you will be able to accurately diagnose and optimise your query.

CXPACKET could show up sometimes with no other Wait Type, this generally happens when SQL Server’s engine is in the process of combining results from multiple Execution Contexts, for larger transactions this might be more noticeable, as the datasets being merged is larger. If you are noticing this alot in your queries, you could try fiddling with the MAXDOP query hint, forcing SQL Server to execute under a smaller degree of parallelism could result in reducing the overhead of merging large Execution Contexts, which could possibly lead to an over-all smaller execution cost.

Hope this helps resolve any issues you have with the CXPACKET wait type.