SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : https://www.brentozar.com/responder/triage-wait-stats-in-sql-server/

Triage Wait Stats in SQL Server

Your SQL Server is online, but it’s incredibly slow. The old way to investigate this was to use SQL Server Perfmon counters, but the new way is to ask SQL Server what the bottleneck is.

While SQL Server is executing queries, it’s constantly tracking what it’s waiting on. For example, f a query has to wait ten seconds for data to come back from storage, SQL adds ten seconds to its total number of PAGEIOLATCH waits. We can query those running totals by looking at the dynamic management view (DMV) sys.dm_os_wait_stats.

The problem with sys.dm_os_wait_stats is that it’s cumulative – it just keeps adding and adding. If you only want to see what you’re waiting on now, that’s where the below query comes in.

First it returns the overall waits on the instance since SQL Server startup (or since the last time the wait data was manually cleared). Then it takes a 30 second sample of what your system is waiting on right now. This query will take 30 seconds to run, but don’t worry – it’s not blocking anyone.

1. /*
2. SQL Server Wait Information from sys.dm_os_wait_stats
3. Copyright (C) 2014, Brent Ozar Unlimited.
4. See http://BrentOzar.com/go/eula for the End User Licensing Agreement.
5. */
7. /*********************************
8. Let's build a list of waits we can safely ignore.
9. *********************************/
10. IF OBJECT_ID('tempdb..#ignorable_waits') IS NOT NULL
11. DROP TABLE #ignorable_waits;
12. GO
14. create table #ignorable_waits (wait_type nvarchar(256) PRIMARY KEY);
15. GO
17. /* We aren't using row constructors to be SQL 2005 compatible */
18. set nocount on;
19. insert #ignorable_waits (wait_type) VALUES ('REQUEST_FOR_DEADLOCK_SEARCH');
20. insert #ignorable_waits (wait_type) VALUES ('SQLTRACE_INCREMENTAL_FLUSH_SLEEP');
21. insert #ignorable_waits (wait_type) VALUES ('SQLTRACE_BUFFER_FLUSH');
22. insert #ignorable_waits (wait_type) VALUES ('LAZYWRITER_SLEEP');
23. insert #ignorable_waits (wait_type) VALUES ('XE_TIMER_EVENT');
24. insert #ignorable_waits (wait_type) VALUES ('XE_DISPATCHER_WAIT');
25. insert #ignorable_waits (wait_type) VALUES ('FT_IFTS_SCHEDULER_IDLE_WAIT');
26. insert #ignorable_waits (wait_type) VALUES ('LOGMGR_QUEUE');
27. insert #ignorable_waits (wait_type) VALUES ('CHECKPOINT_QUEUE');
28. insert #ignorable_waits (wait_type) VALUES ('BROKER_TO_FLUSH');
29. insert #ignorable_waits (wait_type) VALUES ('BROKER_TASK_STOP');
30. insert #ignorable_waits (wait_type) VALUES ('BROKER_EVENTHANDLER');
31. insert #ignorable_waits (wait_type) VALUES ('SLEEP_TASK');
32. insert #ignorable_waits (wait_type) VALUES ('WAITFOR');
33. insert #ignorable_waits (wait_type) VALUES ('DBMIRROR_DBM_MUTEX')
34. insert #ignorable_waits (wait_type) VALUES ('DBMIRROR_EVENTS_QUEUE')
35. insert #ignorable_waits (wait_type) VALUES ('DBMIRRORING_CMD');
36. insert #ignorable_waits (wait_type) VALUES ('DISPATCHER_QUEUE_SEMAPHORE');
37. insert #ignorable_waits (wait_type) VALUES ('BROKER_RECEIVE_WAITFOR');
38. insert #ignorable_waits (wait_type) VALUES ('CLR_AUTO_EVENT');
39. insert #ignorable_waits (wait_type) VALUES ('DIRTY_PAGE_POLL');
40. insert #ignorable_waits (wait_type) VALUES ('HADR_FILESTREAM_IOMGR_IOCOMPLETION');
41. insert #ignorable_waits (wait_type) VALUES ('ONDEMAND_TASK_QUEUE');
42. insert #ignorable_waits (wait_type) VALUES ('FT_IFTSHC_MUTEX');
43. insert #ignorable_waits (wait_type) VALUES ('CLR_MANUAL_EVENT');
44. insert #ignorable_waits (wait_type) VALUES ('SP_SERVER_DIAGNOSTICS_SLEEP');
45. insert #ignorable_waits (wait_type) VALUES ('QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP');
46. insert #ignorable_waits (wait_type) VALUES ('QDS_PERSIST_TASK_MAIN_LOOP_SLEEP');
47. GO
49. /* Want to manually exclude an event and recalculate?*/
50. /* insert #ignorable_waits (wait_type) VALUES (''); */
52. /*********************************
53. What are the highest overall waits since startup?
54. *********************************/
55. SELECT TOP 25
56. os.wait_type,
57. SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
58. CAST(
59. 100.* SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type)
60. / (1. * SUM(os.wait_time_ms) OVER () )
61. AS NUMERIC(12,1)) as pct_wait_time,
62. SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks,
63. CASE WHEN SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) > 0
64. THEN
65. CAST(
66. SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type)
67. / (1. * SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type))
68. AS NUMERIC(12,1))
69. ELSE 0 END AS avg_wait_time_ms,
70. CURRENT_TIMESTAMP as sample_time
71. FROM sys.dm_os_wait_stats os
72. LEFT JOIN #ignorable_waits iw on
73. os.wait_type=iw.wait_type
74. WHERE
75. iw.wait_type is null
76. ORDER BY sum_wait_time_ms DESC;
77. GO
79. /*********************************
80. What are the higest waits *right now*?
81. *********************************/
83. /* Note: this is dependent on the #ignorable_waits table created earlier. */
84. if OBJECT_ID('tempdb..#wait_batches') is not null
85. drop table #wait_batches;
86. if OBJECT_ID('tempdb..#wait_data') is not null
87. drop table #wait_data;
88. GO
90. CREATE TABLE #wait_batches (
91. batch_id int identity primary key,
92. sample_time datetime not null
93. );
94. CREATE TABLE #wait_data
95. ( batch_id INT NOT NULL ,
96. wait_type NVARCHAR(256) NOT NULL ,
97. wait_time_ms BIGINT NOT NULL ,
98. waiting_tasks BIGINT NOT NULL
99. );
100. CREATE CLUSTERED INDEX cx_wait_data on #wait_data(batch_id);
101. GO
103. /*
104. This temporary procedure records wait data to a temp table.
105. */
106. if OBJECT_ID('tempdb..#get_wait_data') IS NOT NULL
107. DROP procedure #get_wait_data;
108. GO
109. CREATE PROCEDURE #get_wait_data
110. @intervals tinyint = 2,
111. @delay char(12)='00:00:30.000' /* 30 seconds*/
112. AS
113. DECLARE @batch_id int,
114. @current_interval tinyint,
115. @msg nvarchar(max);
117. SET NOCOUNT ON;
118. SET @current_interval=1;
120. WHILE @current_interval <= @intervals
121. BEGIN
122. INSERT #wait_batches(sample_time)
123. SELECT CURRENT_TIMESTAMP;
125. SELECT @batch_id=SCOPE_IDENTITY();
127. INSERT #wait_data (batch_id, wait_type, wait_time_ms, waiting_tasks)
128. SELECT
129. @batch_id,
130. os.wait_type,
131. SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
132. SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
133. FROM sys.dm_os_wait_stats os
134. LEFT JOIN #ignorable_waits iw on
135. os.wait_type=iw.wait_type
136. WHERE
137. iw.wait_type is null
138. ORDER BY sum_wait_time_ms DESC;
140. set @msg= CONVERT(char(23),CURRENT_TIMESTAMP,121)+ N': Completed sample '
141. + cast(@current_interval as nvarchar(4))
142. + N' of ' + cast(@intervals as nvarchar(4)) +
143. '.'
144. RAISERROR (@msg,0,1) WITH NOWAIT;
146. SET @current_interval=@current_interval+1;
148. if @current_interval <= @intervals
149. WAITFOR DELAY @delay;
150. END
151. GO
153. /*
154. Let's take two samples 30 seconds apart
155. */
156. exec #get_wait_data @intervals=2, @delay='00:00:30.000';
157. GO
159. /*
160. What were we waiting on?
161. This query compares the most recent two samples.
162. */
163. with max_batch as (
164. select top 1 batch_id, sample_time
165. from #wait_batches
166. order by batch_id desc
167. )
168. SELECT
169. b.sample_time as [Second Sample Time],
170. datediff(ss,wb1.sample_time, b.sample_time) as [Sample Duration in Seconds],
171. wd1.wait_type,
172. cast((wd2.wait_time_ms-wd1.wait_time_ms)/1000. as numeric(12,1)) as [Wait Time (Seconds)],
173. (wd2.waiting_tasks-wd1.waiting_tasks) AS [Number of Waits],
174. CASE WHEN (wd2.waiting_tasks-wd1.waiting_tasks) > 0
175. THEN
176. cast((wd2.wait_time_ms-wd1.wait_time_ms)/
177. (1.0*(wd2.waiting_tasks-wd1.waiting_tasks)) as numeric(12,1))
178. ELSE 0 END AS [Avg ms Per Wait]
179. FROM max_batch b
180. JOIN #wait_data wd2 on
181. wd2.batch_id=b.batch_id
182. JOIN #wait_data wd1 on
183. wd1.wait_type=wd2.wait_type AND
184. wd2.batch_id - 1 = wd1.batch_id
185. join #wait_batches wb1 on
186. wd1.batch_id=wb1.batch_id
187. WHERE (wd2.waiting_tasks-wd1.waiting_tasks) > 0
188. ORDER BY [Wait Time (Seconds)] DESC;
189. GO

