locked
Ambiguous KB articles on maxdop RRS feed

  • Question

  • KB articles 329204 and 2023536 describes the general guidelines on how to set maxdop.

    I think they are a bit ambiguous, as they use the words "processor", "CPU" and "physical processor".

    What is the difference between those words? Is a processor a core or could it be one multi-core processor in one socket?

    Should a system with one multi-core processor with 6 cores and no NUMA be configured with maxdop 6 or 1 according to these kb articles?

    Monday, April 4, 2011 9:33 AM

Answers

  • In my experience, any guidelines on setting the server-wide maximum degree of parallelism setting based purely on the number of cores should be taken with a large pinch of salt. Really you need to come up with a repeatable load test of a typical set of queries, and then submit them to your server with varying changes to the setting and record what the setting was when you got the best performance. You should also periodically review these tests as lots of things can have an affect on the optimal choice for this setting (i.e. query tuning, other hardware changes, consolidation of other databases on to the same instance and so on)

    However to answer your question, I would take "physical processor" and CPU to mean the same thing in the context of these articles which is the number of 'real' (i.e. not hyperthreaded) cores.

    So in your example you have 6 cores = 6 physical processors = 6 CPUs. For OLTP workloads I'd probably set MAXDOP at 4 and test from there.

     

    Ben

    • Proposed as answer by WeiLin Qiao Tuesday, April 5, 2011 2:31 AM
    • Marked as answer by Peter Wretmo Tuesday, April 5, 2011 5:38 AM
    Monday, April 4, 2011 12:14 PM
  • I agree with all that Ben said.

    As far as maxdop goes, processor = core, whether it's physical or logical (hyperthread).

    As everyone says, your mileage may vary, but my personal guidelines at this point are to pretty much never use 0 and never use 1.  I've seen some unfortunate behaviors when set to 1 even for OLTP loads, what had been a reasonable parallel plan for small transactions, turned really bad when single-threaded. 

    For six cores maxdop=4 lets a parallel plan run on four cores while keeping two available for really simple stuff that is naturally single-thread.  That sort of approach has worked best for me.

    Josh

     

    • Proposed as answer by WeiLin Qiao Tuesday, April 5, 2011 2:31 AM
    • Marked as answer by Peter Wretmo Tuesday, April 5, 2011 5:38 AM
    Monday, April 4, 2011 5:15 PM

