Firefighting with Wait Stats

Wait stats analysis is a great skill to have in your arsenal. There are lots of tools out there already. I also suggest you read Tom Davidson's whitepaper on it if you need background on this topic. There was a niche missing in my toolbox. You see, these stats are cumulative since the instance has started or you clear with DBCC SQLPERF(WAITSTATS, CLEAR).

Whatever sys.dm_os_wait_stats reports as the highest waiter may have happened last week. If you want to know what is hammering the server now, you can look at several different DMV's like sys.dm_exec_requests but that data is transient. My rough code (all my code is rough) below will tell you what the server has been waiting for in the last second of time. It is definitely a firefighting "WTF is going on right now" query. It would also complement this query when fighting fires.

Read the whitepaper for a better explanation but, basically, there are two buckets of waits. Signal waits which are actual waits on CPU execution time. The others are resource waits which means SQL is waiting on other stuff like a locks, latches, log writes, memory etc. Again the whitepaper does a great job correlating these sometimes cryptic names with resources.

Lastly, analyzing wait types goes hand and hand with perfmon. For example, you have both high CPU and high IO in perfmon. This script can help you see what SQL is actually waiting on the most so you can tackle that problem first.

SELECT wait_type
,signal_wait_time_ms AS 'CPU'
,wait_time_ms - signal_wait_time_ms AS 'Resource'
INTO #temp
FROM sys.dm_os_wait_stats
WHERE wait_time_ms <> 0

WAITFOR DELAY '00:00:01'

SELECT a.wait_type
, signal_wait_time_ms-cpu AS CPUDiff
, (wait_time_ms - signal_wait_time_ms)-[resource] AS ResourceDiff
FROM sys.dm_os_wait_stats a JOIN #TEMP b ON a.wait_type=b.wait_type
ORDER BY 2 DESC

DROP TABLE #temp

email it! |   |   |   |  | 

Permalink     8 Comments  

Rate this Post: 12345
COMMENTS:

A bit of a plug:

My Who is Active? script helps with snapshot wait analysis, at the session level... Enable the @GET_WAITS option to see the number of threads currently waiting on each wait type, along with information on how long the waits have been going and, if the waits are for "special" data pages (IAM, GAM, SGAM, PFS, etc), information on those:

http://sqlblog.com/blogs/adam_machanic/archive/2009/02/18/who-is-active-v7-30.aspx

posted @ Tuesday, February 24, 2009 11:02 AM by Adam Machanic

---

Actually, not IAM. Obviously I need some more coffee...

posted @ Tuesday, February 24, 2009 11:03 AM by Adam Machanic

---

None of this is available in SQL 2000, correct?

posted @ Tuesday, February 24, 2009 11:27 AM by David Stein

---

Yep. 2000 only.

Adam - I would totally switch over to who's active if you added option for a Read and CPU diff's like this http://statisticsio.com/Home/tabid/36/articleType/ArticleView/articleId/169/categoryId/14/CXPACKET-MAXDOP-and-your-OLTP-system.aspx


Maybe I'll just wrap it and dump it to temp tables.

posted @ Tuesday, February 24, 2009 12:31 PM by JasonMassie

---

A few years ago in SQL Mag Brian Moran wrote that Tom Davidson's article published a few months prior--I'm paraphrasing--was the first fundamentally new approach for performance tuning to come out in years. That got my attention, & I've been using them ever since.

Not onlly do wait stats rock, you've leveraged another powerful trick--taking a delta.

Nicely done, Jason.

posted @ Tuesday, February 24, 2009 12:35 PM by Jimmy May

---

Jason, I'm on it. Watch for the next version, coming soon...

posted @ Tuesday, February 24, 2009 1:25 PM by Adam Machanic

---

Comments from the following blog entry: Wait Stats Introductory References, located at: http://blogs.msdn.com/jimmymay/archive/2009/04/26/wait-stats-introductory-references.aspx

posted @ Monday, April 27, 2009 10:17 AM by Jimmy May, Aspiring Geek: SQL Server Performance, Best Practices, Productivity, etc.

---

Comments from the following blog entry: http://www.brentozar.com/archive/2009/07/the-ozar-family-tradition-of-performance-monitoring/

posted @ Thursday, July 23, 2009 8:02 AM

---

Name (required)

Email (required)

Website

Notify me of followup comments via e-mail

Simple BBCode can be used like [url=http://example.com]Example[/url] and [B]