Database

SQL Server CXPacket Wait Type

SQL Server CXPacket Wait Type

When are CXPACKET waits a problem?

Note: This article, by Thomas LaRock, was posted 8/19/2013 and supercedes the previous version. This update has been made in response to comments made on the original article text.

The SQL Server CXPACKET wait type is a result of parallel query execution and indicates that a session is waiting for the synchronization of threads involved in the parallel process to complete. By itself, the CXPACKET wait type is not an indication of any specific resource bottleneck, it is simply a synchronization wait. If you see this wait type, it means you have queries running in parallel on your instance and that they may be taking longer than desired.

CXPACKET indicates the operation of multiple parallel CPUs, each executing a portion of the query. In an ideal scenario each thread involved in the parallel execution of the query will process a similar workload. However, it is possible that one (or more) threads will process a larger workload than the others. While that thread is waiting to complete its workload, the remaining threads are all assigned the CXPACKET wait type.

The “SQL 2005 Waits and Queues” whitepaper, a gold standard for SQL Server administrators, has this to say regarding CXPACKET waits:

“Ordinarily, a well-tuned OLTP application would not parallelize a query unless an index is missing, there is an incomplete WHERE clause, or the query is not a true OLTP transaction.”

If you have a dedicated OLTP system and are seeing CXPACKET waits for more than 5% of all resource waits, you are likely experiencing a parallelism bottleneck. For OLAP, or even mixed-use instances, you should expect to see some level of CXPACKET waits as data warehouse queries are likely to be run in parallel.

Tuning for the CXPACKET wait type

If you have determined that a parallelism bottleneck exists, then the logical first step is to identify the queries that are experiencing CXPACKET waits.

To see all the associated parallel tasks, you must query the sys.dm_os_waiting_tasks system view. There you will see the tasks that are not waiting for CXPACKET while the query is running. At that point you will be able to determine what actions to take.

For example, if you see CXPACKET waits as well as PAGEIOLATCH_XX waits for these queries, then this may indicate your queries are doing table scans due to inefficient (or missing) indexes, or even a bad query plan. You should consider tuning such queries.

If tuning the query is not an option, either through code or index modifications, consider the following options:

Setting the max degree of parallelism for the instance will be overridden by the use of the MAXDOP query hint, should both be used at the same time.

If a guaranteed degree of parallelism is required for a specific query (or set of queries), then one may consider the use of Resource Governor to set the degree of parallelism for a workload group.


Thomas LaRock is a Head Geek at SolarWinds and a Microsoft® Certified Master, Microsoft Data Platform MVP, VMware® vExpert, and former Microsoft Certified Trainer. He has over 20 years’ experience in the IT industry as a programmer, developer, analyst, and database administrator. LaRock has spent much of his career focused on data and database administration, which led to his being chosen as a Technical Evangelist for Confio Software in 2010, where his research and experience helped to create the initial versions of the software now known as SolarWinds® Database Performance Analyzer. LaRock has served on the board of directors for the Professional Association for SQL Server® (PASS), and is an avid blogger, author, and technical reviewer for numerous books about SQL Server management. He now focuses on working with customers to help resolve problems and answer questions regarding database performance tuning and virtualization for SQL Server, Oracle®, MySQL®, SAP®, and DB2®. He’s made it his mission to give IT and data professionals longer weekends.