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.