Os Wait Types mais comuns! O que são, causas e como tratá-los!


 Olá pessoal, tudo certo? Espero que sim!

 No post de hoje resolvi abordar de forma simplificada alguns wait types do SQL Server que podem ser bem comuns no dia a dia de um DBA: o que são, quais as suas causas e as formas de trata-los. 

PAGEIOLATCH_XX e PAGELATCH_XX
  
 Latches são primitivas de sincronização muito leves usadas pelo SQL Server para garantir a consistência no acesso à várias estruturas de memória, como páginas de dados e índices.
 Se a carga de trabalho estiver experimentando longas esperas associadas a estes wait types, uma primeira abordagem pode ser a seguinte:
  • Verificar se existe latência no acesso aos discos. Alguns contadores do Logical Disk no Performance Monitor podem ajudar:  Avg Disk Sec/Read, Avg Disk Sec/Write e Avg Disk Queue Length.
  • Identificar as queries afetadas e diminuir a quantidade de páginas processadas, simplificando as queries ou adicionando os índices necessários.
 Se estas medidas não forem suficientes para endereçar o problema, será necessário entender o cenário específico desta contenção por latches. Neste caso, o documento abaixo pode ajudar:

Diagnosing and Resolving Latch Contention on SQL Server


CXPACKET
 
 Este wait type pode ocorrer caso existam queries em execução rodando em paralelo e usando múltiplas CPUs. Durante a execução de uma query, a quantidade total de linhas pode se dividir em várias porções a serem processadas em CPU’s diferentes. Em alguns pontos do plano de execução da query é necessário sincronizar estas threads, e aí o CXPACKET é usado.

 Você pode verificar como seu SQL Server está configurado para lidar com o paralelismo através do sp_configure, parâmetro “max degree of parallelism”. A recomendação para a configuração do “max degree of parallelism” é usar no máximo 8 CPUs, ou o número máximo de CPUs físicas visíveis em um nó de processamento NUMA. Apenas uma informação adicional, ao mexer nesta configuração, todos os planos no cache do SQL Server serão descartados automaticamente para serem compilados novamente.

 A partir do SQL Server 2005 surgiram outras formas de se controlar o paralelismo, usando o hint MAXDOP, e a partir do SQL Server 2008 é possível usar o Resource Governor.  Além de tentar ajustar a configuração do paralelismo da sua instância, se você observar muitas esperas deste tipo, o indicado é mapear as queries responsáveis para verificar a possibilidade de ajustes na sua performance. 

NETWORKIO e ASYNC_NETWORK_IO   

 Estes wait types podem ocorrer se os clientes não consomem os resultados rapidamente, ou se existe algum problema ou limitação na rede.

 Para endereçar estes waits o indicado é mapear quais as queries que retornam grandes quantidades de dados e também verificar a latência ou problemas com a rede. Alguns contadores do Network Interface do Performance Monitor podem ajudar: Current Bandwidth, Output Queue Lenght, Bytes/Total, Packets Received Errors e Packets Outbound Errors. Também pode ser útil verificar se os drivers e o software usado nas interfaces de rede estão atualizados e configurados corretamente. 

SOS_SCHEDULER_YIELD 

 O SOS_SCHEDULER_YIELD deve aparecer apenas como last_wait_type na sys.dm_exec_requests, indicando que uma thread abandonou a CPU voluntariamente com o término do seu quantum. Conforme discutimos anteriormente na arquitetura do SQL Server, o SQLOS trabalha de forma não-premptiva, e são as threads que decidem quando devem abandonar o processador e dar chance para que outras threads executem. Quando a thread abandona o processador voluntariamente ela vai diretamente para o final da fila de runnable.

 Se muitas threads estiverem passando pelo SOS_SCHEDULER_YIELD e a utilização de CPU no servidor é alta, isto pode indicar um excesso de demanda por este recurso. Neste caso é preciso verificar se a maior parte da duração de uma query é dispendida na runnable. Se for, pode ser necessário diminuir a carga ou disponibilizar CPUs para a instância do SQL Server. O link abaixo contém informações que podem ser úteis nesta verificação: 


RESOURCE_SEMAPHORE 

 Este wait pode ocorrer quando uma query não consegue a memória necessária para executar. Quando o SQL Server compila o plano para executar uma query ele calcula dois grants de memória, chamados de "required memory" e "additional memory". O primeiro deles é o mínimo de memória necessário para rodar um sort ou um hash join, o segundo, é a quantidade de memória adicional para armazenar as linhas temporárias em memória.

 Quando existem muitos waits deste tipo a recomendação é verificar se existe pressão por memória. Alguns contadores do Performance Monitor podem ajudar aqui: Memory Manager\Memory Grants Pending e Memory Manager\Memory Grants Outstanding, entre outros. Também é útil inspecionar os semáforos e os grants concedidos / pendentes, através de queries nas DMVs sys.dm_exec_query_resource_semaphores e sys.dm_exec_query_memory_grants. Pode ser necessário ajustar a performance de algumas queries para reduzir a sua demanda por memória. 

IO_COMPLETION e ASYNC_IO_COMPLETION 

 Estes waits podem ocorrer quando existem esperas por operações de I/O que precisam ser completadas.

 Para waits deste tipo também é indicado verificar os sistemas de disco para a presença de latências, limitações de capacidade ou problemas de configuração / estabilidade. Contadores do Performance Monitor como Avg Disk Sec/Read e Avg Disk Sec/Write podem ajudar aqui, mas uma revisão do sub-sistema de discos como um todo pode ser necessária. 

WRITELOG e LOGBUFFER 

 Estes waits ocorrem quando as queries estão aguardando por log buffers ou pelo flush do log num checkpoint ou commit. 

 Se sua instância do SQL Server apresenta muitos waits destes tipos é necessário verificar a condição dos discos que contém os arquivos de log das bases de dados. Alguns contadores do Performance Monitor podem ajudar aqui: Avg. Disk Sec/Read, Avg Disk Sec/Write e Avg Disk Queue Lenght. 

 Outros links utilizados para escrita deste post: 

http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ 

 Espero que tenham gostado! Um abraço e até a próxima!

2 comentários:

  1. Muito bom seu post André, muito útil e sucinto. Obrigada pelas informações.

    ResponderExcluir
    Respostas
    1. Obrigado Carol! Fico feliz em poder ajudar :) Abs!

      Excluir