Recentemente estou enfrentando problemas de desempenho em um banco de dados SQL Server e tenho duas consultas que me mostram a espera conforme mostrado abaixo, um dos bancos de dados neste servidor é membro da réplica Always On, mas a carga está no outro banco de dados.
A primeira consulta é minha própria consulta que coleta a estatística de esperas durante 10 segundos e calcula a diferença e a segunda lista se da consulta de Pauls: https://www.sqlskills.com/blogs/paul/wait-statistics-or-please- diga-me onde dói/
Em relação às diferenças, estou me perguntando se devo ignorar CXPACKET e Redo_Thred_Pending_Work esperar e trabalhar na causa raiz da espera de PageIOLatch_SH (talvez indexação ruim). O valor MAXDOP é 0 e o servidor tem 36 núcleos, alguma ideia?
É muito difícil comentar, a menos que saibamos a natureza do problema de desempenho que você está tendo, o que pode ser devido a vários fatores.
Também esperas que você capturou são de 2 meios diferentes. Um coletado para as esperas testemunhadas durante o tempo que você calculou para a execução de consultas específicas. Enquanto outro de Paul Randal é o agregado desde a última reinicialização, apontando CXpackets, o que não indica necessariamente um problema com ele. Que ele coletou estatísticas de espera para todo o processo que seria executado desde a última reinicialização. Portanto, depende se você deseja solucionar problemas de desempenho geral do servidor ou apenas as consultas cujas esperas foram capturadas.
Além disso, dependendo da versão do seu servidor sql, há uma maneira melhor de interpretar as esperas dos pacotes CX. Após o sql 2016 SP2, agora você também tem o cxconsumer e, portanto, pode distinguir entre estatísticas de espera CXpacket boas e ruins. Leia aqui para saber mais sobre isso por Aaron Bertrand.
Também vejo que você tem MAXDOP 0, que deve ser testado com o melhor número mágico, dependendo dos processadores lógicos a serem ajustados. Pela minha experiência e suporte da MS, o melhor número mágico que funciona para a maioria dos nossos números é entre 4 e 8, onde os processadores lógicos são maiores que 8. Você também pode ver dbatools para procurar um número recomendado em seu ambiente.
Além do MAXDOP não se esqueça de verificar o limite de custo de paralelismo para o servidor. 5 o padrão não funciona bem.
No entanto, você também deve procurar outros fatores, como estratégias de indexação, encontrando oportunidades para ajustá-las, ajudando assim no ganho de desempenho
Estou basicamente ecoando a resposta do @KASQLDBA, mas tentando convencê-lo enfaticamente a fazer ajustes no grau máximo de paralelismo e no limite de custo do paralelismo como seu primeiro passo.
NÃO deixe em 0. Ajuste para um nível mais razoável. Você pode seguir as recomendações da Microsoft e defini-lo como 8 ou o número máximo de processadores lógicos por NUMA , o que for menor. Como alternativa, como @KASQLDBA afirmou na resposta acima, experimente o comando dbatools.io Test-DbaMaxDop para obter uma recomendação sem ter que fazer todo o trabalho de perna.
O problema de deixar essa configuração em 0 é que qualquer plano paralelo usará todos os núcleos do servidor. Deixados no padrão, planos paralelos suficientes podem realmente privar seu servidor de recursos de CPU e causar lentidão geral do sistema. Eu vi isso ser tão ruim que todo o servidor irá congelar brevemente para os usuários finais e causar todo tipo de comoção. As esperas do CXPacket que você está vendo provavelmente indicam que essa configuração está configurada incorretamente.
Conforme declarado no início desta resposta, certifique-se também de ajustar o limite de custo do paralelismo . Erik Darling tem um ótimo artigo descrevendo o que é essa configuração, como funciona e o que esse número significa, e recomenda que você comece definindo-o como 50 e ajustando a partir daí.