-1

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.`

0

2 Answers 2

3

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

3

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.

5
  • In your 2nd example, why do 2, 3, 4 have to wait for 1? Assuming there is another process having thread 0 that is responsible to collect the data, can't they handover their data to thread 0 as they complete the work?
    – variable
    May 13 at 15:49
  • 1
    One task/process with 4 threads. Only when all records have been returned are the threads available for other tasks. They start together and end together.
    – John K. N.
    May 13 at 16:04
  • Assuming there are 8 processors then say 1 request resulted in 8 threads, 1 on each processor. While that's happening, say another request comes in which will assign another 8 threads to those 8 processors. Assuming each processors will then assign some time for each thread that it is responsible for. Also based on max worker threads only those many threads can exist at a time. But why does thread not get released by passing g its data into the master (coordinator) thread?
    – variable
    May 13 at 16:35
  • Appreciate your valuable knowledge on this famous yet little understood topic
    – variable
    May 14 at 11:43
  • The current tasks being executed will switch between each other. However, the threads responsible for the parallel process task of reading the data will still run together even if they don't have anything to do. Some of the threads may have nothing to do, but they still have to wait for the whole task to finish before they are released. It's still the same as in the descriptive explanations. Ergo: Having up-to-date statistics can greatly reduce the CX_CONSUMER waits, because the threads will terminate at nearly the same time.
    – John K. N.
    May 17 at 7:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.