SQL Server: Observing SQL Server Data Cache (Buffer Pool concepts)

Hi Friends,

The Data Cache is the largest portion of SQL Server Buffer Pool, the main memory consumer in SQL Server. Data Cache is the place where every 8 KB page is put into before reading and is the place where the page is written to before being put on disk. DMVs, as many of you know are the hidden gems in SQL Server. One such DMV, sys.dm_os_buffer_descriptors, allows you to see the internals of Data Cache. If you simply run a SELCT * from sys.dm_os_buffer_descriptors, you will get bunch of metadata which might not make sense immediately. So here is a small query that gives you Cache Size database-wise:

SELECT count(*)*8/1024 AS 'Data Cache Size(MB)'
,CASE database_id
WHEN 32767 THEN 'RESOURCEDB'
ELSE db_name(database_id)
END AS 'DatabaseName'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY 'Data Cache Size(MB)' DESC

Run the above query and note down the metrics. Run a few select statements that will cause more pages to be loaded into memory. Run the above query again and note the difference.

This is how I tested:

  1. Restarted my SQL R2 instance on my dev machine (not production :)-
  2. Ran the above DMV query.
  3. The output showed a Data Cache Size of 2 MB for AdventureWorks2008R2 database.
  4. I ran a simple select statement – SELECT * from Person.Person; which resulted in loading a few thousand pages into the Data Cache.
  5. I ran the DMV again which showed that the Data Cache size for AdventureWorks2008R2 database has grown to 33 MB.

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

2 Comments on “SQL Server: Observing SQL Server Data Cache (Buffer Pool concepts)”

  1. Thanks Amit.

    Very useful information.

    I am looking more articles on VAS (Bpool, MTL) components. Can you please share.

Leave a Reply

Your email address will not be published. Required fields are marked *