Delta and Cumulative IO Stats

Checking IO statistics has always been one of the most important metrics for any DBA. It is no surprise that most people I know will have some version of the following two scripts that use the dmv sys.dm_io_virtual_file_stats (Transact-SQL). The dmv itself is a cumulative view of your IO, therefore in order to get a particular time slice we need to delta the counters. The two scripts below are therefore cumulative io and  delta io, which I believe for me is quite self-explanatory.

These scripts should give you a rough idea of how your system performs IO wise, and if needed in a particular time window. For example you may see slow down at particular times that you want to have a closer look at.

I have left out mentioning what is a good metric or a bad metric because it will generally depend on your system. A really high amount of io going thru your tempdb could be a sign of a lots of sorts etc happening, or it could be that you are rebuilding your indexes there. High ms waits on reads/writes again could mean that you need to redesign your storage subsystem, or it could be that it spikes at one particular point in the day, due to several load jobs happening at the same time that would be more appropriately spread across a larger window.

Delta IO Stats

--http://jongurgul.com/blog/delta-cumulative-io-stats/
SELECT GETDATE() [dtStart],iovfs.*,mf.[name],mf.[type_desc] INTO #dm_io_virtual_file_stats_start 
FROM sys.master_files mf INNER JOIN sys.dm_io_virtual_file_stats(NULL,NULL) iovfs
ON mf.[database_id] = iovfs.[database_id] and mf.[file_id] = iovfs.[file_id]
WAITFOR DELAY '01:00:00'; --1 hour
SELECT 
DB_NAME(t1.[database_id]) [DatabaseName]
,t1.[file_id] [FileID]
,t1.[name] [LogicalName]
,t1.[type_desc] [FileType]
,SUM(t2.[num_of_bytes_read])-SUM(t1.[num_of_bytes_read]) [Read_bytes]
,SUM(t2.[num_of_bytes_written])-SUM(t1.[num_of_bytes_written]) [Written_bytes]
,(SUM(t2.[num_of_bytes_read])-SUM(t1. [num_of_bytes_read]))/1048576 [Read_MiB]
,(SUM(t2.[num_of_bytes_written])-SUM(t1. [num_of_bytes_written]))/1048576 [Written_MiB]
,SUM(t2.[num_of_reads])-SUM(t1. [num_of_reads]) [Read_Count]
,SUM(t2.[num_of_writes])-SUM(t1. [num_of_writes]) [Write_Count]
,SUM(t2.[num_of_reads]+t2.[num_of_writes])-SUM(t1.[num_of_reads]+t1.[num_of_writes]) [IO_Count]
,CONVERT(DECIMAL (15,2),SUM(t1.[num_of_bytes_read])/(NULLIF(SUM(t1.[num_of_bytes_read]+t1.[num_of_bytes_written]),0)*0.01)) [Read_Percent]
,CONVERT(DECIMAL (15,2),SUM(t1.[num_of_bytes_written])/(NULLIF(SUM(t1.[num_of_bytes_read]+t1.[num_of_bytes_written]),0)*0.01)) [Write_Percent]
,CONVERT(DECIMAL (15,2),SUM(t2.[num_of_bytes_read]-t1.[num_of_bytes_read])/NULLIF((SUM(t2.[num_of_bytes_read]+t2.[num_of_bytes_written]-t1.[num_of_bytes_read]-t1.[num_of_bytes_written])*0.01),0)) [Read_Delta_Percent]
,CONVERT(DECIMAL (15,2),SUM(t2.[num_of_bytes_written]-t1.[num_of_bytes_written])/NULLIF((SUM(t2.[num_of_bytes_read]+t2.[num_of_bytes_written]-t1.[num_of_bytes_read]-t1.[num_of_bytes_written])*0.01),0)) [Write_Delta_Percent]
,CONVERT(DECIMAL (15,2),COALESCE(SUM(t1.[io_stall_read_ms])/NULLIF(SUM(t1.[num_of_reads]*1.0),0),0)) [AverageReadStall_ms]
,CONVERT(DECIMAL (15,2),COALESCE(SUM(t1.[io_stall_write_ms])/NULLIF(SUM(t1.[num_of_writes]*1.0),0),0)) [AverageWriteStall_ms]
,CONVERT(DECIMAL (15,2),COALESCE(SUM(t2.[io_stall_read_ms]-t1.[io_stall_read_ms])/NULLIF(SUM(t2.[num_of_reads]-t1.[num_of_reads]*1.0),0),0)) [AverageReadStall_Delta_ms]
,CONVERT(DECIMAL (15,2),COALESCE(SUM(t2.[io_stall_write_ms]-t1.[io_stall_write_ms])/NULLIF(SUM(t2.[num_of_writes]-t1.[num_of_writes]*1.0),0),0)) [AverageWriteStall_Delta_ms]
,t1.dtStart
,GETDATE() dtEnd
FROM #dm_io_virtual_file_stats_start t1 
INNER JOIN 
(SELECT iovfs.*,mf.[name],mf.[type_desc] FROM sys.master_files mf INNER JOIN sys.dm_io_virtual_file_stats(NULL,NULL) iovfs
ON mf.[database_id] = iovfs.[database_id] AND mf.[file_id] = iovfs.[file_id]
) t2 ON t1.[database_id] = t2.[database_id] AND t1.[file_id] = t2.[file_id]
GROUP BY t1.[dtStart],DB_NAME(t1.[database_id]),t1.[file_id],t1.[name],t1.[type_desc];
DROP TABLE #dm_io_virtual_file_stats_start;

