SQL Server uses a buffer pool of memory to cache data pages, reducing I/O demand and improving overall performance. SQL Server buffer pool also called as buffer cache is a place in the SQL Server system memory that is used for caching table and index 8KB data pages as they are modified or read from disk.
Below script will return data page count and total page size that are currently in the SQL Server buffer pool. When a data page is read from disk, the page is copied into the SQL Server buffer pool first and cached for reuse. DBCC DROPCLEANBUFFER command can be used to clean the buffer. Also The Buffer Pool Extension is an extension for the SQL Server buffer pool that targets non-volatile storage devices, such as solid-state disk drives (SSDs). When the Buffer Pool Extension is enabled, SQL Server uses it for data pages in a similar way to the main buffer pool memory.
[Dirty Page Count],
[Dirty Page Count] * 8 / 1024 AS [Dirty Page Size(MB)],
[Clean Page Count],
[Clean Page Count] * 8 /1024 AS [Clean Page Size(MB)]
DB_NAME([database_id]) AS [DatabaseName],
SUM(CASE WHEN ([is_modified]=1) THEN 1 ELSE 0 END) AS [Dirty Page Count],
SUM(CASE WHEN ([is_modified]=1) THEN 0 ELSE 1 END) AS [Clean Page Count]
GROUP BY [database_id]
) AS buffers
ORDER BY [Databasename]