HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

sp_who returns more rows than the value of "max degree of parallelism"

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sp_whorowsthedegreethanmorevaluemaxreturnsparallelism

Problem

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
)

Solution

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

Context

StackExchange Database Administrators Q#139540, answer score: 10

Revisions (0)

No revisions yet.