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



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



  • 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, https://www.brentozar.com/archive/2013/08/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 https://www.sqlshack.com/troubleshooting-the-cxpacket-wait-type-in-sql-server




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2