RMJCS Logo
 

sys.dm_os_buffer_descriptors

 
 

From BOL: Returns information about all the data pages that are currently in the SQL Server buffer pool. The output of this view can be used to determine the distribution of database pages in the buffer pool according to database, object, or type.

Examples below based closely on the BOL examples.

See Chad Boyd's MSDN blog entry sys.dm_os_buffer_descriptors aggregations for more info.

See the SQLTeam article What Data is in SQL Server's Memory? for more info.

 
     
 

Page count and free space in pages per database

 
 
SELECT 
   CASE WHEN bd.database_id = 32767 THEN 'resourceDb' ELSE db_name(bd.database_id) END AS database_name,
   COUNT(*) AS cached_pages_count,
   AVG(bd.row_count) AS avg_rows_per_page,
   SUM(bd.free_space_in_bytes) AS sum_free_space_in_bytes
FROM 
   sys.dm_os_buffer_descriptors bd
GROUP BY 
   bd.database_id
ORDER BY 
   cached_pages_count DESC
 
     
 

Page count and free space in pages in current database per page type

 
 
SELECT 
   CASE WHEN bd.database_id = 32767 THEN 'resourceDb' ELSE db_name(bd.database_id) END AS database_name,
   COUNT(*) AS cached_pages_count,
   AVG(bd.row_count) AS avg_rows_per_page,
   SUM(bd.free_space_in_bytes) AS sum_free_space_in_bytes
FROM 
   sys.dm_os_buffer_descriptors bd
GROUP BY 
   bd.database_id
ORDER BY 
   cached_pages_count DESC
 
     
Copyright 2007-2015 RMJCS Ltd