--- name: investigate-sql-data-io-pressure description: "Use when data-file reads are slow or heavy: PAGEIOLATCH waits, high read latency, many physical reads, Azure SQL data IO throttling. For WRITELOG or log latency, use `investigate-sql-log-io-pressure`." argument-hint: "[TopN] [DataIoThreshold] [ConfirmLookbackMinutes] [HistoryLookbackHours] [MinExecutionCount] [MinTotalPhysicalReads]" --- # Data IO Pressure Investigation > For **log IO pressure** (WRITELOG waits, log throughput), use the **`investigate-sql-log-io-pressure`** skill instead. ## Scope - Confirms sustained data IO pressure, then identifies real-time and historical physical-read consumers and checks data file latency and buffer pool health. - For a broad CPU, memory and IO resource usage report, use the `resource-usage-sqlserver` skill. ## Required tools - A read-only SQL query tool connected to the target server and database. Prefer, in order: the SQL Tools MCP server that ships with the `microsoft-sql-admin` plugin (`mssql_list_database_connections`, `mssql_connect_database`, then `mssql_execute_query`); any other SQL tool the agent host provides for running T-SQL and reading result sets; [`sqlcmd`](https://learn.microsoft.com/sql/tools/sqlcmd/sqlcmd-utility) from a terminal. If the SQL Tools MCP server is not connected or lists no connections, follow the `sql-tools-mcp-setup` skill, which asks before installing or configuring anything. If no SQL tool can be made available, give the user the T-SQL to run and ask for the results. - Least privilege: connect that tool with a diagnostic login that cannot write, for example one that holds only `VIEW SERVER PERFORMANCE STATE` (`VIEW SERVER STATE` before SQL Server 2022) or, on Azure SQL Database, `VIEW DATABASE PERFORMANCE STATE` or `##MS_ServerStateReader##`, plus any other read permission these queries need. A tool is only as read-only as the login it connects with. ## Prerequisites - Must be connected to the target database. - Confirm which server and database you are connected to (e.g. `SELECT @@SERVERNAME, DB_NAME(), SUSER_SNAME(), SERVERPROPERTY('EngineEdition')`). - Steps 2 and 6 query live DMVs; run them while data IO pressure is active. ## Parameters | Parameter | Default | Description | |-----------|---------|-------------| | TopN | 10 | Number of top queries/sessions to return in each step | | DataIoThreshold | 80 | Data IO percentage threshold for confirming pressure (Azure SQL) | | ConfirmLookbackMinutes | 10 | Window (minutes) for the resource stats confirmation check | | HistoryLookbackHours | 12 | Lookback window (hours) for the cached plan-history query | | MinExecutionCount | 10 | Minimum execution count for a plan to appear in Step 3 | | MinTotalPhysicalReads | 50000 | Minimum cumulative `total_physical_reads` for Step 3 | ## Investigation Workflow Execute all steps in order. Steps 2 and 6 target live sessions — run them while data IO pressure is active. Steps 3–5 cover historical or cumulative data and can be run at any time. --- ### Step 1: Detect Platform and Confirm Data IO Pressure > Execute with the SQL query tool. Detects the SQL Server engine edition at runtime and routes to the appropriate DMV to confirm sustained **data IO** pressure. | EngineEdition | Platform | DMV | Notes | |---|---|---|---| | 5 | Azure SQL Database | `sys.dm_db_resource_stats` | `avg_data_io_percent` — ~15s intervals, ~1h history | | 8 | Azure SQL Managed Instance | `sys.dm_db_resource_stats` | Same as Azure SQL DB | | 12 | Fabric SQL Database | `sys.dm_db_resource_stats` | Behaves like Azure SQL Database | | 6 | Azure Synapse Analytics (Dedicated SQL pool) | N/A | Not supported — IO metrics differ significantly in DW | | 11 | Azure Synapse serverless SQL pool / Fabric Warehouse | N/A | Not supported — IO metrics differ significantly in DW/Warehouse environments | | other | SQL Server on-prem / IaaS | `sys.dm_os_wait_stats` + `sys.dm_io_pending_io_requests` | Cumulative since restart | > **Query:** See [references/queries.md#step-1-detect-platform-and-confirm-data-io-pressure](references/queries.md#step-1-detect-platform-and-confirm-data-io-pressure) **Interpret:** Azure SQL: `avg_data_io_percent >= threshold` = confirmed. On-prem: wait stats are **cumulative since restart** — combine with Step 4 file latency. `pending_io_count > 50` = active IO queued now. | Assessment | Action | |------------|--------| | Data IO pressure confirmed | Proceed to Step 2 — identify active offenders | | Intermittent spikes | Steps 2–3 to catch offenders; Step 4 for persistent latency | | No sustained pressure | Skip Steps 2/6. Step 3 for history, Step 4 for baseline | | `is_unsupported_dw = 1` | **Stop** — this skill does not apply to Azure Synapse (edition 6) or Synapse serverless / Fabric Warehouse (edition 11). Fabric SQL Database (edition 12) is supported. | --- ### Step 2: Real-Time Data IO Consumers — Active Requests with SQL Text and Plans > Execute with the SQL query tool. Shows active requests that are currently experiencing data IO waits or generating high physical reads, along with their SQL text and execution plans. > **Query:** See [references/queries.md#step-2-real-time-data-io-consumers--active-requests-with-sql-text-and-plans](references/queries.md#step-2-real-time-data-io-consumers--active-requests-with-sql-text-and-plans) **Interpret:** `PAGEIOLATCH_SH/EX` = waiting for data pages from disk. `physical_reads_per_sec` = sustained storage pressure rate. `wait_resource` (e.g., `5:1:12345`) shows the specific page. No rows = no active pressure — proceed to Step 3. | Finding | Recommendation | |---------|----------------| | `PAGEIOLATCH_SH` + high physical reads | Check plans for table/index scans | | `PAGEIOLATCH_EX` | Concurrent access on same page — check for hot-page contention | | High `physical_reads_per_sec` | Working set exceeds buffer pool — consider indexing or scaling memory | | Same file in `wait_resource` repeatedly | Hot object/index — optimize query or redistribute filegroups | --- ### Step 3: Historical Data IO Consumers — Plan Cache (Cumulative) > Execute with the SQL query tool. Identifies the most IO-intensive queries from the plan cache based on cumulative physical reads. `total_physical_reads` measures actual disk reads; `total_logical_reads` is included as context (buffer pool reads indicating workload size). > **Query:** See [references/queries.md#step-3-historical-data-io-consumers--plan-cache-cumulative](references/queries.md#step-3-historical-data-io-consumers--plan-cache-cumulative) > **Performance note:** This query does **not** retrieve execution plan XML to avoid high memory and CPU cost on instances with large plan caches. After identifying the top offenders, use `sys.dm_exec_query_plan(plan_handle)` on individual plans for detailed analysis. **Interpret:** `physical_reads_per_exec > 1000` = working set misses buffer pool consistently. `max_physical_reads >> avg` = parameter sensitivity or cold cache. High `logical_reads` with low `physical_reads` = scan-heavy but fits in memory. | Finding | Recommendation | |---------|----------------| | `physical_reads_per_exec` > 1000 | Add covering indexes, reduce scan width, or scale memory | | `logical_reads_per_exec` > 100K | Scan-heavy — review for missing indexes or non-sargable predicates | | `max_physical_reads` >> average | Parameter sensitivity — investigate plan regression | | No rows returned | Lower `MinTotalPhysicalReads` or expand `HistoryLookbackHours` | --- ### Step 4: Data File Latency > Execute with the SQL query tool. Measures data file IO latency from `sys.dm_io_virtual_file_stats`, filtered to **ROWS** files only (data IO). This separates data file performance from log file performance. > **Azure SQL context:** In Azure SQL Database, these metrics reflect how the workload behaves within the service tier's IOPS and throughput limits — not raw storage performance. High latency values typically indicate the database is approaching or hitting its data IO governance limits. > **Query:** See [references/queries.md#step-4-data-file-latency](references/queries.md#step-4-data-file-latency) **Interpret:** `avg_read_latency_ms > 20` = slow data reads (on-prem: disk bottleneck; Azure: approaching IOPS limit). Values are **cumulative since restart** — focus on ratios, not absolutes. | Finding | Recommendation | |---------|----------------| | `avg_read_latency_ms > 20` (on-prem) | Check storage subsystem — SAN queue depth, RAID config | | `avg_read_latency_ms > 20` (Azure SQL) | Hitting tier's data IO limit — scale up or optimize queries | | `avg_write_latency_ms > 15` on ROWS | Heavy dirty-page writes — check checkpoint frequency | | Uneven `total_mb_read` across files | Hot filegroup — redistribute data or add files | --- ### Step 5: Buffer Pool Health > Execute with the SQL query tool. Assesses buffer pool pressure to determine whether data IO pressure is caused by insufficient memory (pages being evicted and re-read from disk) or actual storage bottlenecks. > **Platform note:** Page Life Expectancy and Buffer Cache Hit Ratio are most meaningful on **SQL Server** and **Azure SQL Managed Instance** where the buffer pool is directly managed. On **Azure SQL Database** and **Fabric**, these values are informational — the platform manages memory automatically and PLE/BCHR may not reflect the same pressure patterns as on-premises. #### Step 5a: Page Life Expectancy (NUMA-Aware) > **Query:** See [references/queries.md#step-5a-page-life-expectancy-numa-aware](references/queries.md#step-5a-page-life-expectancy-numa-aware) #### Step 5b: Buffer Cache Hit Ratio and Write Activity > **Query:** See [references/queries.md#step-5b-buffer-cache-hit-ratio-and-write-activity](references/queries.md#step-5b-buffer-cache-hit-ratio-and-write-activity) **Interpret:** > `Buffer cache hit ratio` is a fraction counter (cntr_type 537003264) — the query divides by its base. The `/sec` counters are **cumulative** totals, not rates — sample twice for actual per-second rates. - **PLE:** On-prem guideline ~1000s per NUMA node; < 300 = severe. Azure SQL DB: informational only. - **BCHR < 99%** = more than 1% of page requests hit disk. - **High lazy writes** = memory pressure evicting dirty pages. | Finding | Recommendation | |---------|----------------| | PLE < 300 (on-prem) | Check `max server memory`, reduce memory-intensive queries, or add RAM | | BCHR < 99% | Working set exceeds buffer pool — scale memory or optimize scans | | High lazy writes | Same root cause as low PLE — buffer pool eviction | | Azure SQL with low PLE | Consider scaling to a higher service tier | --- ### Step 6: Data IO Anti-Pattern Detection — Scan-Heavy Queries > Execute with the SQL query tool. Inspects execution plan XML of the **top active data IO consumers** for table scans and clustered index scans — the two patterns that most directly amplify data IO by reading entire tables from disk. > This step targets only the top offenders from Step 2 to minimize overhead. > **Query:** See [references/queries.md#step-6-data-io-anti-pattern-detection--scan-heavy-queries](references/queries.md#step-6-data-io-anti-pattern-detection--scan-heavy-queries) **Interpret:** `has_table_scan = 1` = full heap scan. `has_clustered_index_scan = 1` = full CI scan. Both with high physical reads = primary optimization targets. | Pattern | Recommendation | |---------|----------------| | Table scan on large table | Create clustered index or nonclustered index on filter/join columns | | Clustered index scan | Add covering nonclustered index with INCLUDE columns | | Scan with low selectivity predicate | Functions on columns or implicit conversions prevent seeks | | No anti-patterns detected | Data IO may be legitimate — focus on buffer pool and storage tier | --- ## Report Output After completing all steps, read [references/report-template.md](references/report-template.md) and present the investigation results using that template. ## Root Cause Assessment Use [references/root-cause-guide.md](references/root-cause-guide.md) to determine the root cause and recommended next steps based on findings from all steps.