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

Revised 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 more modifications that I wanted to make available. 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. These are still the same basic procedures as the previous ones (see here) but I have added some additional filters for the Wait Stats to account for SQL2014 and 2016. I also added another optional parameter to make use of the FORMAT() function available in SQL2012 and up. And finally I dropped the year in the name as these should work for SQL2008 and greater releases (minus the formatting for < 2012). So the older versions will work fine since those newer wait types just won’t appear in the first place.

 

Gather procedure changes

  1. Changed the names to remove the SQL version years.
  2. Added a Clustered Index on the Capture Time column.

 

 Reporting procedure changes

  1. Changed the names to remove the SQL version years
  2. Added an optional parameter to allow for formatting the results showing commas, decimals and %.

 

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 different 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. 

--  Gather Stats

 

By default the gather procedures will simply take a 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 first truncate the table and then take a new snapshot.

EXECUTE [dbo].[gather_wait_stats] @Clear

EXECUTE [dbo].[gather_file_stats] @Clear

 

--   Report stats

Both the reporting procedures have an optional Begin and End DateTime parameter to designate a particular time frame to report on. It will find the closest snap shot to the DateTime you specify if it is not exactly the value you passed in. Both have a new parameter that will format the results as strings with commas, percent signs etc. This uses the FORMAT() function new to SQL2012 and can be customized to a specific country code.  One thing to point out is that while the formatting makes for an easier report to read directly in the SSMS results window it gets left justified due to the output being converted to a string vs. numeric as without the formatting. 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.  If you don’t specify a Begin and End DateTime it will default to the very first and last snapshot in the table. The below examples show the optional parameters for each.

EXECUTE [dbo].[report_wait_stats] @BeginTime, @EndTime, @TopNN, @Formatted ;

EXECUTE [dbo].[report_file_stats] @BeginTime, @EndTime, @TopNN, @DBID, @Formatted ;

 

I typically place the gather procedures in a SQL Agent job that runs every 4 hours by default to insert a snapshot of both the file and wait stats into the history tables. From there you can always insert new snapshots at any time manually if needed. The reports will allow you to query at whatever snapshot intervals you want based on what is in the table at that time. This makes it useful to periodically see how your system is doing at regular intervals in a very efficient way.

The files are zipped up and attached to this blog post so have fun and I hope some of you find this useful. Feel free to modify to suite your needs. These are after all intended to be a useful tool not just a hammerJ.

 

Andrew J. Kelly

Published Wednesday, September 2, 2015 8:49 PM by Andrew Kelly
Filed under: , , ,

Attachment(s): FileWaitProcedures.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

This Blog

Syndication

Privacy Statement