CXPACKET wait in sql server occurs during parallel query execution, when a session is waiting on a parallel process to complete. This is usually a normal benign wait type. CXPACKET is not usually the problem, but rather the victim.
CXPACKET will occur whenever you have more than one processor or core.
You might find documentation that says you can get rid of CXPACKET by setting the MAXDOP setting to 1. This does indeed eliminate all CXPACKET waits, however setting MAXDOP to 1 turns off all parallel processing on your SQL Server, which will likely slow down the execution of many queries.
It is likely that if your CXPACKET waits are high then you may be caused bye one of the following:
- Missing indexes
- CPU pressure
- Memory pressure
- 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
- Missing search predicate
- Queries that are forcing a row by row processing of results rather than using sets.
- Client applications not efficiently processing result sets
- Incorrectly configured ‘max degree of parallelism’ server option
- Incorrectly configure ‘cost threshold for parallelism’ server option
One way to help this is to adjust the Cost Threshold for Parallelism which will reduce the number of queries that are being parallelized and may help with the CXPACKET wait, but this may not be the best option.
The right thing to do is to tune the queries that are causing processor load which should help with the other queries that are causing CXPACKET waits.