5

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
)
  • There's a chance that portions of the execution plan are going parallel and waiting for a different portion of the plan to finish doing what its doing (also parallel). Can you post an execution plan of a spid that you are seeing exhibit this behavior? – Kris Gruttemeyer May 25 '16 at 19:10
10

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

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.