maxdop doesn't take effect?

Question

maxdop doesn't take effect?

Viewing 2 posts - 16 through 16 (of 16 total)

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

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

sqldba_newbie (3/19/2013)
opc.three (3/19/2013)
Even though estimated cost is well over 20, i still see query using all the cores?
Cores, or threads? What exactly are you seeing that is making you think it is using all cores?
Server has 16 cores, i see 17 threads

17 threads running on all 16 cores? Or 17 threads with <= maxdop of them running?

Maxdop controls the total cores that can be used, not the total threads that can be created.

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 2

GilaMonster (3/19/2013)
sqldba_newbie (3/19/2013)
opc.three (3/19/2013)
Even though estimated cost is well over 20, i still see query using all the cores?
Cores, or threads? What exactly are you seeing that is making you think it is using all cores?
Server has 16 cores, i see 17 threads
17 threads running on all 16 cores? Or 17 threads with <= maxdop of them running?
Maxdop controls the total cores that can be used, not the total threads that can be created.

My apologies and i will go back and read on this topic. Based on my observation, when i run sp_who2 active, i would see a spid with multiple records most of the times it would be equal to number of cores. Is there a way to find how many threads a spid is using?