CXPACKET

High numbers of CXPACKET waits can mean that queries are being broken up into multiple threads (parallelism), but some of the threads are finishing before the others.

Steps to Troubleshoot

There's two ways to fix high CXPACKET numbers: reduce the number of threads each query is using, or fix why some of the threads return slowly.

As Denny Cherry points out in his article http://itknowledgeexchange.techtarget.com/sql-server/cxpacket-isnt-the-cause-it-is-a-symptom/, "CXPACKET isn't the cause, it's a symptom, CXPACKET by itself may not be a problem. Find out why one of the threads isn't finishing as quickly as the rest, and troubleshoot that. For example, if a query hits several different data files, and one of the files is on a very slow SAN array, you'll need to make that SAN array faster or move the data off of it. Reducing the number of threads the query uses won't make the query finish faster, because it'll still be waiting on that slow SAN array no matter what.

Related Reading on CXPACKET Waits Case Study on CXPACKET Part 1: http://blogs.msdn.com/jimmymay/archive/2008/11/28/case-study-part-1-cxpacket-wait-stats-max-degree-of-parallelism-option-introduction-to-using-wait-stats-to-identify-remediate-query-parallelism-bottlenecks.aspx by Jimmy May Case Study on CXPACKET Part 2: http://blogs.msdn.com/jimmymay/archive/2008/12/02/case-study-part-2-cxpacket-wait-stats-max-degree-of-parallelism-option-suppressing-query-parallelism-eliminated-cxpacket-waits-liberated-30-of-cpu.aspx by Jimmy May CXPACKET isn't the cause, it's the symptom: http://itknowledgeexchange.techtarget.com/sql-server/cxpacket-isnt-the-cause-it-is-a-symptom/ by Denny Cherry

Occurs with parallel query plans when trying to synchronize the query processor exchange iterator. If waiting is excessive and cannot be reduced by tuning the query (such as adding indexes), consider adjusting the cost threshold for parallelism or lowering the degree of parallelism. Information from Microsoft®