Code to calculate numer of reads writes per database in a server

To measure loads we can have a look at the function fn_virtualfilestats. This is a script to get this information for every database in an instance. This is information often requested by customers and will be included in an reporting services report when i find the time to finish that work. The numbers are the total sum from the last restart of the service. By running this once every day on our servers we can calculate the total numbers / day / database.

set nocount on
CREATE TABLE #Perf_stats (
dbid int not NULL,
fileid bigint not null,
timestamps bigint not null,
numberreads bigint not null,
numberwrites bigint not null,
bytesread bigint not null,
byteswritten bigint not null,
iostall bigint not null)

declare @dbide varchar(50)
declare @fileid varchar(50)
declare Nonsysdatabases cursor for
SELECT
sysdatabases.dbid ,
sysaltfiles.fileid
FROM sysaltfiles INNER JOIN sysdatabases
ON sysaltfiles.dbid = sysdatabases.dbid

open Nonsysdatabases
fetch next from Nonsysdatabases into @dbide,@fileid
while @@fetch_status = 0
begin
insert into #Perf_stats SELECT * FROM :: fn_virtualfilestats(@dbide, @fileid)
fetch next from Nonsysdatabases
into @dbide,@fileid
end
close Nonsysdatabases
deallocate Nonsysdatabases
select db_name(dbid)as dbname,fileid,numberreads,numberwrites,bytesread,bytesread/1024/1024/1024 as'read(Gb)',byteswritten,byteswritten/1024/1024/1024 as 'written(gb)',iostall from #Perf_stats
order by numberreads desc
drop table #Perf_stats