Алгоритм настройки MAXDOP для SQL Server

При настройке нового 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

Я понимаю, что это немного субъективно и может варьироваться в зависимости от многих вещей; однако я пытаюсь создать жесткий фрагмент кода для использования в качестве отправной точки для нового сервера.

Кто-нибудь имеет какие-либо данные в этом коде?

58 голосов | спросил Max Vernon 12 MarpmTue, 12 Mar 2013 23:59:37 +04002013-03-12T23:59:37+04:0011 2013, 23:59:37

7 ответов


41

Лучший способ сделать это - использовать 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

EDIT: для будущих посетителей вы можете посмотреть test-dbamaxdop powershell (наряду с другими чрезвычайно полезными функциями DBA (ВСЕ БЕСПЛАТНО!).

ответил Kin 13 MarpmWed, 13 Mar 2013 18:18:48 +04002013-03-13T18:18:48+04:0006 2013, 18:18:48
16

При настройке MAXDOP вы обычно хотите ограничить его количеством ядер в узле NUMA. Таким образом, расписания не пытаются получить доступ к памяти через узлы numa.

ответил mrdenny 13 MaramWed, 13 Mar 2013 04:48:49 +04002013-03-13T04:48:49+04:0004 2013, 04:48:49
11

Глядя на сообщение из команды MSDN , Я придумал способ надежно получить счет физического ядра с компьютера и использовать его для определения хорошего параметра MAXDOP.

Под «хорошим» я подразумеваю консервативный. То есть, мое требование состоит в том, чтобы использовать максимум 75% ядер в узле NUMA или общий максимум 8 ядер.

SQL Server 2016 (13.x) с пакетом обновления 2 (SP2) и выше, а также все версии данных SQL Server 2017 и выше о количестве физического ядра для каждого сокета, количестве сокетов и количестве узлов NUMA, что позволяет определите базовый параметр MAXDOP для новой установки SQL Server.

В версиях, упомянутых выше, этот код будет рекомендовать консервативный параметр MAXDOP, равный 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 до SQL Server 2017 или SQL Server 2016 SP2 вы не можете получить узел core-count-per-numa из sys.dm_os_sys_info. Вместо этого мы можем использовать PowerShell для определения физического ядра:

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

Можно также использовать PowerShell для определения количества логических ядер, которые, вероятно, будут удвоены по количеству физических ядер при включении HyperThreading:

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

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));
ответил Max Vernon 8 +04002014-10-08T00:16:47+04:00312014bEurope/MoscowWed, 08 Oct 2014 00:16:47 +0400 2014, 00:16:47
10

Как правило, используйте более высокую DOP для OLAP-системы и более низкую (или нет) DOP для OLTP-системы. Многие системы находятся где-то посередине, поэтому найдите счастливую среду, которая позволяет случайным крупным рабочим нагрузкам получить достаточный объем процессора, чтобы быстро завершить работу, не задушив ваши рабочие нагрузки OLTP.

Кроме того, будьте осторожны с использованием столбца cpu_count, чтобы получить подсчет ядра. Если включена гиперпоточность, этот столбец, по-видимому, отражает количество выставленных логических процессоров. Вообще говоря, вы не хотите, чтобы DOP превышала число физических ядер. Распространение тяжелой параллельной рабочей нагрузки на логические процессоры просто увеличит накладные расходы без реальной выгоды.

Также есть столбец hyperthread_ratio, но я не уверен, что он представляет. Документация также не очень ясна. Число, которое я вижу в нашей системе, предполагает, что это может быть либо количество физических ядер во всей системе, либо количество логических процессоров на микросхему. Документация утверждает, что я должен видеть совсем другую фигуру.

ответил db2 13 MaramWed, 13 Mar 2013 00:31:28 +04002013-03-13T00:31:28+04:0012 2013, 00:31:28
6

Я также наткнулся на статью 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 @[email protected]
ELSE IF @HTEnabled = 1
    SET @[email protected]

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)
ответил Dennis Winter 12 J0000006Europe/Moscow 2014, 12:14:48
3

Хороший скрипт, но статья kb: http://support.microsoft.com/kb/2806535 не полностью jive с вашим кодом. Что мне не хватает?

Сервер 1
HTEnabled: 1
hyperthreadingRatio: 12
логический cpus: 24
физический cpus: 2
логический cpus per numa: 12
NoOfNuma: 2
Настройка MaxDop должна быть: 6

Сервер 2
HTEnabled: 2
hyperthreadingRatio: 16
логический cpus: 64
физический cpus: 4
логический cpus per numa: 16
NoOfNuma: 4
Настройка MaxDop должна быть: 4

Я понимаю, что это только предложения; но мне не кажется, что сервер (# 2) выше с 4 процессорами вместо 2 и 8 ядер на физический процессор вместо 6; будет рекомендовать MAXDOP на 4, против 6 для менее мощного сервера.

В приведенной выше статье kbb предлагается 8 мой сценарий выше. «Для серверов с включенной настройкой NUMA и гиперпотоком значение MAXDOP не должно превышать количество физических процессоров на узел NUMA».

ответил Bob McC 4 +04002013-10-04T20:02:30+04:00312013bEurope/MoscowFri, 04 Oct 2013 20:02:30 +0400 2013, 20:02:30
3

Эта версия дает вам хороший единый результирующий набор с существующим параметром MAXDOP и должна задерживаться на версиях SQL 2008-2017 без использования xp_cmdshell.

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);
ответил ShadowDancerLV 14 J0000006Europe/Moscow 2018, 21:38:42

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132