SQL Server Performance

MAXDOP setting - performance improvement?

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Sep 4, 2011.

  1. Trev256b Member

    Hi - I've got a server with 8 CPUs and I've been advised to change the MAXDOP setting. I understand the default is MAXDOP=0. MSDN suggest the following: set MAXDOP=8 if I have 8 CPUs. Will changing this from MAXDOP=0 to MAXDOP=8 improve performance? If so how, as Microsoft have verbally recommended this but I can't see anywhere where it is documented as a performance improvement?
  2. preethi Member

    MAXDOP =0 means its dynamic. When the value is set to 0, you allow SQL Server to decide what should be the best degree of parallelism for each operation/query. I prefer it to be unchanged, unless you see some performance improvement by changing it. For an advanced setting like this, I will not trust MSDN or anyone, on the fly.

    For me the sure way to test the MAXDOP value is to test against the workload you have.

    As I remember some blogs have suggested this but they have an updated note at the end.
    http://blogs.msdn.com/b/sqltips/archive/2005/09/14/466387.aspx

    There are few bugs in the system, which may return incorrect value for identity column after an insert when parallel query processing is done.
    http://connect.microsoft.com/SQLSer...e-identity-sometimes-returns-incorrect-value#

    One more note to read: Applicable to all you read
    http://www.sqlskills.com/BLOGS/PAUL/post/Do-yourself-a-favor-Trust-No-One.aspx

    One more note: MAXDOP does not affect the number of processors SQL Server use. It is controlled by CPU affinity mask
  3. satya Moderator

    Cna you be more specific about who advised to change the MAXDOP settings?
    Have you tested the change of such configuration on the system (like to like)?
    You could use the query hint in this case ..r.ather than setting it server wide.
  4. MichaelB Member

    One other thing... In our data warehouse environment when we were on SQL 2005 enterprise we would have times during loads where it would hang on one core (one core would be at 100%) and the others would be dead low (at 0). when we played with the MAXDOP and set it to the number of PHYSICAL processors then it stopped bottlenecking. so for one proc we set MAXDOP at the code level and changed it back for the rest of the processing which is think is a good idea if you have some specific code that is not utilizing all cores. Again testing is critical. Once we switched to 2008 R2 it seems to work well without the tweak of the setting.
  5. preethi Member

    Interesting to know. Thanks for sharing.
  6. Trev256b Member

    hi satya - microsoft support suggested this.

    looks like opinion is divided - preethi likes setting 0, michaelb likes specific setting - but both agree on testing, however, i don't want to test on a live systeem that doesn't have a stress testing uat environemnt - any advise when opinions differ and nobody knows the best practise?
  7. preethi Member

    What I said is to go with 0 until you test and find a figure. Sorry for the confusion. Since Microsoft has suggested this, It is better to raise with them too.
  8. satya Moderator

    You will always get the POLITICAL responses in this case :), reason being its a big configuration setting which changes the query execution behaviour that may lead to total loss or gain on query performance.
    In any case every environment is independent to each other, which means I may get good response when I set the MAXDOP to 2 or 4, likewise for Michael or Preeti. In any case we had gone through the testing of this setting to see whether any performance degradation is happening.

    Further I have explained the reaons behind how it works in my book.. :) .. shameless plug to recommend to buy my book too.
  9. preethi Member

    Just to add to the confusion: I recently had a chat with a DBA. He sets the MAXDOP to 75% - 80% of the available processors or MAXDOP = (Number of processors -2). Leaving the balance for OS level operations. Seems like a solution for busy systems where you give the OS some breathing space.
  10. Luis Martin Moderator

    My experiences is MAXDOP = 1. But I always test.
  11. Trev256b Member

    ok guys - thanks for the input - after choosing - a number between 0 and 8 ;) - what signs of perfomance degradation could i see?
  12. preethi Member

    This may not be a complete list but a good starting point:
    1. Queries taking longer time than usual. This can happen as a frequent behavior or all of the sudden unexpected behavior. (the same query executed in less than 100 milliseconds, 2 mins back is taking more time suddenly while no obvious reason
    2. Less utilization of certain CPUs while certain others in full utilization (Unbalanced utilization)
    3. system hangs..
    4. High values for CXPACKET in dm_os_wait_stats or high signal_wait_time generally
  13. satya Moderator

    Again this depends upon the TEMPDB and type of hardware that is used...
  14. MichaelB Member

    Like Preethi, we had 16 cores and 1 was maxed and the others were lazy for most processing. once we started adjusting we got striping accross processors and duration times dropped.
  15. satya Moderator

    True, but in any case using the MAXDOP method must be tested to see there are no other compelling issues on the system.\
    It is again different case if you opted to use Resource Governor from 2008 onwards, better to resource the system usage.

Share This Page