PAGEIOLATCH_KP
Published 14 February 2023
A query is holding a keep latch on a page on disk. It’s used to retain the page while SQL Server decides which other kind of latch should replace it.
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
- 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
- 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.