Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting:

   This will recommend a MAXDOP setting appropriate for your machine's NUMA memory
   configuration.  You will need to evaluate this setting in a non-production 
   environment before moving it to production.

   MAXDOP can be configured using:  
   EXEC sp_configure 'max degree of parallelism',X;

   If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1 
   (URL wrapped for readability)

   Biztalk (all versions, including 2010): 
   MAXDOP = 1 is only required on the BizTalk Message Box
   database server(s), and must not be changed; all other servers hosting other 
   BizTalk Server databases may return this value to 0 if set.

DECLARE @CoreCount int;
DECLARE @NumaNodes int;

SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1 
    FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64

IF @CoreCount > 4 /* If less than 5 cores, don't bother. */
    DECLARE @MaxDOP int;

    /* 3/4 of Total Cores in Machine */
    SET @MaxDOP = @CoreCount * 0.75; 

    /* if @MaxDOP is greater than the per NUMA node
       Core Count, set @MaxDOP = per NUMA node core count
    IF @MaxDOP > (@CoreCount / @NumaNodes) 
        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;

        Reduce @MaxDOP to an even number 
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);

    /* Cap MAXDOP at 8, according to Microsoft */
    IF @MaxDOP > 8 SET @MaxDOP = 8;

    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));
    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';
    PRINT 'This is the default setting, you likely do not need to do';
    PRINT 'anything.';

I realize this is a bit subjective, and can vary based on many things; however I'm attempting to create a tight catch-all piece of code to use as a starting point for a new server.

Does anyone have any input on this code?

share|improve this question
Make sure the new instances are not for SharePoint(maxdop=1 is recommended) –  DaniSQL Mar 12 '13 at 20:19
Good point - I shall add that to the code (as a comment). –  Max Vernon Mar 12 '13 at 20:23
So, on a Server with 80 schedulers across 4 numa nodes, your script suggests MAXDOP=20? 8 would be a better recommendation without knowing anything about the workload. –  StrayCatDBA Mar 12 '13 at 21:00
In the past, I've based my MAXDOP settings off the information in this KB article from Microsoft. It's been the most comprehensive explanation I've seen so far. –  Mike Fal Mar 12 '13 at 21:22
Biztalk used to have a maxdop 1 requirement also I believe. Not sure if it applies to recent versions. –  Mark Storey-Smith Mar 13 '13 at 1:32
show 1 more comment

4 Answers

When setting MAXDOP you typically want to limit it to the number of cores in a NUMA node. That way schedules aren't trying to access memory across numa nodes.

