What is PAGEIOLATCH_SH wait type in SQL Server?

sql-server tsql optimization
Ryan picture Ryan · Mar 6, 2009 · Viewed 209k times · Source

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?

Answer

Quassnoi picture Quassnoi · Mar 6, 2009

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.

Related questions

Best way to compare the end of a string, use RIGHT, LIKE or other?

I need to compare the end of strings against a list of possible ending in a stored procedure. It will be called a lot and there are around 10-15 candidate endings. At this point a code-only solution is preferable to …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More