/sqldiskio-review

name: sqldiskio-review
description: Analyze SQL Server file-level I/O latency and auto-growth events using sys.dm_io_virtual_file_stats, sys.master_files, and default trace auto-growth records. Applies 15 checks (Z1–Z15) covering data and log file latency thresholds, hot file detection, stall ratio analysis, data and log placement on the same volume, TempDB co-location with user databases, auto-growth event frequency and sizing, file growth during production hours, system drive file placement, and multi-snapshot I/O trend analysis. Use this skill whenever a DBA suspects slow I/O, queries show PAGEIOLATCH or WRITELOG waits, or a file grew unexpectedly. Trigger when pasting output from sys.dm_io_virtual_file_stats or sys.master_files.
triggers:
  - /sqldiskio-review
  - /diskio-review
  - /io-latency

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;

---

Thresholds Reference

| Metric | Good | Warning | Critical | |--------|------|---------|----------| | Data file avg read latency | < 10 ms | 10–20 ms | > 20 ms | | Data file avg write latency | < 10 ms | 10–20 ms | > 20 ms | | Log file avg write latency | < 5 ms | 5–10 ms | > 10 ms | | Stall ratio (avg stall ms per I/O operation) | < 5 ms | 5–15 ms | > 15 ms | | Hot file: single file share of total I/O | < 60% | 60–80% | > 80% | | Auto-growth events in last 24 h | 0 | 1–3 | > 3 | | Auto-growth fixed-size increment | ≥ 256 MB | 64–255 MB | < 64 MB |

> **Threshold provenance:** The data/log avg-latency bands (Z1–Z3) reflect widely-cited storage-latency guidance. The remaining cutoffs — **Z4 (stall ratio), Z5 (hot-file share), and Z11–Z15 (auto-growth event count and increment sizing)** — are **reasonable operational heuristics, not Microsoft-documented values.** Use them to prioritize where to look; confirm an actual problem against the workload's own baseline rather than the number alone.

---

Latency and Stall Checks (Z1–Z5)

Run these first — latency is the direct measure of disk I/O quality.

Z1 — Data File Read Latency

• **Trigger:** `avg_read_ms` for any data file (type_desc = `ROWS`) > 20 ms
• **Severity:** Warning if 10–20 ms; Critical if > 20 ms
• **Fix:** Data read latency above 20 ms indicates the storage subsystem is not keeping up with SQL Server's I/O demands. Root causes: (1) insufficient IOPS on the volume — check if the storage tier (HDD, SSD, NVMe) is appropriate for the workload; (2) storag