io_stall_read_ms seems to be higher than actual run time for query.
create database filestats; use filestats; create table t1_primary( a int identity ,b int ,c datetime ,d char(780) ,constraint pk_p_a primary key clustered (a)); set nocount on; declare @n int = 0; while @n < 12800 begin insert into t1_primary select @n, CURRENT_TIMESTAMP, ABS(CHECKSUM(NEWID())) select @n += 1 end set statistics io, time on; declare @before datetime dbcc dropcleanbuffers checkpoint if object_id('tempdb..#vfsBefore') is not null drop table #vfsBefore if object_id('tempdb..#vfsAfter') is not null drop table #vfsAfter select * into #vfsBefore from sys.dm_io_virtual_file_stats(db_id('filestats'), null) select @before = CURRENT_TIMESTAMP select * from dbo.t1_primary select DATEDIFF(ms, @before, CURRENT_TIMESTAMP) select * into #vfsAfter from sys.dm_io_virtual_file_stats(db_id('filestats'), null) select db_name(b.database_id) ,mf.name ,a.num_of_reads-b.num_of_reads as NumReads ,a.num_of_bytes_read-b.num_of_bytes_read as NumBytesRead ,a.io_stall_read_ms-b.io_stall_read_ms as IOStallReadMS ,a.num_of_writes-b.num_of_writes as NumWrites ,a.num_of_bytes_written-b.num_of_bytes_written as NumBytesWritten ,a.io_stall_write_ms-b.io_stall_write_ms as IOStallWriteMS ,a.io_stall-b.io_stall as IOStallTotal ,a.size_on_disk_bytes-b.size_on_disk_bytes as SizeOnDiskBytes ,mf.physical_name from #vfsBefore b join #vfsAfter a on b.database_id = a.database_id and b.file_id = a.file_id join sys.master_files mf on b.database_id = mf.database_id and b.file_id = mf.file_id
Running this results in the following:
Virtual file stats: num_of_reads = 26, io_stall_read_ms = 2153
Statistics IO: Table 't1_primary'. Scan count 1, logical reads 2571, physical reads 3, read-ahead reads 2552
Statistics time: CPU time = 46 ms, elapsed time = 595 ms.
Run time, DATEDIFF function: 593 ms
Not surprisingly a clustered index scan is performed, single threaded. How come the io_stall_read_ms value is higher than the time executing the query. What is going on here?