(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 latch class is when a thread is waiting for access to the BUF structure that maps a page into the buffer pool. The Books Online description below is confusing. Contention for access to pages in memory (where it says “hot pages”) appears through PAGELATCH_XX wait types (see the PAGELATCH_EX wait type for more details) and I/O contention (where it says “slow I/Os”) appears through PAGEIOLATCH_XX wait types (see the PAGEIOLATCH_SH wait type for more details).
The Access Methods attempts to acquire a latch on a BUF structure that maps the required data file page into memory. If a BUF structure exists for the page, the in-memory page is then latched appropriately (which may result in a PAGELATCH wait, as described above). If no BUF structure exists for the page, one is created for it and the page is read from disk (resulting in a PAGEIOLATCH wait, as described above).
(Books Online description: “Used to synchronize short term access to database pages. A buffer latch is required before reading or modifying any database page. Buffer latch contention can indicate several issues, including hot pages and slow I/Os.
This latch class covers all possible uses of page latches. sys.dm_os_wait_stats makes a difference between page latch waits that are caused by I/O operations and read and write operations on the page.”)
Questions/comments on this latch class? 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 latch_class value:
Maps to the BUF map_value in sys.dm_xe_map_values.
The map_key value in sys.dm_xe_map_values is 33 in 2008 and 2008 R2, and 28 in 2012 and 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:
I have not seen this latch be a noticeable contention point.
You should filter this wait type out of results from sys.dm_os_latch_stats otherwise it will overwhelm the other latch information.
See the LATCH_EX wait type for more information about latches in general and how to troubleshoot them.
Known occurrences in SQL Server (list number matches call stack list):
- Getting access to a BUF structure to retrieve a page pointer (in this case, while reading ahead of the scan point in an index during a sort)
And many, many more similar stacks.
Abbreviated call stacks (list number matches known occurrences list):
- XeSqlPkg::latch_suspend_end::Publish+138
LatchBase::Suspend+16b8
LatchBase::AcquireInternal+415
BUF::AcquireLatch+78
BPool::Get+10f
BTreeMgr::GetNextHPageId+6ca
PageReadAhead::GetPageIdForReadAhead+c2
PageReadAhead::Notify+d3
KeyRangeGenerator::ReadAhead+176
KeyRangeGenerator::GetNextRange+436
IndexDataSetSession::GetNextRangeForChildScan+1c0
IndexDataSetSession::SetupNextChildSubScan+366
IndexDataSetSession::GetNextRowValuesInternal+17a2
RowsetNewSS::FetchNextRow+197
CQScanTableScanNew::GetRow+f2
CQScanSortNew::BuildSortTable+246
CQScanSortNew::OpenHelper+c0
CQScanNew::OpenHelper+41
CQScanXProducerNew::Open+c8
FnProducerOpen+44
FnProducerThread+8c3