(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
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):
- Inserting a record into a page
- Updating a record on a page
- 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)
- 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)
- 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)
- 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):
- 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 - 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 - 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 - 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 - 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 - 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