SHARE
TWEET

Wait Stats Over 1 Minute

SQLSoldier Feb 7th, 2017 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Declare @Waits Table (
  2.     WaitID int identity(1, 1) not null primary key,
  3.     wait_type nvarchar(60),
  4.     wait_time_s decimal(12, 2));
  5.  
  6. WITH Waits AS
  7. (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
  8.     100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
  9.     ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
  10. FROM sys.dm_os_wait_stats
  11. WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',
  12.   'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  13.   'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
  14. Insert Into @Waits (wait_type, wait_time_s)
  15. SELECT W1.wait_type,
  16.   CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
  17. FROM Waits AS W1
  18. INNER JOIN Waits AS W2
  19. ON W2.rn <= W1.rn
  20. GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
  21. HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
  22.  
  23. WaitFor Delay '0:01:00';
  24.  
  25. WITH Waits AS
  26. (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
  27.     100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
  28.     ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
  29. FROM sys.dm_os_wait_stats
  30. WHERE wait_type NOT IN( 'SLEEP_TASK', 'BROKER_TASK_STOP',
  31.   'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
  32.   'LAZYWRITER_SLEEP')) -- filter out additional irrelevant waits
  33. Insert Into @Waits (wait_type, wait_time_s)
  34. SELECT W1.wait_type,
  35.   CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s
  36. FROM Waits AS W1
  37. INNER JOIN Waits AS W2
  38. ON W2.rn <= W1.rn
  39. GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
  40. HAVING SUM(W2.pct) - W1.pct < 95; -- percentage threshold
  41.  
  42. Select wait_type, MAX(wait_time_s) - MIN(wait_time_s) WaitDelta
  43. From @Waits
  44. Group By wait_Type
  45. Order By WaitDelta Desc
RAW Paste Data
Pastebin PRO Summer Special!
Get 40% OFF on Pastebin PRO accounts!
Top