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.
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
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.
Fixed a bug in both procedures that did not
always select the closest snapshot when specifying either the @BeginTime or
Removed the optional parameter to filter out
OLEDB waits from the wait stats report procedure.
Added the ability to return only the TOP nn rows
via an optional parameter called @TopNN.
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.
For wait stats the results are first sorted by
total wait time DESC to produce the TOP nn rows.
Added the ability to specify a database ID via
the optional parameter @DBID. This only returns rows for that database in the
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
-- Gather Stats
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
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.
'20140206 15:51:44.127', '20140208', 10
'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