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.
