How to Use fn_virtualfilestats() for I/O Tests?

If want to use the fn_virtualfilestats() function table to calculate the I/O statistics while doing performance tests, you need to take a copy of the current counters from the fn_virtualfilestats() function before running a test, run the test, then compare counters to their copied values.

Here is an example SQL script, fyi_insert_write.sql, that runs a performance test of writing data using INSERT statements.

-- fyi_insert_write.sql
-- Copyright (c) FYIcenter.com 

DECLARE @Count INT
SET @Count = 100
DECLARE @Size INT
SET @Size = 256

DECLARE @STime DATETIME
DECLARE @ETime DATETIME
DECLARE @Duration INT

DECLARE @I INT
DECLARE @Message VARCHAR(5760)
SET @Message = 'The quick brown fox jumps over the lazy dog. '
SET @I = 7
WHILE @I > 0 BEGIN
   SET @Message = @Message+@Message
   SET @I = @I - 1
END
SET @Message = SUBSTRING(@Message,1,@Size/8-2)

CREATE TABLE FYI_DATA (
   MSG_1 VARCHAR(4096),
   MSG_2 VARCHAR(4096), 
   MSG_3 VARCHAR(4096), 
   MSG_4 VARCHAR(4096), 
   MSG_5 VARCHAR(4096), 
   MSG_6 VARCHAR(4096), 
   MSG_7 VARCHAR(4096), 
   MSG_8 VARCHAR(4096)
) 

select t.name, p.partition_id, p.rows, a.type_desc, 
   a.data_space_id, a.data_pages, f.file_id, 
   s.NumberReads, s.BytesRead, s.NumberWrites, s.BytesWritten
   into #FYI_STATS
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s
   where t.name = 'FYI_DATA'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id
      and f.file_id = s.fileid

SET @STime = GETDATE()
SET @I = 0
WHILE @I < @Count BEGIN
   INSERT INTO FYI_DATA VALUES (@Message,@Message,@Message,@Message,
      @Message,@Message,@Message,@Message)
   SET @I = @I+1
END
SET @ETime = GETDATE()

WAITFOR DELAY '00:00:05'  

SET @Duration = DATEDIFF(millisecond,@STime,@ETime)
SELECT @Size AS Size, @Count AS Count, @Duration AS Duration, 
   1000*@Count/@Duration AS 'Throughput', 
   @Duration/@Count AS Latency,
   p.rows-l.rows as '+Rows', 
   a.type_desc, a.data_pages-l.data_pages as "+Pages", f.file_id,
   s.NumberReads-l.NumberReads as "Reads", 
   s.BytesRead-l.BytesRead as "Bytes Read", 
   s.NumberWrites-l.NumberWrites as "Writes", 
   s.BytesWritten-l.BytesWritten as "Bytes Written", 
   l.*  
   from sys.objects t, sys.partitions p, sys.allocation_units a, 
      sys.database_files f, fn_virtualfilestats(DB_ID(),NULL) s,
      #FYI_STATS l 
   where t.name = 'FYI_DATA'
      and t.object_id = p.object_id 
      and p.hobt_id = a.container_id
      and a.data_space_id = f.data_space_id
      and f.file_id = s.fileid
      and l.file_id = f.file_id
      and l.partition_id = p.partition_id
      and l.type_desc = a.type_desc

DROP TABLE #FYI_STATS
DROP TABLE FYI_DATA

This script uses a temporary table to keep a copy of different counters of about the target table before the test.

New values of those same counters are compared with old values to calculate the changes.

The "WAITFOR DELAY '00:00:05'" function is called to wait for 5 seconds to give enough time for fn_virtualfilestats() to be refreshed after the test is terminated.

Run the script with the default @Size and @Count values on SQL Server. The output may give you 2 records with many fields. But you can divide the record into 2 chunks:

SIZE  COUNT  DURATION  THROUGHPUT  LATENCY  +ROWS
256   100    407       245         4        100  

TYPE_DESC             +PAGES  FILE_ID  READS  BYTES READ  WRITES  BYTES WRITTEN
IN_ROW_DATA           4       1        0      0           1       8192           
ROW_OVERFLOW_DATA     0       1        0      0           1       8192           

As you can see:

• The script reported that 100 rows were added to the partition of the target table. Total of 25,600 bytes of data were stored in the target table.
• The sys.allocation_units view reported that 2 storage units were allocated, one for IN_ROW_DATA and the other for ROW_OVERFLOW_DATA, to the same physical file.
• The sys.allocation_units view also reported that IN_ROW_DATA storage unit increased by 4 pages.
• The fn_virtualfilestats view reported that 1 writes of 8,192 bytes (the writing block is 8 KB) were recorded on the physical file.

If we put all reports together, we see some inconsistencies:

• The script reported 25,600 bytes were stored in the table. But fn_virtualfilestats reported only 8,192 bytes were written to the physical file. May be some data were still the memory cache?
• The sys.allocation_units reported 4 pages were used to store the table of 25,600. It looks like page size is 8,192 bytes.
• The writing block size seems to be the same as the page size of 8,192 bytes (8 KB). So only 1 page was written to the file, the other 3 pages were still in memory, or written after we ran the query on fn_virtualfilestats.

⇒ fn_virtualfilestats() Not Useful on Azure SQL

⇐ Page Size Varies on SQL Server 2016 Database

⇑ I/O Tests on INSERT Statements

⇑⇑ SQL Server Storage Tutorials