Neste artigo, vamos continuar abordando o tema desenvolvido no artigo anterior, sobre os tipos de espera, seus relacionamentos e dependências e por que afetam tanto o desempenho do servidor SQL Server.

Tipos de espera comum

Para investigar os tipos de espera mais comuns ou agregados, e até mesmo para descobrir o funcionamento de uma consulta individual, é preciso entender o que os nomes dos tipos de espera significam. Muitos dos nomes são autodescritivos, alguns são enigmáticos e outros possuem, até certo ponto, nomes enganosos. Acredito que o melhor recurso para ler uma descrição detalhada dos nomes é o whitepaper Esperas e Queues. Ele tem uma lista alfabética dos tipos de espera, descrição e com que recurso físico do servidor está relacionado. O whitepaper é um pouco antigo (abrange o SQL Server 2005), mas ainda é altamente relevante. Os nomes dos tipos de espera que aguardam por recursos importantes não mudaram desde o SQL Server 2005. O que estará ausente serão os nomes dos tipos de espera mais recentes relacionados aos recursos do SQL Server 2005. Os tipos de espera são brevemente documentado também no tópico do MSDN sys.dm_os_wait_types.

Disco e IO relacionado a tipos de espera

PAGEIOLATCH_*

Este é o IO por excelência: os dados lidos do disco são gravados sob a forma de um tipo de espera. A tarefa bloqueada nesse tipo de espera está aguardando dados a serem transferidos entre o disco e o cache de dados em memória (o pool de buffer). Em um sistema que tem alta PAGEIOLATCH_* agregada a algum tipo de espera, é muito provável que a memória seja consumida e esteja gastando muito tempo lendo dados do disco para o buffer. Tenha cuidado para não confundir esse tipo de espera com o tipo PAGELATCH_ * (não possui o IO no nome).

WRITELOG

Esse tipo de espera ocorre quando uma tarefa emite um COMMIT e aguarda o registro ser concluído para escrever a transação no log do disco. Tempos de resposta médios e elevados nesse tipo de espera indicam que o disco está escrevendo o log lentamente, e isso diminui a cada transação. Tempos de resposta muito frequentes nesse tipo de espera são indicativos de que estão criando muitas pequenas transações e terão que ser bloqueadas com frequência para esperar pelo COMMIT (lembre-se de que tipos que escrevem todos os dados exigem uma transação separada e que é implicitamente criada para cada declaração, senão BEGIN TRANSACTION é usado explicitamente).

IO_COMPLETION

Esse tipo de espera ocorre para as tarefas que estão esperando por algo mais do que os dados de IO. Por exemplo, carregar uma DLL, ler e escrever arquivos de ordenação do tempdb, ou então esperam por dados especiais referentes a operações de leitura DBCC.

ASYNC_IO_COMPLETION

Esse tipo de espera é geralmente associado com backup, restauração de dados e operações com arquivos de banco de dados.

Se, em sua análise, o tempo de espera constatar que o registro de IO e disco são importantes tipos de espera, então sua tarefa deve se concentrar em analisar a atividade de disco.

Tipos de espera relacionados à memória

RESOURCE_SEMAPHORE

Esse tipo de espera indica as consultas que estão à espera de uma concessão de memória. Confira o documento Entenda a concessão de memória do servidor SQL. Consultas do tipo de carga de trabalho de OLTP não devem exigir grandes concessões de memória. Caso você se depare com esse tipo de espera em um sistema OLTP, reveja seu projeto de software. Cargas de trabalho OLAP muitas vezes possuem a necessidade de concessões de memória (algumas vezes grande) e grandes tempos de espera que geralmente apontam para o aumento das atualizações de memória RAM.

SOS_VIRTUALMEMORY_LOW

Você ainda está convivendo com sistemas de 32 bits? Siga em frente!

Tipos de espera relacionados à rede

ASYNC_NETWORK_IO

Esse tipo de espera indica que o SQL Server possui determinados conjuntos de resultados que devem ser enviados para o aplicativo, mas este pode não processá-los. Isso pode indicar uma conexão de rede lenta, mas não necessariamente. Mas, mais frequentemente, o problema está relacionado com o código do aplicativo, ou então com algum bloqueio ao processar o conjunto de resultados, ou ainda está solicitando um enorme conjunto de resultados que não estão sendo entregues em tempo hábil.

CPU, disputa e concorrência relacionadas a tipos de espera

LCK_*

Locks ou travas. Todos os tipos de espera que começam com LCK indicam uma tarefa suspensa à espera de um bloqueio qualquer. O tipo de espera LCK_M_S* indica uma tarefa que está esperando para ler dados (que podem ser bloqueios compartilhados) e está bloqueada por outra tarefa que tinha modificado os dados (tinha adquirido uma trava LCK_MX* exclusiva). O tipo de espera LCK_M_SCH* indica bloqueio de objetos relacionados à modificação de esquema e indicam que o acesso a um objeto (como uma tabela) está bloqueada por outra tarefa que fez uma modificação em alguma DLL que acessa esse objeto (ALTER).

PAGELATCH_*