All replies

  • In my experience, any guidelines on setting the server-wide maximum degree of parallelism setting based purely on the number of cores should be taken with a large pinch of salt. Really you need to come up with a repeatable load test of a typical set of queries, and then submit them to your server with varying changes to the setting and record what the setting was when you got the best performance. You should also periodically review these tests as lots of things can have an affect on the optimal choice for this setting (i.e. query tuning, other hardware changes, consolidation of other databases on to the same instance and so on)

    However to answer your question, I would take "physical processor" and CPU to mean the same thing in the context of these articles which is the number of 'real' (i.e. not hyperthreaded) cores.

    So in your example you have 6 cores = 6 physical processors = 6 CPUs. For OLTP workloads I'd probably set MAXDOP at 4 and test from there.

     

    Ben

    • Proposed as answer by WeiLin Qiao Tuesday, April 5, 2011 2:31 AM
    • Marked as answer by Peter Wretmo Tuesday, April 5, 2011 5:38 AM
    Monday, April 4, 2011 12:14 PM
  • Unless you are having an issue, the default setting of 0 should be ok.  Setting the value to any other value requires testing and tuning in your specific environment and your queries. 

    There is not a "one size fits all" answer to how to set the MaxDop.

     

    Monday, April 4, 2011 1:49 PM
  • Hi Pwretmo,

     

    MAXDOP setting control the number of CPUs being used to run a particular query.  A CPU is considered a 'core', so yes, it is a multi-core processor in 1 socket.  However, this setting shouldn't be played around with unless you've done some testing and have good data.  It's not common to set this otherwise.  I was able to get some performance for a client by setting this, but only after going through lots of data.  You might want to monitor for an abnormal growth of CXPACKETs after you do this.


    www.AliRazeghi.Com for blog www.RosonCo.Com for I.T. support and consulting Please help the MSDN community by marking answers as 'Helpful' and as 'answered'. This will allow others to quickly search for the same solution. Thank you.
    Monday, April 4, 2011 5:03 PM
  • I agree with all that Ben said.

    As far as maxdop goes, processor = core, whether it's physical or logical (hyperthread).

    As everyone says, your mileage may vary, but my personal guidelines at this point are to pretty much never use 0 and never use 1.  I've seen some unfortunate behaviors when set to 1 even for OLTP loads, what had been a reasonable parallel plan for small transactions, turned really bad when single-threaded. 

    For six cores maxdop=4 lets a parallel plan run on four cores while keeping two available for really simple stuff that is naturally single-thread.  That sort of approach has worked best for me.

    Josh

     

    • Proposed as answer by WeiLin Qiao Tuesday, April 5, 2011 2:31 AM
    • Marked as answer by Peter Wretmo Tuesday, April 5, 2011 5:38 AM
    Monday, April 4, 2011 5:15 PM
  • Thanks for your replies. I'm looking for a default general guideline for this setting to use as a starting point.

    Also thanks to the advice to leave some cores available, e.g. maxdop = 4 for a 6 core machine with no numa.

    Tuesday, April 5, 2011 5:37 AM
  • In my experience, any guidelines on setting the server-wide maximum degree of parallelism setting based purely on the number of cores should be taken with a large pinch of salt. Really you need to come up with a repeatable load test of a typical set of queries, and then submit them to your server with varying changes to the setting and record what the setting was when you got the best performance. You should also periodically review these tests as lots of things can have an affect on the optimal choice for this setting (i.e. query tuning, other hardware changes, consolidation of other databases on to the same instance and so on)

    However to answer your question, I would take "physical processor" and CPU to mean the same thing in the context of these articles which is the number of 'real' (i.e. not hyperthreaded) cores.

    So in your example you have 6 cores = 6 physical processors = 6 CPUs. For OLTP workloads I'd probably set MAXDOP at 4 and test from there.

    Ben


    I have to completely disagree with this answer based on field experience working with customers in NUMA architectures.  There isn't a 6 core processor out that isn't NUMA based, and the recommendations for NUMA configurations should be the default configuration for a best practice environment, which would be setting the max degree of parallelism to 6, the number of physical processor cores in a single NUMA node.  NUMA architectures are becoming more and more predominant in the workspace for SQL Server, and one of the most common misconfigurations that impacts performance is inappropriately setting the sp_configure option for 'max degree of parallelism'.  If your system wide level of DOP exceeds the number of physical cores in a single NUMA node, the result can be intranode operations being performed across the QPI which impacts throughput because you can have a worker producing outputs in one node that are being consumed by a worker from a different node leading to local vs foreign memory issues in the system.  The Nehalem QPI might be 6.4 GT/s but that doesn't make it faster than local specific processing.  I wouldn't drop below the number of cores in a individual NUMA node unless I was 110% certain that my processors couldn't handle my workload, because 99 times out of 100, it is going to be a different bottleneck in the system that will cause parallelism problems if the system has been configured properly up front. 

    If you engaged me for a health audit of your environment a DOP setting of 4 on a 6 core system would be an immediate cause of additional questions specifically to determine why you have decided to restrict a feature of the product back artificially.  There are certainly cases where reducing system wide DOP benefits the system as a whole, but a lot of times I see this being done, it is to cope with a non-CPU related bottleneck in the system, incorrect indexing leading to excessive parallel scan operations, or incorrect information on the internet.  It may be better for you to tune other aspects of the system like increasing the cost threshold or parallelism based on the costing of the queries that are being run against the system, or to change your indexing and database design to better support your workload.  Sometimes a single indexed view can alleviate parallelism problems by simplifying the query plan generated for queries.  A reduced DOP could impact query performance negatively and lead to longer run times which causes things to stack up in the system leading to other issues; it all depends.

    One of the best things Peter pointed out here is that you should test your workload under different configurations before deciding what is actually right.  The caveat to that is you have to test concurrent workloads against the system and not serial workloads by running a single set of tests in a single connection.  Concurrency is a big factor in the correct configuration of DOP because a 4 socket hexacore system will rock a large query that is run under DOP 0, but if you fire a bunch of connections up running that same query it will sink performance.


    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!

    Monday, April 11, 2011 2:52 AM
  • Jonathan, I'd think that the NUMA numbers would be good upper bounds, but considerations of workload in many cases would argue for smaller maxdop values.

    A single six-core processor would be treated like six single-core processors.  If a server was ever configured like that!

    Josh

     

    Monday, April 11, 2011 4:03 AM
  • Jonathan, I'd think that the NUMA numbers would be good upper bounds, but considerations of workload in many cases would argue for smaller maxdop values.

    A single six-core processor would be treated like six single-core processors.  If a server was ever configured like that!

    Josh

     


    Not necessarily, and not what was said in the answer marked on this thread.  This is a big case of "It Depends" and I see a lot of bad advice being given to people about reducing max degree of parallelism where it is just a symptom of a much bigger root problem.  A good example is any post on this forum where CXPACKET is the leading wait type on the server; without any kind of troubleshooting, it is very common for the responses to be to reduce max degree of parallelism, but the problem is often not parallelism at all if you actually get down into what is happening on the server.  I just two weeks ago worked with a client that approached me from my response on their forum thread saying that reducing max degree of parallelism was hiding the problem, and we got down to it, their biggest problem was implicit column side conversions forcing incredibly expensive clustered index scan's of their database, coupled with incorrect indexing of the tables.  A couple of really low risk changes to their database and application code resolved the problems in about 4 hours of time, and they didn't need to change max degree of parallelism at all, it wasn't the problem, it was actually saving them from bigger problems.

    Jonathan Kehayias | Senior Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Feel free to contact me through My Blog or Twitter
    Please click the Mark as Answer button if a post solves your problem!


    Monday, April 11, 2011 4:31 AM
  • Well sure, that's why all the tech support sessions start with having you power-cycle the system, then disconnect and reconnect all network cables.  But when it's appropriate to talk maxdop, then certain rules apply.

    Josh

     

    Monday, April 11, 2011 8:00 PM
  • Seven year old thread... But, the marked answer is an answer to the question on how to interpret the words "processor", "CPU" and "physical processor" in the stated Microsoft KB articles.

    +1 for elaborating on the maxdop guidelines!

    Thursday, October 18, 2018 11:34 AM