Wait Types mais comuns!

Fala pessoal, nesse post vou detalhar os principais waits que você deve ter atenção quando falamos de desempenho no SQL Server.

BACKUPIO - ocorre em processos de backups e representa latência na geração do arquivo no disco(escrita), pode aparecer principalmente quando os arquivos são gerados na rede, uma possível solução é avaliar o desempenho de IO do local onde os arquivos estão sendo gerados.

Grupo PREEMPETIVE é quando a thread passa a ser controlada pelo Windows e não mais pelo SQL Server.

PREEMPTIVE_OS_FILEOPS - SQL esta aguardando uma resposta da thread controlada pelo windows conclua operações do sistema de arquivos do ambiente. Situações onde podem ocorrer: backup, restauração, inicialização de arquivo (criação e crescimento). Você pode ajustar a configuração no ambiente para evitar a inicialização com "zeros" para arquivos de dados e avaliar problemas de armazenamento.

PREEMPTIVE_OS_CREATEFILE - uma thread está chamando a função Windows CreateFile, no SQL esta relacionado a criação de um arquivo FILESTREAM, um arquivo de backup, Extended Events ou um novo arquivo de banco de dados. Não representa um problema especificamente dentro do banco de dados mas vale observar a parte externa (Escrita/Leitura, por exemplo).

PREEMPTIVE_OS_WRITEFILEGATHER - ocorre quando o SQL aguarda que o sistema operacional termine um processo de gravação no disco, geralmente ocorre quando o Autogrowth do arquivo esta configurado de forma "errada", gerando um crescimento muito alto de uma única vez ou então gerando muitos crescimentos sequenciais com tamanho bem pequeno. Para solucionar uma saída é ajustar a configuração para evitar a inicialização com "zeros" para arquivos de dados e a outra é ajustar a configuração de Autogrowth para um valor mais dentro da realidade.

IO_COMPLETION - indica que o SQL esta aguardando pela leitura/escrita de uma pagina sem dados. Ocorre geralmente durante o processo que chamamos de "spill do tempdb"(que é quando a operação é estimada errada e não existe memória suficiente para realizar um ORDER, por exemplo, e por isso precisa usar o disco para realizar a ordenação) relacionado a operações de classificação e hash. Sugestão de melhoria é otimizar a query que esta causando o "spill".

SOS_SCHEDULER_YIELD - Pode indicar pressão da CPU do servidor onde esta o banco de dados, quando recebemos um tempo alto de espera, podemos concluir que o encadeamento produzido por um processo foi movido para uma fila executável e ele precisou esperar muito tempo para que o agendador o trouxesse de volta ao estado de execução. Geralmente, isso requer algum ajuste de consulta, pois é a própria consulta que está causando o problema. Avalie conversões na query, scan de tabelas e até indices errados que estejam causando um alto consumo de CPU para executar.

Atenção: caso esteja recebendo de forma esporádica esse wait com tempo baixo, não representa um problema.

RESOURCE_SEMAPHORE - Quando o plano de execução de uma query é gerado ele informa ao SQL quanto de memória ele precisa pra conseguir entregar aquela query, quando uma query esta muito ruim com um plano que consome muita memória ou se surgem outras querys ruins quando ela for entrar em execução, o SQL pode não ter a memória suficiente disponível para que ela execute, essa query vai ter esse wait e pra resolver isso você precisa avaliar a query que esta sendo impactada se é possível ajustar índices ou até mesmo mudar a estrutura da query para que consuma menos, outra opção é aumentar recurso no servidor.

PAGEIOLATCH - Existem algumas variações desse wait mas essa família esta vinculada a leitura de paginas no disco, quando aparecem indicam que o SQL esta demorando para conseguir ler os dados no disco, e ele precisa ler do disco porque os dados não estavam em memória.

Imagine se você esta no sofá da sua casa com o controle remoto da TV na sua mão, de uma forma bem rápida e sem esforço você consegue mudar os canais e aumentar o volume, mas se a pilha acabar, você vai começar a precisar levantar e ir até a TV para apertar os botões, mudar de canal, por exemplo. Funciona da mesma forma, porém o esforço e demora é muito maior.

Se o buffer do SQL (que é a memória alocada para o banco guardar os dados e não precisar ficar indo no disco toda hora buscar aquele dado) estiver cheio, muitos processos simultâneos lendo dados podem ter o wait pageiolatch com tempo alto. Partindo do principio que ler dados que estejam em memoria é mais rápido destaco duas possibilidades para resolver o problema.

