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 appeare...
This blog is to, share the encounters and learning’s of Sql Server
