PageIOLatch

PageIOLatch

PageIOLatch_* , this wait type keeps transferring data from disk to memory. This is caused due  to pages been evicted from the buffer cache. 

The pageIOLatch waittype started appearing as the most long standing wait type on one of the servers. It wasn't Immediately obvious what caused the waittype to be such a huge problem.

 Figure 1

  Figure1 , depicts the insert and update operations and the corresponding wait types during the insert and update opearion.

The waittype was appearing against  the insert query which was executed 150k times per a hour. The insert query was a victim in this case and the update query that was executed few seconds later was the cause.  

The update statement looked pretty solid, it was updating the table based on the single parameter which was the clustered key of the table.  :(

The impact 

- Page life expectancy would start reducing when this wait type appeared.
- Log flush rate per sec will be very high, causing logs to be flushed to disk quite rapidly 

Other waittypes that could be observed along side a pageIOLatch
- Writelog 

- hard sync commit

The possible improvement
- Remove/reduce the update query by changing schema design of the table( create new table with the columns which are updated and replace it with a insert )
- Remove/reduce transactions
- Add the log files on faster disk 

- Add more memory :)