SQLDIY: Tracking Wait Stats

One of the most important methodologies to date for troubleshooting problems with the SQL Server engine is examining the wait statistics. As with most of the DMV’s provided in SQL Server 2005/2008 sys.dm_os_wait_stats is a cumulative view. To add to my series SQLDIY: Manage and Monitor SQL Server Yourself  here is my stored procedure for capturing deltas on an ongoing basis. It is modeled the same way I capture sys.dm_io_virtual_file_stats. This procedure captures the delta and the raw data at a point in time. This allows you to do your own roll ups to look at the numbers in different ways. Another reason I do this is to capture what happened before the problem and right at the beginning as the customer started reporting the problems. Starting with the slide deck from Joe Sack (blog|twitter) on Performance Tuning With Wait Statistics as a foundation and working through the links provided, you will be able to get up to speed quickly.

Link to the script Gather Server Wait Stats

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!

Your Homework:
SQL Server 2005 Waits and Queues Tom Davidson
SQL Server Waits and Queues Robert Pearl (blog|twitter)
SQL Server 2008 DR, Testing, Waits and Queues Jose Barreto (blog|twitter)
Drum Roll, Please…The Debut of The SQL DMV All-Stars Dream Team! Jimmy May(blog|twitter)

Specific examples on using wait stats:
Diagnosing Transaction Log Performance Issues and Limits of the Log Manager Mike Ruthruff (blog|twitter)
How do you measure CPU pressure? Tom Davidson

Posted on June 27, 2011, in SQLDIY, SQLServerPedia Syndication, Syndicated, Tools, Troubleshooting, Wait Stats. Bookmark the permalink. 1 Comment.

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

%d bloggers like this: