What is PAGEIOLATCH_SH wait type in SQL Server?

From Microsoft documentation:

PAGEIOLATCH_SH

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.

In practice, this almost always happens due to large scans over big tables. It almost never happens in queries that use indexes efficiently.

If your query is like this:

Select * from <table> where <col1> = <value> order by <PrimaryKey>

, check that you have a composite index on (col1, col_primary_key).

If you don’t have one, then you’ll need either a full INDEX SCAN if the PRIMARY KEY is chosen, or a SORT if an index on col1 is chosen.

Both of them are very disk I/O consuming operations on large tables.