Share via

How It Works: sys.dm_os_buffer_descriptors

  • Article

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