5

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?

2
9

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.

11
  • I can't say for sure but my database should have been active since it is the only one I am using on that instance. After running your script, there were still 10 dirty pages. After running it again a little later there are 0 now. From the performance side, a few buffered pages aren't too important but I would still like to know why it is not writting all dirty pages every time. – Florian Dec 3 '20 at 13:29
  • @Florian Interesting! Is it possible that there are other users or processes connected to this database that are modifying data pages? – Josh Darnell Dec 3 '20 at 13:42
  • 1
    @Florian I just confirmed with a local test that SELECT queries will produce modified rows in sys.dm_os_buffer_descriptors when you have Query Store turned on. That might be what you're seeing. All the metadata about queries gets stored in the user database (query text, execution count, compilation stats, runtime stats, etc), which means it goes through the normal buffer pool -> flush to disk process like other data pages. – Josh Darnell Dec 3 '20 at 15:01
  • 1
    Also look out for ghost record cleanup, which may be dirtying pages as it cleans up ghost records after the checkpoint. – sqL_handLe Dec 3 '20 at 23:16
  • 1
    @sqL thanks, L! I guess there are probably a bazillion different reasons pages might be dirtied after explicit write operations have stopped (deferred drop from a truncate, stats updates, in addition to the things already mentioned). I'll update my answer to indicate that. – Josh Darnell Dec 3 '20 at 23:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.