How It Works: sys.dm_os_buffer_descriptors

Here is a question I was recently asked: “

I am counting pages in the buffer pool using ‘Select count(*) from sys.dm_os_buffer_descriptors’ and I get 6,460 buffers but when when I look at the Buffer Node:Database pages counter it shows 6,599.   Why the difference?”

WARNING: Be careful using dm_os_buffer_descriptors as it can return 200,000+ rows for just a 1.6GB address space.   

The DMV is designed to avoid blocking and contention.   You don’t want the DMV latching the BUF structure for very long and causing additional collisions.  Any additional synchronization of the BUF can lead to slow replenishment of the free list or access to data pages.

Each BUF is protected by a latch (reader/writer lock) structure.   When

enumerating the BUF structures the DMV ONLY attempts immediate latch requests (similar to TryEnterCriticalSection).  It will not wait for a latch that is held.

So when running the query BUFs that are latched at the time the scan attempt to access them are not included in the output and the count can vary from the performance counter values.

Bob Dorr
SQL Server Principal Escalation Engineer

Comments (2)

  1. Percy Reyes says:

    This read is like a SELECT with NOLOCK from BUF. obviously, as you said that is to don't affect the performance. Thanks.