Olá,
Postando aqui um script de uso pessoal, segue um exemplo de coleta de waitstats baseada em snapshot: ideia antiga porém efetiva: realiza a coleta das esperas de determinado servidor em momento A e momento B e retorna a diferença.
A ideia é que, pelo fato da DMV ser cumulativa, uma consulta direta não responde muita coisa além de dar uma ideia geral (e bem abstrata) de quais waits possuem maior número de ocorrências. O problema é que estes dados isolados são difíceis de interpretar.
Já com snapshots, é possível verificar a contagem das esperas por intervalo de tempo de modo e isso auxilia demais análises de troubleshooting (muito mais efetivo se isso é feito no início de qualquer análise), sendo a diferença um número mais factível tendo algum contexto temporal.
Esperas são eventos normais e a maioria delas podem ser ignoradas em análises de troubleshooting e elas estão retratadas nos filtros via NOT IN.
O script abaixo pede apenas um parâmetro em segundos e processa as coletas.
/****************************************************************************************
Nome: sp_waitstats
Descrição: Tira a diferença entre Snapshot de esperas do servidor.
Existe apenas um único parâmetro de entrada, em segundos, que representa o intervalo que deve ser coletado (valor padrão: 60 segundos).
*****************************************************************************************/
CREATE PROCEDURE dbo.sp_waitstats
(
@Segundos SMALLINT = 60
)
AS
BEGIN
IF @Segundos <= 0
BEGIN
PRINT 'Insira um intervalo de coleta, em segundos, maior e diferente de zero.';
END
PRINT 'Aguarde a coleta : '+CAST(@Segundos AS CHAR(3))+' segundos';
-- As amostras serão coletadas e jogadas em duas tabelas temporárias diferentes
IF EXISTS (select * from tempdb.sys.tables where name LIKE '%#SnapshotWaitstats1%')
BEGIN
DROP TABLE TEMPDB..#SnapshotWaitstats1;
END
IF EXISTS (select * from tempdb.sys.tables where name LIKE '%#SnapshotWaitstats2%')
BEGIN
DROP TABLE TEMPDB..#SnapshotWaitstats2;
END
-- Primeira coleta
PRINT 'Primeira coleta realizada às '+CONVERT(CHAR,GETDATE(),108);
DECLARE @DataInicio DATETIME = GETDATE();
SELECT
wait_type,
waiting_tasks_count,
CAST(wait_time_ms AS DECIMAL(18,2)) AS wait_time_ms ,
CAST(max_wait_time_ms AS DECIMAL(18,2)) AS max_wait_time_ms,
CAST(signal_wait_time_ms AS DECIMAL(18,2)) AS signal_wait_time_ms,
CAST(wait_time_ms - signal_wait_time_ms AS DECIMAL(18,2)) AS Resource_wait_time_ms
INTO #SnapshotWaitstats1
FROM sys.dm_os_wait_stats (nolock)
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'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')
AND waiting_tasks_count > 0
/**********************************************************************************************
-- Simula o delay em segundos (de acordo com parâmetro) para realizar a segunda coleta
***********************************************************************************************/
DECLARE @DataFim AS DATETIME = DATEADD(s,@Segundos,GETDATE())
WAITFOR TIME @DataFim
-- Segunda Coleta
PRINT 'Segunda coleta realizada às '+CONVERT(CHAR,@DataFim,108);
SELECT
wait_type,
waiting_tasks_count,
CAST(wait_time_ms AS DECIMAL(18,2)) AS wait_time_ms ,
CAST(max_wait_time_ms AS DECIMAL(18,2)) AS max_wait_time_ms,
CAST(signal_wait_time_ms AS DECIMAL(18,2)) AS signal_wait_time_ms,
CAST(wait_time_ms - signal_wait_time_ms AS DECIMAL(18,2)) AS Resource_wait_time_ms
INTO #SnapshotWaitstats2
FROM sys.dm_os_wait_stats (nolock)
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'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')
AND waiting_tasks_count > 0;
-- Calculando diferença entre as duas coletas:..
SELECT TOP 20
c1.wait_type as [Wait],
c2.waiting_tasks_count - c1.waiting_tasks_count as [Quantidade],
CAST((c2.wait_time_ms - c1.wait_time_ms)/1000. AS DECIMAL (18,2)) AS [Total (seg)],
CAST((C2.Resource_wait_time_ms - C1.Resource_wait_time_ms)/1000. AS DECIMAL(18,2)) AS [Recurso (seg)],
CAST((C2.signal_wait_time_ms - C1.signal_wait_time_ms)/1000. AS DECIMAL(18,2)) AS [Signal (seg)],
@DataInicio as DataInício,
@DataFim as DataFim
FROM #SnapshotWaitstats1 c1 INNER JOIN #SnapshotWaitstats2 c2 ON C2.wait_type = C1.wait_type
ORDER BY [Quantidade] DESC, [Total (seg)] DESC
END
[]’s
Referências
MSDN – sys.dm_os_wait_stats
Script Paul Randal: Script Paul Randal: