MaxDop and Sys.SYSPROCESSES

Question

MaxDop and Sys.SYSPROCESSES

seaport

SSCrazy

Points: 2491
More actions
April 29, 2015 at 2:29 pm

#317813

The question is how MaxDop setting is related to the duplicated spids in the sysprocesses view.
I am using SQL2014. The MaxDop used to be 8.
I ran a large query and got 8 processes running with the same spid. (SELECT SPID ,count(*) FROM SYS.SYSPROCESSES WHERE SPID>50 group by SPID)
I got massive CXPacket wait. I updated MaxDop to 3 to see whether it makes a difference. (I have 4 CPUs on the server, 3 physical core for each CPU. But this post is not about MaxDop setting).
After the MaxDop change, I confirmed the change taking effect with the sp_configure.
After that, I ran the same large query and still got 8 processes running with the same spid, sometime the number dropped to 4. I did not expect that, I thought the number should be 3.
Any advice?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 1

Changing maxdop alone does not impacyt much as your cost threshold for parallelism might be left to defaul of 5 , Maxdop depends on cost threshold for parallelism, based on the cost of the query and your setting for cost threshold to define which is large query in your context and then assign the cpu based on that. more details here [/url]

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

seaport SSCrazy Points: 2491 More actions · Answer 2

Thanks. The cost threshold was set to 50 initially. Now I see the # of processes for my query stay as 4. So my MaxDop change did take effect.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

MAXDOP = number of threads concurrently *running*, not max number of threads. With maxdop of 8 you could see way more than 8 threads. No more than 8 will ever be running at the same time though.

p.s. sysprocesses is from SQL 2000, maybe try the DMVs for something less than 15 years old?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

seaport SSCrazy Points: 2491 More actions · Answer 4

Gail,

With the MaxDop set as 3, I got 41 threads count at one time, using "exec sp_who2 70", where 70 is the spid of my query. I am confused. Is there a definitive way to test the effect of MaxDop setting change?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Again, MAXDOP does not control the number of threads in total. It controls the number of threads that may be concurrently *running* for a single query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

seaport SSCrazy Points: 2491 More actions · Answer 6

Gail,

Thanks.

Now I understand. MaxDop sets the max # of threads/processes per operator, not per query/spid. If the query plan of my query decides to use 15 operators, and each of it utilizes parallelism, then the total # of threads can easily go over 40.