在设置新的sql Server时,我使用以下代码来确定MAXDOP设置的良好起点：
/* 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
我意识到这有点主观,可以根据很多事情而变化;但是我正在尝试创建一个紧凑的全部代码,用作新服务器的起点.
有没有人对此代码有任何意见？
解决方法
最好的方法是 – 使用coreinfo(sysinternals的实用程序),因为这会给你
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
现在,根据以上信息,理想MaxDop设置应计算为
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.
所以答案是 – “这取决于”您的处理器占用空间和NUMA配置,下表将总结我上面解释的内容：
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.
编辑：下面是一个快速而脏的Tsql脚本,用于为MAXDOP设置生成建议
/************************************************************************* 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
编辑：对于未来的访客,你可以看看test-dbamaxdop powershell功能(以及other extremely helpful DBA functions(全部免费!!).
