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
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):
||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
- 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.