PAGEIOLATCH_SH Waits: Slow Storage or Too Many Reads

High PAGEIOLATCH_SH waits can start a long conversation with the storage team. First find out whether reads are slow or whether a query simply asks for too many pages.

Read PAGEIOLATCH_SH Waits in Context

PAGEIOLATCH_SH records time waiting for a data page to be read into the buffer pool for shared access. It points to physical page reads, but it does not say whether the device is slow or the workload is reading too much. I compare wait deltas over the incident window with a healthy window. Lifetime totals since restart can hide the current pattern. Also check whether memory pressure or a cold cache increased physical reads. A read-heavy query can create waits on perfectly healthy storage.

Ask whether the user complaint lines up with the wait spike. An isolated high counter during a maintenance task is different from sustained user-facing latency.

Measure File Read Latency

sys.dm_io_virtual_file_stats exposes cumulative reads and read stall milliseconds per database file. Take two snapshots around the affected period and divide the stall delta by the read-count delta. The one-shot query below gives a lifetime average as a starting point; use interval deltas for the incident. Separate data files from log files. Watch for one file or one database standing out. I avoid declaring an SSD bad from one old cumulative average.

SELECT DB_NAME(v.database_id) AS database_name,
       mf.name AS logical_file_name, mf.type_desc,
       v.num_of_reads, v.io_stall_read_ms,
       v.io_stall_read_ms * 1.0 / NULLIF(v.num_of_reads,0) AS average_read_ms
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS v
JOIN sys.master_files AS mf
  ON mf.database_id = v.database_id AND mf.file_id = v.file_id
ORDER BY average_read_ms DESC;

Find Queries That Demand Pages

Query Store can rank plans by average physical reads within an interval. Join runtime statistics to plan, query, and text, then narrow to the window of the complaint. Run it in the affected database, and change the 24-hour filter to match the incident. A query with high physical reads can be a cold-cache report or a poor access path. Compare logical reads and execution count too. A single large report and a small query called constantly need different fixes. I inspect the actual plan for scans, residual predicates, key lookups, and row estimate errors before creating an index.

SELECT TOP (20) q.query_id, p.plan_id, i.start_time, rs.count_executions,
       rs.avg_physical_io_reads, rs.avg_logical_io_reads,
       qt.query_sql_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE i.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
ORDER BY rs.avg_physical_io_reads DESC;

Make the Storage Decision

High read latency across several unrelated files and workloads, with ordinary read volume, points toward a storage or host-path investigation. Give that team interval read counts, stall deltas, file paths, and the timing of the complaint. Normal latency with one query reading a huge number of pages points toward query and index work. Both conditions can coexist. A large scan can overload storage and raise latency for everyone. The decision is not a vote between two teams; it is a measurement of both sides.

I keep log-write issues separate. WRITELOG or log-file latency needs a different investigation from PAGEIOLATCH_SH. Combining all IO into one average hides the useful distinction.

Test a Query Fix Without Guessing

Try a selective predicate, an appropriate covering index, better statistics, or a rewrite in a restored copy. Measure logical and physical reads under comparable cache conditions. If a new index reduces reads for one query, check its storage and write cost. If physical reads remain high because the working set exceeds memory, an index alone can fail to solve it. What would make the query read fewer pages for the same result? That question is more useful than asking which index name is missing.

Check the actual plan after the change. A proposed index that the optimizer does not use cannot explain an improvement, and a plan that uses it for the wrong reason can regress for other parameters.

Account for Cache Before Blaming PAGEIOLATCH_SH Waits

Physical reads depend on buffer cache state. A report after a restart can show more PAGEIOLATCH_SH waits without any storage regression. I compare cold and warm executions in a test environment and note the state of the cache during the incident. Large scans can evict useful pages and make unrelated queries pay for reads later. That indirect effect is easy to miss if you only examine the report's own duration. Check memory pressure and page life signals alongside the wait, but avoid treating one memory counter as a verdict.

File-level latency averages can hide spikes. Take short interval samples and compare read count and stall together. Ten slow reads and a million ordinary reads require different responses. I also separate files on different storage paths; averaging them into one database number can conceal one troubled volume.

Give the Right Team a Useful Ticket

For storage, provide file path, interval timestamps, read count delta, stall delta, average and peak symptoms, and independent host or SAN metrics where available. For query tuning, provide query text safely, Query Store plan ID, physical and logical reads, actual plan, and the requested result shape. I have seen "the SAN is slow" tickets returned because the query performed a full scan by design. I have also seen an index blamed when every file on one path had high read latency. The same two measurements prevent both detours.

What changes after a fix? A storage repair should reduce latency per read under similar volume. A query fix should reduce pages read for the same result, with user time improving. I run the same snapshot scripts afterward and keep the evidence. That turns a wait-type complaint into a measurable decision rather than a long exchange of screenshots.

If file latency is high only during one scheduled load, compare the load's read and write pattern with the storage path's capacity. A volume can meet ordinary demand and still saturate during concurrent maintenance. I schedule a controlled replay or move the job before declaring the hardware defective. The user impact and interval metrics decide the next action.

Recheck PAGEIOLATCH_SH Waits After the Fix

After the change, take another wait interval, file-latency interval, and Query Store comparison under similar traffic. Report actual measured numbers from those captures. Do not claim the wait disappeared because a dashboard line looked calmer after business hours. I look for user latency improving along with the underlying read volume or device latency. That closes the loop between symptom and fix.

Keep the before and after scripts in the runbook. The next storage escalation will go faster when the team can see whether the instance is waiting longer per read or simply ordering too many reads.

Related reading on this blog: Top 3 Wait Stats from Real-World and Wait Stats Collection Scripts : Updated March 2021.

An IO wait is not a storage verdict, it is a question about latency and volume.

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