1

I have sql server with 8 cpus. Max worker threads setting is say 850. Max dop is say 8. And cost thread hold for parallelism is 50.

This means sql server will breakdown a query going above cost threshold into 8 threads. Since each threads runs on one cpu, then does this mean no other user will be allowed to run a query until at least one of the currently running 8 threads gets released?

So does setting max dop = number of cpu's cause situation where a single query blocks other queries?

3 Answers 3

8

In addition to the other posts:

Once a task has been given access to the core, it has roughly 4 ms to do what it wants to do. After ~4ms, it yields so the next in line will be given access to the core (cooperative multitasking).

You can have 3 different outcomes after a task has been given access to the core:

  1. It finishes before the ~4ms, and the session goes back to sleeping. Next in line is given the core.
  2. It isn't done yet - it wants more CPU and is put last in queue (a signal wait). Next in line is given the core.
  3. It encounters a wait (lock for instance) before the ~4ms and is put on a waiting list (a resource wait). Next in line is given the core.

None of above resulted in blocking.

There can of course be issues with the thread not giving up the core by it self (non-yielding), but that is not the normal, and troubleshooting should be done in such a situation.

1
  • I agree with None of above resulted in blocking.. But does this cause increase in query execution time? Thus giving users an impression of slowness? Will adding more CPUs help here?
    – variable
    Feb 13 at 19:43
6

MAXDOP is the max degree of parallelism for the extra worker threads used to process a parallelized branch of a query plan for a particular executing query (or for all queries, at the server level, if provisioned that way). That means it's an upper threshold to ensure that branch of operators of a query cannot exceed that number of threads, concurrently, when executing.

The default setting on a server is 0 which means no limit on how many degrees of parallelism a branch of operations of a query plan can go. So by setting MAXDOP, you can do no worse than the existing default. In fact, in your example server with 8 CPUs, setting MAXDOP to 8 is the same as the default of 0 aka no threshold. Please see Brent Ozar's Configuring Parallelism Confusion for more information on appropriate configurations.

One other interesting thing to keep in mind is the maximum number of concurrently running threads for the entire query plan, of a parallelized plan, can be up to whatever MAXDOP is set to plus 1. This is because there is still the original thread acting as the parent task to fire off and coordinate other parallel threads for the different operators within the parallelized branches of the plan.

3
  • Does MAXDOP apply to the entire query or each query branch?
    – variable
    Jun 26, 2023 at 20:49
  • @variable Officially, in the docs, it per task. But a task is the equivalent of a branch in the query plan, so yes per query plan branch.
    – J.D.
    Jun 27, 2023 at 3:24
  • MAXDOP indeed controls the number of threads used by the branch, but if there are multiple branches, then combined all the threads (across all branches) will effectively use only N CPUs, where N=maxdop, is this correct to say?
    – variable
    Feb 13 at 19:47
4

SQL Server might split each operation (table scan, index seek, aggregation and so on) up to MAXDOP threads. The threads are created and destroyed on use basis, and the total number of threads used in a query might well exceed MAXDOP, since each parallel operation might have its own thread. As per the docs:

The max degree of parallelism limit is set per task. It is not a per request or per query limit. This means that during a parallel query execution, a single request can spawn multiple tasks up to the MAXDOP limit, and each task will use one worker and one scheduler.

The query optimizer doesn't need to use parallelism. The optimizer looks for good enough a plan, and for trivial queries it is often decided that a serial plan is more efficient than building and running a parallel plan. Parallelism has its drawbacks, not all operations parallelize nicely, and there is extra book-keeping involved on thread synchronization.

See also Paul White's nice overview about parallelism in SQL Server.

Blocking is caused by a session that holds a lock to a resource. That doesn't depend on parallelism - unless there would be a query that greatly benefits from parallel plan, but MAXDOP is set to one. In such case, leveraging parallelism would just release the lock faster, leading to less blocking.

1
  • 1
    Your opening paragraph is misleading. The scope of a task is a plan branch, not a single operator, see my answer dba.stackexchange.com/a/205920/1192. I don't know why people find this so difficult. It's not like they have trouble understanding a single thread can run multiple operators in a serial plan.
    – Paul White
    May 4, 2022 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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