Determining optimal MAXDOP setting from TSQL in SQL Server 2005

  • [アーティクル]

For optimal performance of multi-processor installations, we recommend that the MAXDOP setting remain equal to the number of physical processors that are being used. For example, if the system is configured for two physical processors and four logical processors, MAXDOP should be set to 2. This is documented in the KB article:

 

https://support.microsoft.com/default.aspx/kb/322385

 

But there is no easy way to determine the number of physical processors in case of hyper-threaded CPUs for example from TSQL itself. Often you have to resort to using OS utilities or write small program using Win32 API  to determine the logical processors in a hyper-threaded configuration or look at the BIOS or processor type.

 

In SQL Server 2005, there are set of views and table-valued functions that fall under the umbrella of Dynamic Management Views which expose lot of information about SQL Server, memory structures, SQLOS information and so on. More details can be obtained from the "Dynamic Management Views and Functions " topic in Books Online:

 

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/cf893ecb-0bf6-4cbf-ac00-8a1099e405b1.htm

 

You can now derive the logical processors information from sys.dm_os_sys_info DMV in SQL Server 2005 easily. For example, the query below gives the optimal MAXDOP setting taking into account number of physical processors.
 
select case

         when cpu_count / hyperthread_ratio > 8 then 8

         else cpu_count / hyperthread_ratio

       end as optimal_maxdop_setting

from sys.dm_os_sys_info;

 

 This view also contains other interesting information like physical memory of the system, virtual memory available for processes, and number of schedulers. I will post more tips about the various interesting DMVs in SQL Server 2005. If you want to know about any particular area feel free to post a comment and I will post something on that.

 

[Modified: 20060517]

Recently, I came across a limitation in this system view. The hyperthread_ratio column is > 0 for the multi-core systems too. So there is no way to differentiate between a system with multi-core and hyperthreaded CPUs using the DMV. And since in the case of multi-core processor based systems, the MAXDOP value can be set to the number of CPUs the usefulness of the query is limited. The sure way to know the effect of MAXDOP setting is to test against your workload that involves parallel queries/operations.

Comments

  • Anonymous
    April 28, 2006
    We recently upgraded from SQL Server 2005 (from SQL Server 2000) and also simultaneously the hardware...

  • Anonymous
    April 30, 2006
    We recently upgraded from SQL Server 2000 to SQL 2005.  We simultaneously did a serious upgrade...

  • Anonymous
    May 17, 2006
    is there a similar MAXDOP recommendation for Dual Core Servers ?

  • Anonymous
    May 17, 2006
    For multi-core processors, you can set the MAXDOP value to the number of CPUs up to a maximum of 8. Note that the maximum of 8 is just a recommendation due to the limitation in the parallelism infrastructure (which can overwhelm system resources). The recommended defaults will change in the future and the best way to configure the setting is to start with these recommedations & adjust based on your SQL workload. --Umachandar

  • Anonymous
    May 29, 2007
    SYS.dm_os_sys_info DMV delivers most important information such as Operating System CPU tick count, hyperthread

  • Anonymous
    March 12, 2008
    Welcome to the Dynamics Ax Performance Team's blog. We're putting together a team introduction and hope

  • Anonymous
    July 23, 2008
    PingBack from http://coolhake.wordpress.com/2008/07/24/ax-database-configuration-checklist-part-1/

  • Anonymous
    November 28, 2008
    This is the first of a four-part series: Introduction to Query Parallelism (this post) Flipping the Bit

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2237154-performance-problems-when-running-trhough

  • Anonymous
    June 17, 2009
    PingBack from http://patioumbrellasource.info/story.php?id=343

  • Anonymous
    March 12, 2012
    The comment has been removed

  • Anonymous
    March 21, 2013
    I almost fell victim of this query, thing is my new system has both the hyperthread_ratio and cpu_count equal 4. The query returns 1 in this case, but if apply the MAXDOP value of 1 no query can run using the parallelism resulting in prolonged wait times with no blocking and a higher CPU utilization.