SQL Server PAGEIOLATCH waits: the buffer pool waiting on slow storage

Your top wait type is PAGEIOLATCH_SH or PAGEIOLATCH_EX. Queries that used to be sub-50ms now take seconds. CPU may be low. There are no blocking chains. The buffer pool is waiting on disk.

PAGEIOLATCH waits are the direct fingerprint of physical I/O. A worker needs an 8KB data page that is not in the buffer pool, takes an in-memory latch on the buffer descriptor, and waits for storage to return the page. When the read completes, the worker continues. Sustained PAGEIOLATCH time means the engine is spending wall-clock time waiting on disk.

The trap is assuming the storage is slow. Often it is not. PAGEIOLATCH is the wait type for “any physical read”, and SQL Server may simply be issuing far more reads than it should because the buffer pool is shrinking under memory pressure or because a bad plan is scanning a table it should not. Before opening a ticket with your storage team, separate the failure modes.

The diagnostic fork has three branches: memory pressure spiral, genuinely slow storage, and excess reads from plan regression. For the broader SQL Server failure taxonomy, see the mental model for operators.

What this means

Two PAGEIOLATCH wait types dominate in practice:

  • PAGEIOLATCH_SH (shared): the worker intends to read the page once it is in memory. The dominant form on read-mostly OLTP workloads.
  • PAGEIOLATCH_EX (exclusive): the worker intends to modify the page once it is in memory. The page is still read from disk first, so the wait is on I/O, not on the write.

Both mean “this page is not in the buffer pool, go get it.” Both are distinct from PAGELATCH_*, which is an in-memory latch on a page already in the buffer pool. PAGELATCH_UP and PAGELATCH_EX on pages in database ID 2 are TempDB allocation contention (PFS, GAM, SGAM pages). They are not I/O waits and faster storage does not help them.

The fork has three branches, not two. First, storage is genuinely slow. Second, the buffer pool is shrinking, so the same workload produces more physical reads. Third, SQL Server is issuing more reads than baseline because of plan regression, missing indexes, or implicit conversions. Only the first is a storage problem. The other two are SQL Server problems wearing an I/O mask.

flowchart TD
    A[PAGEIOLATCH_SH/EX dominant] --> B[Snapshot waits, PLE, I/O stall]
    B --> C{PLE dropping?}
    C -- Yes --> D[Memory pressure spiral]
    C -- No, stable --> E{I/O stall per file high?}
    E -- Yes --> F[Storage layer slow]
    E -- No --> G{Wait count rising vs baseline?}
    G -- Yes --> H[Bad plan driving excess reads]
    G -- No --> I[Filter driver or path latency]

A useful operator heuristic: compare wait count and wait time deltas. If the count of PAGEIOLATCH waits is roughly the same as baseline but the average wait time has grown, the I/O subsystem is slower. If the count has grown substantially, SQL Server is driving more I/Os than it should. The two require different fixes.

Common causes

CauseWhat it looks likeFirst thing to check
Memory pressure spiralPLE dropping, buffer cache hit ratio falling, lazy writer active, PAGEIOLATCH rising with itsys.dm_os_sys_memory, per-NUMA-node PLE
Slow storage subsystemPLE stable, I/O stall per file elevated, single-digit to low-double-digit ms latency on readssys.dm_io_virtual_file_stats deltas
Bad plan driving scansPlan regression in Query Store, table scan on a large table, missing index, implicit conversionTop queries by avg_physical_reads
Filter driver latencyI/O stall in SQL but PerfMon disk counters look finefltmc instances from an admin shell
Cold start / restartPLE starts at 1, climbs over minutes to hours, normalizesRecent restart in error log
Maintenance windowDBCC CHECKDB, index rebuild, full backup runningJob history, expected during window

Quick checks

Read-only. Run them in two snapshots 30 to 60 seconds apart for any counter that is cumulative.

-- Top waits (delta the values against a second run):
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
   OR wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

-- PLE, instance-wide and per NUMA node:
SELECT instance_name, cntr_value AS ple_seconds
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy'
  AND object_name LIKE '%Buffer%';

-- I/O stall per file (snapshot twice, compute deltas):
SELECT
    DB_NAME(vfs.database_id) AS db_name,
    mf.name AS file_name,
    mf.type_desc,
    vfs.num_of_reads,
    vfs.io_stall_read_ms,
    CASE WHEN vfs.num_of_reads > 0
         THEN vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads
         ELSE 0 END AS avg_read_latency_ms,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes
         ELSE 0 END AS avg_write_latency_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

-- Buffer cache hit ratio and OS memory pressure notification:
SELECT
    (a.cntr_value * 1.0 / NULLIF(b.cntr_value, 0)) * 100.0 AS buffer_cache_hit_ratio
FROM sys.dm_os_performance_counters a
JOIN sys.dm_os_performance_counters b
    ON a.object_name = b.object_name
   AND a.instance_name = b.instance_name
WHERE a.counter_name = 'Buffer cache hit ratio'
  AND b.counter_name = 'Buffer cache hit ratio base';

SELECT total_physical_memory_kb / 1024 AS total_mb,
       available_physical_memory_kb / 1024 AS available_mb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- Memory grants pending (compounding factor in spiral):
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending'
  AND object_name LIKE '%Memory Manager%';

# Filter drivers (Windows admin shell). Look for AV, backup, encryption, compression:
fltmc instances

How to diagnose it

  1. Snapshot sys.dm_os_wait_stats, wait 30 to 60 seconds, snapshot again. Compute deltas per wait type. The DMV is cumulative since startup; a single read tells you the lifetime average, not current behavior.

  2. Confirm PAGEIOLATCH is actually dominant in the delta, not in the cumulative. A wait that dominated for two days but is now zero will still top the cumulative chart.

  3. Check PLE. Read both the Buffer Manager counter and the per-node Buffer Node counters. The instance-wide value aggregates across NUMA nodes; one node can be starved while the aggregate looks healthy.

  1. Check I/O stall per file with sys.dm_io_virtual_file_stats, snapshotted twice. Calculate per-file average read and write latency from the deltas. Apply these bands from the SQL Server signal catalog:

    • Data file reads: under 10ms excellent, 10 to 20ms acceptable, over 20ms degraded, over 50ms severe.
    • Log file writes: under 2ms excellent, 2 to 5ms acceptable, over 5ms degraded, over 15ms severe.

  2. Apply the fork:

    • PLE dropping AND PAGEIOLATCH rising means memory pressure spiral. The buffer pool is evicting pages that will be needed again. The I/O subsystem may be perfectly healthy but is being asked to do too much. Look at sys.dm_os_sys_memory, sys.dm_exec_query_memory_grants, and the memory clerk breakdown in sys.dm_os_memory_clerks.
    • PLE stable AND PAGEIOLATCH high means the storage layer. The buffer pool is doing its job but reads are returning slowly. The per-file I/O stall numbers will confirm which file and which database. Hand that evidence to the storage team.
    • PLE stable AND I/O stall normal AND PAGEIOLATCH count growing means SQL Server is issuing more reads than baseline. Plan regression, missing index, or implicit conversion driving a scan. Open Query Store.

  3. If storage is the suspected branch but Windows PerfMon disk counters show no latency, suspect filter drivers. Antivirus, backup agents, encryption, and compression products sit between SQL Server and the partition manager and add latency that is invisible to PerfMon but visible to SQL Server. fltmc instances from an elevated command prompt lists them.

  4. Check the error log for I/O warnings. Error 823 is a hard I/O error. Error 824 is a logical consistency error. Error 825 means the read succeeded on retry but the medium is deteriorating. Any of these is immediately actionable.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
PAGEIOLATCH_* wait time (delta)Direct measure of physical read waitSustained above 30 to 40% of total wait time
PLE per NUMA nodeBuffer pool churnSudden 50%+ drop from baseline; one node much lower than others
I/O stall per fileStorage latency as the engine sees itData file reads above 20ms, log writes above 5ms sustained
Buffer cache hit ratioWorking set fit in memoryOLTP under 95% sustained, with PAGEIOLATCH rising
Memory grants pendingMemory pressure compounding the spiralAny sustained nonzero value
Lazy writer pages/secBuffer pool evicting pagesSustained nonzero outside checkpoint
Top queries by avg_physical_readsWhich queries are driving the I/ORegression vs baseline in Query Store
Error log entries 823, 824, 825Storage layer healthAny occurrence

Fixes

If PLE is dropping (memory pressure spiral)

The I/O subsystem is not the problem. The buffer pool is too small for the working set.

  1. Check sys.dm_os_sys_memory. If available_physical_memory_kb is low, the OS is squeezing SQL Server. Either max server memory is set too high (starving the OS) or another process on the host is consuming memory.
  2. Check sys.dm_exec_query_memory_grants. A single query with an oversized grant can evict buffer pool pages. If you find one, KILL the session. Rollback may take time.
  3. Check the memory clerk breakdown. If CACHESTORE_SQLCP (plan cache) is more than 10 to 15% of max server memory, the plan cache is polluted by single-use ad-hoc plans. Enable optimize for ad hoc workloads to cache stubs on first execution.
  4. On multi-NUMA systems, confirm PLE per node. Foreign memory access is invisible in the aggregate counter.
  5. If max server memory is genuinely too low for the working set, raise it after you confirm the OS has headroom. Do not raise it under OS pressure.

If PLE is stable (genuinely slow storage)

The per-file I/O stall numbers from sys.dm_io_virtual_file_stats are your evidence.

  1. Identify the worst file. Join to sys.master_files to confirm type (data vs log) and physical name. Use type_desc, not file_id, to distinguish data from log files.
  2. On cloud VMs, compare current IOPS and throughput to the disk tier’s provisioned cap. Latency that spikes as throughput climbs usually means you have hit the cap. Resize the disk or move the file.
  3. On physical or SAN storage, check controller queue depth, HBA saturation, and any concurrent maintenance such as array rebuild, snapshot, or replication.
  4. Confirm files are placed correctly. Log and data on the same spindle is a classic misconfiguration. TempDB on slow shared storage undermines every spill.
  5. Pre-size database files to avoid auto-grow stalls. Auto-grow pauses I/O to the file while initializing. Log files cannot use Instant File Initialization, so log auto-grow is especially expensive.

If wait count is rising (bad plan driving excess I/O)

The I/O subsystem is fine, PLE is fine, but SQL Server is reading far more pages than baseline.

  1. Open Query Store. Find queries with a recent duration regression and a corresponding increase in avg_physical_reads.
  2. Compare the current plan to the previous known-good plan. Look for: table scan where there used to be an index seek, nested loops driving random I/O on a large set, or a missing join predicate.
  3. Force the known-good plan with sp_query_store_force_plan as a holding measure.
  4. Check for implicit conversions, which kill sargability. Query Store’s plan XML exposes these as warnings.
  5. Update statistics if they are stale. Cardinality estimation errors cause both bad plans and underestimated memory grants.

If filter drivers are the suspect

PerfMon says the disk is fast. SQL Server says it is not. The latency is being added between SQL Server and the partition manager.

  1. Run fltmc instances from an elevated command prompt.
  2. Identify antivirus, backup, encryption, and compression drivers attached to the volume hosting database files.
  3. Exclude the SQL Server data, log, and backup directories from real-time scanning per Microsoft’s published guidance. Do not exclude the SQL Server process binary itself.
  4. Re-snapshot I/O stall after each change. Filter driver latency often disappears immediately after exclusion.

Prevention

  • Snapshot wait stats at 30 to 60 second intervals and store deltas. Cumulative-since-startup values are useless for current-state diagnosis.
  • Track PLE per NUMA node, not just instance-wide. The aggregate hides single-node starvation.
  • Track I/O stall per file, not per volume. A single hot file on a shared volume changes the conversation with the storage team.
  • Establish a batch requests per second baseline by time of day and day of week. Without it you cannot tell a workload surge from a real problem.
  • Pre-size data and log files. Treat auto-grow as an incident, not a routine.
  • Capture top queries by physical reads in Query Store continuously. The plan regression that ends in PAGEIOLATCH usually shows up there days before users notice.
  • Enable optimize for ad hoc workloads. Keeps single-use plans from consuming buffer pool memory.

How Netdata helps

Netdata surfaces the signals for the PAGEIOLATCH fork at per-second resolution, which is the granularity this diagnosis actually needs.

  • The wait statistics stream shows PAGEIOLATCH_SH, PAGEIOLATCH_EX, PAGELATCH_*, WRITELOG, and ASYNC_NETWORK_IO together, so you can confirm which wait is dominant without running a manual delta query.
  • PLE is collected per NUMA node as well as instance-wide, so single-node starvation is visible without a manual DMV query.
  • Buffer cache hit ratio, lazy writer activity, and memory grants pending are on the same dashboard, so the memory pressure spiral is visible as a composite, not as a single counter.
  • ML-based anomaly detection flags the rising PAGEIOLATCH pattern before it crosses a fixed threshold, which is useful on workloads where the absolute baseline varies by time of day.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.