June 12, 2015 at 1:12 pm
Hi Experts,
I have got a question on max degree of parallelism and CPU cores.
If max degree of parallelism = 1, this signifies that sql will use serial execution plan (unless u change it in query level with MAXDOP hint). In serial plan, will the query use all CPU cores (say in my server I have 16 core processors)?
If in serial execution plan only one thread works, then what the other threads doing ? Idle (I may have a defined max server worker thread = 32767(by default)
Unable to create a relationship between this parameters. Please suggest with your expert thoughts.
Happy Friday to All.
Thanks.
June 12, 2015 at 1:15 pm
MAXDOP controls whether a single query plan can be parallelized, and if so, to what extent. Other SQL tasks can, and usually will, still be running across the other CPUs at the same time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2015 at 1:35 pm
How max degree of parallelism and CPU cores are intercinnected???? Can you give some more inside?>
Thanks.
June 12, 2015 at 2:25 pm
SQL-DBA-01 (6/12/2015)
How max degree of parallelism and CPU cores are intercinnected???? Can you give some more inside?>
It's not a direct relationship. Take a close look at the following article:
http://www.brentozar.com/archive/2013/08/what-is-the-cxpacket-wait-type-and-how-do-you-reduce-it/%5B/url%5D
While it starts out talking about a performance concept, what it ends up referring to is MAXDOP, and does a great job of explaining it by analogy.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply