08 December,2012 by Jack Vamvas
SQL Server Books online defines the SQL wait type pageiolatch_ex as “Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.“
The underlying causes typically relate to disk to memory , memory pressure and disk IO subsystem issues such as caching problems.
The following scenario illustrates how this wait type arises. A user process will request some data which is not currently in the buffer cache. At that point – SQL Server will attempt to allocate a buffer page – and while the data is moved from disk to buffer cache an exclusive PageIOLatch_ex is created on the buffer. At the same time a PageIOLatch_sh is created on the buffer from the user process perspective.
When data is written to cache the PageIOLatch_ex is released. As the user still has the PageIOLatch_sh – the user can read the data.
1) PageIOLatch_ex and PageIOLatch_sh are often reported as high – if so, disk io subsystem will be your first target
2) The disk spin rate should not be the focus – but rather the workload. Therefore , for testing – run the workload and monitor disks
3) It’s not just slow IO subsystems relative to workload but also overworked IO subsystems that can be the cause.
4) General database tuning can help – but a single transaction is defined by : amount\speed of disks , memory amount availability , processor speed.
5) Index management
6) Differentiate between disk issues and memory issues – afterall ,if memory is experiencing pressure – than dealing with incoming data will be slower
7) Logical drive profiles – separating random file access with sequential files