7

I run sp_who2 active on box box and get multiple rows per single SPID. For example, see below. Does this mean that SQL Server has broken the query into 23 parallel sub-queries? If that's the case, why is it ignoring MaxDegreeOfParallelism setting of 8? Or is this something else?

enter image description here

AngryHacker
  • 1,961
  • 5
  • 22
  • 33

1 Answers1

11

why is it ignoring MaxDegreeOfParallelism setting of 8?

That is because MAXDOP is per operator and not per execution plan. Have your MAXDOP setting to a good value.

the MAXDOP setting is used to limit the number of threads per operation in the execution plan and does not limit the number of threads used to execute the query. So it is normal to see threads per SPID in sysprocesses greater than MAXDOP setting.

Also see How it Works: SQL Server Per Query Degree Of Parallelism Worker Count(s)

As a side note: use sp_whoIsActive - there are many options and uses DMVs

-- sp_who2 is legacy, uses old deprecated tables and was written during ice age 1995/11/03 10:16 :-)

Community
  • 1
Kin Shah
  • 62,545
  • 6
  • 124
  • 245