Examining waits – some nice TSQL

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.

select *
from sys.dm_os_wait_stats

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:

  1. Removed waits that are shown in the view that causes no problems (in a normal situation).
  2. Written the code so that it only identifies the 95% of the waits (the most important waits).
  3. Created an output that is understandable.

The TSQL looks like:

WITH Waits AS
    (SELECT
        wait_type,
        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
    FROM sys.dm_os_wait_stats
    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’,
        ‘SLEEP_BPOOL_FLUSH’, ‘SQLTRACE_LOCK’)
    )
SELECT
    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
GO

Wow… Would you have figured that code out your self? I wouldn’t, but running it is brilliant. the output looks like:

image

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, *
into waitHistory
from getWaits

and then create a SQL Server Job where you write:

insert into waitHistory
select GETDATE() as capturedate, *
from getWaits

If you want to read Paul Randals orignal post, then click here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s