Archive for the ‘BufferPool’ Category

Database pages in the buffer pool

January 1, 2015 Leave a comment

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



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


Categories: BufferPool