CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: sys.dm_os_buffer_descriptors

How It Works: sys.dm_os_buffer_descriptors

  • Comments 2

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

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
Page 1 of 1 (2 items)