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.
the names to remove the SQL version years.
a Clustered Index on the Capture Time column.
Reporting procedure changes
the names to remove the SQL version years
- Added an optional parameter to
allow for formatting the results showing commas, decimals and %.
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.
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
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.
-- Report stats
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
@BeginTime, @EndTime, @TopNN, @Formatted ;
@BeginTime, @EndTime, @TopNN, @DBID, @Formatted ;
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.
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