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.



Subscribe to this blog
Briefcase
Print
Posted by Anonymous on 13 April 2010
Pingback from Dew Drop – April 13, 2010 | Alvin Ashcraft's Morning Dew
Posted by Jason Brimhall on 13 April 2010
Once again, nice script. I like the time you have taken to put this series together and share the scripts with the community.
Posted by Glenn Berry on 13 April 2010
Thanks Jason. I am glad people are finding these interesting and useful.
Posted by Anonymous on 13 April 2010
Pingback from we bought a salvaged car not to long ago and it so happends that the guy who sold it to us never got it checke? | Insurance Salvage Cars
Posted by Anonymous on 14 April 2010
Pingback from “clean Title” yet insurance co says “salvaged”? | Insurance Salvage Cars
Posted by Anonymous on 15 April 2010
Pingback from a dmv a day – SQL Server Central | Salvage Cars | Automotive Review | Salvage Cars