Why does MAXDOP = 0 result in high CXPACKET wait time?

Question

During a parallel query execution (whether or not to go parallel is decided by the cost threshold for parallelism) MAXDOP limits the number of tasks per request.

Assuming cost threshold for parallelism is a low value, there by causing even smaller queries to work in parallel.

When MAXDOP is set to 0, what makes the processors that have done the work wait (resulting in high CXPACKET wait time). Can they not pick up the next work and not just wait?

What makes the processor wait once it has done its job?

From https://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/

This isn’t really a bottleneck per se – the students could go off and do other work – but they like to complain about how they had to wait around for the slow kids.

That complaining is CXPACKET – Class eXchange Packets. The class is turning in their packets, and complaining about the slow kids.`

Answer 1

It's not a unique problem to MAXDOP = 0, rather it's just easier for excessive waiting to happen between parallel threads and increased overhead when there are more threads used for parallelism for a given set of processes in a query. With MAXDOP = 0 there is no limit (up to the number of CPU cores allocated to the server) on how many threads can be used when a subset of operations in a query plan go parallel.

In the Brent Ozar article you linked, What is the CXPACKET Wait Type, and How Do You Reduce It?, he mentions how Microsoft's recommendation is to not set MAXDOP above 8, as there's usually diminishing returns on parallelism in query plans when more than 8 threads are used. This could result in uneven distributions of the work across the parallelized threads, which would result in some threads waiting with nothing to do while the other threads doing the bulk of the work are still processing. This is one way that CKPACKET waits can occur excessively.

Even when the work is evenly distributed, it may be small enough that too many threads are still no more efficient than a few less threads, e.g. 8 threads might process the work just as quickly as 4, and then there's additional overhead for spinning up those threads and also for the coordinator thread to consume the work back from the 8 threads (the "students turning in their homework" part of Brent Ozar's article) which would've been faster / had less overhead for 4 threads.

So again it's not that MAXDOP = 0 is the problem or any specific value for MAXDOP is the solution, rather it's just ensuring your queries and server are configured appropriately based on the typical workload running on it. And that the chances of excessive overhead from parallelism are increased as more cores are allowed to be used for parallelism in a particular query.

For more information on how CXPACKET waits work, please see Troubleshooting the CXPACKET wait type in SQL Server

Answer 2

You wrote:

What makes the processor wait once it has done its job?

It isn't the processor that is waiting nor is it the process that is waiting, it is the physical operation that has gone parallel that is waiting for the last thread to return its data.

If you had one thread reading the data then you would have a serial read like this:

1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
  ^                                                                              ^
  |                                                                              |
  Start                                                                        End
--> (t)

Example for 80 records

Now if the query optimizer decides to go parallel because of the CTFP (cost threshold for parallelism) and the MAXDOP setting of 0, then in a server with four (for simplification) cores, the process will ideally split the load of reading data via the index onto the four processors:

1 xxxxxxxxxxxxxxxxxxxxxx
2 xxxxxxxxxxxxxxxxxxxxxx
3 xxxxxxxxxxxxxxxxxxxxxx
4 xxxxxxxxxxxxxxxxxxxxxx
  ^                    ^
  |                    |
  Start              End
--> (t)

The process of retrieving 80 records is shorter in duration if the query can be executed in parallel. In doing so the Query Engine assumes an even distribution of data. This is based on up-to-date statistics.

In some case the statistics can be skewed (not up-to-date) and the workload is not distributed equally. Depending on the WHERE clause and tables involved and other factors, you could have a situation like this:

1 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (40 rows)
2 x                                            ( 1 row)
3 xxxxxxxxxxxxxxxxxxxxx                        (19 rows)
4 xxxxxxxxxxxxxxxxxxxxxx                       (20 rows)
  ^                                          ^
  |                                          |
  Start                                    End
--> (t)

As you can see, it takes longer to retrieve the data than if the statistics were up-to-date and the load of retrieving the data would be distributed evenly. It is still faster than a serial retrieval, BUT the other threads (2, 3, 4) have to wait until thread 1 has finished retrieving its lump of data.

These waits are the CXPACKETS and CXCONSUMER you may be observing. CXPACKETS (retrieving data in parallel) aren't bad. They even occur when date being retrieved is evenly distributed between the the threads, but then you have less CXCONSUMER (waiting for parallel data).

Answering Your Question

What makes the processor wait once it has done its job?

It's not waiting. It's performing.

• CXPACKET
• CXCONSUMER