Gathering I/O statistics down to the database file level

When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible. SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database?

Solution
As mentioned above, SQL Server offers many great little functions and utility programs to help you gain an insight as to what is occurring on the server. One of these tools is fn_virtualfilestats.

This function, fn_virtualfilestats allows you to get information for each physical file that is being used to hold your data including both the data and log files. The function returns read and write information as well as stall information, which is the time users had to wait for an I/O operation to complete. Each time this function is called it returns the overall numbers that SQL Server has collected since the last time the database engine was started, so to use this effectively you need to gather data from two different points of time and then do a comparison.

To run this function to get data for all databases and all files this can be done as easily as this:

SQL 2005

SELECT * FROM fn_virtualfilestats(NULL,NULL);

SQL 2000

SELECT * FROM :: fn_virtualfilestats(-1, -1)

The output for SQL 2000 and 2005 is pretty much the same, but some additional columns have been added for SQL Server 2005.

Column Name	Notes	Description
DbId		Database ID.
FileId		File ID.
TimeStamp		Database timestamp at which the data was taken.
NumberReads		Number of reads issued on the file.
BytesRead		Number of bytes read issued on the file.
IoStallReadMS	SQL2005 only	Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file.
NumberWrites		Number of writes made on the file.
BytesWritten		Number of bytes written made on the file.
IoStallWriteMS	SQL2005 only	Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file.
IoStallMS		Sum of IoStallReadMS and IoStallWriteMS.
FileHandle		Value of the file handle.
BytesOnDisk	SQL2005 only	Physical file size (count of bytes) on disk. For database files, this is the same value as size in sys.database_files, but is expressed in bytes rather than pages. For database snapshot spare files, this is the space the operating system is using for the file.

(Source SQL Server 2005 Books Online)

Sample Output

As you can see from the sample output, the Dbid and FileId columns are pretty cryptic. The Dbid can be be translated to the database name pretty easily by using the DB_NAME() function, but the fileId needs to be looked up from one of the system tables.

To lookup the filename from the system tables you can use these queries.

SQL 2005

SELECT dbid, fileid, filename FROM sys.sysaltfiles WHERE dbid = 5 and fileid in (1,2)

SQL 2000

SELECT dbid, fileid, filename FROM dbo.sysaltfiles WHERE dbid = 5 and fileid in (1,2)

Here is sample output.

From just using this function directly you can gather data from two different points in time and then do a comparison to determine the change that has occurred between these two periods of time. Here is a sample query that gathers data, waits for a period of time and then gathers data again to show you a comparison.

This example is written for SQL Server 2005, but can easily be changed for SQL 2000.

USE master GO -- create table IF NOT EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]') AND type IN (N'U')) BEGIN CREATE TABLE filestats (dbname VARCHAR(128), fName VARCHAR(2048), timeStart datetime, timeEnd datetime, timeDiff bigint, readsNum1 bigint, readsNum2 bigint, readsBytes1 bigint, readsBytes2 bigint, readsIoStall1 bigint, readsIoStall2 bigint, writesNum1 bigint, writesNum2 bigint, writesBytes1 bigint, writesBytes2 bigint, writesIoStall1 bigint, writesIoStall2 bigint, ioStall1 bigint, ioStall2 bigint ) END -- clear data TRUNCATE TABLE dbo.filestats -- insert first segment counters INSERT INTO dbo.filestats (dbname, fName, TimeStart, readsNum1, readsBytes1, readsIoStall1, writesNum1, writesBytes1, writesIoStall1, IoStall1 ) SELECT DB_NAME(a.dbid) AS Database_name, b.filename, GETDATE(), numberReads, BytesRead, IoStallReadMS, NumberWrites, BytesWritten, IoStallWriteMS, IoStallMS FROM fn_virtualfilestats(NULL,NULL) a INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid ORDER BY Database_Name /*Delay second read */ WAITFOR DELAY '000:01:00' -- add second segment counters UPDATE dbo.filestats SET timeEnd = GETDATE(), readsNum2 = a.numberReads, readsBytes2 = a.BytesRead, readsIoStall2 = a.IoStallReadMS , writesNum2 = a.NumberWrites, writesBytes2 = a.BytesWritten, writesIoStall2 = a.IoStallWriteMS, IoStall2 = a.IoStallMS, timeDiff = DATEDIFF(s,timeStart,GETDATE()) FROM fn_virtualfilestats(NULL,NULL) a INNER JOIN sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid WHERE fName= b.filename AND dbname=DB_NAME(a.dbid) -- select data SELECT dbname, fName, timeDiff, readsNum2 - readsNum1 AS readsNumDiff, readsBytes2 - readsBytes2 AS readsBytesDiff, readsIoStall2 - readsIOStall1 AS readsIOStallDiff, writesNum2 - writesNum1 AS writesNumDiff, writesBytes2 - writesBytes2 AS writesBytesDiff, writesIoStall2 - writesIOStall1 AS writesIOStallDiff, ioStall2 - ioStall1 AS ioStallDiff FROM dbo.filestats

Summary
One problem that you may be faced with though is that not all files are stored on their own physical disks, so you may have a case where you want to look at things from a drive perspective vs. at an individual file level. Here is a previous article written by Andy Novick that has the entire process broken down into functions, so you can aggregate things to a drive perspective. The article can be found here, Examining SQL Server's I/O Statistics