Desempenho mais lento do SQL Server após alocar mais CPU e RAM

Há muita coisa acontecendo aqui, e a maior parte é bastante ampla e vaga.

1. 2008R2 RTM foi lançado em 21 de abril de 2010. Está totalmente sem suporte. Você deve priorizar a obtenção do Service Pack mais recente, lançado há apenas 3 anos. Dessa forma, você estará coberto se estiver atingindo um bug estranho ou algo assim. Acesse aqui para descobrir o que você precisa baixar.

2. Como você adicionou vCPUs (de 1 a 4) e não alterou nenhuma configuração, suas consultas agora podem ser paralelas. Eu sei que isso soa como se todos fossem mais rápidos, mas espere!

3. Você pode ter adicionado RAM, mas pode não ter alterado a Memória Máxima do Servidor para que seu servidor possa aproveitá-la.

4. Descubra o que seu servidor está esperando. Um projeto de código aberto em que trabalho fornece scripts gratuitos para ajudá-lo a medir seu SQL Server. Passe por aqui se quiser experimentá-los.

Você vai querer pegar sp_BlitzFirst para verificar as estatísticas de espera do seu servidor. Você pode executá-lo de algumas maneiras.

Isso mostrará o que seu servidor está esperando desde que foi inicializado.

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

Isso mostrará quais consultas estão aguardando agora, durante uma janela de 30 segundos.

EXEC dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

Depois de descobrir quais consultas estão esperando (há uma tonelada de coisas escritas sobre estatísticas de espera por aí), você pode começar a fazer alterações para manter as coisas sob controle.

Se você os vir esperando CXPACKET, isso significa que suas consultas estão sendo paralelas e talvez atropelando umas às outras. Se você acertar isso, provavelmente vai querer considerar aumentar o Limite de Custo para Paralelismo para 50 e talvez diminuir o MAXDOP para 2.

Após esta etapa, é quando você deseja usar algo como sp_WhoIsActive ou sp_BlitzWho (o último está no repositório GitHub anterior) para começar a capturar planos de consulta. Além das estatísticas de espera, elas são uma das coisas mais importantes que você pode observar para descobrir o que está acontecendo de errado.

Você também pode conferir este artigo de Jonathan Kehayias sobre VMWare Counters para verificar em relação ao SQL Server.

Atualizar

Revendo as estatísticas de espera e cara, elas são estranhas. Definitivamente, há algo acontecendo com as CPUs. Seu servidor está principalmente sentado entediado, mas quando as coisas esquentam, as coisas ficam ruins. Vou tentar quebrar isso facilmente.

1. Você está acertando uma espera venenosa chamada THREADPOOL. Você não tem muito disso, mas isso faz sentido porque seu servidor não está muito ativo. Vou explicar por que em um minuto.

2. Você tem esperas médias realmente longas SOS_SCHEDULER_YIELDe CXPACKET. Você está em uma VM, então você vai querer ter certeza de que o SQL Server tem reservas ou que a caixa não está horrivelmente sobrecarregada. Um vizinho barulhento pode realmente arruinar seu dia aqui. Você também vai querer certificar-se de que o servidor/convidado da VM/host da VM não esteja sendo executado no modo de energia balanceada. Isso faz com que suas CPUs diminuam para velocidades desnecessariamente baixas e elas não voltem imediatamente à velocidade máxima.

3. Como eles se ligam? Com 4 CPUs você tem 512 threads de trabalho. Lembre-se de que você tinha a mesma quantidade com uma única CPU, mas agora que suas consultas podem ser paralelas, elas podem consumir muito mais threads de trabalho. No seu caso, 4 threads por ramificação paralela de uma consulta paralela.

O que está acontecendo em paralelo? Muito provavelmente tudo. O limite de custo padrão para paralelismo é 5. Esse número se tornou o padrão em algum momento no final dos anos 90, trabalhando em um desktop parecido com este .

É verdade que seu hardware é menor do que a maioria dos laptops, mas você ainda está um pouco à frente disso.

Quando muitas consultas paralelas são executadas, você fica sem esses threads de trabalho. Quando isso acontece, as consultas ficam paradas esperando que os encadeamentos comecem. É aí também que SOS_SCHEDULER_YIELDentra. As consultas estão saindo das CPUs e não voltando por um longo tempo. Eu não vejo nenhuma espera de bloqueio, então você provavelmente está cheio de esperas de paralelismo intra-consulta.

O que você pode fazer?

1. Certifique-se de que nada esteja no modo Balanced Power
2. Alterar MAXDOP para 2
3. Altere o limite de custo para paralelismo para 50
4. Siga o artigo de Jon K. acima para validar a integridade da VM
5. Use o script chamado sp_BlitzIndexpara procurar quaisquer solicitações de índice ausentes.

Para uma solução de problemas mais completa, confira o whitepaper que escrevi para o Google sobre dimensionamento de hardware na nuvem.

Espero que isto ajude!

Sim! Eu experimentei esse tipo de situação no SQL Server vms em nosso farm de servidores. Observe o tempo de prontidão da CPU do host da VM e os contadores do driver do balão de memória. CPU READY TIME – BLOG PART I and Understanding VMware Ballooning Trabalhar com meu sysadmin foi fundamental, mas não foi fácil...

Uma coisa que não vi apontada é que adicionar vCPUs a uma VM pode muitas vezes desacelerá-la devido ao agendamento.

A ideia básica é que, se uma VM tiver 4 vCPUs, o hipervisor deverá aguardar a disponibilidade de 4 núcleos físicos para poder agendar todas as vCPUs, mesmo que 3 delas estejam ociosas.

Se você não tiver muitos núcleos em seu host e suas outras cargas de trabalho estiverem ocupadas, isso poderá resultar em espera extra e uma queda significativa no desempenho.

No VMware ESXi você pode vê-lo nos gráficos avançados via CPU Ready.

Aqui está um dos muitos artigos com um exemplo do mundo real disso acontecendo e como foi diagnosticado .

Adicionar mais RAM também pode causar uma queda repentina de desempenho se a alocação de RAM da VM for maior que um nó NUMA.

Além disso, a configuração de suas vCPUs (vSockets vs. vCores) pode afetar alguns aplicativos, como o SQL Server. Isso ocorre porque o próprio SQL Server reconhece NUMA (para evitar o mesmo tipo de queda de desempenho que abrange NUMA) e porque o VMware pode apresentar nós NUMA virtuais de maneira diferente.

Isso é abordado em uma postagem de blog no próprio site da VMware .

Dito isso, fico feliz que você tenha resolvido os problemas com a ajuda de Erik, mas você pode querer analisar e considerar essas coisas também.

Apenas uma ajudinha (não posso postar isso como um comentário) continuando a resposta do @sp_BlitzErik, recebi algumas consultas com Pinal e Max Vernon (não consigo lembrar onde) que dizem quanto MAXDOP você deve usar:

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

-------------------------------------------------- -------

--MAX VERNON 

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