Descrever estatísticas de espera

Concluído

Uma abordagem abrangente para monitorar o desempenho do servidor envolve avaliar o que o servidor está aguardando. As estatísticas de espera são complexas e o SQL Server é equipado com centenas de tipos de espera que monitoram cada thread em execução e registram o que o thread está esperando.

Para detectar e solucionar problemas de desempenho do SQL Server com eficiência, é essencial entender como as estatísticas de espera funcionam e como o mecanismo de banco de dados as utiliza durante o processamento de solicitações. Esse conhecimento permite identificar gargalos e otimizar o desempenho com mais precisão.

As estatísticas de espera são divididas em três tipos de espera: esperas de recurso, de fila e externas.

• As esperas de recurso ocorrem quando um thread de trabalho no SQL Server solicita acesso a um recurso que atualmente está sendo usado por um thread. Exemplos de espera de recursos são bloqueios, travas e esperas de E/S de disco.
• As esperas de fila acontecem quando um thread de trabalho está inativo e aguardando a atribuição de trabalho. Exemplos de esperas de fila são monitoramento de deadlock e limpeza de registros excluídos.
• As esperas externas ocorrem quando o SQL Server está aguardando a conclusão de um processo externo, como uma consulta de servidor vinculado. Um exemplo de espera externa é a espera de rede relacionada ao retorno de um conjunto de resultados grande para um aplicativo cliente.

Você pode verificar a exibição do sistema sys.dm_os_wait_stats para explorar todas as esperas encontradas pelos threads executados e sys.dm_db_wait_stats para o Banco de Dados SQL do Azure. O modo de exibição do sistema sys.dm_exec_session_wait_stats lista sessões de espera ativas.

Essas exibições do sistema permitem que você obtenha uma visão geral do desempenho do servidor e identifique prontamente problemas de configuração ou hardware. Esses dados são mantidos desde o momento da inicialização da instância, mas os dados podem ser limpos conforme necessário para identificar as alterações.

As estatísticas de espera são avaliadas como uma porcentagem do total de esperas no servidor.

O resultado desta consulta de sys.dm_os_wait_stats mostra o tipo de espera e a agregação da porcentagem de tempo de espera (coluna Porcentagem de Espera) e o tempo médio de espera em segundos para cada tipo de espera.

Nesse caso, o servidor tem Grupos de Disponibilidade Always On em vigor, conforme indicado pelos tipos de espera REDO_THREAD_PENDING_WORK e PARALLEL_REDO_TRAN_TURN. A porcentagem relativamente alta de esperas CXPACKET e SOS_SCHEDULER_YIELD indica que esse servidor está sob alguma pressão de CPU.

Como as DMVs fornecem uma lista de tipos de espera com o maior tempo acumulado desde a última inicialização do SQL Server, coletar e armazenar dados estatísticos de espera periodicamente pode ajudar você a entender e correlacionar problemas de desempenho com outros eventos de banco de dados.

Considerando que as DMVs fornecem uma lista de tipos de espera com o maior tempo acumulado desde a última inicialização do SQL Server, coletar e armazenar estatísticas de espera periodicamente pode ajudar você a entender e correlacionar problemas de desempenho com outros eventos do banco de dados.

Existem vários tipos de esperas disponíveis no SQL Server, mas alguns deles são comuns.

• RESOURCE_SEMAPHORE — indica que as consultas estão aguardando que a memória fique disponível, muitas vezes devido a concessões excessivas de memória a determinadas consultas. Esse problema normalmente se manifesta como runtimes de consulta longos ou até mesmo tempos limite. As causas desses tipos de espera podem incluir estatísticas desatualizadas, índices ausentes e alta simultaneidade de consulta.

• LCK_M_X — frequentemente indica um problema de bloqueio. Esse problema pode ser resolvido alterando para o READ COMMITTED SNAPSHOT nível de isolamento, otimizando a indexação para reduzir os tempos de transação ou melhorando o gerenciamento de transações no código T-SQL.

• PAGEIOLATCH_SH— esse tipo de espera pode indicar problemas com índices ou a ausência de índices úteis, fazendo com que o SQL Server examine quantidades excessivas de dados. Como alternativa, se a contagem de espera for baixa, mas o tempo de espera for alto, isso poderá sugerir problemas de desempenho de armazenamento. Você pode observar esse comportamento analisando os dados no waiting_tasks_count modo de exibição do sistema e wait_time_ms colunas sys.dm_os_wait_stats para calcular o tempo médio de espera de um determinado tipo de espera.

• SOS_SCHEDULER_YIELD – esse tipo de espera pode indicar alta utilização da CPU, que está correlacionada a um alto número de verificações grandes ou a índices ausentes, geralmente com grandes números de esperas CXPACKET.

• CXPACKET — Uma ocorrência alta desse tipo de espera pode indicar uma configuração inadequada. Antes do SQL Server 2019, a configuração padrão do maxdop (grau máximo de paralelismo) era usar todas as CPUs disponíveis para consultas. Além disso, o limite de custo para paralelismo foi definido como 5, o que poderia fazer com que pequenas consultas fossem executadas em paralelo, limitando a taxa de transferência. Para reduzir esse tipo de espera, você pode reduzir a configuração MAXDOP e aumentar o limite de custo para paralelismo. No entanto, o tipo de espera CXPACKET também pode indicar alta utilização da CPU, que normalmente é resolvida por meio do ajuste de índice.

• PAGEIOLATCH_UP — Esse tipo de espera nas páginas de dados 2:1:1 pode indicar a contenção de TempDB nas páginas de dados PFS (Espaço Livre de Página). Cada arquivo de dados tem uma página PFS por 64 MB de dados. Essa espera normalmente é causada por ter apenas um arquivo TempDB, já que antes do SQL Server 2016, o comportamento padrão era usar um arquivo de dados para o TempDB. A melhor prática para o TempDB é usar um arquivo por núcleo de CPU, até oito arquivos. Também é importante garantir que os arquivos de dados do TempDB tenham o mesmo tamanho e as mesmas configurações de aumento automático para assegurar que sejam usados uniformemente. O SQL Server 2016 e versões posteriores controlam o crescimento de arquivos de dados do TempDB para garantir que eles cresçam de maneira consistente e simultânea.

Além das DMVs mencionadas anteriormente, o Repositório de Consultas também acompanha as esperas associadas a consultas específicas. Embora os dados de espera acompanhados pelo Repositório de Consultas não sejam tão granulares quanto os dados nas DMVs, ele ainda fornece uma visão geral útil do que uma consulta está aguardando.