none
fn_virtualfilestats - NumberWrites and umberReads column

    Question

  • So in SQL Server 2008 and beyond what do the columns numberReads and numberWrites mean?  I thought this was simply the number of reads the number of writes issued by the sqlserver.exe process.  I went to test this idea and pulled up process explorer on a test machine.  I ran a simple backup that netted 10 reads and 16 writes in the database I backed-up and as well as 1 write in the tempdb log, 1 write in the msdblog, and 4 writes in the mastlog according to the fn_virtualFileStats.  (10 total reads 22 total writes)  According to process Explorer the SQLServe.exe process issued 16 reads and 46 total writes.  Now my guess is the SQLServer.exe had to write to the new backup file and that could account for the number of write differences.  What accounts for the number of read differences?  Does the SQLServer.exe process read the new backup file while/after it creates it?

    I'm looking to make sure I understand the information displayed in fn_virtualFileStats, I always tookthe numberReads and numberWrites to be a measure of IO, is this incorrect?

    Thanks ahead of time,

    Steve

    Sunday, August 18, 2013 1:12 AM

Answers

  • Both sys.dm_io_virtual_file_stats and sys.fn_virtualFileStats show IO information at the file level.  My question has to do with why the file IO shown by the either the DMV or the sys.fn show a certain amount of file IO, 10 total reads and 22 total writes but watching the process itself shows 16 reads and 46 writes.  I'm looking for confirmation that the additional reads are part of the backup process and are executed against the backup file, which there is no DMV to show the IO for.  I'm looking for an explanation of the differences.  sys.dm_io_virtual_file_stats shows same information as sys.fn_virtualFileStats as far as the IO of the individual files as I mentioned in the original question.

    Thanks for the help ahead of time.

    Steve

    Sunday, August 18, 2013 8:40 PM
  • Hello,

    The difference is that you're looking at the process as a WHOLE versus what SQL Server is telling you it's doing. The differences could be a multitude of different processes inside of SQL Server that are operating against a file that aren't counted as part of the virtual file stats. My guess would be things such as lazy writer, checkpoint, etc.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, August 19, 2013 12:49 AM
    Answerer

All replies

  • Hello,

    fn_virtualFilestats return Returns I/O statistics for database files, including log files.So no of reads you are getting is for both why dont you used its advanced version sys.dm_io_virtual_file_stats for taking look into IO request happening on particular data file.

    Below query will give you IOPS details for you datafile.Replace 1 with 2 to get info about Log file.Assuming you have single data and log file for your DB

    SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'DB_NAME'), 1);
    GO


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Sunday, August 18, 2013 5:08 AM corrected typo
    Sunday, August 18, 2013 5:03 AM
  • Both sys.dm_io_virtual_file_stats and sys.fn_virtualFileStats show IO information at the file level.  My question has to do with why the file IO shown by the either the DMV or the sys.fn show a certain amount of file IO, 10 total reads and 22 total writes but watching the process itself shows 16 reads and 46 writes.  I'm looking for confirmation that the additional reads are part of the backup process and are executed against the backup file, which there is no DMV to show the IO for.  I'm looking for an explanation of the differences.  sys.dm_io_virtual_file_stats shows same information as sys.fn_virtualFileStats as far as the IO of the individual files as I mentioned in the original question.

    Thanks for the help ahead of time.

    Steve

    Sunday, August 18, 2013 8:40 PM
  • Hello,

    The difference is that you're looking at the process as a WHOLE versus what SQL Server is telling you it's doing. The differences could be a multitude of different processes inside of SQL Server that are operating against a file that aren't counted as part of the virtual file stats. My guess would be things such as lazy writer, checkpoint, etc.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, August 19, 2013 12:49 AM
    Answerer
  •   According to process Explorer the SQLServe.exe process issued 16 reads and 46 total writes.  Now my guess is the SQLServer.exe had to write to the new backup file and that could account for the number of write differences.  What accounts for the number of read differences?  Does the SQLServer.exe process read the new backup file while/after it creates it?

    I'm looking to make sure I understand the information displayed in fn_virtualFileStats, I always tookthe numberReads and numberWrites to be a measure of IO, is this incorrect?

    Thanks ahead of time,

    Steve

    Hello Steve,

    You said you are looking in process explorer it will show you IO not only for SQL server but for OS also.That is why you are getting the difference.Above DMV only gives info at file level IOPS for DB only and process explorer provides info  for complete IOPS


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, August 19, 2013 4:18 AM
  • Sean,

    I believe I follow what you are saying but that means a process under the SQLServer.exe is writing to one of database files and its not being included in the DMV, that seems a little strange and would make the DMV pointless.  I would like to think that the File IO is taking place against a file not listed in the DMV and that is what I would like to confirm.  I guess I'll look for something to watch the backup file or to list all of the file IO sqlserver.exe is doing and against what files.

    Thanks,

    Steve

    Monday, August 19, 2013 10:13 AM