Things to know about DBCC DROPCLEANBUFFERS

You may have observed that sometimes DROPCLEANBUFFERS is not working as you expected. And there are still some pages in buffer area.

This is because "DBCC DROPCLEANBUFFERS" drops only CLEAN pages from the buffer pool.

A clean page is one that has not been changed since it was read into memory or last written to disk. A dirty page is one that has not been written to disk since it was last changed. Dirty pages are not dropped by DBCC DROPCLEANBUFFERS, they are only made clean by writing them to disk (either through one of the various kinds of checkpoints or by the lazy writer – or one of the per-NUMA node lazy writers if you have NUMA configured).

If you want that all pages from a database are flushed from memory, you need to first perform a manual CHECKPOINT of that database and then run DBCC DROPCLEANBUFFERS.

Thank You!

Script to find Dirty/Clean Pages in SQL Server

I use below script to find out the dirty and clean pages in memory. Along with this,the script
will also give you the size for both Dirty and Clean pages: 


T-SQL Script:

SELECT *,

    [DirtyPageCount] * 8 / 1024 AS [DirtyPageMB],

    [CleanPageCount] * 8 / 1024 AS [CleanPageMB]

from

    (SELECT

        (CASE WHEN ([database_id] = 32767)

            THEN N'Resource Database'

            ELSE DB_NAME ([database_id]) END) AS [DatabaseName],

        SUM (CASE WHEN ([is_modified] = 1)

            THEN 1 ELSE 0 END) AS [DirtyPageCount],

        SUM (CASE WHEN ([is_modified] = 1)

            THEN 0 ELSE 1 END) AS [CleanPageCount]

    FROM sys.dm_os_buffer_descriptors

    GROUP BY [database_id]) AS [buffers]

ORDER BY [DatabaseName]

Go

Thank You!

Reference: Paul Randal Blog