Max Degree of Parallelism

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

  • 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”

  • 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.

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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/

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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.

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

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