To monitor the I/O of an SQL Server database I use the following SQL :-
SELECT @@TOTAL_READ [Total Reads]
, @@TOTAL_WRITE as [Total Writes]
, CAST(@@IO_BUSY as FLOAT) * @@TIMETICKS / 1000000.0 as [IO In Secs]
GO
select @@TOTAL_READ [Total Reads]
, @@TOTAL_WRITE as [Total Writes]
, @@TOTAL_READ + @@TOTAL_WRITE as [Total IO]
, CAST(@@IO_BUSY as FLOAT) * @@TIMETICKS / 1000000.0 as [IO In Secs]
, (CAST(@@IO_BUSY as FLOAT) * @@TIMETICKS / 1000000.0) / (cast(getdate() – crdate as float)*24*60*60) as [IO per Sec]
, cast(getdate() – crdate as float)*24*60*60 as [Up Time In Secs] from sys.sysdatabases where name=’tempdb’
go
SELECT *
FROM fn_virtualfilestats(NULL,NULL);
GO
sp_monitor
go