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.