If you want to clear the contents of the table, you can call the stored procedure
with an optional parameter that has a value of 1. Just remember that the counters
are reset with each SQL Server restart. So, if you have this table in a place
other than tempdb, you might want to clear it right after each restart. Otherwise,
to get proper deltas, you'll have to add additional logic to the reporting stored
procedure, which I'll share in a moment. How often you collect the data is up
to you, but I tend to collect it every 10 minutes, which gives me the flexibility
to report down to a 10-minute time span. You can create a basic SQL Agent job
that executes this stored procedure at the frequency with which you want to
populate the table with the snapshots.
Now, you need some way to view the data you're collecting. That's where the
reporting stored procedure, which Listing
2 shows, comes into play. When this stored procedure runs with no parameters,
it determines the first snapshot and the last snapshot taken, then calculates
the difference or delta between them. It also performs certain calculations
to display the counters in a format that's easier on the eye and to break the
results down to megabytes instead of just bytes. The stored procedure then joins
the results on some system tables to display the actual names of the files and
databases in the report. An optional parameter returns only the files for a
supplied database ID. You can easily enhance this code to allow for any calculations
you want to report on or to specify a specific date/time range. You might even
want to perform hourly deltas to see what times of day you experience the most
physical I/O. And don't be surprised if that time turns out to be in the middle
of the night, when no one is around: That's when your backups are probably occurring!
Gather It and Use It
You have an extremely useful tool at your disposable in SQL Server 2005. By
spending a few hours getting to know this DMV, along with a few lines of custom
code, and implementing it into your environment, you'll save a lot of time and
aggravation down the road. You'll have a head start on creating a baseline for
your system, especially when it comes to the I/O aspect. But this DMV can also
come in handy for troubleshooting performance problems as they appear. If you
aren't regularly capturing this information, you're probably missing some crucial
snapshots that could help you in a pinch. The overhead of capturing this data
every 10 minutes—even on a busy system—is negligible, so there's
little excuse for not collecting it. Just remember that the data is no good
unless you do something with it!
End of Article
Prev. page
1
[2]
next page -->
