Max Degree of Parallelism

Question

Max Degree of Parallelism

Kendra.Little

Old Hand

Points: 325
More actions
December 10, 2018 at 10:09 pm

#416548

Comments posted to this topic are about the item Max Degree of Parallelism

Viewing 9 posts - 1 through 8 (of 8 total)

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

Stewart "Arturius" Campbell SSC Guru Points: 72616 More actions · Answer 1

Nice question,Kendra
haven't thought about this for quite some time... good to dust off those cobwebs every so often...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

lmalatesta SSChasing Mays Points: 637 More actions · Answer 2

It's also one of the first settings I change whenever someone asks me to tune up their database. The default setting of 0 isn't great for most server instances. In most cases, MAXDOP should be set to the number of cores on a single CPU card.

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 3

lmalatesta - Tuesday, December 11, 2018 12:41 PM
It's also one of the first settings I change whenever someone asks me to tune up their database. The default setting of 0 isn't great for most server instances. In most cases, MAXDOP should be set to the number of cores on a single CPU card.

Ok... where did you come up with that rule?

p.s. And, no... that's not a challenge. There's a bit of logic to that and I'd like to know more.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Shayn Thomas SSCertifiable Points: 5863 More actions · Answer 4

an interview question i got wrong many moons ago and has stuck with me since 🙂

---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know

lmalatesta SSChasing Mays Points: 637 More actions · Answer 5

Jeff Moden - Tuesday, December 11, 2018 6:19 PM
lmalatesta - Tuesday, December 11, 2018 12:41 PM
It's also one of the first settings I change whenever someone asks me to tune up their database. The default setting of 0 isn't great for most server instances. In most cases, MAXDOP should be set to the number of cores on a single CPU card.
Ok... where did you come up with that rule?
p.s. And, no... that's not a challenge. There's a bit of logic to that and I'd like to know more.

It's Microsoft's recommendation and something Brent Ozar repeatedly hammers on.

See: https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Jeff Moden SSC Guru Points: 1004280 More actions · Answer 6

lmalatesta - Wednesday, December 12, 2018 7:54 AM
Jeff Moden - Tuesday, December 11, 2018 6:19 PM
lmalatesta - Tuesday, December 11, 2018 12:41 PM
It's also one of the first settings I change whenever someone asks me to tune up their database. The default setting of 0 isn't great for most server instances. In most cases, MAXDOP should be set to the number of cores on a single CPU card.
Ok... where did you come up with that rule?
p.s. And, no... that's not a challenge. There's a bit of logic to that and I'd like to know more.
It's Microsoft's recommendation and something Brent Ozar repeatedly hammers on.
See: https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/

Interesting. Thanks for the links.

My general rule of thumb is that no one or thing general gets to use more than 25% of the total CPU available or 8 CPUs, whatever is less. On boxes with a very low number of CPUs, I'll make exceptions based on the task(s) at hand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Conn SSCertifiable Points: 6010 More actions · Answer 7

Jeff Moden - Wednesday, December 12, 2018 7:54 PM
lmalatesta - Wednesday, December 12, 2018 7:54 AM
Jeff Moden - Tuesday, December 11, 2018 6:19 PM
lmalatesta - Tuesday, December 11, 2018 12:41 PM
It's also one of the first settings I change whenever someone asks me to tune up their database. The default setting of 0 isn't great for most server instances. In most cases, MAXDOP should be set to the number of cores on a single CPU card.
Ok... where did you come up with that rule?
p.s. And, no... that's not a challenge. There's a bit of logic to that and I'd like to know more.
It's Microsoft's recommendation and something Brent Ozar repeatedly hammers on.
See: https://support.microsoft.com/en-us/help/2806535/recommendations-and-guidelines-for-the-max-degree-of-parallelism-confi
https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
Interesting. Thanks for the links.
My general rule of thumb is that no one or thing general gets to use more than 25% of the total CPU available or 8 CPUs, whatever is less. On boxes with a very low number of CPUs, I'll make exceptions based on the task(s) at hand.

I had an Issue recently but nothing I found via Google had a Value higher than 8.

lmalatesta SSChasing Mays Points: 637 More actions · Answer 8

David Conn - Wednesday, December 19, 2018 12:40 AM
I had an Issue recently but nothing I found via Google had a Value higher than 8.

And, in fact, most of the resources I've come across suggest that 8 should be the max. I left that out of my original comment for brevity's sake.