SQL Server Threads and Degree Of Parallelism

Question

My understanding about SQL Server threads is that SQL Server breaks down an execution request within a session into several tasks and associates each task with a worker thread. So SQL Server always tries to use multiple threads (if available and needed ) to run a query, regardless of whether it is a parallel or nonparallel query.

But the following statement (From book Microsoft SQL Server 2012 Internals (MS Press)) seems to state something different:

A parallel query execution plan can use more than one thread; a serial execution plan, used by a nonparallel query, uses only a single thread.

Edit: My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.

For a parallel execution, multiple schedulers/processors would process the same execution request (which is broken down to several tasks) at the same time, and each of the scheduler will use (again, when there is a need) multiple workers/threads from their respective NUMA node.

In other words, parallel or not, there is a chance multiple threads are used.

Obviously there must be a flaw in my original understanding, but I don't know where the misconception is. Does my original understanding only apply when it is a PARALLEL query being executed?

Thanks

Answer 1

My original understanding is (likely incorrect) a scheduler may (when there is a need) use multiple workers/threads from the same NUMA node to process different tasks of the same execution request when it is a serial execution.

A serial plan will not use multiple threads it will only use single thread to perform the task. When the serial query would start it would be bound to a scheduler and that scheduler will not perform any other task unless it completes this query. The scheduler may wait for a process to complete thus forcing query to wait.

It is also worthy noting that MAXDOP setting controls the amount of thread used for parallel processing and MAXDOP is always specified per operator in the execution plan and not per execution plan

I strongly suggest you read below article by Paul White

Understanding and Using Parallelism in SQL Server