Post reply

Changing Max Degree of Parallism

  • January 7, 2013 at 7:54 am

    #268469

    Hi,

    How do you explain to someone, what is the best way to set MDOP. I am reading the below suggestion from Microsoft.

    http://support.microsoft.com/kb/2023536

    Application team is asking to change the MDOP to 1, this particular server has 16 CPUs, so, I am not sure 1 is the best way to go about it.

    Their finding was since the “ Max Degree of Parallism” is set to 0 (zero), it is causing a lot of waits in the system and hence reducing the throughput of the quereies which run in the system. We would like this to be changed to 1.

    What would you recommened?

    SueTons.

    Regards,
    SQLisAwe5oMe.

  • January 7, 2013 at 7:58 am

    #1574321

    Don't set it to 1 unless you want to hobble your server. SQL uses parallelism to run larger queries fast. If you would prefer that larger queries run significantly slower than they currently do, then sure, set maxdop to 1.

    CX Packet wait may not be a cause for concern. It does not indicate there's a problem, it indicates that queries are running in parallel, nothing more.

    http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapter 1, chapter 3 at the very least

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2013 at 9:30 am

    #1574390

    Ok...Thanks Gail.

    SueTons.

    Regards,
    SQLisAwe5oMe.

Viewing 3 posts - 1 through 2 (of 2 total)

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