PAGEIOLATCH_SH

A query is waiting for shared read access to a page, but the page is not yet in memory and has to be loaded from disk.

Page latches are lightweight, non-configurable locks used by internal processes within SQL Server to manage access to the page buffer in memory. When SQL Server has to read pages from the disk into the memory buffer or from the buffer out to disk, it must place latches on the buffer pages while the processes take place.

It’s normal to see some PAGEIOLATCH_* waits, but if you’re frequently seeing them with wait times consistently above 10 milliseconds and you’re experiencing some type of latency, it suggests that the I/O subsystem is under pressure. Conversely, if you see many brief waits, you may be affected by increased I/O activity.

Investigating latency

Investigating I/O activity

  • Check the affected queries and the Top queries table to see which queries are using the most I/O. Inspect the execution plans for expensive read and write operations, such as scanning large tables and indexes. Tune them if necessary; check for missing indexes, or indexes containing an incorrect sort order. See: Query tuning and Showplan Operator of the Week – SORT. 
  • Check the following metrics on the Analysis page to find out which systems are affected. High values may indicate bottlenecking or excessive pressure on the database engine:
    • Avg. disk queue length
    • Buffer cache hit ratio
    • SQL Server: free memory
    • Buffer page life expectancy
    • Machine: memory used

See each metric’s Description tab for guideline values and possible solutions.

  • As a last resort, review whether disk resources are adequate for your server load:
    • If your disk drivers and BIOS are out of date, consider upgrading.
    • Think about moving log files to a different disk, so they’re separate from database data files. See: Storage Top 10 Best Practices.
    • Consider splitting data across different physical drives to distribute the I/O workload more evenly.

Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?