patternsqlModerate
What does it mean to have many rows for a SPID in sp_who2
Viewed 0 times
rowssp_who2whatmeanfordoesmanyspidhave
Problem
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?Solution
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 :-)
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 :-)
Context
StackExchange Database Administrators Q#148948, answer score: 11
Revisions (0)
No revisions yet.