Tip: Tracking I/O Statistics with SQL Server 2000's fn_virtualfilestats

A new SQL Server 2000 function called fn_virtualfilestats lets you track I/O statistics from the SQL Server 2000 version of T-SQL without relying on the Windows 2000 or Windows NT Performance Monitor. Fn_virtualfilestats is a system table-valued function that provides statistical information about a file, such as the number of asynchronous I/Os. Table-valued functions return a result set that looks and acts like a table and to which you can assign the new Table data type. You can use a table function wherever T-SQL allows a regular table name.

The fn_virtualfilestats function helps track how long users have to wait to read or write to a file. The function also helps identify files that experience a lot of I/O activity. From time to time, using I/O monitoring procedures that don't rely on Performance Monitor can be convenient, such as when you don't have administrator rights for running Performance Monitor on the NT machine or when you simply want to capture results in a SQL Server table for historical reporting. The fn_virtualfilestats function can prove handy in those situations.

Fn_virtualfilestats' syntax isfn_virtualfilestats ( [@DatabaseID=] database_id    , [ @FileID = ] file_id )

Table A describes the columns in the table that fn_virtualfilestats returns.

As you can see, you need to know the file_id of the file for which you want to track I/O statistics. If you know the file's logical file name, you can easily obtain the file's file_id by using the FILE_ID function:

SELECT file_id('master')

Alternatively, you can use sp_helpfile to generate a complete list of files, including their file_ids, for the current database.

As I noted earlier, you can call table-valued functions such as fn_virtualfilestats anywhere you would reference a real table, but you must preface calls to table-valued functions with '::', as the following statement shows:

SELECT * FROM ::fn_virtualfilestats(1, 1)