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:
While query execution, I again checked the state of worker threads:
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:
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?