daily pastebin goal
69%
SHARE
TWEET

Wait Stats Over 1 Minute

SQLSoldier Feb 7th, 2017 81 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top