3

I have AdventureWorks2014 database installed on my test SQL Server 2014. I am planning to execute the following query:

SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

Before actually executing it, I have the following picture of my worker threads:

enter image description here

While query execution, I again checked the state of worker threads:

enter image description here

As you can see, total active_workers_count in the 2nd picture is higher than that of in the 1st screenshot. From the 2nd screenshot I can assume that total worker threads needed for query execution was 1+2+1+2=6. However when I take a look at properties of index scan operator in my execution plan, I see:

enter image description here

It seems that my degree of parallelism was 4 which means that 4 threads were parallelly processing rows.

I am confused and can't determine how many threads were running during query execution. Can someone explain to me why active_workers_count is different from DOP?

8

I am confused and can't determine how many threads were running during query execution.

To understand that, you really need to look at the execution plan. Here's what I get for this query on SQL Server 2017 at DOP 4 (in the SQL Server 2014 compatibility mode):

screenshot of execution plan showing the different parallel branches

Each parallel branch gets DOP threads. So at first glance, it looks like 12 workers could be active for this query at once, maximum.

However, a Hash Match join has to consume the entire build (upper) input branch before it activates the probe (lower) input branch. So here's how execution goes, generally:

  • 4 worker threads start running the red branch (remember, execution starts at the upper left of a query plan)
  • The Hash Match asks for rows from the black branch
  • 4 worker threads start running the black branch (a maximum of 8 worker threads are active now)
  • Once the black branch is done, the probe side of the execution plan is activated
  • The 4 workers threads used by the black branch are reused by the blue branch (for a maximum of 8 active worker threads still)

Thus this query will use at most 8 parallel worker threads at a time. SQL Server is smart enough to realize that only two branches can be active at once (because of the blocking nature of the Hash Match), and only reserves 8 threads (instead of 12):

screenshot of thread stat node from SSMS execution plan

Notice that it says "2" for branches - that's the number of branches that can execute concurrently.

To address your screenshot of active workers, some threads might finish all of their work before others (because they have less rows to process, for example). So at a given point in the query's execution, you might only have 6 threads running (out of the 8 possible threads).

Related reading:

Parallel Execution Plans – Branches and Threads
In SQL Server, is parallelism per operator, or something else?

2
  • 1
    Thank you Sir for detailed and precise answer – RaufDBA Jul 1 '20 at 21:11
  • @RaufDBA You're welcome, glad to help! – Josh Darnell Jul 1 '20 at 21:27
1

On a machine with four processors, only four threads can be run at a time. However, you can have more than four threads. They just wait on one another to complete steps and go through the processors again and again until complete. So, during heavier loads, you'll see higher thread counts than at lower loads. However, the number running simultaneously is limited by the Max Degree of Parallelism on your server, or the physical number on your box.

2
  • Thank you Sir for your respond. If so, then it seems that the last screenshot either does not show how many rows were processed by other two threads or Thread 1, Thread 2, Thread 3, and Thread 4 in the last pic actually show how many rows were processed by each scheduler. What can you tell about the information given in the last pic? – RaufDBA Jul 1 '20 at 11:53
  • 4

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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