Sql-Server
SQL Server 的 MAXDOP 設置算法
在設置新的 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現在，根據上述資訊，Ideal 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 函式（以及其他非常有用的 DBA 函式（全部免費！！）。