Why wait statistics are important and how you can start collecting them now!

Wait statistics have always been a very important tool in troubleshooting complex performance issues or monitoring the impact of changes on your SQL Server. I use wait statistics daily in helping clients troubleshoot performance issues they are experiencing.

Wait statistics are collected by the SQL Server OS and can give you a look in how good (or bad) SQL Server is performing. There is a very large collection of different types of wait stats in SQL Server and they are growing every release! SQL Server 2012 already has 649 different types of wait stats! While all of these give you specific information about the actions or parts of SQL Server they monitor, not all of them indicate a performance problem, it is important to understand what wait is bad and which ones aren’t.

The first step in learning how to analyze wait statistics is by starting to collect them, this article will give you some help in that direction by giving you some tool to start collecting and analyzing the wait statistics on your server(s) now!

SQL Server Baseline Performance Capture and Compare

For collecting and quickly analyzing wait statistics I wrote a series of scripts collected under the name SQL Server Baseline Performance Capture and Compare. It captures wait statistics into a table and uses a couple of stored procedures to analyze the data you have captured.

You can download a .zip file containing all the scripts here.
(http://www.dotnine.nl/index.php/downloads)

The following files are included in the package:

Installation.sql
This is the installation file that creates the table structure and the stored procedures, you can run this against a database of your choosing.

uspFetchSQLWaitStats_Exec.sql
An example file on how to use the uspFetchSQLWaitStats stored procedure.
This is the stored procedure that captures the wait statistics and resets them again, you should run this stored procedure inside a SQL Agent job using a schedule of your choosing (I normally use a interval of 10-15 minutes).

uspGetTopWaits_Exec.sql
An example file on how to use the uspGetTopWaits stored procedure.
This stored procedure shows the top x waits on your server for a specific date.

uspGetBaselineCompare_Exec.sql
An example file on how to use the uspGetBaselineCompare stored procedure.
This stored procedure makes it possible to compare a specific date against a baseline that is created dynamically (it gets calculated in days, so you can build a baseline of the last x days) on a specific wait type.

Also included is a readme file that has more information about the scripts and the tables needed.

Keep in mind though that you should have a couple of days worth of data before generating the baselines. The more data you have collected for a longer amount of time the better you can learn how your server behaves.

Finally, these scripts are a work in progress and I try to improve them all the time. I have tested the script on SQL 2008 and SQL 2012 servers but as always make sure you test them before running them on your production servers!

I hope these scripts will help you collect and quickly analyze the wait stats on your server!
If you find any bugs or have any questions regarding these scripts, let me know!