MAXDOP setting algorithm for SQL Server
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
EDIT: For future visitors, you can look at test-dbamaxdop powershell function (along with other extremely helpful DBA functions (ALL FREE !!).
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.
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.
SQL Server 2016 (13.x) SP2 and above, and all versions of SQL Server 2017 and above surface details about the physical core count per socket, the socket count, and the number of NUMA nodes, allowing a tidy way to determine the baseline MAXDOP setting for a new SQL Server installation.
For the versions mentioned above, this code will recommend a conservative MAXDOP setting of 75% of the number of physical cores in a NUMA node:
DECLARE @socket_count int;
DECLARE @cores_per_socket int;
DECLARE @numa_node_count int;
DECLARE @memory_model nvarchar(120);
DECLARE @hyperthread_ratio int;
SELECT @socket_count = dosi.socket_count
, @cores_per_socket = dosi.cores_per_socket
, @numa_node_count = dosi.numa_node_count
, @memory_model = dosi.sql_memory_model_desc
, @hyperthread_ratio = dosi.hyperthread_ratio
FROM sys.dm_os_sys_info dosi;
SELECT [Socket Count] = @socket_count
, [Cores Per Socket] = @cores_per_socket
, [Number of NUMA nodes] = @numa_node_count
, [Hyperthreading Enabled] = CASE WHEN @hyperthread_ratio > @cores_per_socket THEN 1 ELSE 0 END
, [Lock Pages in Memory granted?] = CASE WHEN @memory_model = N'CONVENTIONAL' THEN 0 ELSE 1 END;
DECLARE @MAXDOP int = @cores_per_socket;
SET @MAXDOP = @MAXDOP * 0.75;
IF @MAXDOP >= 8 SET @MAXDOP = 8;
SELECT [Recommended MAXDOP setting] = @MAXDOP
, [Command] = 'EXEC sys.sp_configure N''max degree of parallelism'', ' + CONVERT(nvarchar(10), @MAXDOP) + ';RECONFIGURE;';
For versions of SQL Server prior to SQL Server 2017 or SQL Server 2016 SP2, you cannot obtain the core-count-per-numa-node from sys.dm_os_sys_info. Instead, we can use PowerShell 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));