What is CXPackets Wait Type?

 

CXPACKET waits occur when a parallel query is executed and one or more threads have to wait on one of the other threads to complete.  The time spent in the waiting threads of a parallel query is the time that is measured by CXPACKET.

You can get CXPACKET wait when you run sys.dm_os_wait_stats. 

Why must parallel queries wait?

SQL Server will parallelize a query by dividing each operation into equal sized sets for processing.  These sets are then split up into multiple threads targeting specific processors.  This allows each thread to be executed in parallel by each of the processors.  Even with SQL Server splitting up the work into equal sized pieces, it is still impossible for all of the threads to complete at exactly the same time to prevent one or more of the threads from experiencing CXPACKET waits.  In short, if you have parallel query execution you will have CXPACKET waits.  

 Should I be concerned about CXPACKET waits?

This is not something to be concerned about until the CXPACKET waits become excessive.  Once CXPACKET waits are excessive, you know you have some performance tuning that needs to be performed on a specific query or the whole SQL Server.

CXPACKET waits are only a symptom and not an actual problem.  If you are experiencing high CXPACKET waits, you need to find out why the SQL Server engine divided a query into equal size sets that don’t get processed within a relatively equal time period.  This can be due to any of the following:

• Out of data statistics causing SQL Server to incorrectly divide the query into equal sized sets
• Fragmented indexes causing slower IO speeds that impact one thread over the others
• Client applications not efficiently processing result sets
• Hyper-Threading that causes SQL Server to process threads on hyper-threaded cores instead of only physical cores
• CPU pressure
• Memory pressure
• Incorrectly configured ‘max degree of parallelism’ server option
• Incorrectly configure ‘cost threshold for parallelism’ server option

What should I do about high CXPACKET waits?

As with everything dealing with SQL Server performance, the answer is always the same, it depends.  Now that we have the standard ‘it depends’ answer out of the way, let me give you and easy for me answer:

• Make sure that ‘auto create statistics’ and ‘auto update statistics’ is enabled all all databases
• Rebuild all indexes
• Perform an ‘UPDATE STATISTICS’ on all stats objects with the FULLSCAN
• Hyper-Threading should be disable
• ‘max degree of parallelism’ should not be set to a number greater than the number of physical processor cores
• ‘cost threshold for parallelism’ should not be set too low

You can use MAXDOP option to avoid while executing query using all cpu. Using MAXDOP you can specify exactly how many CPU query will use to perform execution. MAXDOP 0 means use as many CPU as available. 

Example:

SELECT *
FROM Sales.SalesOrderDetail
ORDER BY ProductID
OPTION (MAXDOP 1)

There is a lot of misunderstanding about CXPACKET. CXPACKET isn't the cause of your problems, it is a side effect. Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else too.

Link: http://sqlserverperformance.idera.com/network-performance/cxpacket