(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

PAGEIOLATCH_SH

(Back to main page…)

Description:

This wait type is when a thread is waiting for the read of a data file page from disk to complete, and the thread is going to read (not modify) the page structure once it is in memory (SH = SHare mode). The Latches Whitepaper in the sidebar on the right has a description of all latch modes and their compatibility with other latch modes.

(Books Online description: “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.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 66 in all versions through 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

The knee-jerk reaction to this wait type is that it must be the I/O subsystem that has a problem, and even the Books Online description mentions this. It’s true that long waits for I/Os indicate the I/O subsystem is overloaded, but it’s more likely that the problem is in SQL Server than with the I/O subsystem. You need to think about *why* SQL Server is doing so many reads.

I wrote a long article on troubleshooting PAGEIOLATCH_XX waits on sqlperformance.com – basically identifying the query that’s doing the reads and then figuring out which table is being read from and why that data isn’t already in memory. You can get the workflow for identifying a table from a page resource (from sys.dm_os_waiting_tasks) in this blog post.

Known occurrences in SQL Server (list number matches call stack list):

  1. Reading a heap data page (in this case, during a scan)
  2. Reading an index leaf-level page (in this case, during a scan)
  3. Reading an IAM page (in this case, to get the next extent as part of an unordered scan of an index)
  4. Reading a text page (in this case, as part of retrieving table statistics for the Query Optimizer during query compilation)

And many, many more similar stacks to do with reading data file pages from disk.

Abbreviated call stacks (list number matches known occurrences list):

  1. SOS_Task::PostWait+90
    EventInternal::Wait+1f9
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+ea
    FixPageNotThruLinkage+1b3
    HeapPageManager::GetNextPage+1ea
    HeapDataSetSession::GetNextRowValuesInternal+52f
    RowsetNewSS::FetchNextRow+197
    CQScanRowsetNew::GetRowWithPrefetch+51
    CQScanTableScanNew::GetRow+8a
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+36a
    CXStmtSelect::XretExecute+2f7
    CMsqlExecContext::ExecuteStmts<0,1>+864
    CMsqlExecContext::FExecute+a48
    CSQLSource::Execute+866
  2. SOS_Task::PostWait+90
    EventInternal::Wait+1f9
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+ea
    IndexPageManager::GetNextPage+438
    IndexRowScanner::MoveKeyOrderToRowOnNextPage+16c
    IndexRowScanner::MoveToRowOnNextPage+23b
    IndexDataSetSession::GetNextRowValuesInternal+105b
    DatasetSession::GetNextRowValuesNoLock+ce
    RowsetNewSS::FetchNextRow+18c
    CQScanRowsetNew::GetRowWithPrefetch+51
    CQScanRangeNew::GetRow+15b
    CQScanStreamAggregateNew::GetRowHelper+1fc
    CQScanStreamAggregateNew::GetCalculatedRow+21
    CQueryScan::StartupQuery+240
    CXStmtQuery::SetupQueryScanAndExpression+2bd
    CXStmtQuery::InitForExecute+34
    CXStmtQuery::ErsqExecuteQuery+36a
    CXStmtSelect::XretExecute+2f7
    CMsqlExecContext::ExecuteStmts<1,1>+400
  3. SOS_Task::PostWait+90
    EventInternal::Wait+1f9
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+ea
    IAMPageRef::Fix+b5
    AllocScan::GetNextScanIAM+b9
    AllocScan::GetNextScanExtent+48b
    AllocScan::MoveToStartOfScan+1ca
    UnOrderPageIdScanner::GetNextPageId+31f
    UnOrderPageScanner::SetupSubScanner+114
    UnOrderPageScanner::StartScan+10a
    IndexPageManager::CreateScanner+257
    IndexRowScanner::SetRangeAllocationOrdered+113
    IndexDataSetSession::StartScan+413
    RowsetNewSS::EnsureStartOfScanLong+97
    RowsetNewSS::FetchNextRow+36
    CQScanTableScanNew::GetRow+f2
    CQScanSortNew::BuildSortTable+87
    CQScanSortNew::OpenHelper+c0
    CQScanNLJoinNew::Open+24
  4. SOS_Task::PostWait+90
    EventInternal::Wait+1f9
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+ea
    BlobBase::FixNode+210
    Blob::RWBlob+30e
    Blob::ReadWriteAt+139
    BlobManager::ReadAtInternal+1d2
    LockBytesSS::ReadAt+40
    CMEDScanLob::CbGetNext+52
    CStatMan::Init+116
    CTableMetadata::LoadStats+159
    CTableMetadata::LoadStatisticsBlob+17f
    CTableMetadata::GatherStatisticsBlobs+2b5
    CTableMetadata::LoadMetadata+398
    OptimizerUtil::LoadNewStats+543