Neste artigo, vamos continuar abordando o tema desenvolvido no artigo anterior, sobre a análise do desempenho do banco de dados e estudando o funcionamento das travas, transações em espera e seu impacto sobre o desempenho do servidor SQL Server.

Status de espera agregados: sys.dm_os_wait_stats

As estatísticas sobre agregados do servidor SQL incidem sobre todos os tipos de espera e as expõe em uma tabela chamada sys.dm_os_wait_stats. Ao olhar para as solicitações atualmente em execução e as tarefas em espera, vemos o que estava sendo esperado a qualquer momento, e as estatísticas agregadas mostram uma visão geral do que foi acumulado desde a inicialização do servidor. Consultar esse DMV é simples, mas a interpretação dos resultados é um pouco mais complicada:

select * 
from sys.dm_os_wait_stats
order by wait_time_ms desc;

Primeiro, quais são todos esses tipos de espera no topo do resultado? DIRTY_PAGE_POOL, REQUEST_FOR_DEADLOCK_SEARCH e LAZYWRITER_SLEEP? Não vemos qualquer pedido esperando por isso! Aqui está o motivo: ao adicionar WHERE session_id> = 50, filtramos as tarefas em segundo plano, regras internas que o SQL Server possui e que têm a tarefa de realizar vários trabalhos de manutenção. Muitas dessas tarefas em segundo plano seguem um padrão como “esperar por um determinado evento; quando o evento estiver sinalizado como executando algum trabalho, então espere novamente”, enquanto outras são padronizados em um tipo de ciclo de espera (ou sleep) (“aguardar por cinco segundos, acordar e fazer algo, voltar para espera durante cinco segundos”). Uma vez que nenhuma tarefa pode terminar sem fornecer algum tipo de espera, existem tipos de espera para capturar o momento em que essas tarefas em segundo plano terminam. Como esses padrões de execução exultam na tarefa sendo realmente suspensa quase todo o tempo, o tempo de espera agregado para cada tarefa muitas vezes supera qualquer outro tipo de espera. A comunidade de usuários SQL Server veio para citar esses tipos de espera para soluções “benignas” para resolver isso, e a maioria dos especialistas tem uma lista de tipos de espera que podem simplesmente filtrar sem gastar processamento de máquina e sem interferir em outros processos. Leia a documentação Filtros de espera benignos para mais detalhes.

select * 
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;

Agora temos um quadro mais coerente das transações que possuem espera. A imagem anterior nos diz o que esperar e quais tipos de espera são aqueles que prevalecem, em conjunto, nessa instância do SQL Server. Isso pode ser um passo importante para a identificação de um potencial problema de desempenho. Além disso, a interpretação dos dados é subjetiva. O valor agregado WRITELOG de 4636805 milissegundos é bom ou ruim? Eu não sei! É um valor agregado, com valores acumulados desde que o processo está em execução, então, obviamente, irá aumentar de forma crescente. Talvez ele tinha acumulado valores de períodos em que o servidor estava executando sem transações e de períodos em que ele estava sobrecarregado. No entanto, agora sei que, a partir de todos os tipos “não benignos” de espera, o WRITELOG é o que tem o maior tempo total de espera.

Sabemos também qual é o max_wait_time para esses tipos de espera, por isso sei que houve pelo menos uma vez quando uma tarefa teve que esperar 1,4 segundo para o registro ficar limpo. O wait_task_count me diz quanto tempo uma tarefa esperou por um tipo de espera em particular, e dividindo wait_time_ms/wait_task_count vai dizer o tempo médio que um tipo de espera em particular tem aguardado. Vemos vários tipos de espera de bloqueio relacionados uns com os outros logo acima: LCK_M_S, LCK_M_IS e LCK_M_IX. Com isso, já posso dizer algumas coisas sobre o comportamento geral do servidor, do ponto de vista do desempenho: a consistência do log é o mais esperado dos recursos do servidor e a contenção de bloqueio parece ser o próximo grande problema que o CXPACKET espera dali em diante, mas sabemos que, na maioria das vezes, o CXPACKET espera itens que são apenas substitutos para outros tipos de espera, como discutido anteriormente.

Vamos aprender, no próximo artigo, outros tipos na lista, como SOS_SCHEDULER_YIELD, PAGELATCH_EX SH e PAGEIOLATCH_EX/SH/UP, como funcionam e por que existem.

http://rusanu.com/2014/02/24/how-to-analyse-sql-server-performance/