Sql-server – sp_who returns more rows than the value of “max degree of parallelism”

parallelismsql server

sp_configure returns the following values on a server.

name                      minimum   maximum config_value    run_value
max degree of parallelism       0     32767            8    8

However, sp_who1 and sp_WhoIsActive show that some spids have more than 8 rows (like 18 or so). Shouldn't it be at most 8 rows in this case?

This query returns a lot of rows from time to time:

SELECT * 
FROM sys.sysprocesses sp
where exists 
(
    select spid 
    from sys.sysprocesses 
    where spid =sp.spid 
    and sp.waitresource = ''
    group by spid 
    having count(*)>9
)

Best Answer

Shouldn't it be at most 8 rows in this case?

No, because a single execution plan can use more threads than specified by the effective degree of parallelism (DOP).

Only a maximum of DOP threads will be active on a scheduler at a given moment in time. SQL Server (2005 onward) limits a parallel query to DOP schedulers, not DOP threads.

Since a scheduler abstracts a logical processor, this effectively limits the parallelism as desired.

DOP threads are assigned to each parallel branch, where a branch is delimited by Parallelism (exchange) operators.

For more information, see my Simple Talk article:

Understanding and Using Parallelism in SQL Server

and/or my PASS Summit 2013 session recording:

Parallel Query Execution

Related Question