Max Degree of Parallelism

The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs.

The value must be equal to the number of physical processors, not including hyperthreading.

One possible calculation formula is:

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;

 

Configure via Management Studio:

1. Open SQL Server 2005 Management Studio
2- Once the tool loads, navigate to the intended server in the Object Explorer and right click on server name
3. Select the 'Properties' option
4. Select the 'Advanced' page from the left navigation
5. Review the configurations for the 'Parallelism' section

Configure via T-SQL Script:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 8;

GO

RECONFIGURE WITH OVERRIDE;

GO