1 - Aumentar o tamanho do cache, se a pilha do controle zerou, você tem mais algumas reservas, da mesma forma, se tiver memória livre disponível no ambiente, libere mais para o SQL, com isso vai conseguir guardar mais dados em memória evitando ter que ir no disco.

2 - Melhorar o desempenho da query para que consuma menos memória para executar o processo e com isso tenha mais espaço para dados.

WRITELOG - Esse tipo de espera ocorre quando você fica aguardando que um bloco de log seja gravado no disco no log de transações, é o momento onde o SQL pega os dados de log em memória e guarda eles no disco, como se você tivesse com vários documentos em cima da sua mesa e resolvesse guardar eles em uma pasta no seu armário porque não cabem mais na mesa, geralmente esse processo ocorre quando o bloco de log enche ou quando uma transação é confirmada.

O problema é que quando você recebe uma espera alta ou muito frequente para esse processo significa que o desempenho do disco onde o log de transação esta alocado esta ruim, ou o banco esta executando muitas vezes seguidas o processo de gravação no disco. Imagina um loop de 10 mil linhas a cada passagem no loop ele confirma uma transação, serão 10 mil vezes que o SQL irá registrar os blocos de log no disco. Para diminuir esse wait você pode seguir dois caminhos, começando por por:

1 - Identificar se existe algum processo realizando muitas vezes seguidas a confirmação de transação como no exemplo do loop. Nesse caso, você pode agrupar essas transações em um bloco apenas e "committar" todas as alterações de uma só vez.

2 - O segundo caminho é avaliar a possibilidade de melhorar o desempenho do disco onde o log de transações esta alocado.

ASYNC_NETWORK_IO - Algumas aplicações trabalham no formato cliente-servidor, em que a conexão entre aplicação e banco é realizada pela rede, por uma conexão de rede. Quando uma query apresenta um tempo de espera alto para esse wait type indica que esta ocorrendo uma grande transferência de dados via rede ou que o ambiente esta com problemas de desempenho na rede. Sabe quando você esta conversando com alguém por aplicativo, trocando áudio que são pequenos, é praticamente instantâneo e de repente e pessoa te manda um vídeo bem grande e demora alguns minutos pra baixar ele, pensa em uma query que vai retornar milhões de linhas e vai trafegar elas via rede para serem exibidas em um painel na maquina do usuário. É ai que vem o ASYNC NETWORK IO. Algumas sugestões para melhorar são :

1 - Identificar quais as querys que estão apontando esse wait

2 - Levantar o volume de informação que elas retornarão

3 - Avaliar se existe algo errado na query, um parâmetro informado errado que não deveria ou até mesmo a possibilidade de inserir algum filtro para reduzir a quantidade de dados

4 - Solicitar ajuda da equipe de infra para avaliar se não existe algum problema na rede

LCK - É a família dos locks, tem muita gente que acha o lock ruim, mas ele tem o seu valor. Pensa numa prateleira cheia de livros, quando você vai pegar um livro, o Caio chega mais rápido e pega o livro na sua frente, começa a folhar ali o livro e você fica do lado aguardando, depois de uns 30 segundos ela devolve o livro na prateleira e então você consegue pegar ele pra ver. Nessa história a prateleira é a tabela, o livro é o dado, o Caio é um usuário e você é outro usuário, o dado estava lá disponível, quando você foi acessa-lo outro usuário chegou primeiro e começou a usar aquele dado, somente quando ele terminou você conseguiu acessar e ler o dado, durante aqueles 30 segundos que ficou aguardando você ficou "sofrendo lock". Quando a query no banco esta com o wait que começa com LCK significa que aquela query esta aguardando outro processo terminar de usar a tabela ou o dado em si para poder de fato executar.

Os locks podem ocorrer em vários níveis, mas o que você precisa saber é que a query que esta sofrendo o lock esta demorando porque esta aguardando e não necessariamente porque esta lenta.

Para resolver esse caso, você deve identificar qual é a query que esta causando o lock e avaliar ela, se esta demorando demais e precisa/pode ser otimizada, ou se uma transação ficou aberta no banco e não finalizou gerando o bloqueio ou até mesmo se a tabela que vai ser usada esta sofrendo algum tipo de manipulação, como a troca de um tipo de campo.

Vale ressaltar que existem vários outros WAITs mas esses são os que devemos ter uma atenção especial!

Nos acompanhe em nossas redes sociais!

Grupo VIP Telegram: DBA On boarding

Youtube(vídeos novos todas as quartas): DBA On boarding

Face & Instagram(conteúdo diário): DBA On boarding

Até a próxima, tchau!

#CG_Administration