Wait Stats Explained

                One of the most useful Dynamic Management Views (DMV) is sys.dm_os_wait_stats.  This DMV gives information about the waits that SQL Server runs into when executing processes.  sys.dm_os_wait_stats is a great start to diagnosing potential issues that could be causing performance problems, or worse…failure.  The amount of wait types that are returned by a SELECT * from sys.dm_os_wait_stats query is extremely extensive, if not daunting.  This DMV gives information such as delays from parallelism (CXPACKET), full-text operation synchronization (FULLTEXT GATHERER), all flavors of locks (LCK_XXX), log flush and transaction commits (WRITELOG), and many…many more.

                This is one of those DMVs that you surely need Books Online (BOL) to be handy, as many of these wait types are not easily understood right off the bat by their sometimes-cryptic name.  Sure, you can have MSDN open while you sift through the longer wait stats, but I have whipped up some T-SQL to ease the burden a little.

                The following code does two things:

1. Creates the base lookup table with the wait stats names as well as BOL descriptions
2. Creates the stored procedure to execute when you want to see the current wait statistics, as well as their BOL description correlated with them
3. this stored procedure sorts them by wait time descending, so it’s a handy way to quickly see the egregious offenders
4. there’s an optional parameter for the stored procedure so you can just get a sampling of the longer wait stats (or leave it empty/NULL to get all wait stats available)   

Base Lookup Table:

use master

go

if object_id('master.dbo.wait_stats_explained') is not null

      drop table dbo.wait_stats_explained

go

create table dbo.wait_stats_explained

(

      wait_type nvarchar(60) not null,

      wait_description nvarchar(1000) null

)

go

insert into master.dbo.wait_stats_explained

values

('ABR', 'Identified for .....'),

('ASSEMBLY_LOAD', 'Occurs during .....'),

('ASYNC_DISKPOOL_LOCK', 'Occurs when .....'),

('ASYNC_IO_COMPLETION', 'Occurs when .....'),

('ASYNC_NETWORK_IO', 'Occurs on network writes .....')

-- .... full definitions and total stats ommitted

--          due to extensive text

--          see BELOW for link to full scripts

Create Stored Procedure Script:

use master

go

if object_id('master.dbo.sp_wait_stats_explained') is not null

      drop procedure dbo.sp_wait_stats_explained

go

create procedure dbo.sp_wait_stats_explained

      @top_amount int = null

as

      set nocount on;

      select top (coalesce(@top_amount, 999999))

            ws.wait_type,

            ws.waiting_tasks_count,

            ws.wait_time_ms,

            ws.max_wait_time_ms,

            ws.signal_wait_time_ms,

            e.wait_description

      from sys.dm_os_wait_stats ws

      left join dbo.wait_stats_explained e

      on ws.wait_type = e.wait_type

      order by ws.wait_time_ms desc

go

Usage:

-- omit parameters, result set contains all wait stats

exec dbo.sp_wait_stats_explained

go

-- retrieve top 10 longest wait stats

exec dbo.sp_wait_stats_explained

      @top_amount = 10

go

               

                As you can see from the above code, it’s simply just a LEFT JOIN so the result set contains a description (if it is available for the particular wait stat) of what that wait stat is and a possible cause.

Source Code

                I am a firm believer in the version control (particularly distributed version control) of all types of code… and that includes Database Administration code.  The power of a DBA isn’t in mouse clicks through a GUI, but through T-SQL, PowerShell, and other forms of well-written code to maintain, secure, recover, and tune databases.

                I am currently using Git on my local machine, and I use GitHub for remote repositories to push my DBA code to.  Not only does this allow for the version control of my code, but it also permits me to share code with you, the viewers of this blog, or any other DBA that would like use the code (Git/GitHub possibilities are endless, much beyond the scope of this blog post).

                I have a repository for all of my DBA code, and this repository can be located here: SQLSalt.  As for the scripts for this blog post, they can be viewed/retrieved at the following locations:

Full Script to Create Base Table: WaitStatsExplained_1_CreateTable.sql

Full Script to Create sp_wait_stats_explained:  WaitStatsExplained_2_CreateProc.sql

                Welcome to the wonderful world of Git!  If you have any questions, recommendations, or comments please feel free to leave a comment or email me at sqlsalt@gmail.com.