How does sql server know whether the dirty pages have been written to the data file?

Question

I understand sql server writes the updates to the data pages (making them dirty) in memory and writes the tlog onto the disk before sending the commit notification to the user.

This question is to ask in either of following scenarios:

1. After flushing dirty pages from memory to disk
2. Or after reboot post sql crash

How does the sql server know which dirty pages (data changes due to the recent set of tlogs) had been written to the disk or are yet to be written to disk?

Answer 1

Ref:

• Database checkpoints (SQL Server)
• A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions by Paul Randal

As Sean Gallardy mentioned in the comment, SQL Server does not need to know which dirty pages are written to disk. SQL Server only needs to know which pages are dirty.

The key here is all dirty pages, it does not matter if the transaction is committed or not.

For performance reasons, the Database Engine performs modifications to database pages in memory, in the buffer cache, and doesn't write these pages to disk after every change. Rather, the Database Engine periodically issues a checkpoint on each database. A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk, and also records the information in the transaction log.

After a crash database boot page has the log sequence number of the last checkpoint. That is how SQL Server knows from where to start the crash recovery.