PAGEIOLATCH wait type

Question

I'm trying to get a better understanding of how serious this wait type is. I'm using sp_WhoIsActive stored procedure. Our DBAs don't seem to know much about this wait type. But I often see it when I run the stored procedure. Most of them are PAGEIOLATCH_SH but some are PAGEIOLATCH_EX. When they occur, the average wait time seems to be in the 20-25 millisecond range.

Questions:

1) Is this more of an indication of a problem with I/O as opposed to memory?

2) Is there a guideline to how often someone could expect to see this before it's considered a real problem?

Answer 1

The Microsoft definition of this wait type is:

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

As mentioned above, excessive PAGEIOLATCH_SH wait types don’t mean necessarily that the I/O subsystem is the root cause. It can often be some other reason, such as: bad index management, memory pressure, synchronous mirroring and AlwaysOn AG, logical/physical drive misconception, network issues/network latency, overloaded I/O subsystem by another processes that are producing the high I/O activity.

You may want to try some of the following to resolve having excessive PAGEIOLATCH_SH wait type values:

1. Keep in mind that in case of high safety Mirroring or synchronous-commit availability in AlwaysOn AG, increased/excessive PAGEIOLATCH_SH can be expected
2. Check your SQL Server queries and indexes as very often this could be found as a root cause of the excessive PAGEIOLATCH_SH wait types
3. Check the memory pressure before jumping into any I/O subsystem troubleshooting

If more details on this wait type are needed, including real-word situations that are causing this wait type values to be excessive, take a look at the Handling excessive SQL Server PAGEIOLATCH_SH wait types article.

Answer 2

PAGEIOLATCH_XX waits do not necessarily indicate problems with the IO subsytem. These waits are logged when SQL Server is waiting for data page to be read from disk into memory.

More information from Paul Randal can be found here as mentioned in the comments.

So if there was a problem with the IO subsystem it could cause high levels of PAGEIOLATCH_XX waits but they can also be caused by the buffer pool being full so SQL server has to wait for space to be made before the pages are read into memory. Any applications running on the same server as SQL Server could cause these waits by trying to use the same memory that SQL Server wants.

Non optimised queries that are not supported with suitable indexes can also cause many PAGEIOLATCH_XX waits as the query may perform index scans instead of seeks which will cause more IO and mean that more pages will need to be read from the IO subsystem into memory. Index scans could also be caused by out of date statistics which cause SQL Server to produce non optimal query plans.

Obtaining a baseline for your wait stats is a great way to enable you (or your DBA team) to spot wait stats during moments of poor performance that may indicate the cause of the performance drop. Again Paul Randal talks about this here and also gives some explanations of common wait stats. I have these explanations saved in an offline cheat sheet that I always have open for reference.