帶有查詢的等待類型

我有一個查詢來查找我的系統正在遭受的等待類型。

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
GO

查詢取自 Paul Randal 的部落格Wait statistics，或者請告訴我哪裡痛

現在有一種方法可以將等待類型與我的實際查詢相關聯。經過一些研究，我發現我們可以配置擴展事件來擷取具有等待類型的查詢。

通過任何 DMV 是否有任何其他選擇，我們可以得到等待類型以及查詢。

您可以使用 Adam Mechanics sp_whoisactive或以下腳本，並定期將結果插入表中以供以後分析。

Brent Ozar 有一篇關於如何做到這一點的好文章是你決定使用 sp_whoisactive

   SELECT
   [owt].[session_id],
   [owt].[exec_context_id],
   [owt].[wait_duration_ms],
   [owt].[wait_type],
   [owt].[blocking_session_id],
   [owt].[resource_description],
   CASE [owt].[wait_type]
       WHEN N'CXPACKET' THEN
           RIGHT ([owt].[resource_description],
           CHARINDEX (N'=', REVERSE ([owt].[resource_description])) - 1)
       ELSE NULL
   END AS [Node ID],
   [es].[program_name],
   [est].text,
   [er].[database_id],
   [eqp].[query_plan],
   [er].[cpu_time]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON
   [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON
   [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE
   [es].[is_user_process] = 1
ORDER BY
   [owt].[session_id],
   [owt].[exec_context_id];
GO

對於您的資訊，dm_os_waiting_tasks 是您的 sql 伺服器上目前發生的事情，dm_os_wait_stats 是過去/或自從您重新啟動 sql 或其服務以來發生的事情。

如果您使用 SQL Server 2016+，您可以使用新的動態管理視圖或 DMV，按會話查看目前連接會話的等待統計資訊：

SELECT *
FROM sys.dm_exec_session_wait_stats desws
WHERE desws.session_id = <session_id>;

替換<session_id>為您感興趣的 SPID。

輸出包含每種等待類型的一行，以及等待的任務數和等待的時間量。

引用自：https://dba.stackexchange.com/questions/167708