none
Max degree of parallelism setting

    Question

  • Hi,

    I have some servers in which max degree of parallelism set to 1 for below configured server

    Virtual processor count=16

    Logical CPU Count=16 and Physical CPU Count=4

    So my question is, this is valid to have  max degree of parallelism=1 or do I need to change the setting?

    Thanks.

    Thursday, December 20, 2012 5:20 AM

Answers

  • Hello,

    It is valid to set max degree to 1, but then for all queries only one (virtual) cpu will be used, even if SQL Server could paralleze the query to increase the performance.

    On the other hand, with this setting one query could never stress all cpu's, there will still be enough cpu's available for other queries.


    Olaf Helper

    Blog Xing

    Thursday, December 20, 2012 5:40 AM
    Moderator
  • Hi,

    I have some servers in which max degree of parallelism set to 1 for below configured server

    Virtual processor count=16

    Logical CPU Count=16 and Physical CPU Count=4

    So my question is, this is valid to have  max degree of parallelism=1 or do I need to change the setting?

    Thanks.

    Hallo Randive,

    if you have 16 cpu I'm wondering why this option has been set. Basically we run this option if we have multiple instances running on one machine (ie. Cluster or multi instance servers). As Olaf has mentioned this "may" decrease the performance of querries but from my point of view the problem is not the parallelism which powers down the performance but bad written queries and wrong set indexes.

    Basically I suppose that the dba who has set this option has been run into such a problem that ONE query was using all cpu and other requests could not be handled.

    Localize the bad queries and try to optimize them - would be better than setting MAXDOP as a GLOBAL setting.
    I like the good and simple explanation of pro and con from Pinal Dave.
    http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/

    In OLTP with small transaction set it to 1
    If it is a dwh set it higher but check previously the workloads


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Thursday, December 20, 2012 8:10 AM

All replies

  • Hello,

    It is valid to set max degree to 1, but then for all queries only one (virtual) cpu will be used, even if SQL Server could paralleze the query to increase the performance.

    On the other hand, with this setting one query could never stress all cpu's, there will still be enough cpu's available for other queries.


    Olaf Helper

    Blog Xing

    Thursday, December 20, 2012 5:40 AM
    Moderator
  • Also, if this SQL instance is hosting databases for applications like sharepoint then the MaxDoP 1 is the correct value. So it depends on the application on what is the right value for MaxDoP.
    Thursday, December 20, 2012 5:48 AM
  • Hi Randive09,

    There has been known issues related to parallel processing of queries, especially in SQL Server 2000 on hyper threaded processors. The suggestion was setting MAXDOP to number of physical processors. But parallelism has been improved significantly for SQL Server 2005 and next releases.

    I have no idea of what has been in mind of person who has changed this setting on your server. May be he/she has discovered such known issues. In order to not to waste your processors, it is good idea to observe parallelism on your server and if any problem was detected, then decide about limiting MAXDOP.

    If there's nobody available to tell you why this setting has changed, then reset MAXDOP, clear wait statistics using:

    DBCC SQLPERF ("sys.dm_os_wait_stats" , CLEAR)

    Let the server to operate for at least 24 hours. Then use this query to collect wait types:

    SELECT TOP 10
            wait_type ,
            max_wait_time_ms wait_time_ms ,
            signal_wait_time_ms ,
            wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
            100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS percent_total_waits ,
            100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( ) AS percent_total_signal_waits ,
            100.0 * ( wait_time_ms - signal_wait_time_ms ) / SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
    FROM    sys.dm_os_wait_stats
    WHERE   wait_time_ms > 0 -- remove zero wait_time
            AND wait_type NOT IN -- filter out additional irrelevant waits
    ( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH', 'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH', 'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX', 'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP', 'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS', 'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN', 'RESOURCE_QUEUE' )
    ORDER BY percent_total_waits DESC

    One of the symptoms of parallelism issues is increase of CXPACKET, SOS_SCHEDULER_YIELD and LATCH_* wait types.

    Thursday, December 20, 2012 5:51 AM
  • A good discussion is there in the below thread:

    A question about the "max degree of parallelism"

    Another suggestion from Brad McGehee is available here (pg. 83, 84, and 85)

    And also have a look at the Microsoft article: Recommendations and Guidelines for 'max degree of parallelism' configuration option


    Krishnakumar S

    Thursday, December 20, 2012 6:04 AM
  • Just adding to this good discussion, default value is 0. By making 1, you have suppressed parallel plan generation.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.

    Thursday, December 20, 2012 7:04 AM
  • Hi,

    I have some servers in which max degree of parallelism set to 1 for below configured server

    Virtual processor count=16

    Logical CPU Count=16 and Physical CPU Count=4

    So my question is, this is valid to have  max degree of parallelism=1 or do I need to change the setting?

    Thanks.

    Hallo Randive,

    if you have 16 cpu I'm wondering why this option has been set. Basically we run this option if we have multiple instances running on one machine (ie. Cluster or multi instance servers). As Olaf has mentioned this "may" decrease the performance of querries but from my point of view the problem is not the parallelism which powers down the performance but bad written queries and wrong set indexes.

    Basically I suppose that the dba who has set this option has been run into such a problem that ONE query was using all cpu and other requests could not be handled.

    Localize the bad queries and try to optimize them - would be better than setting MAXDOP as a GLOBAL setting.
    I like the good and simple explanation of pro and con from Pinal Dave.
    http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/

    In OLTP with small transaction set it to 1
    If it is a dwh set it higher but check previously the workloads


    Uwe Ricken

    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Thursday, December 20, 2012 8:10 AM
  • Thanks All, Its very much helpful.
    Thursday, December 20, 2012 3:52 PM