fn_virtualfilestats: Measure the I/O load on your disk system

Microsoft included the fn_virtualfilestats function in SQL Server so you can monitor the I/O load the instance executes on the disk subsystem.

I use this function mainly to:
- Get the load on TempDB and check if it’s a good idea to move it to it’s own disks (if you have not already done so)
- Get the I/O profile (more read of write activity?) on the database level to help determine if it might be an idea to replace a raid-5 array to a raid-10 or to move datafiles to additional diskarrays (see my post http:// for this)
- Check if there are any disk related issues, in other words are there many stalled I/O’s?
(stalled I/O is an I/O waiting for another I/O)
- Check if the system can handle more db’s, eliminating additional servers

Real life example:
I managed to save big $$$ and could avoid buying new hardware by letting our vendor run this script in comaparable environent of another customer and so proving the load was not so big as they claimed, so we could run the new system on our existing hardware.

Here’s the T-SQL to get the I/O profile of all your database and data files:

DECLARE @TotalIO    BIGINT,
    @TotalBytes BIGINT,
    @TotalStall BIGINT

SELECT @TotalIO  = SUM(NumberReads + NumberWrites),
       @TotalBytes = SUM(BytesRead + BytesWritten),
       @TotalStall = SUM(IoStallMS)
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)

SELECT [DbName] = DB_NAME([DbId]),
      (SELECT name FROM sys.master_files
        WHERE database_id = [DbId]
              and FILE_ID = [FileId]) filename,
    [%ReadWrites]       = (100 * (NumberReads + NumberWrites) / @TotalIO),
    [%Bytes]        = (100 * (BytesRead + BytesWritten) / @TotalBytes),
    [%Stall]        = (100 * IoStallMS / @TotalStall),
    [NumberReads],
    [NumberWrites],
    [TotalIO]       = CAST((NumberReads + NumberWrites) AS BIGINT),
    [MBsRead]       = [BytesRead] / (1024*1024),
    [MBsWritten]        = [BytesWritten] / (1024*1024),
    [TotalMBs]      = (BytesRead + BytesWritten) / (1024*1024),
    [IoStallMS],
    IoStallReadMS,
    IoStallWriteMS,
    [AvgStallPerIO]     = ([IoStallMS] / ([NumberReads] + [NumberWrites] + 1)),
    [AvgStallPerReadIO] = (IoStallReadMS / ([NumberReads] + 1)),
    [AvgStallPerWriteIO]= (IoStallWriteMS / ( [NumberWrites] + 1)),

    [AvgBytesPerRead]  = ((BytesRead) / (NumberReads + 1)),
    [AvgBytesPerWrite] = ((BytesWritten) / (NumberWrites + 1))
FROM ::FN_VIRTUALFILESTATS(NULL, NULL)
ORDER BY dbname
 

Be ware: counters are re-set after the instance is restarted!

The result (not all columns shown here). Click on image to enlarge