SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

Hannah Vernon

Asked: 2013-03-13 11:59:37 +0800 CST2013-03-13 11:59:37 +0800 CST 2013-03-13 11:59:37 +0800 CST

Algoritmo de configuração MAXDOP para SQL Server

772

Ao configurar um novo SQL Server, uso o seguinte código para determinar um bom ponto de partida para a MAXDOPconfiguração:

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

Percebo que isso é um pouco subjetivo e pode variar com base em muitas coisas; no entanto, estou tentando criar um código abrangente para usar como ponto de partida para um novo servidor.

Alguém tem alguma entrada sobre este código?

8 respostas

Voted

Kin Shah · Answer 1 · 2013-03-14T06:18:48+08:00

A melhor maneira de fazer é -- use coreinfo (utilitário de sysinternals), pois isso lhe dará

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

Agora, com base nas informações acima, a configuração Ideal MaxDop deve ser calculada como

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.

Portanto, a resposta é - " depende " do tamanho do seu processador e da configuração do NUMA e a tabela abaixo resumirá o que expliquei acima:

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.

Editado: Abaixo está um script TSQL rápido e sujo para gerar a configuração de recomendação para 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: Para futuros visitantes, você pode ver a função powershell test-dbamaxdop (juntamente com outras funções DBA extremamente úteis (TODAS GRATUITAS !!).

mrdenny · Answer 2 · 2013-03-13T16:48:49+08:00

mrdenny

2013-03-13T16:48:49+08:002013-03-13T16:48:49+08:00

Ao definir MAXDOP, você normalmente deseja limitá-lo ao número de núcleos em um nó NUMA. Dessa forma, os agendamentos não estão tentando acessar a memória nos nós do numa.

17

Hannah Vernon · Answer 3 · 2014-10-08T12:16:47+08:00

Olhando para uma postagem da equipe do MSDN , encontrei uma maneira confiável de obter a contagem de núcleos físicos de uma máquina e usá-la para determinar uma boa configuração de MAXDOP.

Por "bom", quero dizer conservador. Ou seja, meu requisito é usar no máximo 75% dos núcleos em um nó NUMA ou um máximo geral de 8 núcleos.

SQL Server 2016 (13.x) SP2 e superior e todas as versões do SQL Server 2017 e superior apresentam detalhes sobre a contagem de núcleos físicos por soquete, a contagem de soquetes e o número de nós NUMA, permitindo uma maneira organizada de determinar a linha de base Configuração MAXDOP para uma nova instalação do SQL Server.

Para as versões mencionadas acima, este código recomendará uma configuração conservadora de MAXDOP de 75% do número de núcleos físicos em um nó 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;';

Para versões do SQL Server anteriores ao SQL Server 2017 ou SQL Server 2016 SP2, você não pode obter o core-count-per-numa-node do sys.dm_os_sys_info. Em vez disso, podemos usar o PowerShell para determinar a contagem de núcleos físicos:

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

Também é possível usar o PowerShell para determinar o número de núcleos lógicos, que provavelmente seria o dobro do número de núcleos físicos se o HyperThreading estiver ativado:

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

O 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));

db2 · Answer 4 · 2013-03-13T12:31:28+08:00

Como regra geral, use um DOP mais alto para um sistema OLAP e um DOP mais baixo (ou nenhum) para um sistema OLTP. Muitos sistemas estão em algum lugar no meio, portanto, encontre um meio-termo que permita que a grande carga de trabalho ocasional obtenha CPU suficiente para concluir rapidamente, sem estrangular suas cargas de trabalho OLTP.

Além disso, tenha cuidado ao usar a cpu_countcoluna para obter uma contagem de núcleos. Se o hyperthreading estiver habilitado, esta coluna parece refletir o número de processadores lógicos expostos. De um modo geral, você não deseja que o DOP seja maior que o número de núcleos físicos. Espalhar uma carga de trabalho paralela pesada entre processadores lógicos apenas aumentará a sobrecarga sem nenhum benefício real.

Há também uma hyperthread_ratiocoluna, mas não tenho certeza do que ela representa. A documentação também não é muito clara. O número que vejo em nosso sistema sugere que pode ser o número de núcleos físicos em todo o sistema ou o número de processadores lógicos por chip. A documentação afirma que eu deveria estar vendo uma figura totalmente diferente.

Dennis Winter · Answer 5 · 2014-06-13T00:14:48+08:00

Eu também tropecei no artigo http://support.microsoft.com/kb/2806535 e não consegui encontrar a correlação com os scripts acima.

Também estou querendo saber, por que existe uma diferenciação para "@logicalCPUs >= 8 e @HTEnabled = 1 e @NoofNUMA = 1" e "@logicalCPUs >= 8 e @HTEnabled = 1 e @NoofNUMA > 1" como resultado torna-se o mesmo.

Afinal, acabei escrevendo meu próprio código combinando com o artigo acima, embora mesmo lá eu adoraria uma definição e/ou diferenciação mais precisa sobre "processadores", "CPU" e "processadores físicos".

Sinta-se livre para dar sua volta com ele.

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

ShadowDancerLV · Answer 6 · 2018-06-15T10:38:42+08:00

Esta versão oferece um bom conjunto de resultados único com a configuração MAXDOP existente e deve se manter nas versões SQL 2008-2017 sem a necessidade de usar 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);

Bob McC · Answer 7 · 2013-10-05T08:02:30+08:00

Bob McC

2013-10-05T08:02:30+08:002013-10-05T08:02:30+08:00

Bom script, mas o artigo kb: http://support.microsoft.com/kb/2806535 não combina completamente com seu código. o que estou perdendo?

Servidor 1
HTEnabled: 1
hyperthreadingRatio: 12
cpus lógico: 24
cpus físico: 2
cpus lógicos por numa: 12
NoOfNuma: 2
configuração MaxDop deve ser: 6

Server 2
HTEnabled: 2
hyperthreadingRatio: 16
cpus lógico: 64
cpus físico: 4
cpus lógicos por numa: 16
NoOfNuma: 4
A configuração de MaxDop deve ser: 4

Eu percebo que estas são apenas sugestões; mas algo não me parece certo que um servidor (#2) acima com 4 processadores em vez de 2 e 8 núcleos por CPU física em vez de 6; recomendaria o MAXDOP em 4, contra 6 para o servidor menos poderoso.

O artigo kbb acima sugere 8 meu cenário acima. "Para servidores que têm NUMA configurado e hyperthreading habilitado, o valor MAXDOP não deve exceder o número de processadores físicos por nó NUMA."

3

lad2025 · Answer 8 · 2019-05-25T10:04:34+08:00

lad2025

2019-05-25T10:04:34+08:002019-05-25T10:04:34+08:00

Durante a instalação do SQL Server 2019 CTP 3.0, há uma nova guia MaxDOP. O valor real é predefinido (nas versões anteriores o padrão era 0).

Configurando MAXDOP durante a instalação do SQL Server 2019

_{Fonte da imagem: https://www.brentozar.com/wp-content/uploads/2019/05/SQL_Server_2019_Setup.png}

3

Algoritmo de configuração MAXDOP para SQL Server

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Conceder acesso a todas as tabelas para um usuário

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Algoritmo de configuração MAXDOP para SQL Server

8 respostas

relate perguntas