设置新的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


我意识到这有点主观,并且可以根据许多因素而有所不同;但是我正在尝试创建一个紧凑的代码段,以用作新服务器的起点。 >

#1 楼

最好的方法是-使用coreinfo(sysinternals实用程序),因为这将为您提供
现在,根据上述信息,理想MaxDop设置应计算为
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

所以答案是-“这取决于您的处理器占用空间和NUMA配置,下表将总结我在上面解释的内容:
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.

编辑:下面是一个快速而肮脏的TSQL用于生成MAXDOP设置建议的脚本
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.


编辑:对于将来的访问者,您可以查看test-dbamaxdop powershell函数(以及其他非常有用的DBA函数(全部免费!)。

#2 楼

设置MAXDOP时,通常希望将其限制为NUMA节点中的内核数。这样,调度就不会尝试跨Numa节点访问内存。

#3 楼

在查看MSDN团队的帖子时,我想出了一种方法,可以可靠地从计算机获取物理核心计数,并使用该方法来确定良好的MAXDOP设置。 ,我的意思是保守的。也就是说,我的要求是在NUMA节点中最多使用75%的内核,或者总共最多使用8个内核。

SQL Server 2016(13.x)SP2及更高版本,以及所有版本的SQL Server 2017及更高版本,详细介绍了每个套接字的物理核心数,套接字数以及NUMA节点数,允许以整洁的方式确定新安装的SQL Server的基准MAXDOP设置。

对于上述版本,此代码将建议保守地将MAXD​​OP设置为75%的物理核心数。 NUMA节点:

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


对于SQL Server 2017或SQL Server 2016 SP2之前的SQL Server版本,您不能从以下位置获取核心数: sys.dm_os_sys_info。取而代之的是,我们可以使用PowerShell确定物理核心数:

如果打开了超线程,则物理核心的数量:

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


T-SQL:

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


#4 楼

通常,对于OLAP系统,请使用较高的DOP,而对于OLTP系统,请使用较低(或不使用)DOP。许多系统介于两者之间,因此请找到一个令人满意的介质,该介质允许偶尔的大工作量获得足够的CPU快速完成,而不会扼杀OLTP工作量。

此外,请谨慎使用cpu_count列来获得核心数量。如果启用了超线程,则此列似乎反映了公开的逻辑处理器的数量。一般来说,您不希望DOP高于物理核心的数量。在逻辑处理器上分散繁重的并行工作负载只会增加开销,而没有真正的好处。

还有一个hyperthread_ratio列,但我不确定它代表什么。该文档也不是很清楚。我在系统上看到的数字表明,它可能是整个系统中的物理核心数,或者是每个芯片的逻辑处理器数。文档声称我应该完全看到一个不同的数字。

评论


我相信hyperthread_ratio是每个处理器的逻辑核心数量。我前几天碰到了那个,如果我没记错的话,那就是我得出的结论。也许@AaronBertrand对此有更多信息。在验证之前,请不要将其视为一成不变的事实。

–托马斯·斯金格
13 Mar 13 '13 at 0:25



@ThomasStringer文档指出,从多台计算机上运行它的外观就是这样。但是,很难从那一列中得知是否确实启用了超线程。例如,在我的一台服务器上它报告8-该服务器有2个物理CPU,每个CPU上有4个内核,并且启用了超线程。在没有超线程的计算机上,在相同的情况下,它会报告4,但是如果没有重新启动(并关闭超线程),您将永远看不到这种变化!

– Max Vernon♦
13年3月13日在13:24

#5 楼

我也偶然发现了http://support.microsoft.com/kb/2806535的文章,却找不到与上述脚本的关联。由于结果变为相同,因此“ @logicalCPUs> = 8且@HTEnabled = 1且@NoofNUMA = 1”和“ @logicalCPUs> = 8且@HTEnabled = 1且@NoofNUMA> 1”。

毕竟,我还是写了自己的一段代码,与上面的文章匹配,尽管即使在那儿,我也希望对“处理器”,“ CPU”和“物理处理器”进行更精确的定义和/或区分。

随意旋转。

/*************************************************************************
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)


评论


不错的代码。我不确定您是否意识到sys.dm_os_sys_info中的hyperthread_ratio列是否会引起误解...例如,在我的工作站上,我有一个启用了超线程的4核CPU-任务管理器看到8个逻辑CPU,并且您的代码报告超线程比率为1。

– Max Vernon♦
2014年6月12日11:54



作为参考,我的代码为此机器推荐了6个建议,即使在压力最大的并行查询下也将保留2个内核。

– Max Vernon♦
2014年6月12日上午11:58

尽管无法更好地解决-至少就我所知,hyperthread_ratio确实是一个问题。有关更多详细信息,请参见此博客:sqlblog.com/blogs/kalen_delaney/archive/2007/12/08 / ...关于您的第二篇文章,我很高兴知道,您选择了“最大平行度”的哪个值为您的机器。 :-D在这个话题上我也很陌生-只是因为我以前不知道并且需要这些信息而迷失了方向。因此,您的结论是,仍然可以使用2个内核是好事还是坏事?

–丹尼斯·温特
2014年6月13日,0:57



#6 楼

此版本为您提供了一个具有现有MAXDOP设置的不错的单一结果集,并且可以在不需要使用xp_cmdshell的情况下支持SQL 2008-2017版本。

select
[ServerName]                    = @@SERVERNAME
, [ComputerName]                = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
, [LogicalCPUs]             
, hyperthread_ratio 
, [PhysicalCPU]             
, [HTEnabled]               
, LogicalCPUPerNuma
, [NoOfNUMA]
, [MaxDop_Recommended]          = convert(int,case when [MaxDop_RAW] > 10 then 10 else [MaxDop_RAW] end)
, [MaxDop_Current]              = sc.value
, [MaxDop_RAW]
, [Number of Cores] 
from
(
select
     [LogicalCPUs]              
    , hyperthread_ratio 
    , [PhysicalCPU]             
    , [HTEnabled]               
    , LogicalCPUPerNuma
    , [NoOfNUMA]
    , [Number of Cores] 
    , [MaxDop_RAW]              = 
        case
            when [NoOfNUMA] > 1 AND HTEnabled = 0 then logicalCPUPerNuma 
            when [NoOfNUMA] > 1 AND HTEnabled = 1 then convert(decimal(9,4),[NoOfNUMA]/ convert(decimal(9,4),Res_MAXDOP.PhysicalCPU) * convert(decimal(9,4),1))
            when HTEnabled = 0 then  Res_MAXDOP.LogicalCPUs
            when HTEnabled = 1 then  Res_MAXDOP.PhysicalCPU
        end
from
(
    select
         [LogicalCPUs]              = osi.cpu_count
        , osi.hyperthread_ratio 
        , [PhysicalCPU]             = osi.cpu_count/osi.hyperthread_ratio
        , [HTEnabled]               = case when osi.cpu_count > osi.hyperthread_ratio then 1 else 0 end
        , LogicalCPUPerNuma
        , [NoOfNUMA]
        , [Number of Cores] 
    from 
    (
        select
            [NoOfNUMA]  = count(res.parent_node_id)
            ,[Number of Cores]  = res.LogicalCPUPerNuma/count(res.parent_node_id)
            ,res.LogicalCPUPerNuma
        from
        (
            Select
                s.parent_node_id
                ,LogicalCPUPerNuma  = count(1)
            from
                sys.dm_os_schedulers s
            where
                s.parent_node_id < 64
                and
                s.status = 'VISIBLE ONLINE'
            group by 
                s.parent_node_id
        ) Res
        group by
            res.LogicalCPUPerNuma
    ) Res_NUMA
    cross apply sys.dm_os_sys_info osi
) Res_MAXDOP
)Res_Final
cross apply sys.sysconfigures sc
where sc.comment = 'maximum degree of parallelism'
option (recompile);


#7 楼

脚本不错,但kb文章:http://support.microsoft.com/kb/2806535并不完全符合您的代码。我缺少什么?

服务器1
HTEnabled:1
超线程比率:12
逻辑cpus:24 逻辑cpus per numa:12
NoOfNuma:2
MaxDop设置应为:6
服务器2
HTEnabled:2
hyperthreadingRatio:16
逻辑cpus:64
物理cpus:4
每个numa逻辑cpus:16
NoOfNuma:4
MaxDop设置应为:4

我意识到这些只是建议;但是在我看来,上面的服务器(#2)具有4个处理器而不是2个处理器,每个物理CPU 8个核心而不是6个内核是不对的;建议将MAXD​​OP设置为4,而对于功能较弱的服务器则建议使用6。

上面的kbb文章提出了我上面的8个方案。 “对于已配置NUMA并启用了超线程的服务器,MAXDOP值不应超过每个NUMA节点的物理处理器数。”

评论


如果将MAXD​​OP设置为高于内核数/数字节点数,则最终将导致对远内存的调用,其调用速度比近内存调用慢许多倍。这是因为每个numa节点都有其自己的内存;使查询使用的线程多于单个numa模式中存在的线程,将使CPU负载分布在多个内核上,因此会在多个内存节点上进行分配。

– Max Vernon♦
13年10月4日在16:08

我建议将MAXD​​OP设置为对服务器运行负载有意义的设置。只有您可以为您的特定负载确定最佳设置。这篇文章仅供参考。

– Max Vernon♦
13年10月4日在16:10

#8 楼

在安装SQL Server 2019 CTP 3.0的过程中,有一个新的选项卡MaxDOP。实际值是预定义的(在以前的版本中默认值为0)。

在SQL Server 2019安装过程中设置MAXDOP



图像源: https://www.brentozar.com/wp-content/uploads/2019/05/SQL_Server_2019_Setup.png