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)
Script results screen:
Script results detailed description:
|Column name||Data type||Description|
|DatabaseName||sysname||Name of database for pages distribution.|
|CleanPageCount||int||Count of pages that have not been modified after it was read from the disk. Is nullable.|
|DirtyPageCount||int||Count of pages that have been modified after it was read from the disk. Is nullable.|