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.
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?
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.
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:
PAGEIOLATCH_SHcan be expected
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.