[…] CXPACKET—This wait type is involved in parallel query execution, and indicates that the SPID is waiting on a parallel process to complete or start. Excessive CXPacket waits may indicate a problem with the WHERE clause in the query. […]
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 (below) 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 then 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 then 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 be able to 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:
- Adjust the cost threshold for parallelism
- Use the MAXDOP query hint
- Limit parallelism for the entire instance by setting max degree of parallelism to some number less than the total number of CPUs. For example if you have 8 processors, set max degree of parallelism to <=7
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.
Whoever wrote the article has so little understanding of what parallelism is…
Shouldn’t someone from Confio review the articles before they are published and send to everyone by e-mail? It makes me think that there are no SQL specialists in Confio and casts doubt on the quality of the products.
@mind72
I did a quick google search on tuning cxpacket wait types. Almost every single site provides similar advice. Why don’t you share your knowledge if you find something wrong instead of critizing Confio or the author.
@lappesjl This statement, “Queries which are heavily balanced to one sub query or another are a common cause of these waits. If, for example, your query retrieved records from four tables and one of them held the vast majority of records, and parallelism caused this to be spread across several threads, three of them would have to wait on the largest and you would see Exchange wait types.”, is just sooo wrong, that I really don’t see a reason to discuss any advices given here.
If you really want to know more about parallelism there are a lot of better sources of information. For example Paul White: https://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
@mind72 Thanks for your feedback. I’ve updated the article and would appreciate any additional feedback that you may have regarding this or any other article here on LogicalRead.