SQL Server - Buffer pool pages distribution

Check page count stored in buffer pool for every database. 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. When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse.

Use this script to quick overview how is your load distributed among databases.

TIP: You can colect results on regular basis (create SQL Agent job) for further analysis.

Script source: https://logicalread.com/sql-server-memory-buffer-pools-pd01

SELECT
	CASE 
		WHEN [database_id] = 32767 THEN 'Resource Database' 
		ELSE DB_NAME(database_id) 
    END AS DatabaseName, 
    SUM(CASE 
		WHEN [is_modified] = 1 THEN 0 
		ELSE 1 
    END) AS CleanPageCount,
	SUM(CASE 
        WHEN [is_modified] = 1 THEN 1 
        ELSE 0 
    END) AS DirtyPageCount
FROM sys.dm_os_buffer_descriptors 
GROUP BY database_id 
ORDER BY DB_NAME(database_id)

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
DatabaseNamesysnameName of database for pages distribution.
CleanPageCountintCount of pages that have not been modified after it was read from the disk. Is nullable.
DirtyPageCountintCount of pages that have been modified after it was read from the disk. Is nullable.

Leave a Reply

Your email address will not be published. Required fields are marked *