Dicas para um DBA Iniciante – Listando os principais Wait Types da instância

Fala pessoas!!!!!

A dica de hoje é para ajudá-los a encontrar os principais wait types existentes em seu ambiente de banco de dados.

Esta consultar foi escrita pelo time do SQLSkills e é bem completa, pois já retorna também o link para a documentação escrita pelo Paul Randal e que explica minuciosamente cada um dos wait types encontrados e como resolvê-los.

[code language=”sql”]
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’,
— Maybe uncomment these four if you have mirroring issues
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’,
— Maybe uncomment these six if you have AG issues
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’MEMORY_ALLOCATION_EXT’,
N’ONDEMAND_TASK_QUEUE’,
N’PREEMPTIVE_XE_GETTARGETSTATE’,
N’PWAIT_ALL_COMPONENTS_INITIALIZED’,
N’PWAIT_DIRECTLOGCONSUMER_GETNEXT’,
N’QDS_PERSIST_TASK_MAIN_LOOP_SLEEP’
,
N’QDS_ASYNC_QUEUE’,
N’QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP’,
N’QDS_SHUTDOWN_QUEUE’,
N’REDO_THREAD_PENDING_WORK’,
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_RECOVERY’, 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)
SELECT Max ([W1].[wait_type])
AS
[WaitType],
Cast (Max ([W1].[waits]) AS DECIMAL (16, 2))
AS [Wait_S],
Cast (Max ([W1].[resources]) AS DECIMAL (16, 2))
AS [Resource_S],
Cast (Max ([W1].[signals]) AS DECIMAL (16, 2))
AS [Signal_S],
Max ([W1].[waitcount])
AS [WaitCount],
Cast (Max ([W1].[percentage]) AS DECIMAL (5, 2))
AS [Percentage],
Cast (( Max ([W1].[waits]) / Max ([W1].[waitcount]) ) AS DECIMAL (16, 4))
AS
[AvgWait_S],
Cast (( Max ([W1].[resources]) / Max ([W1].[waitcount]) ) AS
DECIMAL (16, 4)) AS
[AvgRes_S],
Cast (( Max ([W1].[signals]) / Max ([W1].[waitcount]) ) AS
DECIMAL (16, 4)) AS
[AvgSig_S],
Cast (‘https://www.sqlskills.com/help/waits/’
+ Max ([W1].[wait_type]) AS XML)
AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[rownum] <= [W1].[rownum]
GROUP BY [W1].[rownum]
HAVING Sum ([W2].[percentage]) – Max([W1].[percentage]) < 95; — percentage threshold

[/code]

Espero que gostem e não deixem de inscreverem-se no blog, no canal do youtube, no grupo de discussão SQLManiacs  e em nosso grupo no TELEGRAM.

Grande abraço a todos.

1 pensou em “Dicas para um DBA Iniciante – Listando os principais Wait Types da instância

  1. Pingback: Dicas para um DBA Iniciante – Listando os principais Wait Types da instância – DBA BRASIL

Deixe uma resposta