- When a page is read from disk in memory, it is considered a clean page because it is similar to its equivalent on disk.
- However, once the page has been modified in memory it is marked as a dirty page means Any pages which are available in buffer pool different from disk are known as Dirty Pages.
- Simply we can say that the pages which are modified in the buffer cache is called as a ‘Dirty page’.
- Clean pages can be flushed with cache using Dbcc dropcleanbuffers, which can work when You are troubleshooting the development and testing environment because it forces subsequent reads to be Completed from disk instead of cache, but it does not touch any dirty page.
- A dirty page is simply a page that has been changed in memory since it was loaded from disk and is now different from the on-disk page.
- we can find out how many dirty pages exist in each databases using sys.dm_os_buffer_descriptors DMV.
Through below query we can find out how many dirty pages are available in each databases:-
SELECT db_name(database_id) AS 'Database',count(page_id) AS 'Dirty Pages' FROM sys.dm_os_buffer_descriptors WHERE is_modified =1 GROUP BY db_name(database_id) ORDER BY count(page_id) DESC
