Investigate PAGEIOLATCH Waits

Why this problem matters

PAGEIOLATCH waits occur while data pages are read from storage into memory. Elevated values can result from slow storage, large scans, or insufficient cache, among other causes.

How to diagnose it

Compare relevant wait counters with cumulative read latency for database files. Preserve distinctions by file, time interval, and query read behavior instead of relying on one average.

The query reads file I/O values accumulated since SQL Server startup or counter reset. Averages hide distribution and brief spikes, so they should be supplemented with time-series samples.

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.name AS logical_file_name,
       mf.type_desc,
       vfs.num_of_reads,
       vfs.io_stall_read_ms,
       CASE WHEN vfs.num_of_reads = 0 THEN NULL
            ELSE 1.0 * vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
ORDER BY avg_read_stall_ms DESC;

A safe solution approach

First investigate queries producing unnecessary reads and missing access paths, then validate storage capability. Memory or storage changes should not be the default remedy before measuring query-driven read volume.

Why continuous monitoring matters

I/O latency can spike briefly during backups, scans, and peak workload periods. Continuous moon samples help distinguish persistent changes and route alerts through Slack, PagerDuty, or webhooks.