A DMV a Day – Day 13

The DMV for Day 13 is sys.dm_io_virtual_file_stats, which is described by BOL as:

Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.

This DMV has two parameters, which are database_id and file_id. You can specify NULL for either parameter, in which case, information on all of the databases and/or all of the files will be returned.  This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Calculates average stalls per read, per write, and per total input/output for each database file. 
SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms, 
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) 
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;

This query allows you to see the number of reads and writes on each data and log file for every database running on the instance. It is sorted by average io stall time in milliseconds. This allows you to see which files are waiting the most time for disk I/O. It can help you to decide where to locate individual files based on the disk resources you have available. You can also use it to help persuade someone like a SAN engineer that SQL Server is seeing disk bottlenecks for certain files.

About these ads
This entry was posted in SQL Server 2008. Bookmark the permalink.

7 Responses to A DMV a Day – Day 13

  1. shaurav says:

    Hi Glen, You are doing a super amazing work…keep it up and i just love this blog…

  2. Glenn says:

    Thanks for the kind words.

  3. Fabrício says:

    Another useful query.You can write a dmv book!!!! More detailed. There any?

  4. Scott says:

    Would love a bit more info on how to interpret the results, and perhaps some guidelines on what "acceptable" values might be.

  5. SeattleDBA says:

    Hi Glenn, what’s an acceptable value for avg_io_stall_ms? The highest value I have is 720.2 ms for a specific file, would that be considered too high of a value? Or do we use baseline values to compare it against to make decisions on possibly changing the LUN location?

  6. SeattleDBA says:

    Thank you very much Glenn, this helps a ton. Have a very happy holiday season! I appreciate all of your posts, I’ve learned a lot from you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s