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:
� Number of reads and writes issued against this file
� Total number of bytes read from and written to this file
� Total time, in milliseconds, users waited for I/O completions overall (also reads and writes specifically on 2005 and 2008)
� Number of disk bytes used by this file
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.
_________________________________________________________________