I am not a fan of borrowing from others, but with this blog I would like to show you a cool SQL-statement that can be used to understand the data from the dynamic management view sys.dm_os_wait_stats.
This views gives you information about waits on the SQL Server. It gives you information about waits since the last restart of the instance or since you last flushed the data with
dbcc sqlperf(‘sys.dm_os_wait_stats’, clear)
If you write the following SQL statement, then you get the result from the view, and you see a lot of rows and columns, that basically are very difficult to understand.
Paul Randal from SQL Skills, has published a script on his blog, where he has included his brilliant understanding of how the SQL Server works. Three things he has done are:
- Removed waits that are shown in the view that causes no problems (in a normal situation).
- Written the code so that it only identifies the 95% of the waits (the most important waits).
- Created an output that is understandable.
The TSQL looks like:
WITH Waits AS
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms – signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
WHERE wait_type NOT IN (
‘CLR_SEMAPHORE’, ‘LAZYWRITER_SLEEP’, ‘RESOURCE_QUEUE’, ‘SLEEP_TASK’,
‘SLEEP_SYSTEMTASK’, ‘SQLTRACE_BUFFER_FLUSH’, ‘WAITFOR’, ‘LOGMGR_QUEUE’,
‘CHECKPOINT_QUEUE’, ‘REQUEST_FOR_DEADLOCK_SEARCH’, ‘XE_TIMER_EVENT’, ‘BROKER_TO_FLUSH’,
‘BROKER_TASK_STOP’, ‘CLR_MANUAL_EVENT’, ‘CLR_AUTO_EVENT’, ‘DISPATCHER_QUEUE_SEMAPHORE’,
‘FT_IFTS_SCHEDULER_IDLE_WAIT’, ‘XE_DISPATCHER_WAIT’, ‘XE_DISPATCHER_JOIN’, ‘BROKER_EVENTHANDLER’,
‘TRACEWRITE’, ‘FT_IFTSHC_MUTEX’, ‘SQLTRACE_INCREMENTAL_FLUSH_SLEEP’,
‘BROKER_RECEIVE_WAITFOR’, ‘ONDEMAND_TASK_QUEUE’, ‘DBMIRROR_EVENTS_QUEUE’,
‘DBMIRRORING_CMD’, ‘BROKER_TRANSMITTER’, ‘SQLTRACE_WAIT_ENTRIES’,
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) – W1.Percentage < 95; — percentage threshold
Wow… Would you have figured that code out your self? I wouldn’t, but running it is brilliant. the output looks like:
What I can see from here, then the waittype that my topwaittype is IO_COMPLETION, and if I want to then, I could start to investigate what that means and how to solve it. But here is the first warning!!!!!
Do NOT jump to any hasty conclusions. Always be clever and think one step ahead, and don’t conclude: I am waiting for my disk system so I must buy a faster disk system. YOU WILL NEVER SUCCEED using money. You will succeed if you use your brain.
So if you have waittypes that indicates that the disksystem is under preassure, then find out why it is under preassure.
How to improve the TSQL SCRIPT? Basically I would add CREATE VIEW getWaits AS in front of the TSQL. Paul Randal has in his teaching on PluralSight suggested that you put the information into a table and then track it over time. Set it up by writing:
select GETDATE() as capturedate, *
and then create a SQL Server Job where you write:
insert into waitHistory
select GETDATE() as capturedate, *
If you want to read Paul Randals orignal post, then click here.