DB MAXDOP Settings

  • Comments posted to this topic are about the item DB MAXDOP Settings

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • Cool option. Yet another reason to upgrade to 2016.

  • Ken Wymore - Tuesday, March 21, 2017 8:00 AM

    Cool option. Yet another reason to upgrade to 2016.

    For sure.
    Nice question, thanks Henriko

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

  • Please excuse me if this obvious, but what are primary and secondary databases? Are we talking about databases involved in HA? Are these only Always On HA or does it include Log Shipping? If you are able to answer this can you provide a reference.

  • bretknoll - Sunday, March 26, 2017 7:53 AM

    Please excuse me if this obvious, but what are primary and secondary databases? Are we talking about databases involved in HA? Are these only Always On HA or does it include Log Shipping? If you are able to answer this can you provide a reference.

    I could certainly be wrong but I'm thinking it was a typo.  I believe what was meant was "Primary and Secondary FILES".

    @ Henrico Bekker ... can you verify, please?  And if you really did mean "Databases" and not "Files", then I agree with Bret... do you have a reference link because I've never heard of "Primary and Secondary DATABASES" beforeEDIT: SEE THE NEXT POST BELOW FOR A CLARIFYING LINK!

    Shifting gears a bit, this IS a really cool feature because I have predominately batch-oriented databases that would benefit from a higher max dop and busy OLTP databases that should be relegated to a lower MAXDOP on the same box.  In the past, my rule of thumb was MAXDOP <= Core/4 < 8 .  I can tweak the hell out of that when we get to 2016 in a couple of months.

    Another really helpful 2016 feature for databases that haven't yet been brought up to snuff to fix years of legacy development and poor coding practices that causes the bad form of parameter sniffing is the new ability to clear proc cache at the database level.

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

  • Yeah... I was wrong.  They really do mean "Primary and Secondary DATABASES".  Here's a reference that's a bit more clear rather than leaving those of us that don't know "in a lurch for a search" for what they meant.  It's one of those things where if you don't know about it, it's hard to find.
    http://www.sqlskills.com/blogs/glenn/using-alter-database-scoped-configuration-in-sql-server-2016/ 

    To quote the pertinent statement from the article:

    If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG.

    Heh... the good part about being wrong is that I learned something new today! ::) :blush:

    --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 - Sunday, March 26, 2017 10:48 AM

    Yeah... I was wrong.  They really do mean "Primary and Secondary DATABASES".  Here's a reference that's a bit more clear rather than leaving those of us that don't know "in a lurch for a search" for what they meant.  It's one of those things where if you don't know about it, it's hard to find.
    http://www.sqlskills.com/blogs/glenn/using-alter-database-scoped-configuration-in-sql-server-2016/ 

    To quote the pertinent statement from the article:

    If you are using AlwaysOn Availability Groups, the first four commands can also be used to affect the database-level configuration setting for ALL of the secondary database copies that are in a AG.

    Heh... the good part about being wrong is that I learned something new today! ::) :blush:

    Apologies for not responding to the thread sooner, but thank you for resonding to bretknoll.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

  • No problem.  And thank you!  Thanks to your question, I learned something new.

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

  • Ken Wymore - Tuesday, March 21, 2017 8:00 AM

    Cool option. Yet another reason to upgrade to 2016.

    Agreed. This is very beneficial.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • This is a very useful setting!

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

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

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