What is Database Checkpoint in SQL Server

Database Checkpoint process is one of the most important part of SQL Server Architecture. Before you read this article, you need to understand the significance of the transaction log file.

I would recommend reading the article “What is SQL Server Transaction Log“.

I wanted to write this article because many people have question marks about the working principle of sql server.

You might think this article is a little theoretical. But I want to insist on reading anyone who wants to deepen on SQL Server. First you should understand SQL Server write ahead logging mechanism to understand checpoint.

Write Ahead Logging in SQL Server

In SQL Server, data is not read directly from the disk.

Data is first transferred from the disk to the buffer cache (the space used by the sql server for data and index pages in memory).

As long as the data is in buffer cache, if a query needs this data, it reads this data from the buffer cache.

Over time SQL Server need to modify the data in the buffer cache. So what happens to changed data in the buffer cache?

The answer is below question;

What is Dity Page in SQL Server?

Pages that have been modified in the Buffer Cache and have not yet been written to the disk are called dirty pages.

Well, data changed in memory. And then? When will the changed data be written to disk?

What is Log Flush in SQL Server?

When a page in the Buffer Cache is modified, it is not immediately written to the disk.

First a log is created in the log cache (the area where the log records are stored in memory).

Unless the relevant record in the log cache is physically written to the transaction log file(this is known as log flush), the record that has changed in the buffer cache is not written physically to the data file. This protect mechanism called as Write Ahead Logging.

If the data is written to the data file before it is physically written to the transaction log file, rollback operation of uncommitted transactions cannot be performed if the server is shut down. Because, SQL Server performs recovery operations using Transaction Log file. If it can not find the transaction records in the Transaction Log file, it can not rollback these transactions. That’s why, SQL Server protects data integrity using WAL mechanism we have mentioned just before.

What is Lazy Writer in SQL Server?

The write operation of data from the buffer cache to the disk is known as page flushing.

The page flushing operation is performed by “Lazy Writer”.

SQL Server regularly monitors resources, and triggers Lazy Writer when there is contention on memory.

And this way, when dirty pages in the buffer cache are written to the disk, enough space is available in the buffer cache for other sessions to use.

What is Checkpoint in SQL Server?

SQL Server periodically runs the checkpoint process.

When Checkpoint runs, records in the log cache and dirty pages will be written to disk as I mentioned above(records that have changed since read from disk or since the last checkpoint was run).

Checkpoint can also be run manually with the CHEKCPOINT command.

But I recommend you not to interfere with SQL Server internal processes.

Checkpoint’s purpose is to shorten the recovery time when the database is opened.

I described a scenario related to this in the article named “What is SQL Server Transaction Log“.

SQL Server Recovery Process

Assume that the SQL Server Server unexpectedly shuts down.

I refer to the LSN concept in the article titled “What is SQL Server Transaction Log“.

The page header(A 96-byte field that stores metadata information for the page) of all data pages contains the LSN information of the last log file that affects this page.

I have already told you that SQL Server uses the transaction log file while it is being recovered.

In the header of the data pages, the LSN information of the corresponding log record determines how the log record is to be recovered.(redone/roll forward or undone/roll back)

Below you can see what happens in any situation.

For a log that is associated with a committed transaction;

• If the LSN in the header of the data page is greater than or equal to the LSN of the log record, this indicates that the corresponding log record has been written to disk and no operation is performed.
• If the LSN in the header of the data page is smaller than the LSN of the log record, this indicates that the corresponding log record has not been written to disk, and a redo operation is performed to write the relevant log record to the disk.

For a log record that is associated with an uncommitted transaction;

• If the LSN in the header of the data page is greater than or equal to the LSN of the log record, the relevant log record must be undone. (Under normal circumstances, the WAL mechanism does not allow such a situation.)
• If the LSN in the header of the data page is less than the LSN of the log record, this indicates that the corresponding log record has not been written to disk and there is no transaction commit, so no action is taken.

For more information about WAL, you may want to read the article on the link below.

https://technet.microsoft.com/en-us/library/jj835093(v=sql.110).aspx

If you want to learn more about SQL Server’s post-crash recovery process, you might want to read Paul Randal’s article on the link below.

https://technet.microsoft.com/en-us/2009.02.logging

If you are using the simple recovery model, non-active logs are truncated after Checkpoint.

You may want to read the article titled “What is Database Recovery Model“.

Checkpoint Types in SQL Server

Automatic Checkpoint in SQL Server

Automatic Checkpoints are triggered depending on the setting in the following script. The default value is 0 and means that checkpoint will be triggered every 60 seconds.

execute sp_configure for seeing other parameters.

Indirect Checkpoint in SQL Server

This is a database-based setting and is determined by the following command. This setting will override the Automatic setting specified at the Instance level.

Manual Checkpoint in SQL Server

Checkpoint process can be triggered by running the CHECKPOINT command. Do not execute manually if you dont know what you are doing. 🙂

Internal Checkpoint in SQL Server

It is automatically triggered by the database engine in operations such as backing up, creating a database Snapshot, adding or removing database files, a clean shutdown for the SQL Server service.