How to solve MAXDOP setting algorithm for SQL Server?
When setting up a new SQL Server, 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> 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?
The best way to do sql server maxdop 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 xss=removed status] = 'VISIBLE ONLINE'>