TSQL Tuesday #005 – Wait Stat Report – #TSQL2sDay

I’ve been meaning to join in on the T-SQL Tuesday event for the last couple months and let the time opportunity slip by.  This month’s topic is reporting and its seems like a good time to get in on the event.

The great thing about reporting is that for the past few months I’ve been building towards this very post.  A few months ago, I posted a blog on a process that I often use with clients and on my own machines to capture wait stats over time.  A while later, I followed-up with a blog on categorizing wait stat and providing definitions for them.

While that was all fine and good, I hadn’t provided any method to report this information in a manner that would make gathering this information useful at all.  Part of the delay was that shortly after the first post I realized that the reports I had intended to put up were useless.  They were pretty and had lots of data, but there wasn’t anything you could really accomplish with them.  What’s the point of useless content?

So, here I’ll walk through a level-100 report based on the information that’s been collected.  The point of this report will simply be to provide a snapshot over a number of days of all wait stats and their degree of activity on a SQL Server instance.

Wait Stat Snapshot Procedure

Before I can provide the report, we’ll first need a stored procedure to work off of.  It’s a fairly simple procedure based on the previous two posts on this topic.  Those ones I mentioned above.

USE [DBADiagnostics]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Report].[WaitStatSnapshot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Report].[WaitStatSnapshot]
GO

/*============================================================
Procedure:	Report.WaitStatSnapshot
Author:		Jason Strate
Date:		April 1, 2010

Synopsis:
This procedure aggregates wait stats for the selected date range.  Data is
joined with category information to provide description and action
guidance if available.

Parameters
@StartDate  : Begin date for aggregating wait stats.  Begins from the start
of the date.
@EndDate    : End date for aggregating wait stats.  Includes all data for the
end date.

============================================================
Revision History:
Date:		By			Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Report].[WaitStatSnapshot]
(
@StartDate datetime
,@EndDate datetime
)
As

;WITH WaitStatCTE
AS (
SELECT COALESCE(wtc.Category, 'UNKNOWN') AS Category
,COALESCE(wtc.Resource, 'UNKNOWN') AS Resource
,wtc.Description
,wtc.Action
,wsh.WaitType
,SUM(wsh.WaitingTasksCount) AS WaitingTasksCount
,SUM(wsh.WaitTimeMs) AS WaitTimeMs
,SUM(wsh.SignalWaitTimeMs) AS SignalWaitTimeMs
FROM Monitor.WaitStatHistory wsh
INNER JOIN Resources.WaitTypeCategory wtc ON wsh.WaitType = wtc.WaitType
WHERE wsh.WaitingTasksCount  0
AND	wsh.CreateDate BETWEEN @StartDate AND DATEADD(ms, -3, DATEADD(d, 1, @EndDate))
GROUP BY COALESCE(wtc.Category, 'UNKNOWN')
,COALESCE(wtc.Resource, 'UNKNOWN')
,wsh.WaitType
,wtc.Description
,wtc.Action
)
SELECT Category
,Resource
,[Description]
,[Action]
,WaitType
,WaitTimeMs
,WaitingTasksCount
,SUM(WaitTimeMs) OVER() AS TotalWaitTimeMS
,SignalWaitTimeMs
,CAST((1.*WaitTimeMs)/SUM(WaitTimeMs) OVER() AS decimal(8,6)) AS PercentWaitTimeMS
FROM WaitStatCTE
ORDER BY WaitTimeMs DESC
GO

Wait Stat Snapshot Report

The stored procedures above returns all of the wait stats aggregated over the selected time period and displays them on the report shown below.  By default the report won’t display Description or Action information, but you can easily drill-down into that information.

The report can be downloaded here :

To deploy this report, you’ll have to update the data source information for the report.  Beyond that it should be easy to deploy and begin using.

Finding Some Benefit

I’ve been using this report to keep an eye on what’s happening on the SQL Servers that I’ve been monitoring for clients and it provides a good snapshot of what’s been happening since I last reviewed the server.  The analysis isn’t too deep, but then it wouldn’t be called a snapshot report.