需要在“等待任務”上設置 sql 警報 - 類似於 Activity Monitor 中的“等待任務”

我的經理要求我應要求工作。我不確定這是否合理或奇怪，但他希望我根據我們在活動監視器中看到的“等待任務”設置警報。基本上，如果“等待任務”中的值超過 10，那麼我們需要收到警報。我不確定如何複製我們在活動監視器中看到的相同資訊，因為這似乎是實時數據。最近的 DMV 和我能想到的列是 sys.dm_os_wait_stats 中的“waiting_tasks_count”。但是，這似乎與他要查找的內容不符，並且該列包含自上次重新啟動 SQL 以來一段時間內的累積值。有人可以說明我在哪裡可以找到可以發出警報的查詢或計數器嗎？

我此時考慮過一個查詢，但我認為我無法正確計算增量，它總是向我顯示 1。有人可以幫我準確地找出我在這裡出錯的地方。

select  so1.wait_type,
       so1.waiting_tasks_count as no_of_waits,
       so1.waiting_tasks_count - so2.waiting_tasks_count as delta_wait_count,
       so1.signal_wait_time_ms,
       so1.wait_time_ms - so2.signal_wait_time_ms as resource_wait_time,
       getdate() as sample_time
from  sys.dm_os_wait_stats as so1
inner join sys.dm_os_wait_stats as so2
on so2.waiting_tasks_count = so1.waiting_tasks_count - 1

您可以在探查器中找出確切的查詢。下面是您所指的查詢

WITH profiled_sessions as (
   SELECT DISTINCT session_id profiled_session_id from sys.dm_exec_query_profiles
)
SELECT 
  [Session ID]    = s.session_id, 
  [User Process]  = CONVERT(CHAR(1), s.is_user_process),
  [Login]         = s.login_name,   
  [Database]      = case when p.dbid=0 then N'' else ISNULL(db_name(p.dbid),N'') end, 
  [Task State]    = ISNULL(t.task_state, N''), 
  [Command]       = ISNULL(r.command, N''), 
  [Application]   = ISNULL(s.program_name, N''), 
  [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
  [Wait Type]     = ISNULL(w.wait_type, N''),
  [Wait Resource] = ISNULL(w.resource_description, N''), 
  [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
  [Head Blocker]  = 
       CASE 
           -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
           WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1' 
           -- session is either not blocking someone, or is blocking someone but is blocked by another party
           ELSE ''
       END, 
  [Total CPU (ms)] = s.cpu_time, 
  [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024, 
  [Memory Use (KB)]  = s.memory_usage * (8192 / 1024), 
  [Open Transactions] = ISNULL(r.open_transaction_count,0), 
  [Login Time]    = s.login_time, 
  [Last Request Start Time] = s.last_request_start_time,
  [Host Name]     = ISNULL(s.host_name, N''),
  [Net Address]   = ISNULL(c.client_net_address, N''), 
  [Execution Context ID] = ISNULL(t.exec_context_id, 0),
  [Request ID] = ISNULL(r.request_id, 0),
  [Workload Group] = ISNULL(g.name, N''),
  [Profiled Session Id] = profiled_session_id
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN 
(
   -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as 
   -- waiting for several different threads.  This will cause that thread to show up in multiple rows 
   -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread, 
   -- and use it as representative of the other wait relationships this thread is involved in. 
   SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
   FROM sys.dm_os_waiting_tasks 
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
LEFT OUTER JOIN profiled_sessions ON profiled_session_id = s.session_id
ORDER BY s.session_id;

您可以在警報下的 SQL 代理中執行此操作。（此圖像狀態高於 9 而不是您想要的 10，但概念是相同的。在“響應”選項卡上，您可以選擇執行工作或發送電子郵件。

這只會給你 Lock Waits you。

創建“新警報”

配置警報以在等待時觸發：

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