Measure database file i/o with fn_virtualfilestats (SQL2000 AND SQL2005)

I'm posting another admin script today which can be useed on either SQL Server 2000 or SQL Server 2005 to measure i/o behaviour against specific database files, using the fn_virtualfilestats() system TSQL function.

This script is useful when you need to identify the source of disk bottlenecks on a database server.

For example, you might have identified that a disk bottleneck on your exists on a server via use of a Perfmon counter such as [\\YOURSERVERNAME\LogicalDisk(D:)\Avg. Disk Read Queue Length], but multiple database files exist on the D:\ volume and you're not sure which specific file is causing the i/o activity that is the source of the bottleneck.

The output from this script provides a break-down of specific i/o data on a per-database-file basis, allowing you to see how many bytes have been read to or written from each file (total, since the last restart of SQL Server) and also the total stall time for each file (ms).

More columns were added to the output of fn_virtualfilestats() for SQL Server 2005, providing even further detailed data than from the SQL Server 2000 version of fn_virtualfilestats(). Most significantly, stall time is only provided as a total in the SQL 2000 version but is broken down into read stalls & write stalls in the SQL 2005 version.

When this script runs, it determines which version of SQL Server it is running against & provides the appropriate output for the discovered version. So, when you run this against SQL 2000 you can expect the new SQL 2005 output columns to remain blank.

I've often found fn_virtualfilestats() very useful in demonstrating how much i/o the tempdb can generate compared to user databases. In fact, this is what prompted me to post this script today - I used it yesterday to show a customer that their tempdb was generating 5 x the write i/o activity compared to their main production database. This led to some further useful discussion about file placement.


/**********
* fn_virtualfilestats() script
* Greg Linwood
* greg@SolidQualityLearning.com
* Feb 25th, 2006
*
* Collects fn_virtualfilestats() for all databases
* on either SQL 2000 or SQL 2005 instances.
**********/
set nocount on
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go
declare @ver int
set @ver = cast(serverproperty('Productversion') as char(1))
create table #filestats (
DbId int not null,
DBName varchar (255) not null,
fileid int not null,
FileName varchar (255) not null,
SampleTime datetime not null,
TS bigint null,
NumberReads bigint null,
BytesRead bigint null,
IoStallReadMS bigint null,
NumberWrites bigint null,
BytesWritten bigint null,
IOStallWriteMS bigint null,
IOStallMS bigint null,
BytesOnDisk bigint null,
constraint pk primary key (DbId, fileid, SampleTime))
declare @database_name varchar(255)
declare @database_id int
declare @file_id int
declare @file_name varchar (1000)
declare @sql nvarchar(4000)
declare cr cursor for
select sd.name, sd.dbid, saf.fileid, saf.name
from sysaltfiles saf
join sysdatabases sd on saf.dbid = sd.dbid
order by sd.name, saf.fileid
open cr
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
while @@fetch_status = 0
begin
if @ver = 8
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, NumberWrites, BytesWritten, IOStallMS)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, NumberWrites, BytesWritten, IOStallMS
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end


if @ver = 9
begin
select @sql = N'
insert into #filestats (DBName, FileName, DbId, FileId, SampleTime,
TS, NumberReads, BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk)
select @dn, @fn, DbId, FileId, getdate(), [Timestamp], NumberReads,
BytesRead, IoStallReadMS, NumberWrites, BytesWritten,
IOStallWriteMS, IOStallMS, BytesOnDisk
from :: fn_virtualfilestats(@database_id, @file_id) '
execute sp_executesql @sql
, N'@dn varchar (255), @fn varchar (255), @database_id int, @file_id int'
, @dn = @database_name
, @fn = @file_name
, @database_id = @database_id
, @file_id = @file_id
end
fetch next from cr
into @database_name, @database_id, @file_id, @file_name
end
go
close cr
deallocate cr
go
select * from #filestats
go
if object_id('tempdb..#filestats') > 0
drop table #filestats
go