107

I have a query that is taking a long time in the middle of a transaction. When I get the wait_type of the process it is PAGEIOLATCH_SH.

What does this wait type mean and how can this be resolved?

gotqn
  • 42,737
  • 46
  • 157
  • 243
Ryan
  • 4,602
  • 8
  • 37
  • 43

2 Answers2

126

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.

Frédéric
  • 9,364
  • 3
  • 62
  • 112
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • It's a real simple query. Select * from where = order by . We also have an index on just col1 and have tried rebuilding the index.
     – Ryan Mar 09 '09 at 15:36
  • Can you suggest some resources for learning the understanding you have of what is disk intensive, what requires a full index scan, what requires a sort etc – Greg B Feb 03 '11 at 11:42
  • 2
    @GregB: if you already have a basic knowledge of SQL, you could read Joe Celko's books (all of them but especially `SQL for Smarties` and `Thinking in Sets`) and my blog of course :) – Quassnoi Feb 03 '11 at 12:01
  • 4
    This did indicate an error in our disk subsystem. A RAID disk had failed without triggering the monitoring system. Checking the event logs found that SMART had indeed marked a drive as bad. – Gomibushi Oct 15 '12 at 18:15
10

PAGEIOLATCH_SH wait type usually comes up as the result of fragmented or unoptimized index.

Often reasons for excessive PAGEIOLATCH_SH wait type are:

  • I/O subsystem has a problem or is misconfigured
  • Overloaded I/O subsystem by other processes that are producing the high I/O activity
  • Bad index management
  • Logical or physical drive misconception
  • Network issues/latency
  • Memory pressure
  • Synchronous Mirroring and AlwaysOn AG

In order to try and resolve having high PAGEIOLATCH_SH wait type, you can check:

  • SQL Server, queries and indexes, as very often this could be found as a root cause of the excessive PAGEIOLATCH_SH wait types
  • For memory pressure before jumping into any I/O subsystem troubleshooting

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

You can find more details about this topic in the article Handling excessive SQL Server PAGEIOLATCH_SH wait types

G.Hunt
  • 109
  • 1
  • 4