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_statsis 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 tabledbo.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 proceduredbo.sp_wait_stats_explained
go
create procedure dbo.sp_wait_stats_explained
      @top_amount int = null
as
      set nocounton;
      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 joindbo.wait_stats_explained e
      on ws.wait_type = e.wait_type
      order byws.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.
Advertisements

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