Managing Max Degree of Parallelism

  • Herve Roggero

    Ten Centuries

    Points: 1230

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hroggero/managingmaxdegreeofparallelism.asp

    Herve Roggero
    hroggero@pynlogic.com
    MCDBA, MCSE, MCSD
    SQL Server Database Proxy/Firewall and Auditing

  • Simon Sabin

    SSCrazy Eights

    Points: 8142

    MAXDOP well where to start.

    The first encounter of this was with SQL 7. a very badly written statment resulted in an almost infite loop due to the parallel processes not getting back together.

    My view is that if you have a well defined statement then the default works well. However if you are resulting in large rowsets in your query i.e two sub queries returning lots or rows that then have to be joined, then using MAXDOP is a serious consideration.

    When handling large sets of data within a query I have often seen SQL choose a parallel query plan that results in the query not completing.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • Herve Roggero

    Ten Centuries

    Points: 1230

    This is a very good point. This is where the hardware architecture makes a difference...

    For instance, on an ES7000, you could affinitize SQL Server to run on only 12 processors out of 16, and specify a MAX DOP of 4. However, there is no way to tell SQL Server which 4 processors are to be used for a given query. So SQL Server could pick 4 processors that are all on different sub-pods, or 4 processors that are all on the same sub-pod.

    You would get very different results since in the first case the hardware has to do a lot of 3rd-level cache synchronization across sub-pods, whereas in the second scenario it would be minimized, and hence reducing concurrency issues. What makes it worse is that this is highly unpredictable.

    Since you really never know exactly which processors are going to be used (because of the underlying hardware and the current CPU load), it is difficult to predict the performance outcome of any given statement, with or without the DOP implemented.

    Because of the hardware-dependent nature of parallel queries, it is important to look at the DOP from a macro standpoint. What is the "overall" impact of its implementation? This question is valid even if the DOP is implemented for a single query.

    I agree with you that you don't want to change the DOP unless necessary. SQL Server 2000 seems to implement the DOP in a very stable fashion. I know of a client that has successfully implemented this technique in production.

    All-in-all, it is difficult to talk about parallel queries without considering the hardware SQL Server is running on. The underlying hardware architecture can have a very dramatic impact on SQL Server's behavior.

    quote:


    MAXDOP well where to start.

    The first encounter of this was with SQL 7. a very badly written statment resulted in an almost infite loop due to the parallel processes not getting back together.

    My view is that if you have a well defined statement then the default works well. However if you are resulting in large rowsets in your query i.e two sub queries returning lots or rows that then have to be joined, then using MAXDOP is a serious consideration.

    When handling large sets of data within a query I have often seen SQL choose a parallel query plan that results in the query not completing.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Herve Roggero
    hroggero@pynlogic.com
    MCDBA, MCSE, MCSD
    SQL Server Database Proxy/Firewall and Auditing

  • Mark_Holst

    SSC-Addicted

    Points: 456

    I do have one question that I never resolved in my own mind - how does MAXDOP work with HyperThreaded CPU's?

    If, for instance, on a Dual-Xeon/P-IV system, SQL Server thinks it has 4 processors.  Each virtual processor isn't really an entire processor independant of one of the others in its own right, so increasing the load on one will reduce the available load on the 2nd within the pair.

    Does SQL Server have the smarts to get around this, by not allocating busy threads to the 2nd virtual CPU in a pair?  Or does it try to squeeze both through?  Are the internal algorithms tuned for Hyperthreading in some way?

    In regards to licensing, I'm aware that you don't need extra licenses for these Hyperthreaded chips.

  • frank garcia-214722

    Valued Member

    Points: 59

    I have seen MAXDOP work best when added as a hint within specific parallel queries after it being tested. 

    However, I have seen that most programmers recommend it in the interest of reducing deadlocks.  MAXDOP does not stop or alleviate deadlocks especially in a hybrid environment. 

    I have fought programmers on this suggestion and have proven my point.  Setting it at the server level may increase the performance of paralell queries (a handful) but however adversely affects all other processing.

    Thanks.

     

     

     

     

     

     

  • Paul Campbell-243089

    SSC Enthusiast

    Points: 113

    We have a system with 2 Dual Core Opterons 275's, has anyone had any experience using MAXDOP to increase performance with these processors?

  • George-73571

    SSC Enthusiast

    Points: 153

    Wow, this is an old thread..

    I do have a question though. In SQL 2005, can a value be passed to OPTION (MAXDOP n) with out creating dynamic sql code?

    EX: OPTION (MAXDOP @maxdopvalue)

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

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