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

BUFFER

(Back to main page…)

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):

  1. 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):

  1. 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