O que são waits(esperas) no SQL Server?

por viniciusfonsecadba, publicado em Administração, DBA, Dicas, Scripts, SQL Server

Fala turma, tudo bem?

Antes de explicar o que são os waits, já quero deixar claro aqui que se você estiver iniciando na carreira e pensando em fazer tuning ou troubleshooting de queries ou determinados comportamentos do sql server, você precisa conhecer e conviver bem com a análise das estítiscas dos waits.

Mas vamos lá, o que são os waits?

A grosso modo são esperas que o sql registra quando uma thread que está em execução (a execução de uma query, por exemplo) no processador não pode prosseguir porque precisa de um recurso que não está disponível no momento, então o SQL vai ter que esperar esse recurso ficar disponível.

O recurso que está sendo esperado, é rastreado pelo SQL Server, que por sua vez, mantém o controle de todos os diferentes recursos, com que frequência eles devem ser “esperados” e por quanto tempo são esperados por todas as threads.

Cada um dos vários recursos é mapeado para um tipo de espera e são os tipos de espera que obtemos em algumas DMVs.

Esses registros são armazenados em memória e dão insumo para os DBA’s avaliarem diversos tipos de problemas, portanto, vale a pensa se aprofundar no tema, aprender a analisar os wait statistics (estatísticas de espera).

Vejam esse exemplo de estatísticas de uma instância que tive acesso a pouco tempo:

A query que utilizei para levantar essas informações, foi:

-- Retorna Wait Statistics
WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT',
		N'SOS_WORK_DISPATCHER')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

Temos 3 tipos de waits no SQL Server.

As esperas de recursos ocorrem quando um trabalhador solicita acesso a um recurso que não está disponível porque o recurso está sendo usado por algum outro trabalho ou ainda não está disponível. Exemplos de esperas de recursos são bloqueios, travas, esperas de E/S de rede e disco. Bloqueio e trava são esperas em objetos de sincronização.

As esperas da fila ocorrem quando um trabalhador está ocioso, aguardando o trabalho ser atribuído. As esperas de fila são geralmente vistas com tarefas em segundo plano de sistema, como monitor de deadlocks e tarefas de limpeza de registro excluídas. Essas tarefas aguardarão as solicitações de trabalho serem colocadas em uma fila de trabalho. Esperas de fila também poderão ficar periodicamente ativas mesmo se nenhum pacote novo tiver sido colocado na fila.

As esperas externas ocorrem quando um SQL Server trabalho está aguardando um evento externo, como uma chamada de procedimento armazenado estendido ou uma consulta de servidor vinculado, para concluir. Ao diagnosticar problemas de bloqueio, lembre-se de que as esperas externas nem sempre implicam que o trabalho está ocioso, pois o trabalho pode estar executando ativamente algum código externo.

Você pode saber mais, inclusive todos as esperas por cada tipo clicando nesse link.

As estatísticas de espera não são persistentes, ou seja, após a reinicialização do mecanismo de banco de dados e todos os dados são cumulativos desde a última vez em que as estatísticas foram redefinidas ou o mecanismo de banco de dados foi iniciado. Use a coluna sqlserver_start_time em sys.dm_os_sys_info para localizar a última hora de inicialização do mecanismo de banco de dados.

Para redefinir ou reiniciar a contagem dessas estatísticas, você pode utilizar o comando. Você não deve fazer isso em produção, até ter certeza do impacto, principalmente, de análise que vc pode gerar na sua equipe ou outras pessoas que possam utilizar essa informação.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO

Para finalizar esse post introdutório no assunto, quero apenas deixar claro que nem todos os waits precisam ser investigados em um processo de resolução de problema, pois muitos waits ocorrem pq o SQL registro todas as esperas, sendo elas boas ou ruins. Então, entenda o contexto geral dos waits e tente entrar no detalhe dos que são considerados waits causadores de problemas, por exemplo, como eu eliminei diversos waits na query citada acima.

Então é isso pessoal, obrigado e vamos que vamos!

Abs

Deixe um comentário