Published 31 January 2017
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.
- Check the latency of your disk subsystem. See How to examine I/O subsystem latencies from within SQL Server.
- On the Analysis page, check these metrics to see how long it’s taking to read from and write to the disk:
- Disk avg. read time
- Disk avg. write time
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
- 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.