Which TWO actions can help reduce `PAGEIOLATCH_UP` waits on an Azure SQL Database?
Trap 1: Reduce MAXDOP to 1.
Incorrect. Reducing MAXDOP to 1 affects parallelism and CPU usage but does not directly address the I/O waits for update latches. It might even increase I/O if queries become serial.
Trap 2: Increase the buffer pool size by adding more memory.
Incorrect. Increasing buffer pool size helps with PAGEIOLATCH_SH waits (read latches), but PAGEIOLATCH_UP waits are for updates; they often require addressing I/O throughput or index design rather than memory size.
- A
Add a nonclustered index to reduce update I/O.
Correct. A nonclustered index can reduce the number of pages that need to be updated, which decreases the I/O that causes PAGEIOLATCH_UP waits.
- B
Reduce MAXDOP to 1.
Why wrong: Incorrect. Reducing MAXDOP to 1 affects parallelism and CPU usage but does not directly address the I/O waits for update latches. It might even increase I/O if queries become serial.
- C
Increase the log write throughput by moving to a higher tier.
Correct. Upgrading to a higher tier increases I/O throughput, which can reduce the time spent waiting for page I/O during update latch operations.
- D
Increase the buffer pool size by adding more memory.
Why wrong: Incorrect. Increasing buffer pool size helps with PAGEIOLATCH_SH waits (read latches), but PAGEIOLATCH_UP waits are for updates; they often require addressing I/O throughput or index design rather than memory size.