THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

sys.dm_io_virtual_file_stats

Excellent dmv that shows, for each file that SQL Server uses for the databases, stats on how frequently the file has been used by the database. This is one of the primary dynamic management views I use almost daily (well, not usually when I am on vacation, but even sometimes then!)  The file can be any file used in the database, including the log or full text files.

Type: Function

Parameters:

  • database_id – key of the database, retrieved from sys.databases
  • file_id - key of a file in a database. Can be retrieved from sys.database_files if you are working in the context of a database, or sys.master_files will give you all files in all databases

Columns:

  • database_id, file_id – same as the parameter descriptions
  • sample_ms – the number of milliseconds that have passed since the values for sys.dm_io_virtual_file_stats were reset the only way to reset the values is to restart the server.
  • num_of_reads – number of individual read operations that were issued to the file. Note that this is physical reads, not logical reads. Logical reads would not be registered.
  • num_of_bytes_read – the number of bytes that were read, as opposed to the number of reads. The size of a read is not a constant value that can be calculated by the number of reads.
  • Io_stall_read_ms – total time user processes waited for IO. Note that this number can be much greater than the sample_ms. If 10 processes are trying to use the file simultaneously, but the disk is only able to server 1, then you might get 9 seconds waiting over a 10 second time period.
  • num_of_writes , num_of_bytes_written, io_stall_write_ms - the same as the read values, except for writes.
  • io_stall – sum of io_stall_write_ms and io_stall_read_ms
  • size_on_disk_bytes – the size of the file in bytes
  • file_handle – the Windows file handle of the file (Books Online)

Example:

For all databases, get vital stats on how busy the file has been, since the last restart:

select db_name(mf.database_id) as databaseName, mf.physical_name, 
       
num_of_reads, num_of_bytes_read, io_stall_read_ms, num_of_writes,
      
num_of_bytes_written, io_stall_write_ms, io_stall,size_on_disk_bytes
from sys.dm_io_virtual_file_stats(null,null) as divfs
        
join sys.master_files as mf
             
on mf.database_id = divfs.database_id
                
and mf.file_id = divfs.file_id

Published Friday, July 27, 2007 9:26 PM by drsql

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Linchi Shea said:

Louis;

Are you sure num_of_writes are the physical writes for the data files?

Linchi

July 29, 2007 11:29 PM
 

drsql said:

Linchi, I am 99% sure, based on testing.  On my laptop, I ran the query in the Examples, and then this query (which was not in cache):

select *

from   adventureWorks.sales.individual

Then I reran the example query, and it showed a change in reads.  I then ran the query again, and this time no change in reads.  Next I ran a different query:

select *

from   adventureWorks.sales.customer

And saw the same behavior.  If I am interpreting this wrong, please let me know :)

July 31, 2007 11:20 PM
 

Linchi Shea said:

Louis;

But that's for reads. I was referring to the num_of_writes column.

August 1, 2007 5:11 PM
 

drsql said:

Duh, sorry I didn't read your question well enough.  I assumed that based on the value in the reads column.   This is something I will need to verify (or someone who reads this could :) but I am quite sure of it.  Thanks for pointing this out.

August 1, 2007 11:32 PM
 

Turgay Sahtiyan said:

ty for this good post.

June 7, 2010 2:03 AM
 

Jason said:

Logical reads on memory, not on file. Logical reads is not neccessary physical reads. Logical writes is always physical writes (Lazy writer will write dirty pages back to files).

July 26, 2011 11:41 AM
 

drsql said:

Jason, I think you have it wrong in this case. For most of the read counters, you are completely correct, but this DMV is all about physical writes. It tells you the activity to the physical files, not the reads for any given SQL operation. Look at my reply to Linchi about the select from AdventureWorks.  It incremented when the data was not in cache, but did when it was in cache.

July 26, 2011 2:23 PM
 

Jason said:

sys.dm_io_virtual_file_stats DMV reports physical writes. See BOL. I think you have misunderstood what I am trying to show. (there is no need to report logical writes)

July 26, 2011 2:43 PM
 

Jason said:

By the way, select is a "read". If the data is already cached, it turns to logical reads; if not, you could see read-ahead reads, i.e. physical reads. This effect can be easily produced.

July 26, 2011 2:49 PM
 

drsql said:

Now I am confused. You said logical reads on memory, not on file.

All I was saying is that this DMV is all about physical activity, not logical, read or write. So you use this DMV to determine how active the file is.  You wouldn't see any read activity for logical reads, and you wouldn't see the logical writes to data pages in here either, but you would see them when the lazy writer flushed the pages, and you would all log writes (which are all directly to the disk subsystem)

July 26, 2011 4:51 PM
 

Jason said:

That is right. I have investigated this for a long time. sys.dm_io_virtual_file_stats shows physical reads/writes information. I was addressing one of the question people asked, whether it is physical writes. In contrast, there is nothing else, logical writes will not be reported here. Every logical write will show up in physical write so when monitoring you just need to pull one.

July 27, 2011 11:25 AM
 

Chirag said:

i have very huge DB and below are the results for "sys.dm_io_virtual_file_stats"

database_id:7

file_id: 2

sample_ms: 53280097

num_of_reads: 1781

num_of_bytes_read: 14368768

io_stall_read_ms: 1627211

num_of_writes: 7694188

num_of_bytes_written: 10186775552

io_stall_write_ms: 629210284

io_stall: 630837495

size_on_disk_bytes: 21706571776

file_handle: 0x000000000000079C

currently i am facing SQL SERVER slow issues.

i will appreciate if any body help me

August 24, 2011 7:44 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement