I came across a recent article by Marnix Wolf and S.Carrilho regarding a little known SQL setting known as Max Degree of Parallelism (MDoP)

When SQL Server runs on a computer with more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

This becomes an issue on servers with hyper-thread enabled processors, as by the nature of hyper-threading the system thinks that there are more cores available then there physically are.

This setting can be found under SQL Server advanced properties:

In order to calculate the recommended value for this setting we need to use the MDoP calulator which makes use of two queries:

1. Output of following query from the SQL Server instance: 

SELECT COUNT(DISTINCT memory_node_id) AS NUMA_Nodes FROM sys.dm_os_memory_clerks WHERE memory_node_id!=64

2. Launch Powershell and get the output of following PS command:
Get-WmiObject -namespace “root\CIMV2” -class Win32_Processor -Property NumberOfCores | select NumberOfCores

3. Input these value into the calculator:

In this example the SQL query returned a value of 1 and the PS returned a count of 4 cores, the calculator recommends an MDoP setting of 4.

I’ve had situations in the past where no amount of tweaking seemed to improve console performance and this certainly a factor I will be taking into account in the future. Definitely give Marnixs’ article a read as he covers the topic in more detail with additional findings from his field experience.

There are other ways to improve console performance, I will combine them together in a future blog post about complete console tuning.