Disk performance is a common issue on SQL Server systems. In these situations, it is very useful to know how much SQL Server is waiting for I/O and what files are handling the most work or experiencing the poorest performance. The virtual file stats DMF provides this information and is ideal for periodic captures, e.g., at the beginning, middle, and end of a day. This function returns one row per file and can return this information either for specific files or for every file attached to a SQL Server instance.
To retrieve information for all files attached to a 2005 or 2008 instance, use the following syntax:
select * from sys.dm_io_virtual_file_stats (NULL, NULL)
The equivalent on SQL 2000 is as follows:
select * from ::fn_virtualfilestats(-1,-1)
Some of the most useful information is shown below:
Since these values are cumulative from the last time the SQL Server instance was restarted, differences between samples must be computed. Either SQL Server T-SQL or Excel can be used to perform these calculations. In addition, the database and file IDs must be translated into meaningful names using the sys.databases and sys.database_files DMVs on 2005 and 2008. For those still using 2000, system tables must be used to decode these values.
Using this information, a DBA can prove whether the disk subsystem is performing adequately and possibly find alternative disk locations to which database files may be moved.