Não confunda esse tipo de espera com o PAGEIOLATCH_*. Tempos de espera elevados para PAGELATCH_* indicam um ponto de grande acesso no banco de dados, uma região de dados que são é frequentemente atualizada (que, por exemplo, poderia ser um único registro em uma tabela que é constantemente modificada). Para uma análise mais aprofundada, recomendo o whitepaper Diagnosticando e resolvendo disputas e travas no SQL Server.

LATCH_*

Esses tipos de espera indicam contenção em recursos internos do SQL Server, mas não necessariamente em dados (ao contrário do PAGELATCH_*, não indicam um ponto muito movimentado do servidor). Para investigar essas esperas, será preciso cavar ainda mais fundo usando os sys.dm_os_latch_stats DMV que detalham os tempos de espera por tipo de trava. Mais uma vez, é uma boa ideia ler o whitepaper Diagnosticando e resolvendo disputas e travas no SQL Server.

CMEMTHREAD

Esse tipo de espera ocorre quando as tarefas estão bloqueadas, esperando para acessar um alocador de memória compartilhada. Coloquei esse tipo aqui, na seção de concorrência, e não na seção de “memória”, pois o problema está relacionado com a concorrência interna do SQL Server. Se você ver tipos de espera com altos valores em CMEMTHREAD, certifique-se de que você está utilizando a versão mais recente do SQL Server Service Pack disponível e também a Atualização Cumulativa para a sua versão, porque alguns desses tipos de problemas reportam questões internas do SQL Server e muitas vezes são tratados em versões mais recentes.

SOS_SCHEDULER_YIELD

Esse tipo de espera pode indicar uma contenção do tipo spinlock. Spinlocks são tipos de espera extremamente leves e primitivos no SQL Server, utilizados para proteger o acesso a recursos que podem ser modificados dentro de poucas instruções de bloqueio da CPU. Tarefas do SQL Server adquirem spinlocks por fazer operações interligadas à CPU dentro de um loop, assim, a contenção em spinlocks queima um monte de tempo de CPU (contadores de uso de CPU mostram entre 90-100% de uso, mas o progresso é lento). Uma análise mais aprofundada precisa ser feita usando sys.dm_os_spinlock_stats:

select * from sys.dm_os_spinlock_stats 
order by spins desc;

Para mais detalhes, recomendo a leitura do whitepaper Diagnosticando e resolvendo contenções spinlock no SQL Server.

RESOURCE_SEMAPHORE_QUERY_COMPILE

Esse tipo de espera indica que uma tarefa está esperando para compilar seu pedido. Tempos de resposta elevados para esse tipo de espera indicam que a compilação da consulta enfrenta um problema de desempenho. Para mais detalhes, recomendo a leitura do documento Resolução de problemas com cache.

SQLCLR_QUANTUM_PUNISHMENT

Esse tipo de espera ocorre se for executado código CLR dentro do motor SQL Server, e esse código CLR não ceder espaço de CPU. Isso resulta em um estrangulamento do código CLR. Se você tiver o código CLR que potencialmente poderá sequestrar o uso de CPU por um longo período, deve chamar Thread.BeginThreadAffinity(). Para mais detalhes, recomendo conferir o link Dados mais rápidos: técnicas para melhorar o desempenho do Microsoft SQL Server com SQLCLR.

Tipos de espera especiais

TRACEWRITE

Esse tipo de espera indica que as tarefas são bloqueadas pelo SQL Profiler. Esse tipo de espera ocorre somente se você tiver o SQL Profiler conectado ao servidor e ocorre com frequência durante a investigação de problemas de desempenho, se você tiver criado um rastreamento SQL Profiler muito agressivo (que recebe muitos eventos, por exemplo).

PREEMPTIVE_OS_WRITEFILEGATHER

Esse tipo de espera ocorre, entre outros motivos, quando o aumento automático dos arquivos é acionado. Técnica chamada de autocrescimento, ela ocorre quando um arquivo de tamanho insuficiente é mantido pelo SQL Server em um evento muito dispendioso para a CPU do servidor. Durante o crescimento do arquivo, toda a atividade no banco de dados estará congelada. Esse crescimento do arquivo de dados pode ser feito rapidamente, permitindo o crescimento do arquivo instantâneos – consulte Arquivo de inicialização de banco de dados para mais informações. Mas o crescimento do log não pode se beneficiar da inicialização instantânea de arquivo de log, porque o crescimento é sempre lento, e às vezes muito lento. Registrar eventos de autocrescimento pode ser diagnosticado simplesmente olhando para o contador de desempenho no log (confira o link banco de dados de objetos SQL Server para mais informações), onde 0 significa que o log registrou o autocrescimento pelo menos uma vez. O monitoramento em tempo real pode ser feito observando o arquivo de dados de autocrescimento e o log de autocrescimento de arquivos no SQL Profiler.

Não vou abordar aqui alguns tipos de espera, como CLR_*, SQLCLR_*, SOSHOST_*, HADR_*, DTC_* e alguns mais). Caso encontre um tipo de espera que não entende, geralmente apenas pesquisar na Internet sobre seu nome irá revelar informações sobre o que esse tipo de espera é, e o potencial problema de desempenho que apresenta, se houver.

Na próxima parte deste artigo, vamos abordar as atividades de disco, estatísticas de entrada e saída de dados e execução de consultas.

***

Artigo traduzido pela Redação iMasters com autorização do autor. Publicado originalmente em http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/