Sql-server – SQL Server Threads and Degree Of Parallelism

maxdopmulti-threadparallelismsql server

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

Best Answer

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

Related Solutions

Sql-server – Is it possible to see which SPID uses which scheduler (worker thread)

Thanks to Solomon Rutzky:

Have you tried correlating to the scheduler_id column in sys.dm_exec_requests?

I was able to get the information I was looking for.

With this query, I can see which session is using which CPU_ID (scheduler):

SELECT
    s.cpu_id,
    s.status,
    db_name(r.database_id) as [databaseName],
    w.last_wait_type, 
    w.return_code,
    t.task_state,
    t.pending_io_count,
    t.session_id,
    r.sql_handle
FROM  sys.dm_os_Schedulers s 
JOIN sys.dm_os_workers w 
    ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t 
    ON t.task_address = w.task_address
JOIN sys.dm_exec_requests r 
    ON r.scheduler_id = s.scheduler_id
order by 1,3

To get the SQL statements that are running, I change the query to:

SELECT
    s.cpu_id,
    s.status,
    db_name(r.database_id) as [databaseName],
    w.last_wait_type, 
    w.return_code,
    t.task_state,
    t.pending_io_count,
    t.session_id,
    r.sql_handle,
    te.text
FROM  sys.dm_os_Schedulers s 
JOIN sys.dm_os_workers w 
    ON w.scheduler_address = s.scheduler_address
JOIN sys.dm_os_tasks t 
    ON t.task_address = w.task_address
JOIN sys.dm_exec_requests r 
    ON r.scheduler_id = s.scheduler_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) te   
order by 1,3

But that only gives me (obviously) the tasks that have an SQL_handle.

It seems that most of the threads on that server are being used by the system itself to keep everything in sync. Most of the workers are being used as "system jobs". The last_wait_type of those tasks is mostly REDO_THREAD_PENDING_WORK or HADR_WORK_QUEUE.

Although I have the answer I was looking for, I still didn't find the source of the problem. I'm going to open another question for that ( Who is using my worker threads? SQL Server 2014 - HADR ).

Sql-server – Who is using the worker threads? SQL Server 2014 – HADR

_{Community wiki answer:}

You have a high number of databases in availability groups, that's going to be where your threads are going. There is a lot involved in the compression, encryption, and transport cost. Try turning off compression, it will reduce your thread usage by a about a third (depending on the replica count).

The question is tagged SQL Server 2014, which will by default use compression. SQL Server 2016, by default, will not use compression for sync.

You may need to increase the worker threads on the instance, or better: balance out the most active ones and inactive ones on multiple servers. See the related Q & A AlwaysON Availability group query very slow.

You may also find it is an application that is unable to close requests properly. This can result in lot of sleeping sessions lying around (which consume workers).

The number of threads actually used depends on how active the databases are. You could have 1,000 databases and, if most are idle 95% of the time, you won't have any issues. It seems that your databases have become active more often and have eaten more of your threads. That's the long and short of it.

Best Answer

Related Solutions

Sql-server – Is it possible to see which SPID uses which scheduler (worker thread)

Sql-server – Who is using the worker threads? SQL Server 2014 – HADR

Related Question