sqldiskio-reviewlisted

# 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 ```sql -- 1. I/O latency snapshot (cumulative since restart or since last baseline) -- Best practice: capture twice 60 seconds apart and subtract to get