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;
   RECONFIGURE

   If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1 
   (URL wrapped for readability)
   http://blogs.msdn.com/b/rcormier/archive/2012/10/25/
   you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx

   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.
   http://support.microsoft.com/kb/899000
*/


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. */
BEGIN
    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));
END
ELSE
BEGIN
    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.';
END

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
4  
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

6 Answers 6

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 ....
select
    --- 8 or less processors and NO HT enabled
    case 
        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
1  
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
1  
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

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

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
1  
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

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

I also have stumbled about the article http://support.microsoft.com/kb/2806535 and cannot find the correlation to the scripts above.

Also I'm wondering, why there exists a differentiation for "@logicalCPUs >= 8 and @HTEnabled = 1 and @NoofNUMA = 1" and "@logicalCPUs >= 8 and @HTEnabled = 1 and @NoofNUMA > 1" as the result becomes the same.

After all I ended up writing my own piece of code matching the article from above, though even there I would have loved a more precise definition and/or differentiation about "processors" "CPU" and "physical processors".

Feel free to have your spin with it.

/*************************************************************************
Author          :   Dennis Winter (Thought: Adapted from a script from "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
declare @MaxDOP 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

IF @NoofNUMA > 1 AND @HTEnabled = 0
    SET @MaxDOP= @logicalCPUPerNuma 
ELSE IF  @NoofNUMA > 1 AND @HTEnabled = 1
    SET @MaxDOP=round( @NoofNUMA  / @physicalCPU *1.0,0)
ELSE IF @HTEnabled = 0
    SET @MaxDOP=@logicalCPUs
ELSE IF @HTEnabled = 1
    SET @MaxDOP=@physicalCPU

IF @MaxDOP > 10
    SET @MaxDOP=10
IF @MaxDOP = 0
    SET @MaxDOP=1

PRINT 'logicalCPUs : '         + CONVERT(VARCHAR, @logicalCPUs)
PRINT 'hyperthreadingRatio : ' + CONVERT(VARCHAR, @hyperthreadingRatio) 
PRINT 'physicalCPU : '         + CONVERT(VARCHAR, @physicalCPU) 
PRINT 'HTEnabled : '           + CONVERT(VARCHAR, @HTEnabled)
PRINT 'logicalCPUPerNuma : '   + CONVERT(VARCHAR, @logicalCPUPerNuma) 
PRINT 'NoOfNUMA : '            + CONVERT(VARCHAR, @NoOfNUMA)
PRINT '---------------------------'
Print 'MAXDOP setting should be : ' + CONVERT(VARCHAR, @MaxDOP)
share|improve this answer
    
+1 Nice piece of code. I'm not sure if you realize the hyperthread_ratio column in sys.dm_os_sys_info is misleading... on my workstation, for example, I have a single 4-core CPU with hyperthreading enabled - Task Manager sees 8 logical CPUs, and your code reports the hyperthreading ratio to be 1. –  Max Vernon Jun 12 at 11:54
    
As an FYI, my code produces a recommendation of 6 for this machine, which will leave 2 cores available even under the most stressful parallel queries. –  Max Vernon Jun 12 at 11:58
    
The hyperthread_ratio is indeed an issue, though cannot be solved better - at least not to my knowledge. See this Blog for some further details: sqlblog.com/blogs/kalen_delaney/archive/2007/12/08/… And about your second post - I would be nice to know, which value for "max degree of parallism" you have chosen for your machine. :-D Also I'm quite new in this topic - just stumbled over this just because I didn't knew before and needed this information. Thus what would be your conclusion, is the 2 cores still available a good thing or bad? –  Dennis Winter Jun 13 at 0:57

Looking at a post from the MSDN team, I've come up with a way to reliably get the physical core count from a machine, and use that to determine a good MAXDOP setting.

By "good", I mean conservative. That is, my requirement is to use a maximum of 75% of the cores in a NUMA node, or an overall maximum of 8 cores.

PowerShell is used to determine the physical core count:

powershell -OutputFormat Text -NoLogo -Command "& {Get-WmiObject -namespace 
"root\CIMV2" -class Win32_Processor -Property NumberOfCores} | select NumberOfCores"

One can also use PowerShell to determine the number of logical cores, which would likely be double the number of physical cores if HyperThreading is turned on:

powershell -OutputFormat Text -NoLogo -Command "& {Get-WmiObject -namespace 
"root\CIMV2" -class Win32_Processor -Property NumberOfCores} 
| select NumberOfLogicalProcessors"

The T-SQL:

/* 
   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;
   RECONFIGURE

   If this instance is hosting a Sharepoint database, you MUST specify MAXDOP=1 
   (URL wrapped for readability)
   http://blogs.msdn.com/b/rcormier/archive/2012/10/25/
   you-shall-configure-your-maxdop-when-using-sharepoint-2013.aspx

   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.
   http://support.microsoft.com/kb/899000
*/
SET NOCOUNT ON;

DECLARE @CoreCount int;
SET @CoreCount = 0;
DECLARE @NumaNodes int;

/*  see if xp_cmdshell is enabled, so we can try to use 
    PowerShell to determine the real core count
*/
DECLARE @T TABLE (
    name varchar(255)
    , minimum int
    , maximum int
    , config_value int
    , run_value int
);
INSERT INTO @T 
EXEC sp_configure 'xp_cmdshell';
DECLARE @cmdshellEnabled BIT;
SET @cmdshellEnabled = 0;
SELECT @cmdshellEnabled = 1 
FROM @T
WHERE run_value = 1;
IF @cmdshellEnabled = 1
BEGIN
    CREATE TABLE #cmdshell
    (
        txt VARCHAR(255)
    );
    INSERT INTO #cmdshell (txt)
    EXEC xp_cmdshell 'powershell -OutputFormat Text -NoLogo -Command "& {Get-WmiObject -namespace "root\CIMV2" -class Win32_Processor -Property NumberOfCores} | select NumberOfCores"';
    SELECT @CoreCount = CONVERT(INT, LTRIM(RTRIM(txt)))
    FROM #cmdshell
    WHERE ISNUMERIC(LTRIM(RTRIM(txt)))=1;
    DROP TABLE #cmdshell;
END
IF @CoreCount = 0 
BEGIN
    /* 
        Could not use PowerShell to get the corecount, use SQL Server's 
        unreliable number.  For machines with hyperthreading enabled
        this number is (typically) twice the physical core count.
    */
    SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i); 
END

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

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));
share|improve this answer
1  
Your script wont suggest accurate MAXDOP setting as I ran it on a 2 numa node machine with 2 sockets each having 8 logical processors and it suggested MAXDOP = 0 which is not quiet correct, it should suggest MAXDOP = 4 --> this is in-line with MS best practice suggestions 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. –  Kin Oct 7 at 20:44
    
So your machine has 2 sockets with 4 cores each, with Hyperthreading enabled? You're correct - 0 should not be suggested when there is more than 1 numa node, no matter how many cores there are. I will fix that. –  Max Vernon Oct 7 at 21:43
    
yes that is correct. It should suggest 4 in my case. –  Kin Oct 7 at 21:45
    
I've corrected the code to always suggest a MAXDOP setting. You may still not see it suggest 4; since the code is shrinking the MAXDOP by 25% and rounding-down to an even number, which may mean it will suggest a MAXDOP of 2 for your machine. –  Max Vernon Oct 8 at 13:43

Your Answer

 
discard

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.