Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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:
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.
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.
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.
