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:- 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
