none
MAXDOP = 1 setting is causing longer run times and application timeouts. RRS feed

  • Question

  • MAXDOP = 1 setting is  causing longer run times and application timeouts. Could you please let me know the reason and how to fix it.

    Kiran

    Friday, November 22, 2019 1:43 PM

All replies

  • Yes.  Because you limited every query to single threaded.  That is the expected behavior when you set MAXDOP = 1.

    Friday, November 22, 2019 1:55 PM
    Moderator
  • suppose you have a query that takes 100 CPU-sec to run at DOP 1, and happens to run in 100 sec, i.e., no disk IO waits, no network waits, no waiting for free worker.
    Suppose further, that this execution plan has perfect scaling with parallelism. Hence,
    at DOP 2, it still takes 100 CPU-sec, but now using 2 threads, runs in 50 sec elapsed time
    at DOP 4, 100 CPU-sec, 25 sec elapsed time

    if your command timeout were 30 sec, then you need to run at DOP 4 to avoid time out, assuming otherwise ideal conditions.

    In the old days, default MAX DOP was 0, meaning if an execution plan were parallel, it would attempt to use all threads. So the system had a binary option of single thread or all threads. This was ok on systems with 4 cores, but a poor tactic at 64-core.

    In newer versions (2012?), soft-NUMA is automatic, in which the system is partitioned into several NUMA nodes, each having between 6-10 or so cores (total cores divided evenly), with MAXDOP at the number of cores in the soft-NUMA node. Behavior is still binary, at 1 thread, or X threads, this time X is a more reasonable number than all cores.

    This might be the end of this, but ...

    look into your parallel execution plan (set DOP to 2 or higher, preferable the soft-NUMA value)
    do you see a Bitmap operator?

    if so, that execution plan might run in less CPU-sec with the parallel plan than the single-threaded plan.
    Hence, I suggest not setting MAXDOP = 1. Choose either the default soft-NUMA value (6=10?) or at minimum, MAXDOP = 2


    jchang

    Friday, November 22, 2019 2:27 PM
  • Changing MAXDOP to any other value is producing deadlocks. 

    Kiran

    Friday, November 22, 2019 3:52 PM
  • Changing MAXDOP does not cause deadlocks.  Code causes deadlocks.

    You should investigate the actual deadlocks, not change MAXDOP.  This is not a fix for deadlocks.

    Friday, November 22, 2019 4:02 PM
    Moderator
  • Changing MAXDOP does not cause deadlocks.  Code causes deadlocks.

    Tom, Juniorkiran has started other threads where he experiences intra-query deadlocks. Such deadlocks can happen. And they are not precisely trivial to sort out.

    But I don't understand why Juniorkiran started a new thread with a short one-liner, rather than pursuing the older thread. As I recall, the last thing that happened in that thread was that I asked for query plans in XML format.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, November 22, 2019 10:52 PM
  • what is the setting on: cost threshold for parallelism" ?, abbreviated CTOP.
    the default setting of 5 is way too low for modern processors,
    somewhere between 20-50 is probably a good starting point

    I would look to see if there is a query plan called frequently that has plan cost somewhat over the CTOP setting and gets a parallel plan, and has CPU/worker time below 300ms. First, make sure this gets good indexes. It that does not bring it below CTOP, set CTOP just over the plan cost of that query


    jchang

    Saturday, November 23, 2019 1:35 PM
  • MAXDOP = 1 setting is  causing longer run times and application timeouts. Could you please let me know the reason and how to fix it.

    Try this if you can.

    • Set the system maxdop back up to a higher number, less than your number of cores but greater than 1.
    • On just that one query that deadlocks, use the OPTION (MAXDOP 1).

    This can be difficult if the deadlock code is generated by something like EF, then you might be able to use something like resource pools, etc.

    If you want advice as to the proper maxdop to use, please share how many cores you have.

    Josh

    Sunday, November 24, 2019 12:36 AM
  • Hi juniorkiran,

    >>MAXDOP = 1 setting is  causing longer run times and application timeouts. Could you please let me know the reason and how to fix it.

    When the value of MDOP is set to 1, it means that all sessions can only use only one CPU. But SQL server will want to use more than one CPU to support this session when the session will spend a long time. If the value of MDOP is set to 1, it will take longer time. For setting the value of MDOP, please refer to Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, November 25, 2019 8:16 AM