Featured Database Articles

Examining SQL Server's I/O Statistics - Page 2

Using sp_monitor to Get I/O Statistics

sp_monitor is a system stored
procedure that produces four recordsets that display the values of system
statistical functions since sp_monitor was last run. It also shows the last_run
time, the current_run time, and the number of seconds in the measurement.
There are no parameters to sp_monitor. Here is a short script to see its
output:

sp_monitor uses the system
statistical functions discussed in the previous section to get its
information. It stores the value from the last time the procedure was invoked
in the table spt_monitor in the master database.

Each of the measurements, such as
cpu_busy and total_read, are character strings that have several parts. The
first number is the measurements from when the instance started. The second
number, the one in parenthesis, is the measurement since the last time sp_monitor
was run. Finally, for the CPU related measurements in the second recordset,
the percentage of time in the column's category follows.

I generally find the sp_monitor
is the best way to get a quick picture of what is happening to your system;
just: run it, wait 10 to 100 seconds and run it again. The result is a
snapshot of your system activity.

Other than getting a quick look
at your system, several factors weigh against trying to use sp_monitor for
long-term information gathering:

There is only one set of statistics that can be saved at a time.
If someone else runs sp_monitor while you're waiting, you see the results since
anyone last ran it.

The four recordsets of output are difficult to work with in
T-SQL.

Combining the measurement from the instance startup time with the
current measurement, and possibly a percentage also makes the output difficult
to work with from a program.

The numbers are aggregated to the instance level.

These factors combine to limit
the usefulness of the procedure. I generally use a system build-in function, fn_virtualfilestats,
to get a more detailed look at Input/Output statistics.

Using fn_virtualfilestats to Get I/O Statistics

fn_virtualfilestats returns a
table of I/O statistics at the file level. It takes two parameters: the Db_ID
of a database to retrieve information for and the file_id of the file to
retrieve information for. Supplying -1 to either of the parameters asks for
all information about the dimension. For example, executing this query:

select * from ::fn_virtualfilestats(-1, -1)
asks for information about all files in all databases. Executing
select * from ::fn_virtualfilestats(-1, 2)

asks for information about file
number 2, usually the first log file, for all databases.

Table 2 lists the output
columns for fn_virtualfilestasts. All measurements are "since the
instance started."

Table 2 Output columns from fn_virtualfilestats

Column

Data Type

Description

Dbid

Smallint

Database ID from master..sysdatabases

FileID

Smallint

File ID from sysfiles

Timestamp

Int

Number of seconds since the
instance started.

NumberReads

Bigint

Number of reads.

NumberWrites

Bigint

Writes.

BytesRead

Bigint

Bytes read.

BytesWritten

Bigint

Bytes written.

IoStallMS

Bigint

Milliseconds users waited for
I/O complete on the file.

Here is a short query that shows
the I/O statistics for the current database with sample output:

The parameter to udf_Perf_FS_ByDriveTAB
is a drive letter, which you can use if you are only interested in
information about a single drive. Otherwise, use NULL to retrieve information
for all drives that have SQL Server data or log files.

The most important factor needed
to analyze these numbers is knowledge of the disk configuration that is
associated with each drive letter. Is it a single drive? Is there a RAID
configuration? If so, which one? Are there multiple partitions on any of the
drives? A SAN? The answers to all of these questions make a big difference in
how you evaluate the results. Different configurations, such as RAID 0 vs.
RAID 5, have different capacities and you will have to know your configuration
to make meaningful comparisons.

A problem with the numbers
produced by fn_virtualfilestats is that they include information from the start
of the instance. The numbers during peak usage are more interesting and the
next section develops a solution to gathering them that is similar to the
solution used by sp_monitor.