CHECKPOINT is an internal SQL Server process that runs periodically to write dirty pages (changed pages) and transaction log records from memory to disk. It then marks a point in the transaction log. The database engine uses this log information as a starting point during the recovery process after an unexpected database crash or server shutdown. Unlike lazy-writer it doesn’t free up the memory but it only identifies the pages which are modified in memory and write to the disk.
There are four types of CHECKPOINT automatic, indirect, manual and internal. Though there are several types of CHECKPOINT the operation is the same and it only differs in the execution circumstance. For example, the indirect checkpoint will be trigged based on the recovery time setting in the database and the manual checkpoint is triggered manually from the code.
The following example shows how the manual checkpoint works
Executing the SELECT statement brings the relevant pages from disk to memory. The is_modified field is 0 (as shown in the below image). The value 0 in the is_modified indicates that the pages are not modified. The statement is a simple SELECT statement and the pages brought into the memory were not modified.
Select * from SalesOrder where Orderid<=10
(The below screenshot shows the in-memory pages. Read the post Identifying cached tables in SQL Server buffer to know how to find in-memory pages)
The below code is now executed to modify the records and as shown in the image there are many pages with is_modified values as 1 which indicates that the pages are changed in memory but the changes are not stored in the data file on the disk yet.
update SalesOrder set Orderdate = Orderdate where Orderid<=10
The below screen scrap is captured after the execution of the CHECKPOINT statement, as shown in the image is_modified is changed from 1 to 0 for all the pages which is the effect of checkpoint (i.e.) The changed pages were written to disk and marked those in-memory pages as clean. The pages are still in memory because the CHECKPOINT only writes the changed pages to disk and does not clear the memory.
