PAGEIOLATCH_UP

A query is waiting for an update latch on a page, but the page is not yet in memory and has to be loaded from disk. Update latches are commonly used when there’s row versioning activity in tempdb, or when system allocation pages are being updated. Unlike exclusive latches, they do not update the page data itself. The update latch remains in place until pages are successfully written to disk. It cannot be held concurrently with existing destroy or exclusive latches.

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 10 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
    • Buffer free pages
    • 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 Practises.
    • 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?