What is PAGEIOLATCH SH wait type in SQL Server

Question

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

User · Answer

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

User · Answer

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  lt table gt  where  lt col1 gt     lt value gt  order by  lt PrimaryKey gt     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

[sql-server] What is PAGEIOLATCH_SH wait type in SQL Server?

Examples related to sql-server

Examples related to tsql

Examples related to optimization