How Fast and Robust Is Your Storage? - Professional SQL Server 2005 Performance Tuning - page 418

LookingatI/OPerformancefromwithinSQLServer
There are a number of very useful DMVs in SQL Server 2005 that show you information about the I/Os
being generated by SQL Server.

The four I/O-related DMVs are:

❑ sys.dm_io_backup_tapes

❑ sys.dm_io_pending_io_requests

❑ sys.dm_io_cluster_shared_drives

❑ sys.dm_io_virtual_file_stats

Only two of these are of any interest here, and these are discussed below.

sys.dm_io_pending_io_requests
This DMV shows any pending I/O requests. This is actually a view, so you can select directly from it
using the following T-SQL:

select * from sys.dm_io_pending_io_requests

This will give you a result set that looks similar to the output in Table 12-1, but will reflect any
outstanding I/O requests on your system at the time it was run.

However if you have no pending I/O requests, you get nothing.

Table 12-1 is a very long table that won't fit on the printed page. It has been broken into three segments
for easier viewing.

sys.dm_io_virtual_file_stats
This DMV shows you the cumulative I/O counters for each file configured in SQL Server and is the
replacement of the SQL Server 2000 function fn_virtual_file_stats . This DMV is a function rather
than a view, so it needs to be given arguments for the database id and file. These values can be null, but
don't default to null, so you have to use the following T-SQL to get all the file stats:

Select * from sys.dm_io_virtual_file_stats (null,null)

The parameters for this function are the database id and the file id.

Table 12-2 shows an example of the first few lines of the result set when we ran this on one of our
SQL Servers.

Table 12-2 is a very long table that won't fit on the printed page. It has been broken into three segments
for easier viewing.

What's more useful is to target the function at either a specific database, or preferably a specific file.
To do that you can use something like the following T-SQL.

First, find out what DBs you have available:

Select * from sys.databases