none
A question about the "max degree of parallelism"

    Question

  • I've checked the page about "max degree of parallelism" on MSDN.com.It said that "The default value is 0, which uses the actual number of available CPUs".But now i have a quesiton,if i have a Sql server 2008 server that has four processors,how do i set the "max degree of parallelism"  with sp_configure? 4 or default? what the difference between  them?
    Tuesday, May 03, 2011 7:13 AM

Answers

  • It is always better to leave it as default as generally SQL Server is smart enough to automatically detect the level of parallelism required.

    If you want you can change it by deciding on what would be the best based on your testings, but depending on the changes in the system resources, workload, data etc you need to regularly revist the same.

    More read on Degree of Parallelism http://msdn.microsoft.com/en-us/library/ms188611.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    Tuesday, May 03, 2011 7:25 AM
  • First , SQL Server treats a logical processor as 1 procesor .So if you have 2 Quad cores , SQL Server will detect 8 . You can run select cpu_count from sys.dm_os_sys_info to find the CPUs the SQL Server is detecting .Now ,If SQL Server is the main applicatiion on the server then its OK to keep the default value but I would still suggest you to set the affinity mask so that even if SQL Server goes for parallelism ,it does so but makes sure it leaves some CPUs for other activities (assuming OS activities , even though OS is preemptive and there are thread priorities...not going in to that right now) ...

    But on servers where SQL server is not the only one ( or there are more instances or client application is hosted as well ) , I would recommend to use MAXDOP as well as Affinity mask ...

    you can also have  a look at http://support.microsoft.com/kb/2023536


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Tuesday, May 03, 2011 6:04 PM
  • On newer hardware systems, the default value for Max Degree of Parallelism is usually not the recommended best practice from Microsoft.  Newer systems use a NUMA architecture that divides the hardware resources into physical NUMA nodes that allow for better scalability of the system resources.  In NUMA systems, the recommended configuration for max degree of parallelism is the number of physical processor cores in a single NUMA node.  

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

    On Intel Nehalem processors, except older Dunnington line, this is the number of cores on a single die, older Xeon processors may or may not be NUMA configured, and for AMD it depends on the processor as the 12 core Magny-Cours line actually has two memory controllers on a single die and the core count per NUMA node is 6.

    Unless you have a specific performance problem, and the existence of CXPACKET waits in sys.dm_os_wait_stats is not a performance problem it is just a sign that you have queries using parallelism, I wouldn't recommend changing the value from the default, unless you are certain that you have a NUMA system and you know the number of cores in a single node.


    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!

    Wednesday, May 04, 2011 12:52 AM
    Moderator

All replies

  • Default , as it will use all available  cpu... You need to conduct depply testing to identify whether you will be benefit from disabling parallelism
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, May 03, 2011 7:18 AM
    Answerer
  • It is always better to leave it as default as generally SQL Server is smart enough to automatically detect the level of parallelism required.

    If you want you can change it by deciding on what would be the best based on your testings, but depending on the changes in the system resources, workload, data etc you need to regularly revist the same.

    More read on Degree of Parallelism http://msdn.microsoft.com/en-us/library/ms188611.aspx


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.wordpress.com
    Tuesday, May 03, 2011 7:25 AM
  • Typically the best practice is to run withe the default settings unless you have a good reason no to do so.

    It is possible that for some queries you may get better performance while disabling parraelisim and using a single CPU instead. For this queries you can use the hint OPTION (MAXDOP 1).

     

    In addition please take a look at this link and search for CXPACKET

    http://msdn.microsoft.com/en-us/library/ms179984.aspx

     


    -- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi
    Tuesday, May 03, 2011 8:14 AM
  • First , SQL Server treats a logical processor as 1 procesor .So if you have 2 Quad cores , SQL Server will detect 8 . You can run select cpu_count from sys.dm_os_sys_info to find the CPUs the SQL Server is detecting .Now ,If SQL Server is the main applicatiion on the server then its OK to keep the default value but I would still suggest you to set the affinity mask so that even if SQL Server goes for parallelism ,it does so but makes sure it leaves some CPUs for other activities (assuming OS activities , even though OS is preemptive and there are thread priorities...not going in to that right now) ...

    But on servers where SQL server is not the only one ( or there are more instances or client application is hosted as well ) , I would recommend to use MAXDOP as well as Affinity mask ...

    you can also have  a look at http://support.microsoft.com/kb/2023536


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Tuesday, May 03, 2011 6:04 PM
  • Ab,

     

    Actually its going to detect like 8 only if a phisycal machine, if your server is virtual, its goiing to detect only 1.

     

    The best is (normally) the default value (0), in another words, every query going to use every processor, but, i was a few days ago with a queue on my processor, so, i change from 4 to 2, the querys are now taking a little bit longer to complete, and my CPU is higher, but, no more queue, in my opnion, and the users one, its better.


    Oracle OCA11g, MCC 2011!
    Tuesday, May 03, 2011 8:23 PM
  • On newer hardware systems, the default value for Max Degree of Parallelism is usually not the recommended best practice from Microsoft.  Newer systems use a NUMA architecture that divides the hardware resources into physical NUMA nodes that allow for better scalability of the system resources.  In NUMA systems, the recommended configuration for max degree of parallelism is the number of physical processor cores in a single NUMA node.  

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

    On Intel Nehalem processors, except older Dunnington line, this is the number of cores on a single die, older Xeon processors may or may not be NUMA configured, and for AMD it depends on the processor as the 12 core Magny-Cours line actually has two memory controllers on a single die and the core count per NUMA node is 6.

    Unless you have a specific performance problem, and the existence of CXPACKET waits in sys.dm_os_wait_stats is not a performance problem it is just a sign that you have queries using parallelism, I wouldn't recommend changing the value from the default, unless you are certain that you have a NUMA system and you know the number of cores in a single node.


    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!

    Wednesday, May 04, 2011 12:52 AM
    Moderator
  • Thanks a lot Jonathan!!! That really helped.

    We just migrated our servers to SQL 2008 R2 two days ago. And i started seeing a consistent CPU spike sitting at 65%. I have changed the DOP after careful readings and watching the workloads on my system. Yes we have a NUMA enabled hardware with 8 CPUs hexa core..totalling to 48 CPUs. and 256 GB ram.

    As soon as i changed the MAX DOP to 1, boom it came down to 20%. And its been performing real good.  I'm going to keep monitoring to see if needs to be changed to 6 as per your theory above. We have six cores on each NUMA node. But now its all good...

    Thanks again, you made my day!!

    /M

     

    Monday, May 23, 2011 6:39 PM
  • I'd really like to hear your results when you try maxdop=6 as well.

    It depends a lot on the specifics of your code, but could be a lot better than maxdop=1.

    Thanks.

    Josh

     

    Monday, May 23, 2011 11:34 PM