PAGELATCH_EX

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

(Back to main page…)

Description:

This wait type is when a thread is waiting for access to a data file page in memory (usually a page from a table/index) so that it can modify the page structure (EX = EXclusive 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 not in an I/O request. The latch request is in Exclusive mode.”)

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 52 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 so sometimes people add more memory or investigate the I/O subsystem, but these waits are for pages that are *already* in memory.

Common causes of PAGELATCH_XX contention are:

  • Allocation bitmap contention in tempdb (PAGELATCH_UP for multiple threads trying to change the same bitmap), and under extreme loads, in user databases
  • Table/index insert hotspot (PAGELATCH_EX for threads inserting onto the same page and possibly PAGELATCH_SH for threads reading from that page)
  • Excessive page splits from random inserts (PAGELATCH_EX for threads trying to insert/update rows on a page and possibly PAGELATCH_SH for threads reading from that page)

I wrote a long article on troubleshooting PAGELATCH_XX waits on sqlperformance.com that covers the first two cases above, basically identifying the cause of the contention from the page resource involved and troubleshooting further from there. Troubleshooting the third case involves figuring out which index is undergoing the page splits and then usually implementing a fill factor and regular index maintenance. You can get the workflow for identifying a table from a page resource (from sys.dm_os_waiting_tasks) in this blog post. There’s also Microsoft blog post with some interesting ideas for spreading out inserts here.

There is a known bug in early builds of SQL Server 2016 that can result in excessive PAGELATCH_EX waits on pages in the sys.sysobjvalues system table. See KB article 4013999 for details.

Note that you may see a wait resource of (0:0:0) in the output from some queries. This is normal – see this post for an explanation.

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

  1. Inserting a record into a page
  2. Updating a record on a page
  3. Removing a row from a page (in this case, from a nonclustered index on the sys.sysrowsets Storage Engine system table while dropping a rowset as part of a DROP TABLE)
  4. Clearing the ‘this page has one or more ghost records’ bit in the byte for a page in a PFS page (in this case, as part of the ghost cleanup task)
  5. Updating a b-tree link in a page’s header (in this case, as part of moving one of the page’s neighbors at the same level in the b-tree while performing a data file shrink)
  6. Updating a b-tree link in a page’s header (in this case, as part of a page split while trying to insert a record into a page without enough information)

And many more similar stacks where pages in memory are being modified.

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

  1. SOS_Task::PostWait+9e
    EventInternal::Wait+1fb
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+10f
    IndexPageManager::GetPageWithKey+1250
    IndexDataSetSession::LocatePageForInsert+204
    IndexDataSetSession::InsertSmallRecord+cd2
    IndexDataSetSession::InsertRowInternal+2de1
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRow::SetDataX+5b
    CQScanUpdateNew::GetRow+43d
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+36d
    CXStmtDML::XretDMLExecute+31c
    CXStmtDML::XretExecute+ad
    CMsqlExecContext::ExecuteStmts<1,1>+427
    CMsqlExecContext::FExecute+a43
    CSQLSource::Execute+86c
  2. SOS_Task::PostWait+9e
    EventInternal::Wait+1fb
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+10f
    IndexPageManager::GetPageWithKey+1250
    GetRowForKeyValue+205
    IndexDataSetSession::GetRowByKeyValue+12c
    IndexDataSetSession::FetchRowByKeyValueInternal+210
    RowsetNewSS::FetchRowByKeyValue+514
    CValFetchByKey::ManipData+83
    CValFetchByKeyForUpdate::ManipData+e
    CQScanUpdateNew::GetRow+1a1
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+36d
    CXStmtDML::XretDMLExecute+31c
    CXStmtDML::XretExecute+ad
    CMsqlExecContext::ExecuteStmts<0,1>+8bd
    CMsqlExecContext::FExecute+a58
    CSQLSource::Execute+86c
  3. SOS_Task::PostWait+90
    EventInternal::Wait+1f9
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+ea
    IndexPageManager::GetPageWithKey+1206
    GetRowForKeyValue+264
    IndexRowScanner::EstablishInitialKeyOrderPosition+1b0
    IndexDataSetSession::GetNextRowValuesInternal+19e
    RowsetNewSS::GetNextRows+b4
    CMEDScan::DeleteNCRow+291
    CMEDScan::DeleteRow+4b
    CMEDCatKatmaiIndex::DropRowset+5f5
    VisibleHoBt::DropHoBt+357
    SEDropRowsetInternal+68f
    DDLAgent::SEDropRowsets+4b
    CIndexDDL::DropRowset+72
    CIndexDDL::DropAllRowsets+ae
    DropAllRowsetsForTable+c1
    DropObject+cb
  4. SOS_Task::PostWait+9e
    EventInternal::Wait+1fb
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    GhostExorciser::GetNextGhostPageInPFS+a9
    GhostExorciser::GetNextGhostedPage+b5
    TaskGhostCleanup::ProcessTskPkt+610
    GhostRecordCleanupTask+197
    CGhostCleanupTask::ProcessTskPkt+9
    TaskReqPktTimer::ExecuteTask+63
    OnDemandTaskContext::ProcessTskPkt+3e2
    SystemTaskEntryPoint+426
    OnDemandTaskContext::FuncEntryPoint+25
    SOS_Task::Param::Execute+21e
    SOS_Scheduler::RunTask+ab
  5. OS_Task::PostWait+9e
    EventInternal::Wait+1fb
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+10f
    IndexPageManager::GetPageForLinkModification+1cc5
    SwapPageUnderSystemTransaction+17a
    IndexDataSetSession::MoveDataPageCommon+91f
    IndexDataSetSession::MovePages+2c0
    IndexDataSetSession::ShrinkInternal+f7
    DatasetSession::Shrink+b2
    UtilDbccMoveBtreePages+2ca
    UtilDbccMovePages+1cbd
    UtilDbccProcessPagesInShrink+62d
    UtilDbccShrinkDataFiles+18c8
    UtilDbccShrinkDataFiles+76e
    UtilDbccShrinkDatabase+8fc
    DbccShrink+49e
    DbccCommand::Execute+153
    CStmtDbcc::XretExecute+7cd
  6. XeSosPkg::wait_info::Publish+138
    SOS_Scheduler::UpdateWaitTimeStats+2ad
    SOS_Task::PostWait+9e
    EventInternal::Wait+1fb
    LatchBase::Suspend+633
    LatchBase::AcquireInternal+415
    BUF::AcquireLatch+78
    BPool::Get+10f
    IndexPageManager::GetPageForLinkModification+172b
    SplitPage+3b3
    IndexDataSetSession::InsertSmallRecord+1a1d
    IndexDataSetSession::InsertRowInternal+2de1
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRowMult::SetDataX+357
    sqlTsEs.dll!0x000007FEF1C31437
    CQScanUpdateNew::GetRow+43d
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+36d
    CXStmtDML::XretDMLExecute+31c
    CXStmtDML::XretExecute+ad
    CMsqlExecContext::ExecuteStmts<1,1>+427
    CMsqlExecContext::FExecute+a43
    CSQLSource::Execute+86c