http://gallery.technet.microsoft.com/scriptcenter/Delta-IO-Stats-61d7deaa

Cumulative IO Stats

--http://jongurgul.com/blog/delta-cumulative-io-stats/
SELECT 
 QUOTENAME(DB_NAME(iovfs.[database_id])) [DatabaseName]
,iovfs.[file_id] [FileID]
,mf.[name] [LogicalName]
,mf.[type_desc] [FileType]
,SUM(iovfs.[num_of_bytes_read]) [Read_bytes]
,SUM(iovfs.[num_of_bytes_written]) [Written_bytes]
,SUM(iovfs.[num_of_bytes_read])/1048576 [Read_MiB]
,SUM(iovfs.[num_of_bytes_written])/1048576 [Written_MiB]
,SUM(iovfs.[num_of_reads]) [Read_Count]
,SUM(iovfs.[num_of_writes]) [Write_Count]
,SUM(iovfs.[num_of_reads]+iovfs.[num_of_writes]) [IO_Count]
,CONVERT(DECIMAL (15,2),SUM([num_of_bytes_read])/(SUM([num_of_bytes_read]+[num_of_bytes_written])*0.01)) [Read_Percent]
,CONVERT(DECIMAL (15,2),SUM([num_of_bytes_written])/(SUM([num_of_bytes_read]+[num_of_bytes_written])*0.01)) [Write_Percent]
,CONVERT(DECIMAL (15,2),COALESCE(SUM(iovfs.[io_stall_read_ms])/NULLIF(SUM(iovfs.[num_of_reads]*1.0),0),0)) [AverageReadStall_ms]
,CONVERT(DECIMAL (15,2),COALESCE(SUM(iovfs.[io_stall_write_ms])/NULLIF(SUM(iovfs.[num_of_writes]*1.0),0),0)) [AverageWriteStall_ms]
FROM sys.master_files mf INNER JOIN sys.dm_io_virtual_file_stats(NULL,NULL) iovfs
ON mf.[database_id] = iovfs.[database_id]
AND mf.[file_id] = iovfs.[file_id] 
GROUP BY iovfs.[database_id],iovfs.[file_id],mf.[name],mf.[type_desc]
--GROUP BY GROUPING SETS ((iovfs.[database_id],iovfs.[file_id],mf.[name],mf.[type_desc]),(iovfs.[database_id]),()) --SQL2008
ORDER BY QUOTENAME(DB_NAME(iovfs.[database_id])),iovfs.[file_id]

http://gallery.technet.microsoft.com/scriptcenter/Cumulative-IO-Stats-3af893cf

One thought on “Delta and Cumulative IO Stats

  1. Pingback: I/O,I/O it’s off to … Bang my head against a wall! | Padre's SQL Resort

Leave a Reply