Post reply

How many threads are being used while query execution?

  • July 1, 2020 at 9:09 am

    #3766630

    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

    I took screenshots before query execution and while executing it correspondingly.

    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 can see that 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?

     

    • This topic was modified 4 years, 5 months ago by  RaufDBA.

    Attachments:
    You must be logged in to view attached files.

  • July 1, 2020 at 11:55 am

    #3766664

    The actual plan shows the number of threads used. Counting worker threads you're seeing other artifacts from other processes, including system processes. MAXDOP limits the number of concurrent processors used and therefore the number of concurrent, active, threads. However, that doesn't limit the number of threads period. Two processes running, minimum, two threads. On one CPU, they take turns until they complete.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 22, 2020 at 4:28 am

    #3772244

    Grant Fritchey wrote:

    The actual plan shows the number of threads used. Counting worker threads you're seeing other artifacts from other processes 8 ball pool, including system processes. MAXDOP limits the number of concurrent processors used and therefore the number of concurrent, active, threads. However, that doesn't limit the number of threads period. Two processes running, minimum, two threads. On one CPU, they take turns until they complete.

    thanks for taking the time to explain.

    • This reply was modified 4 years, 4 months ago by  dexterdaniel.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply