sqldiskio-review

SQL Server Disk I/O Review Skill

Purpose

Analyze SQL Server file-level I/O performance and storage configuration issues. Applies 15 checks (Z1–Z15) across three categories:

• Z1–Z5 — Latency and stall analysis: data file read/write latency, log file write latency, stall ratio per file, and hot file detection
• Z6–Z10 — Storage placement and configuration: data and log on the same volume, TempDB co-location, system drive placement, file count imbalance, and TempDB log latency
• Z11–Z15 — Auto-growth patterns: auto-growth events in recent hours, fixed-MB growth on data files, log file growth too small, growth events during peak hours, and multi-snapshot I/O trend worsening

Input

Accept any of:

• A snapshot pair from sys.dm_io_virtual_file_stats capture query below — two captures taken seconds/minutes apart with the delta calculated (preferred)
• A single raw output from sys.dm_io_virtual_file_stats (cumulative since startup); note that single captures reflect all-time averages since the last SQL Server restart
• Output from sys.master_files for file placement and auto-growth configuration
• Default trace query output showing recent auto-growth events
• A natural language description of symptoms ("log file grew three times today, data drive showing 80ms reads")

Recommended capture queries

-- 1. I/O latency snapshot (cumulative since restart or since last baseline)
-- Best practice: capture twice 60 seconds apart and subtract to get interval stats
SELECT
    DB_NAME(vfs.database_id)            AS database_name,
    mf.physical_name,
    mf.type_desc,
    vfs.io_stall_read_ms,
    vfs.num_of_reads,
    vfs.io_stall_write_ms,
    vfs.num_of_writes,
    vfs.io_stall,
    vfs.num_of_bytes_read    / 1048576  AS mb_read,
    vfs.num_of_bytes_written / 1048576  AS mb_written,
    CASE WHEN vfs.num_of_reads  > 0
         THEN vfs.io_stall_read_ms  / vfs.num_of_reads  ELSE 0 END AS avg_read_ms,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms / vfs.num_of_writes ELSE 0 END AS avg_write_ms,
    CASE WHEN (vfs.num_of_reads + vfs.num_of_writes) > 0
         THEN vfs.io_stall / (vfs.num_of_reads + vfs.num_of_writes)
         ELSE 0 END AS avg_stall_per_io_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON vfs.database_id = mf.database_id
 AND vfs.file_id     = mf.file_id
ORDER BY vfs.io_stall DESC;

-- 2. File configuration (placement and auto-growth settings)
SELECT
    DB_NAME(database_id) AS database_name,
    name                 AS logical_name,
    physical_name,
    type_desc,
    size * 8 / 1024      AS size_mb,
    CASE is_percent_growth
         WHEN 1 THEN CAST(growth AS VARCHAR) + '%'
         ELSE CAST(growth * 8 / 1024 AS VARCHAR) + ' MB'
    END AS growth_setting,
    max_size,
    is_read_only
FROM sys.master_files
ORDER BY database_id, type;

-- 3. Auto-growth events from default trace (last 24 hours)
DECLARE @tracefile NVARCHAR(500);
SELECT @tracefile = REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), 500))
    + N'log.trc'
FROM sys.traces WHERE is_default = 1;

SELECT
    DatabaseName,
    FileName,
    CASE EventClass WHEN 92 THEN 'Data Auto-Grow'
                    WHEN 93 THEN 'Log Auto-Grow' END AS event_type,
    Duration                        AS duration_ms,  -- Duration is reported in milliseconds for event classes 92/93
    IntegerData * 8 / 1024          AS growth_mb,
    StartTime
FROM fn_trace_gettable(@tracefile, DEFAULT)
WHERE EventClass IN (92, 93)
  AND StartTime >= DATEADD(HOUR, -24, GETDATE())
ORDER BY StartTime DESC;