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
This content is published under the Attribution-Share Alike 3.0 Unported license.
Comments
-
Leif
-
statisticsio
My RSS
-
My LifeStream
My Presence
