Home > BufferPool > Database pages in the buffer pool

Database pages in the buffer pool

When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.

SELECT DB_NAME(database_id) AS [Database Name] ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2)) AS [Cached Size (MB)] FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) WHERE database_id not in (1,3,4) --{Do not include system database pages} AND database_id <> 32767 -- ResourceDB GROUP BY DB_NAME(database_id) ORDER BY [Cached Size (MB)] DESC

 

image

Non Buffer Pool Information

select name,sum(pages_kb)/128.0 [Cache Size (MB)] from sys.dm_os_memory_cache_entries where pages_kb > 0 group by name order by sum(pages_kb) desc

image

Categories: BufferPool
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: