PAGEIOLATCH Wait Stats: Waiting for Data From Disk

PAGEIOLATCH wait stats measure the time queries wait while data pages are read from disk into memory. Every server has some. When they lead your wait list, either your queries read too much or your storage reads too slowly.

This post is part of my wait stats series, told as one story at the Clipboard Diner. Every post is listed in the series guide.

Night 9 at the Clipboard Diner

Casey spent the morning sorting the cheese tray, and Wednesday’s square dance never came back. Thursday night brought a new trouble instead. The stuffed pepper special needs fourteen ingredients, and the prep counter holds about half of them.

So every few minutes a cook hollered for something from the basement. The dishwasher ran down the steep stairs into the walk-in cooler, felt around in the cold, and climbed back up. The ticket sat on the spike the whole time. The cook waited with it.

Casey kept tally on the chalkboard by the stairs. By 8 PM there were thirty-one marks. Then Casey looked at what came up. One trip was a whole crate of lettuce, because Jules wanted a single leaf for a garnish. The crate took over the counter and pushed the salsa off the end. Twenty minutes later, someone sent for salsa.

The stairs were slow too. The bulb over them had burned out on Tuesday, and nobody had replaced it. The dishwasher was finding the cooler door by memory.

At closing, the clipboard got the count: 31 trips downstairs. Half for things we didn’t need.

What PAGEIOLATCH Means

That basement is your data files. The prep counter is the buffer pool, SQL Server’s cache of data pages in memory. A query reads every row from a page in memory. When the page it needs isn’t there, SQL Server reads it from disk first. The query waits until the page arrives.

During that read, SQL Server holds a latch on the page’s spot in memory. A latch is a short internal lock that protects a page in memory while it’s read in or changed. The wait for that read is PAGEIOLATCH, and the ending names the latch mode the task asked for:

• PAGEIOLATCH_SH: shared mode. The query wants to read the page. This is the one you’ll see most.
• PAGEIOLATCH_EX: exclusive mode, while the page has I/O in flight. A task that will change the page waits here, and so does one behind a page being written to disk.
• PAGEIOLATCH_UP: update mode. Less common, and you’ll see it mostly on allocation and system pages.

This family covers data pages only. Log writes wait on WRITELOG. Other disk work, like sorts that spill, shows up as IO_COMPLETION, which is tomorrow’s wait.

Two Causes, One Wait

The wait looks the same for both causes, so you have to tell them apart. Too many reads is the lettuce crate. A scan reads a whole table to find a few rows, and it pushes useful pages out of memory. Later queries then read those pages again.

Slow storage is the dark stairway. Every read takes too long, no matter how few you make. Memory sits in the middle of both. A bigger counter means fewer trips, but a scan of a huge table will fill any counter you buy.

It’s easy to blame storage for every PAGEIOLATCH wait. Then a storage admin shows you disks that are mostly idle, serving one scan over and over. That’s why I check the queries first.

Normal or a Problem?

See It on Your Server

The first query reads the PAGEIOLATCH family from the clipboard, with the average time per wait. That isn’t disk latency. The second query measures latency per file.

-- PAGEIOLATCH waits since the last restart
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms,
       CAST(1.0 * wait_time_ms / NULLIF(waiting_tasks_count, 0) AS decimal(18, 2)) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE N'PAGEIOLATCH%'
ORDER BY wait_time_ms DESC;

A high count with a low average means fast reads. Check whether the volume is above your normal workload. A high average points to slow storage. Both totals cover the time since the last restart. Measure a busy window with Wait Stats Over Time before you decide.

The second query shows read and write latency for every database file. The average read latency is the read stall time divided by the number of reads. A file with no reads yet shows an empty average, not a zero. The list is sorted by total read stall, so the files that cost queries the most time come first.

-- Which database files cost the most read waiting, and how long does one read take?
SELECT DB_NAME(f.database_id) AS database_name,
       m.name AS file_name,
       m.type_desc AS file_type,
       f.num_of_reads AS reads_done,
       CAST(1.0 * f.io_stall_read_ms / NULLIF(f.num_of_reads, 0) AS decimal(10, 1)) AS avg_read_ms,
       f.num_of_writes AS writes_done,
       CAST(1.0 * f.io_stall_write_ms / NULLIF(f.num_of_writes, 0) AS decimal(10, 1)) AS avg_write_ms,
       m.physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS f
JOIN sys.master_files AS m
    ON m.database_id = f.database_id
   AND m.file_id = f.file_id
ORDER BY f.io_stall_read_ms DESC;

Look at the ROWS files at the top of the list. On modern SSD storage I expect data file reads in single-digit milliseconds. When busy-hour reads average above 20 ms, I start asking storage questions. These are my working numbers, not a law.

Fix It

1. Measure a busy window, so a restart or a nightly job doesn’t fool you.
2. Find the queries with the most logical reads. Use the top CPU query from SOS_SCHEDULER_YIELD Wait Stats, ordered by total_logical_reads.
3. Fix their scans: add the missing index, update statistics, and select only the columns you need.
4. Check file latency per file. If one volume is slow, show your storage team those numbers.
5. Check that max server memory leaves the buffer pool enough room for your busy data.
6. Upgrade storage or add memory last, once the reads are lean.

You could say memory is cheap now, so add more and the waits go away. Fair point, more memory hides a lot of reads. But it hides them only until the table grows. A fixed scan stays fixed.

New in SQL Server 2022 and 2025

Nothing new changes what PAGEIOLATCH means. A page still has to come from disk before a query can use it. What still matters is the old work: lean reads, healthy storage and enough memory. Since SQL Server 2022, Query Store is on by default for new databases. That makes the top-read queries easier to find.

Related Reading

• PAGEIOLATCH_SH Waits: Slow Storage or Too Many Reads
• How to Optimize Your Server Performance by Reducing IO Waits
• Script: Current IO Related Waits on SQL Server

PAGEIOLATCH is not proof of a slow disk, it is a reason to check what the queries read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.