SQL Exec Stats, Copyright © 2006-2012 Elemental Inc. All rights reserved.

Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, Exec Stats, Stored Procs, Dist Stats, Appendix

File IO

The File IO tab displays file IO information from dm_io_virtual_file_stats. This represent the counter values since the instance startup. Later versions may show differential file IO values over set time period. Note that the first line of totals is for data files and the second line is for log files.

The last columns show the calculated Average Bytes/Read, Average ms/Read, Average Bytes per Write, the Average ms/Write, and the physical file name. Care should be taken in interpreting the file IO values based on this view. In particular, the reads probably includes database backups, which may generate 1MB reads at high latency, distorting the average values for normal operations.


The disk latencies for data reads and log writes should be color coded for high values. For some unknown reason, the first run does not show the color coding. But a subsequent runs do show color coding.

The SQL for this is in the text file output. Example below:

--File IO Stats

SELECT DB_NAME(v.database_id) [Database], m.name [File] ,
  v.database_id [dbid], v.file_id [fileid], m.type, m.data_space_id dsid, m.state,
  CONVERT(real, (m.size*8./1024.)) [Size], 0 TotExtents, 0 UsedExtents,
  num_of_reads Reads, num_of_bytes_read ReadBytes, io_stall_read_ms RdIOStall,
  num_of_writes Writes, num_of_bytes_written WrBytes, io_stall_write_ms WrIOStall,
  ISNULL(p.io_pending_ms_ticks,0) IOPen_ms,
  ISNULL(p.io_pending,0) IOPending,
  ISNULL(IOPenCnt,0) IOPenCnt,
  m.physical_name AS PhyName
FROM sys.dm_io_virtual_file_stats(NULL, NULL) v
INNER JOIN sys.master_files m WITH (NOLOCK) ON m.database_id = v.database_id AND m.file_id = v.file_id
LEFT JOIN (
 SELECT io_handle, COUNT(*) IOPenCnt,
  SUM(io_pending_ms_ticks) io_pending_ms_ticks, SUM(io_pending) io_pending
 FROM sys.dm_io_pending_io_requests WITH (NOLOCK)
 GROUP BY io_handle
) p ON p.io_handle = v.file_handle
ORDER BY v.database_id, v.file_id