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:

 


 

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 (12)

  1. We recently upgraded from SQL Server 2005 (from SQL Server 2000) and also simultaneously the hardware…

  2. We recently upgraded from SQL Server 2000 to SQL 2005.  We simultaneously did a serious upgrade…

  3. Andy Ball says:

    is there a similar MAXDOP recommendation for Dual Core Servers ?

  4. sqletips says:
    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
  5. SYS.dm_os_sys_info DMV delivers most important information such as Operating System CPU tick count, hyperthread

  6. Welcome to the Dynamics Ax Performance Team’s blog. We’re putting together a team introduction and hope

  7. This is the first of a four-part series: Introduction to Query Parallelism (this post) Flipping the Bit

  8. Pingback from  Troubleshoot high CPU issue without using profile traces « SQL Server Blogs

  9. ArthurZ says:

    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.