DB - MS-SQL - Buffer Pool Memory Management

There are a lot more system DMVs available to gather the information at very high level. The idea is to gather the details of the buffer pool management at the database level. The sys.dm_os_buffer_descriptors DMV has a row for each data page read into memory and cached. It is used to determine how the buffer pool is organized and used.


USE MASTER
GO


DECLARE @DML nvarchar(MAX)

DECLARE @bufferpoolmngmntgrv TABLE(
MIICRRAS [nvarchar](128) NULL,
BufferPageCnt int,
BufferSizeMB [decimal](10, 2) NULL,
PageStatus varchar(10)
)


SET @DML='SELECT MIICRRAS = CASE WHEN database_id = 32767 THEN ''RESOURCEDB''
ELSE DB_NAME(database_id) END,
Bufferpage=count_BIG(*),
BufferSizeMB = COUNT(1)/128,
PageStatus = max(CASE WHEN is_modified = 1 THEN ''Dirty'' 
ELSE ''Clean'' END)
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY 2 DESC'

INSERT INTO @bufferpoolmngmntgrv
EXEC sp_executesql @DML

SELECT * FROM @bufferpoolmngmntgrv