share|improve this answer
Good idea. I know SQL Server is NUMA-aware; will it try to keep parallel execution within a single node? –  db2 Mar 13 '13 at 12:03
Yes, SQL Server will run all threads on a single NUMA core if the MAXDOP <= [# Cores per Numa Node]. If MAXDOP > [# Cores per Numa Node] and the query optimizer picks a parallel plan, it will use as many cores as the MAXDOP setting says to use. –  Max Vernon Mar 13 '13 at 13:26
add comment

As a general rule, use higher DOP for an OLAP system, and lower (or no) DOP for an OLTP system. Many systems are somewhere in between, so find a happy medium that allows the occasional large workload to get enough CPU to complete quickly, without strangling your OLTP workloads.

Also, be careful about using the cpu_count column to get a core count. If hyperthreading is enabled, this column seems to reflect the number of logical processors exposed. Generally speaking, you don't want DOP to be higher than the number of physical cores. Spreading a heavy parallel workload across logical processors will just increase overhead with no real benefit.

There's also a hyperthread_ratio column, but I'm not certain what it represents. The documentation isn't very clear either. The number I see on our system suggests it could either be the number of physical cores in the entire system, or the number of logical processors per chip. The documentation claims I should be seeing a different figure entirely.

share|improve this answer
I guess the OLAP vs OLTP question boils down to the degree of concurrency expected. If there is a high degree of concurrency (OLTP) then the system will benefit from a low MAXDOP since more cores are available. Many of our servers are mixed load, which is why I tend towards a smaller number. –  Max Vernon Mar 12 '13 at 20:34
Yup, that's the big consideration: lots of small queries vs. a few really heavy queries. –  db2 Mar 12 '13 at 20:37
I believe the hyperthread_ratio is the amount of logical cores per processor. I ran into that a little while back and if I'm remember correctly that is the conclusion I came to. Maybe @AaronBertrand has more info on that. Don't take that as a hard and fast fact yet before verification. –  Thomas Stringer Mar 13 '13 at 0:25
@ThomasStringer the documentation states that, and from running it on multiple machines, that is what it looks like. However, it is pretty difficult to tell from that column whether hyperthreading is actually enabled or not. For instance, on one of my servers it reports 8 - the server has 2 physical CPUs, with 4 cores on each CPU, with hyperthreading enabled. On machines without hyperthreading it reports 4 under the same circumstances, but without rebooting (and turning hyperthreading off), you'd never see that change! –  Max Vernon Mar 13 '13 at 13:24
add comment

The best way to do is -- use coreinfo (utility by sysinternals) as this will give you

a. Logical to Physical Processor Map
b. Logical Processor to Socket Map
c. Logical Processor to NUMA Node Map as below :

Logical to Physical Processor Map:
**----------------------  Physical Processor 0 (Hyperthreaded)
--**--------------------  Physical Processor 1 (Hyperthreaded)
----**------------------  Physical Processor 2 (Hyperthreaded)
------**----------------  Physical Processor 3 (Hyperthreaded)
--------**--------------  Physical Processor 4 (Hyperthreaded)
----------**------------  Physical Processor 5 (Hyperthreaded)
------------**----------  Physical Processor 6 (Hyperthreaded)
--------------**--------  Physical Processor 7 (Hyperthreaded)
----------------**------  Physical Processor 8 (Hyperthreaded)
------------------**----  Physical Processor 9 (Hyperthreaded)
--------------------**--  Physical Processor 10 (Hyperthreaded)
----------------------**  Physical Processor 11 (Hyperthreaded)

Logical Processor to Socket Map:
************------------  Socket 0
------------************  Socket 1

Logical Processor to NUMA Node Map:
************------------  NUMA Node 0
------------************  NUMA Node 1

Now, based on the above info, the Ideal MaxDop setting should be calculated as

a.  It has 12 CPU’s which are hyper threaded giving us 24 CPUs.
b.  It has 2 NUMA node [Node 0 and 1] each having 12 CPU’s with Hyperthreading ON.
c.  Number of sockets are 2 [socket 0 and 1] which are housing 12 CPU’s each.

Considering all above factors, the max degree of Parallelism should be set to 6 which is ideal value for server with above configuration.

So the answer is -- "it depends" on your processor footprint and the NUMA configuration and below table will summarize what I explained above:

8 or less processors    ===> 0 to N (where N= no. of processors)
More than 8 processors  ===> 8
NUMA configured         ===> MAXDOP should not exceed no of CPU’s assigned to each 
                                 NUMA node with max value capped to 8
Hyper threading Enabled ===> Should not exceed the number of physical processors.

Edited: Below is a quick and dirty TSQL script to generate Recommendation for MAXDOP setting

Author          :   Kin Shah
Purpose         :   Recommend MaxDop settings for the server instance
Tested RDBMS    :   SQL Server 2008R2

declare @hyperthreadingRatio bit
declare @logicalCPUs int
declare @HTEnabled int
declare @physicalCPU int
declare @SOCKET int
declare @logicalCPUPerNuma int
declare @NoOfNUMA int

select @logicalCPUs = cpu_count -- [Logical CPU Count]
    ,@hyperthreadingRatio = hyperthread_ratio --  [Hyperthread Ratio]
    ,@physicalCPU = cpu_count / hyperthread_ratio -- [Physical CPU Count]
    ,@HTEnabled = case 
        when cpu_count > hyperthread_ratio
            then 1
        else 0
        end -- HTEnabled
from sys.dm_os_sys_info
option (recompile);

select @logicalCPUPerNuma = COUNT(parent_node_id) -- [NumberOfLogicalProcessorsPerNuma]
from sys.dm_os_schedulers
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64
group by parent_node_id
option (recompile);

select @NoOfNUMA = count(distinct parent_node_id)
from sys.dm_os_schedulers -- find NO OF NUMA Nodes 
where [status] = 'VISIBLE ONLINE'
    and parent_node_id < 64

-- Report the recommendations ....
    --- 8 or less processors and NO HT enabled
        when @logicalCPUs < 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : ' + CAST(@logicalCPUs as varchar(3))
                --- 8 or more processors and NO HT enabled
        when @logicalCPUs >= 8
            and @HTEnabled = 0
            then 'MAXDOP setting should be : 8'
                --- 8 or more processors and HT enabled and NO NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA = 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
                --- 8 or more processors and HT enabled and NUMA
        when @logicalCPUs >= 8
            and @HTEnabled = 1
            and @NoofNUMA > 1
            then 'MaxDop setting should be : ' + CAST(@logicalCPUPerNuma / @physicalCPU as varchar(3))
        else ''
        end as Recommendations
share|improve this answer
Nice one suggesting SysInternals tools. I would really like a way to get this info inside SQL Server ideally through a T-SQL query, without having to go through xp_cmdshell. –  Max Vernon Mar 13 '13 at 14:24
Unfortunately, select cpu_count , hyperthread_ratio, case when cpu_count > hyperthread_ratio then 1 else 0 end FROM sys.dm_os_sys_info does not indicate if Hyperthreading is enabled. On a machine with a single CPU, cpu_count and hyperthread_ratio are the same, and I have Hyperthreading turned on. –  Max Vernon Mar 13 '13 at 20:58
Other than that, nice work! –  Max Vernon Mar 13 '13 at 20:58
Thanks Max ! Unfortunately there is no other way using TSQL to accurately tell if HT is enabled or not. This is the best guess you can make. As I suggested coreinfo, processexplorer, etc tools can be useful for finding it out. –  Kin Mar 13 '13 at 21:16
add comment

Nice script, but the kb article: http://support.microsoft.com/kb/2806535 doesn't completely jive with your code. What am I missing?

Server 1
HTEnabled: 1
hyperthreadingRatio: 12
logical cpus: 24
physical cpus: 2
logical cpus per numa: 12
NoOfNuma: 2
MaxDop setting should be : 6

Server 2
HTEnabled: 2
hyperthreadingRatio: 16
logical cpus: 64
physical cpus: 4
logical cpus per numa: 16
NoOfNuma: 4
MaxDop setting should be : 4

I realize these are just suggestions; but something doesn't seem right to me that a server (#2) above with 4 processors instead of 2, and 8 cores per physical CPU instead of 6; would recommend the MAXDOP at 4, versus 6 for the less powerful server.

The kbb article above suggests 8 my scenario above. "For servers that have NUMA configured and hyperthreading enabled, the MAXDOP value should not exceed number of physical processors per NUMA node."

Bob McC

share|improve this answer
If you set MAXDOP higher than the number of cores/numa node, you end up with calls into far memory which are many many times slower than calling near memory. This is because each numa node has its own memory; having a query use more threads than are present in a single numa mode will spread the CPU load over multiple cores, and therefore multiple memory nodes. –  Max Vernon Oct 4 '13 at 16:08
I recommend setting MAXDOP to a setting that makes sense for your server running your load. Only you can determine the best setting for your particular load; this post is only a guideline. –  Max Vernon Oct 4 '13 at 16:10
@BobMcC refer to my answer for explanation. –  Kin Oct 4 '13 at 16:25
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.