Dirty buffer pages after issuing CHECKPOINT

Question

I am currently working on a test system and due to the nature of the queries I want to optimise, I am trying to simulate a "cold" read a well as I can. Part of that is clearing the buffer cache before performing the queries. From everything I can find dirty buffer pages are supposed to be written during a checkpoint. However, even after issuing a CHECKPOINT, there still seem to be 169 dirty pages of my database in the buffer pool (assessed via SELECT * FROM sys.dm_os_buffer_descriptors WHERE database_id=7 AND is_modified=1).

Is there anything I am misunderstanding about checkpoints or the content of sys.dm_os_buffer_descriptors? If not, why do I still have dirty pages after they were supposedly written away?

Answer 1

A manual CHECKPOINT should write all dirty pages to disk. It does this for the database that is active for your connection, not for all databases. Make sure the current database is the name of whatever database_id=7 is:

USE [YourDatabaseName];
GO
CHECKPOINT;
GO
SELECT * 
FROM sys.dm_os_buffer_descriptors 
WHERE database_id = 7 AND is_modified = 1;

See How do checkpoints work and what gets logged:

All dirty data file pages for the database are written to disk (all pages that have changed in memory since they were read from disk or since the last checkpoint), regardless of the state of the transaction that made the change.

And Database Checkpoints (SQL Server):

Name	Transact-SQL Interface	Description
Manual	CHECKPOINT [checkpoint_duration]	Issued when you execute a Transact-SQL CHECKPOINT command. The manual checkpoint occurs in the current database for your connection...

Note that a bunch of things can cause dirty pages show up in sys.dm_os_buffer_descriptors after all explicit (user initiated) write operations have completed. Some of these could be in response to SELECT queries:

• sync or async stats updates from plan compilation
• query store data collection

Or due to background tasks related to specific SQL Server features:

• columnstore tuple mover
• deferred drop (if tables were truncated or dropped, but extents are still being deallocated in the background
• Change Tracking autocleaup
• PVS cleanup (if using Accelerated Database Recovery on SQL Server 2019)

That's not an exhaustive list, but gives you an idea that you might see dirty pages for many reasons beyond just inserts, updates, and deletes.