I'm noticing a high number of waitingRequestsCount on SQL server. The latch_class is buffer. See attached screenshots. Going through SQL server dashboard, I see high number (32221334) waits for Buffer Pool. Can someone please explain what could contribute to high number of waits?
- Diagnosing and Resolving Latch Contention on SQL Server by Microsoft Corporation
- Buffer Latch Class by SQLSkills.com
Some latch contention is to be expected as a normal part of the operation of the SQL Server engine. It is inevitable that multiple concurrent latch requests of varying compatibility will occur on a high concurrency system. SQL Server enforces latch compatibility by requiring the incompatible latch requests to wait in a queue until outstanding latch requests are completed.
Latches are lightweight synchronization primitives that are used by the SQL Server engine to guarantee consistency of in-memory structures including; index, data pages and internal structures such as non-leaf pages in a B-Tree. SQL Server uses buffer latches to protect pages in the buffer pool and I/O latches to protect pages not yet loaded into the buffer pool.
Whenever data is written to or read from a page in the SQL Server buffer pool a worker thread must first acquire a buffer latch for the page. There are various buffer latch types available for accessing pages in the buffer pool including exclusive latch (PAGELATCH_EX) and shared latch (PAGELATCH_SH). When SQL Server attempts to access a page which is not already present in the buffer pool, an asynchronous I/O is posted to load the page into the buffer pool. If SQL Server needs to wait for the I/O subsystem to respond it will wait on an exclusive (PAGEIOLATCH_EX) or shared (PAGEIOLATCH_SH) I/O latch depending on the type of request; this is done to prevent another worker thread from loading the same page into the buffer pool with an incompatible latch.
Latches are also used to protect access to internal memory structures other than buffer pool pages; these are known as Non-Buffer latches.
My recommendation to you will be instead of focusing on the number of buffer class latch, start from wait types. Especially Pagelatch_XX and Pageiolatch_xx type in this case if those are showing up as the top waits in your case. You will find details about these wait types here by sqlskills.com
Quote from Paul Randal:
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.