PAGEIOLATCH is one of the most commonly seen wait types and basically represents the time that SQL Server had to wait while pages were being read from disk into memory.
When the data pages that SQL Server needs to traverse in order to resolve a particular query are not cached, they must be read from disk into memory. While this IO occurs, PAGEIOLATCH wait time will increase and while it may be very tempting to immediately blame storage for abnormal high wait times, often the problem lies elsewhere.
In order to illustrate such a scenery, where the root cause of this wait time is not related to storage, we’ll execute the following useless query on a fresh restore of StackOverflow2010 database:
SELECT COUNT(1) AS PostCount FROM Posts WHERE CreationDate < GETDATE() GO 10
Upon completion, PAGEIOLATCH will be, by far, our top wait type:
By analyzing the execution plan of the query we’ve repeatedly executed, we’ll notice that its cost is mostly related to the Clustered Index Scan operation:
Looking further into the metadata of the Posts table, it’s possible to notice that it holds almost 7GB of data, over 3 729 195 rows.
But, despite this being quite a significant weight for SQL Server to “lift” from disk to memory, it only explains part of the spectacular amount of PAGEIOLATCH waits we’re generating.
The final piece of the puzzle that I hid, is related to the maximum amount of memory available. If my server had enough RAM to cache all that data into memory, we would have lower PAGEIOLATCH wait time, because it would occur only in the first execution, since all subsequent executions would read the data from memory.
But, because I’m cheap and my server has just 2GB of RAM available, it’s impossible for SQL Server to cache that data entirely. So, it needs to be constantly pulling data out of disk into memory, reading the data and then flushing the memory for the next rows, until the entire index is scanned.
So, now that we know what’s the root cause of our problem, let’s fix it.
In this case we can choose to do either query or index tuning. We can create an index to support the above query with the only column needed – CreationDate, or we can remove the entire WHERE clause, because in this query we’re always going to count every row in the table at a given moment and so, removing it would allow SQL Server to use the Clustered Index efficiently to do so. Nevertheless, I’ll go for the index:
CREATE INDEX IX_CreationDate ON Posts (CreationDate)
After its creation, we can restart SQL Server service in order to clean the wait stats and then execute the same query over the Posts table 10 times, as specified in the beginning of the post.
The following image refers to one of those executions, where it’s possible to confirm that our index is being used and the query is lightning fast.
Accordingly, if we look into the wait stats, we’ll notice that PAGEIOLATCH waits decreased to a point where they are no longer significant.
In conclusion, if we’re experiencing high PAGEIOLATCH wait times, it’s probably wise to start by looking into index / query tuning opportunities. Then check the memory that the server has available and evaluate the possibility of adding more RAM. And just then, after everything failed us, let’s blame storage and tune it.