Examining SQL Server’s I/O Statistics

Reading and writing to the disk
is the heart of what any database management system does, SQL Server included.
Input/Output (I/O) performance can make or break an application. This article
discusses the diagnostic tools that can be used to examine SQL Server’s I/O
statistics so that you can make fact-based judgments about disk configurations.

There are several ways to request
I/O statistics from SQL Server such as the System Statistical functions, sp_monitor,
and fn_virtualfilestats. Each method has its advantages and disadvantages. I’ll
show you how they work and their pros and cons.

I rely primarily on fn_virtualfilestats
because it gives the most detailed information. The other methods aggregate
information at the instance level. The instance level may be the only
meaningful alternative when ‘you are accounting for the CPU, but when working
with file I/O having the detailed breakdown is helpful.

One of the limitations of all of
system statistical functions and fn_virtualfilestats is that their reports are
always based on the resources consumed since the instance started. This
includes both peak usage times and low usage times. If your instance has been
running through several cycles of peak to low usage these overall aggregates
may be of some interest, but they are usually most interesting during times of
peak usage. After we discuss the various methods for statistics gathering, I
will show you a stored procedure for gathering I/O statistics during peak time
periods.

Ways to get I/O Statistics

Although the statistics are nearly
identical, there are several ways to request them from SQL Server 2000. The
methods are:

  • The system statistical functions such as @@CPU_BUSY
  • sp_monitor
  • fn_virtualfilestats

The first two methods give you
information that is aggregated at the instance level. Let’s take a look at
them first.

Using the System Statistical Functions

The system statistical functions
cover I/O, network and CPU resource utilization. Table 1 lists them.

Table 1 System Statistical Functions

Function

Description

@@CONNECTIONS

The number of connections or
attempted connections.

@@CPU_BUSY

Timer ticks that the CPU has been working for SQL Server.

@@IDLE

Time in timer ticks that SQL
Server has been idle.

@@IO_BUSY

Timer ticks that SQL
Server has spent performing I/O operations.

@@PACKET_ERRORS

Number of network packet errors
that have occurred.

@@PACK_RECEIVED

Number of packets read from the
network.

@@PACK_SENT

Number of packets written to
the network.

@@TIMETICKS

Number of millionths of a second in a timer tick.

@@TOTAL_ERRORS

Number of read/write errors
during I/O operations.

@@TOTAL_READ

Number of disk reads.

@@TOTAL_WRITE

Number of disk writes.

For monitoring I/O the most
interesting numbers are @@IO_BUSY, @@Total_READ and @@TOTAL_WRITE. Here is a
simple query that shows the raw statistics:


— Take a look at raw I/O Statistics
SELECT @@TOTAL_READ [Total Reads]
, @@TOTAL_WRITE as [Total Writes]
, CAST(@@IO_BUSY as FLOAT) * @@TIMETICKS / 1000000.0 as [IO Sec]
GO

(Results)

Total Reads Total Writes IO Sec
———– ———— ———–
85336 322109 25.375

When using the functions @@IO_BUSY, @@CPU_BUSY, and @@IDLE, the function returns clock ticks. To convert ticks to seconds, multiply by @@TIMERTICKS and then divide by one million. Be sure to convert the quantities to floating point, numeric, or bigint to avoid integer overflow during intermediate calculations.

The raw numbers alone aren’t very
interesting. ‘It is more informative to turn the numbers into rates. To do
that you need to know how long the instance has been running. This next script
uses a user-defined function (UDF), udf_SQL_StartDT, which uses the
start time of the Lazy Writer process as a proxy for the start time of the
instance. udf_SQL_StartDT is available from my free T-SQL UDF of the
Week Newsletter and you can download it at this URL: http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-11-udf_SQL_StartDT.htm
.

The start time is turned into a
number of seconds and the script performs the division, being careful to CAST
to data types that ‘will not lose information due to rounding or integer
division:


— Turn the raw statistics into rates
DECLARE @SecFromStart bigint
SET @SecFromStart = DATEDIFF(s, dbo.udf_SQL_StartDT(), getdate())

SELECT CAST(CAST(@@TOTAL_READ as Numeric (18,2))/@SecFromStart
as Numeric (18,2)) as [Reads/Sec]
, CAST(CAST(@@TOTAL_WRITE as Numeric (18,2))/@SecFromStart
as Numeric (18,2)) as [Writes/Sec]
, CAST(@@IO_BUSY * @TIMETICKS/10000.0/@SecFromStart
as Numeric (18,2)) as [Percent I/O Time]
GO


(Results)


Reads/Sec Writes/Sec Percent I/O Time
——————– ——————– ——————–
24.34 92.53 .42

The read and write rates are
often in the tens or hundreds, at least over short time spans. You might ask, "Why
do you bother to retain even two digits to the right of the decimal?" Most
of the time these extra two digits do not come into play. However, when a
system has been idle for a long time, let’s say over the weekend after being
restarted on Friday night, it’s possible to have rates that are less than one.
Showing zero for the rates is confusing, so I have tried to be sure that at
least a small number shows up.

Showing rates from the time the
instance started until the query is run forces you to average over a long time
period. SQL Server supplies a stored procedure that shows the values of the
system statistical functions since it was last run, this let’s you get a quick
snapshot of your I/O rates.

Andrew Novick
Andrew Novick
Andrew Novick develops applications as consultant, project manager, trainer, author, and Principal of Novick Software. His firm specializes in implementing solutions using the Microsoft tool set: SQL Server, ASP, ASP.Net, Visual Basic and VB.Net. 2003 marks his 32nd year of computer programming, starting in High School with a PDP-8 and moving on a degree in Computer Science, an MBA, and then programming mainframes, minicomputers and for the last 16 years PCs. In addition to writing articles for Database Journal, Andrew is author of the book SQL Server User Defined functions, which will be published by Wordware in the fall of 2003. He co-authored SQL Server 2000 XML Distilled, published by Curlingstone in October of 2002. He also writes the free T-SQL User-Defined Function of the Week newsletter. When not programming he enjoys coaching Little League baseball, woodworking, mowing the lawn, and the occasional movie with his wife.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles