PAGEIOLATCH wait type

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?

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:

• Keep in mind that in case of high safety Mirroring or synchronous-commit availability in AlwaysOn AG, increased/excessive PAGEIOLATCH_SH can be expected

• 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

• 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.

StackExchange Database Administrators Q#117092, answer score: 4