How to Interpret the Output

Here’s our favorite links for performance tuning SQL Server with wait stats:

INTRODUCTIONS TO WAIT STATS

• The Ozar Family Tradition of Performance Monitoring – When my grandfather needed to tune his SQL Servers, he used Perfmon, and he passed his knowledge on down through my father to me.  Today, though, we’ve got a better way – wait stats – and I explain how they’re different.
• Waits and Queues Whitepaper – by the Microsoft SQL Server Customer Advisory Team, aka SQLCAT.
• Our Book – Professional SQL Server Internals and Troubleshooting – in chapter 3, we discuss how the SQL Server engine tracks what it’s waiting for with wait statistics.

EXPLANATIONS OF EACH WAIT TYPE

• sys.dm_os_wait_stats in Books Online – very brief one-line explanations of many of the common waits.
• Service Broker Wait Types – even if you’re not using this obscure SQL Server feature, the engine itself relies on it.
• Technet Magazine – SQL Server Delays Demystified – introductory article from prominent community members.
• Don’t Fix CXPACKET with MAXDOP = 1 – From my Consulting Lines series post, “SQL Server Needs a Dog.”

TOOLS TO ANALYZE WAITS AND QUEUES

• sp_WhoIsActive from Adam Machanic – this awesome free replacement for sp_who shows you what’s REALLY happening inside your database server including the queries, wait statistics, and locking/blocking issues.  Call it with @get_plans = 1 to see execution plans, too.
• SQLNexus – when you want to analyze performance over time, use this free tool from Microsoft to generate pretty graphs of your SQL Server’s activity.  Not for the faint of heart, but we discuss it in detail in chapter 13 of our book, Professional SQL Server 2008 Internals and Troubleshooting.

Next Steps to Learn More

This query is part of our free SQL Server First Responder Kit. It’s checklists and tools to help you fix performance problems fast.

Check out our free training videos – every Tuesday, we offer free webcasts, and the past recorded episodes are available in our Video Archives.

And for even more advanced details, read about our upcoming in-person training classes.