THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

Updated File & Wait Statistics Procedures

    

     For many years I have been using a set of stored procedures to capture and report on both the file and wait statistics inside of SQL Server and I just recently did some minor modifications that I want others to be aware of. Since many of you have received copies of the past revisions via conferences or articles and such I would recommend taking a look at the newly modified ones as well. Most of the changes come in the form of optional parameters but three of them may not be drop in compatible and I will explain each of them below. BTW these procedures are all labeled 2012 but I believe they will work just fine in 2008 or even 2005. The only reason I even had a different procedure for each version of SQL Server was due to additional wait types that were added over the years that I chose to filter out of the results. So the older versions will work fine since those newer wait types just won’t appear in the first place.

Gather procedure changes

  1. Swapped the begin and end time parameters in the file stats proc to be consistent with the wait stats one. I don’t know why it was ever different but both now use @BeginTime first and @EndTime as the second parameter.

  2. Removed the ability to reset the actual SQL Server Wait Statistics counters when specifying the optional @Clear parameter. Now both procedures simply truncate the tables before taking a fresh snapshot.

 Reporting procedure changes

  1. Fixed a bug in both procedures that did not always select the closest snapshot when specifying either the @BeginTime or @EndTime parameters.

  2. Removed the optional parameter to filter out OLEDB waits from the wait stats report procedure.

  3. Added the ability to return only the TOP nn rows via an optional parameter called @TopNN.

    1. For file stats the results are first sorted by total IOStallMs DESC to produce the TOP nn rows.  If @DBID parameter is specified then only the Top nn rows for that database are returned.

    2. For wait stats the results are first sorted by total wait time DESC to produce the TOP nn rows.

  4. Added the ability to specify a database ID via the optional parameter @DBID. This only returns rows for that database in the report.

 

The overall use of the stored procedures has not changed and is very simple overall. They can be used in an adhoc fashion or in conjunction with a SQL Agent job to periodically collect the statistics.  Once you have 2 or more snapshots in the tables you can use the report stored procedures to display the data via whatever time frame and style you wish. I have even included a couple of spreadsheet templates for logging the report results which do a better job of formatting the numbers than the report procedures themselves. We all know there are plenty of ways and available scripts to report on the file or wait stats and I am in no way trying to push the use of these. This is mostly to give everyone who already uses them my updates and to make them available for anyone else who wishes to use them as well.  

Here are some quick examples on how to call them. All parameters are optional and work just like any other TSQL stored procedure does with parameters.  By default the gather procedures will simply take another snapshot and insert the results into the table of whatever database you decide to create these procedures in. If you specify a 1 as the parameter it will Truncate the table and then take a new snapshot.

--  Gather Stats

EXEC dbo.gather_wait_stats_2012

EXEC dbo.gather_file_stats_2012

Both the reporting procedures have an optional Begin and End DateTime parameter to designate a particular time frame to report on. The next optional parameter for both is the number of rows to limit the final report results to. And finally the file stats procedure has an optional database ID parameter.  So the first 2 examples below will give a report for everything of the delta of the very first and very last snapshots in the tables.

--   Report stats

EXEC dbo.report_wait_stats_2012

EXEC dbo.report_file_stats_2012

 These show how you can specify a Date or a Datetime along with a TOP 10 limit and in the case of the File Stats only show tempdb’s data.

EXEC dbo.report_wait_stats_2012 '20140206 15:51:44.127', '20140208', 10

EXEC dbo.report_file_stats_2012 '20140206 15:51:44.127', '20140208', 10, 2

 

The files are zipped up and attached to this blog post so have fun and I hope some of you find this useful,

 

Andrew J. Kelly

 



Published Thursday, February 13, 2014 6:23 PM by Andrew Kelly

Attachment(s): FileWaitProceduresFeb132014.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement