Descrever estatísticas de espera

Concluído

Uma maneira holística de monitorar o desempenho do servidor é avaliar o que o servidor está esperando. As estatísticas de espera são complexas e o SQL Server é instrumentado com centenas de tipos de espera, que monitora cada thread em execução e registra o que o thread está aguardando.

Detetar e solucionar problemas de desempenho do SQL Server requer uma compreensão de como as estatísticas de espera funcionam e como o mecanismo de banco de dados as usa durante o processamento de uma solicitação.

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

• As esperas de recursos ocorrem quando um thread de trabalho no SQL Server solicita acesso a um recurso que está sendo usado atualmente por um thread. Exemplos de recursos aguardados são bloqueios, travas e espera de E/S de disco.
• As esperas na fila ocorrem quando um thread de trabalho está ocioso e aguardando a atribuição de trabalho. Exemplos de espera 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 é uma espera de rede relacionada ao retorno de um grande conjunto de resultados para um aplicativo cliente.

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

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

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

O resultado dessa consulta 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 sys.dm_os_wait_stats 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 percentagem relativamente elevada de espera CXPACKET e SOS_SCHEDULER_YIELD indica que este servidor está sob alguma pressão da CPU.

Como os 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 ajudá-lo a entender e correlacionar problemas de desempenho com outros eventos de banco de dados.

Considerando que os 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 ajudá-lo a entender e correlacionar problemas de desempenho com outros eventos de banco de dados.

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

• RESOURCE_SEMAPHORE—Esse tipo de espera é indicativo de consultas aguardando na memória para ficar disponível e pode indicar concessões excessivas de memória para algumas consultas. Esse problema geralmente é observado por longos tempos de execução de consulta ou até mesmo tempos limites. Esses tipos de espera podem ser causados por estatísticas desatualizadas, índices ausentes e simultaneidade excessiva de consultas.

• LCK_M_X—ocorrências frequentes desse tipo de espera podem indicar um problema de bloqueio, que pode ser resolvido alterando para o nível de isolamento ou fazendo alterações na indexação para reduzir os READ COMMITTED SNAPSHOT tempos de transação ou, possivelmente, um melhor gerenciamento de transações no código T-SQL.

• PAGEIOLATCH_SH—esse tipo de espera pode indicar um problema com índices (ou falta de índices úteis), em que o SQL Server está verificando muitos dados. Como alternativa, se a contagem de espera for baixa, mas o tempo de espera for alto, isso pode indicar problemas de desempenho de armazenamento. Você pode observar esse comportamento analisando os dados nas colunas waiting_tasks_count e wait_time_ms na visualização do sys.dm_os_wait_stats sistema, para calcular um tempo médio de espera para um determinado tipo de espera.

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

• CXPACKET—se esse tipo de espera for alto, pode indicar configuração incorreta. Antes do SQL Server 2019, a configuração padrão do grau máximo de paralelismo é usar todas as CPUs disponíveis para consultas. Além disso, o limite de custo para a configuração de paralelismo é padrão para 5, o que pode levar a pequenas consultas sendo executadas em paralelo, o que pode limitar a taxa de transferência. Reduzir o MAXDOP e aumentar o limite de custo para paralelismo pode reduzir esse tipo de espera, mas o tipo de espera CXPACKET também pode indicar alta utilização da CPU, que normalmente é resolvida por meio de ajuste de índice.

• PAGEIOLATCH_UP—esse tipo de espera nas páginas de dados 2:1:1 pode indicar a contenção do TempDB nas páginas de dados do Espaço Livre de Página (PFS). Cada ficheiro de dados tem uma página PFS por cada 64 MB de dados. Essa espera geralmente é 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 prática recomendada é usar um arquivo por núcleo de CPU até oito arquivos. Também é importante garantir que seus arquivos de dados do TempDB tenham o mesmo tamanho e as mesmas configurações de crescimento automático para garantir que sejam usados uniformemente. O SQL Server 2016 e superior controlam o crescimento dos arquivos de dados do TempDB para garantir que eles cresçam de forma consistente e simultânea.

Além dos DMVs acima mencionados, o Repositório de Consultas também rastreia as esperas associadas a uma determinada consulta. No entanto, os dados de espera rastreados pelo Repositório de Consultas não são rastreados com a mesma granularidade que os dados nos DMVs, mas podem fornecer uma boa visão geral do que uma consulta está aguardando.