Parent, SQL Exec Stats, Copyright 2006-2018 Elemental Inc. All rights reserved.
Read Me, Intro, Main, General, Databases, File IO, Tables, Indexes, ExecStats, StoredProcs, DistStats, IxOpStats, Appendix

update in progress 2015-Feb

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.

File IO 1a

The original code used DBCC SHOWFILESTATS to get file spaceusage, which reported Total and Used Extents (64K units). Prior to SQL Server 2012, dm_db_file_space_usage only reported tempdb usage. For 2012, version 11, the DMV now reports file space usage for all databases. In this grid, the Total and Used columns are now reported in MB.

The code for 2008 and earlier:

SELECT database_id, file_id, filegroup_id
, (unallocated_extent_page_count/128) Unallocated
, (version_store_reserved_page_count + user_object_reserved_page_count
+ internal_object_reserved_page_count + mixed_extent_page_count)/128) AS allocated_page_ct
FROM sys.dm_db_file_space_usage

The code for 2012:

SELECT database_id, file_id, filegroup_id
, (total_page_count/128) TotalExtents
, (allocated_extent_page_count/128) UsedExtents
FROM sys.dm_db_file_space_usage

There are additional columns in dm_db_file_space_usage for version store, user reserved, internal reserved, and mixed extents which I may include at a later time.

File IO 1b

The columns Reads, Read Bytes, Read IO Stall, Writes, Write Bytes and Write IO Stall are from dm_io_virtual_file_stats. Columns Average Bytes/Read, Average ms/Read, Average Bytes per Write, and Average ms/Write are calculated from the previous columns.

In between are columns with IO pending values from dm_io_pending_io_requests. The last column is the physical file name. There might be a is percent column for the growth value?

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.

File IO SQL

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]
, CASE WHEN is_percent_growth = CONVERT(real,1) THEN growth
ELSE CONVERT(real,growth*8./1024.) END Grow
, 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, CONVERT(tinyint,m.is_percent_growth) is_percent
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

 

Builds from 2013-Sep-23 on will have a Volumes tab using information from dm_os_volume_stats, which is only available for SQL Server 2008 R2 and later. The file IO read and write stats are rolled up by database in the Database tab, by FileGroup in the Dataspace tab and be OS Volume in the Volumes tab.

 

Prev        Next