• It won't work for every SQL Server instance out there but I tend to use MAXDOP as a bit of a poor-man's resource governor. In my eyes, no one process should get to use more than 1/4 of the total CPU usage and so usually start off by setting MAXDOP for 1/4th the number of CPUs.

    Of course, that's also being a bit lazy. Our daytime "load" is a whole lot different than our night time load and, on a system with only 12 CPUs, that would drive me to set MAXDOP to only 3, possibly 4 because we do have some heavy lifting processes that necessarily run (SLAs, etc) during the day. Fortunately, I don't have to worry about that too much because we have 32 processors and anything that needs more than 8 CPUs (1/4th of 32) deserves to run slow and needs to have its code repaired for performance.

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