Início / dba / Perguntas / 289016
Accepted
Aleksey Vitsko
Asked: 2021-04-01 02:37:57 +0800 CST

Alto uso de CPU do SQL Server e esperas de RESOURCE_SEMAPHORE

  • 772

Na semana passada, houve um problema em um dos SQL Servers, a CPU começou a queimar mais de 80% (o normal é 10-30%)
Isso durou cerca de 2 horas até que eu fiz failover manualmente para a réplica secundária no AG (e isso resolveu o problema)

Início do problema: 12h15
Fim do problema: 14h15 (após o failover manual do AG)

CPU

Informações do servidor:

SQL Server 2017  
32 logical processors (max DOP = 8)  
256 GB RAM (Max Server Memory = 180 GB, used 179 GB)

As métricas abaixo NÃO mudaram visivelmente antes do problema x após o início do problema

  • Conexões do usuário/s (média 200-300)
  • Solicitações em lote/s (média 200 e inferior)
  • Memória Cache do Banco de Dados (~ 150 GB)

Abaixo das métricas PICOS consideravelmente, o que NÃO é típico para este servidor (geralmente estes são baixos):

  • CPU (mais de 80%)
  • Concessões de memória pendentes
  • Bloqueio de Esperas/s, Avg. Tempo de espera de bloqueio, impasses
  • Tempo de espera da trava
  • Memória de espaço de trabalho concedida e memória de servidor reservada

Consultas:

Não notei mudanças nas cargas de trabalho para este servidor quando o problema começou
Os desenvolvedores também confirmaram que os aplicativos fizeram seu trabalho normal e estavam executando consultas normais, sem picos na carga do aplicativo

Durante esse problema de "alto uso da CPU", as 10 principais consultas por CPU não pareciam incomuns
Todas as mesmas 10 principais consultas que costumamos ver mesmo quando a CPU está normal (10-30%)

Problema:

O problema parecia estar em alguns procedimentos armazenados relacionados, esse aplicativo geralmente é executado de 1 a 4 vezes / segundo e geralmente é concluído em 50 ms, mas durante o problema, sempre que verifiquei o sys.dm_exec_requests (também usado exec ViewSessionsConnections 'running' https ://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Sessions%20-%20ViewSessionsConnections.sql ), estavam sentados como 50-70 sessões de 1 aplicativo, todos tentando concluir os procedimentos acima mencionados , e foi lento

Ao analisar as 10 principais consultas por Duração em uma ferramenta de monitoramento, as principais 1 e 2 eram duas instruções dos procedimentos acima - elas não consumiam muita CPU, MAS tinham esperas excessivas (RESOURCE_SEMAPHORE, LCK_M_IS)

Normalmente, eles são concluídos em 10 ms ou menos, executados de 1 a 4 vezes por segundo e não causam problemas, e agora começaram a ter duração de 4.000 a 8.000 ms por 1 execução, que era o problema

RESOURCE_SEMAPHORE NÃO é absolutamente típico para este servidor, mas durante o problema estava entre as principais esperas (RESOURCE_SEMAPHORE - Consultas aguardando a concessão de memória; total de 135400234 ms em 2 horas; média de 4174 ms )

Granted Workspace Memorye Reserved System Memoryno SQL Server disparou de 0 para ~ 110 GB durante o problema

Perguntas:

  1. Quais são seus pensamentos e experiências acima?

  2. As esperas constantes de RESOURCE_SEMAPHORE e concessões de memória pendentes podem causar pressão na CPU apenas para alocar memória do espaço de trabalho para consultas? Porque quando analisamos as 10 principais consultas por CPU durante o problema, os números da CPU pareciam normais / usuais

  3. Como pode ser isso Granted Workspace Memorye Reserved Server Memorycomeçar a consumir ~ 112 e 110 GB quando o problema começou, visto que Max Server Memorysão 180 GB e Database Cache Memory remained~ 150 GB o tempo todo? Será que sobrecarrega a memória ou algo assim?

  4. Por que uma instrução dentro do SP que geralmente é concluída em 10 ms por meses, começa a experimentar RESOURCE_SEMAPHORE esperar e concluir em 4000-8000 ms?

  5. Como o problema pode ser resolvido de maneira mais cirúrgica, sem fazer failover manual para a réplica secundária? Como posso acalmar a consulta e trazê-la de volta para 10 ms? O plano precisa ser descartado ou a consulta recompilada, etc. ? Qual é a melhor maneira de fazê-lo e monitorá-lo?

  6. Brent Ozar First Responder Kit ou outros procedimentos de diagnóstico - em que sequência devem ser executados durante problemas de desempenho, para entender melhor o que está acontecendo?

sql-server performance

1 respostas

  1. Best Answer

    Quais são seus pensamentos e experiências acima?

    Por que uma instrução dentro do SP que geralmente é concluída em 10 ms por meses, começa a experimentar RESOURCE_SEMAPHORE esperar e concluir em 4000-8000 ms?

    Pressão da CPU causada por planos ruins. Você deve acompanhar e gerenciar a estabilidade do plano com o Query Store , além de investigar planos ruins e remediar com índices e estatísticas adicionais e talvez alterações nas consultas.

    As esperas constantes de RESOURCE_SEMAPHORE e concessões de memória pendentes podem causar pressão na CPU apenas para alocar memória do espaço de trabalho para consultas?

    Não, é o contrário. Planos ruins consomem muitos recursos, gerando grandes alocações de memória e uso de CPU.

    • 5

relate perguntas