CHECKPOINT writes all dirty pages for the current database to disk.
I will try to explain this with an example. I am going to create a sample table to illustrate the example.
— Creating the sample table called dbo.[test_checktable]
CREATE TABLE dbo.[test_checktable]
I have inserted 15000 records in the table and then submits an update query as follows:
WHERE [fname] like ‘%Sam%’
In SQL Server, following operations take place:
- Data pages from [test_checktable] are read from disk to memory so that the matching rows can be searched.
- It is found that five data pages hold fifteen rows that match the WHERE clause predicate.
- IMPLICIT TRANSACTION is started by the Storage Engine.
- Five data pages and fifteen rows are LOCKED to allow the updates to occur.
- Appropriate changes are made to all the fifteen rows in the five data pages in the memory.
- In Transaction Log, all the changes are recorded.
- Storage Engine COMMITS the IMPLICIT TRANSACTION.
The data pages will be written be written to disk when the next checkpoint occurs.
Checkpoints exist for two reasons—to batch up write I/Os to improve performance and to reduce the time required for crash recovery. In terms of performance, if a data page were forced out to disk each time it was updated, the number of write I/Os occurring on a busy system could easily overwhelm the I/O subsystem. It’s better to periodically write out dirty pages (pages that have been changed since being read from disk) than to write out pages immediately as they are changed.
One common misconception about checkpoints is that they only write out pages with changes from committed transactions. This is not true—a checkpoint always writes out all dirty pages, regardless of whether the transaction that changed a page has committed or not.