26 February,2011 by Jack Vamvas
According to SQL Server BOL – the CXPCACKET wait type
“Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.”
The CXPACKET wait type occurs when more than one CPU core exists. Queries will run in parallel and the waits will occur when one part of the query is waiting for another thread to complete it’s workload.
CXPACKET wait types are not necessarily a sign that queries are performing inefficiently- and should be considered as normal when multiple cores exists.
The approach I take when analysing this wait type is to first identify which queries are running with parallelism and then determine why ? they are running with parallelism. Are they frequent queries?
Reducing the CXPACKET is not just about looking at parallelism – but also consider other factors , such as workload type – (OLTP,DSS). The complexity arises when considering a mixed system , as is typical with many OLTP system – that also have reporting , such as Crystal Reports running over them . The recommended setting for OLTP is MAXDOP = 1 , but when there is reporting , what is the correct level?
The nature of OLTP systems would suggest the less parallelism the better – as we are looking for a high volume , but short transactions . For DSS systems – this is not such a concern , but may still indicate that there are other problems , such as : queries need tuning, out of date statistics, index reorganize
For example recently on a mixed OLTP\Reporting Server I switched to MAXDOP = 2 , which solved some problems – but created others. It was only after some thorough testing I switched back to MAXDOP=1 , and adjusted some of the queries. Hardware is also an issue
Configuring parallelism can be achieved on 2 levels:
1)At an server instance level – this will apply to all connections
2)Use a Table hint Option (Maxdop N) – N representing the processor number. This applies to the one session on the one query