What is Checkpoint Process in SQL Server?

Have you deep dive into SQL Server checkpoint process like what checkpoints are and how they work? Here is an article which will explain you about checkpoint process in SQL Server.

What is Checkpoint Process in SQL Server?

As per book online, a checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

There are lot of operations run in database. If SQL Server performs any modifications to any database pages in memory/buffer cache, SQL Server doesn’t write these modified pages to disk after every change. Rather, SQL Server Database Engine periodically issues a checkpoint on each database and these modified pages and transaction logs in memory will be written to disk as part of checkpoint process.

Checkpoint Types

SQL Server supports four types of checkpoints. Which checkpoint SQL Server database engine will use to write dirty pages to disk is based on their nature of operations and few configuration parameters which we set on instance level as well as database level. Below are the list of checkpoints SQL Server performs.

Automatic Checkpoints

An automatic checkpoint occurs each time the number of log records reaches the number the Database Engine estimates it can process during the time specified in the recovery interval server configuration option. You can set the value of recovery interval using below system stored procedure.

sp_configure 'recovery interval','seconds'

Recovery interval is maximum time that a given server instance should use to recover a database during a system restart. Database Engine estimates the maximum number of log records it can process within the recovery interval. When a database using automatic checkpoints reaches this maximum number of log records, the Database Engine issues an checkpoint on the database.

The frequency depends on the value of recovery interval option of server configuration. The time interval between automatic checkpoints can be highly variable. A database with a substantial transaction workload will have more frequent checkpoints than a database used primarily for read-only operations.

Database Engine generates automatic checkpoints for every database if you have not defined any value for target recovery time for a database. If you define user value for target recovery time for any database, database engine will not use automatic checkpoint and generate indirect checkpoint which I will discuss in next section.

Under the simple recovery model, an automatic checkpoint is also queued if the log becomes 70 percent full.

After a system crash, the length of time required to recover a given database depends largely on the amount of random I/O needed to redo pages that were dirty at the time of the crash. This means that the recovery interval setting is unreliable. It cannot determine an accurate recovery duration. Furthermore, when an automatic checkpoint is in progress, the general I/O activity for data increases significantly and quite unpredictably.

For an online transaction processing (OLTP) system using short transactions, recovery interval is the primary factor determining recovery time. However, the recovery interval option does not affect the time required to undo a long-running transaction. Recovery of a database with a long-running transaction can take much longer than the specified in the recovery interval option.

Typically, the default values provides optimal recovery performance. However, changing the recovery interval might improve performance in the following circumstances:

  1. If recovery routinely takes significantly longer than 1 minute when long-running transactions are not being rolled back.
  2. If you notice that frequent checkpoints are impairing performance on a database.

If you decide to increase the recovery interval setting, we recommend increasing it gradually by small increments and evaluating the effect of each incremental increase on recovery performance. This approach is important because as the recovery interval setting increases, database recovery takes that many times longer to complete. For example, if you change recovery interval 10 minutes, recovery takes approximately 10 times longer to complete than when recovery interval is set to 1 minute.

Indirect Checkpoints

This checkpoint was introduced in SQL Server 2012. Indirect checkpoints provide a configurable database-level alternative to automatic checkpoints. In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time than automatic checkpoints. We can set the TARGET_RECOVERY_TIME in Indirect checkpoint for any database by executing below ALTER DATABASE statement.

--Change DBNAME with your database name.
ALTER DATABASE DBNAME SET TARGET_RECOVERY_TIME =target_recovery_time {SECONDS | MINUTES}

The difference between Automatic and Indirect checkpoint is, recovery interval configuration option uses the number of transactions to determine the recovery time as opposed to indirect checkpoints which makes use of number of dirty pages.

When indirect checkpoints are enabled on a database receiving a large number of DML operations, the background writer can start aggressively flushing dirty buffers to disk to ensure that the time required to perform recovery is within the target recovery time set of the database. This can cause additional I/O activity on certain systems which can contribute to a performance bottleneck if the disk subsystem is operating above or nearing the I/O threshold.

  1. Indirect checkpoints enable you to reliably control database recovery time by factoring in the cost of random I/O during REDO.
  2. Indirect checkpoints reduce checkpoint-related I/O spiking by continually writing dirty pages to disk in the background. However, an online transactional workload on a database configured for indirect checkpoints can experience performance degradation. This is because the background writer used by indirect checkpoint sometimes increases the total write load for a server instance.

Indirect checkpoint is the default behavior for new databases created in SQL Server 2016. Databases which were upgraded in place or restored from a previous version of SQL Server will use the previous automatic checkpoint behavior unless explicitly altered to use indirect checkpoint.

Manual Checkpoints

Manual checkpoint issued when you execute a T-SQL CHECKPOINT command for a database. By default, manual checkpoints run to completion. Throttling works the same way as for automatic checkpoints.

Internal Checkpoints

Internal Checkpoints are generated by various server components to guarantee that disk images match the current state of the log. Internal checkpoint are generated in response to the following events:

  1. Database files have been added or removed by using ALTER DATABASE.
  2. A database backup is taken.
  3. A database snapshot is created, whether explicitly or internally for DBCC CHECK.
  4. An activity requiring a database shutdown is performed. For example, AUTO_CLOSE is ON and the last user connection to the database is closed, or a database option change is made that requires a restart of the database.
  5. An instance of SQL Server is stopped by stopping the SQL Server (MSSQLSERVER) service. Either action causes a checkpoint in each database in the instance of SQL Server.
  6. Bringing a SQL Server failover cluster instance (FCI) offline.

Checkpoint generated by SQL Server in different scenario

Now one question might come to your mind that how SQL Server decide which checkpoint should be used between Automatic and Indirect checkpoint if a database has target recovery time value is set to use indirect checkpoint. Below are the configuration values based on which SQL Server considers to generate either automatic checkpoint or indirect checkpoint?

  1. Automatic checkpoints whose target recovery interval is 1 minute will be generated if ‘recovery interval’ value in sp_configure command and TARGET_RECOVERY_TIME value in ALTER DATABASE command is set to 0.
  2. Automatic checkpoints whose target recovery interval is specified by the user defined setting of the sp_ configure recovery interval option if TARGET_RECOVERY_TIME value in ALTER DATABASE command is set to 0 and if ‘recovery interval’ value in sp_configure command is non zero.
  3. Indirect checkpoints when target recovery time is determined by the TARGET_RECOVERY_TIME value in ALTER DATABASE command.

You can follow attached link to understand SQL Server Transaction Log Architecture which is also related to checkpoints topic. I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Read More:

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *