As we know, the SQL Server buffer cache is an area or region inside the server's memory where SQL Server keeps the data it needs for processing queries.
Primarily, the buffer cache's job is to reduce the amount of time SQL Server spends reading data from the disk. It does this by keeping frequently used data in memory, which is much faster to access than disk storage.
Additionally, the buffer also contains data that don't already exist on the disk, meaning in the SQL Server data files. That would be the case for the new data, when new data are added, although SQL Server will write those data immediately to the transaction log file (unless the Delayed Durability is turned on), these new data are not immediately written/stored into the data files, SQL Server waits until the the next CHECKPOINT process kicks in which then writes those called "dirty" into the data files. The LAZY WRITER process also writes dirty pages to the disk but it does so continuously, in a much less aggressive manner.
To calculate the size of the dirty pages in buffer cache, SQL Server exposes some meta data regarding the pages in buffer cache through sys.dm_os_buffer_descriptors DMV.
If you have VIEW SERVER STATE permission or, the SYSDBA role membership, you can query sys.dm_os_buffer_descriptors, mostly to get some summary information as it only contains the meta data. The below query is an example of such, which returns the ratio/percentage of dirty pages against the overall/total size of the buffer cache.
;WITH cte
AS (SELECT Cast(Count(*) AS FLOAT) Total_Pages,
Count(CASE WHEN is_modified = 0 THEN 1 END) Clean_Pages,
Count(CASE WHEN is_modified = 1 THEN 1 END) Dirty_Pages
FROM sys.dm_os_buffer_descriptors
WHERE page_type IN ( 'DATA_PAGE', 'INDEX_PAGE', 'TEXT_MIX_PAGE' ))
SELECT Total_Pages,
Clean_Pages,
Dirty_Pages,
Dirty_Pages_Percentage = Cast(( Dirty_Pages / Total_Pages ) * 100
AS DECIMAL(4, 2))
FROM cte;
To get this information for each database:
;WITH cte
AS (SELECT
database_id,
Case When database_id = 32767 then '(ResourceDB)'
Else Coalesce(db_name(database_id),'***** TOTAL *****')
End [Database],
Cast(Count(*) AS FLOAT) Total_Pages,
Count(CASE WHEN is_modified = 0 THEN 1 END) Clean_Pages,
Count(CASE WHEN is_modified = 1 THEN 1 END) Dirty_Pages
FROM sys.dm_os_buffer_descriptors
WHERE page_type IN ( 'DATA_PAGE', 'INDEX_PAGE', 'TEXT_MIX_PAGE' )
GROUP BY ROLLUP (database_id))
SELECT [Database],
Total_Pages,
Clean_Pages,
Dirty_Pages,
Dirty_Pages_Percentage = Cast(( Dirty_Pages / Total_Pages ) * 100
AS DECIMAL(4, 2))
FROM cte
ORDER BY Total_Pages desc;
A word of caution: if you have a very large buffer cache (like 75GB or even 750GB), querying this DMV can be slow. The larger the cache, the more data the DMV has to process. For a 75GB cache, it might need to process about ~10 million rows; for a 750GB cache, that number could jump to ~100 million rows!
Please also note that when using the sys.dm_os_buffer_descriptors tool to view the buffer cache contents, keep in mind that it also includes pages used by SQL Server's internal Resource database. Often, these pages are left out when people are summing up or analyzing the buffer cache data. However, in this particular example, I've chosen to include them in the count and analysis.