SQL Server Checkpoint and Lazy Writer

Good day, everyone! I hope you're doing well, and Today I'll show you how to use SQL Server's Checkpoint and Lazy Writer functions, as well as the many types of checkpoints available.

Checkpoint-

• Checkpoint is an internal operation that writes all dirty (modified) pages from the Buffer Cache to the physical disc, as well as log entries from the log buffer to the physical file. Hardening of dirty pages is the process of writing dirty pages from the buffer cache to the data file.
• It's a specialized process that SQL Server runs at predetermined times. SQL Server performs checkpoints for each database separately.
• Checkpoint speeds up SQL Server recovery time in the case of an unexpected shutdown or system failure.

Fig.1

Advantages of Checkpoint-

• It speeds up data recovery process.
• Most of the DBMS products automatically checkpoints themselves.
• Checkpoint records in log file is used to prevent unnecessary redo operations.
• Since dirty pages are flushed out continuously in the background, it has very low overhead and can be done frequently.

Types of Checkpoints-

There are four different kinds of checkpoints in SQL Server.

• Automatic checkpoint
• Indirect checkpoint
• Manual checkpoint
• Internal checkpoint

Automatic checkpoint-

When SQL Server satisfies certain requirements linked to the recovery interval set in the server configurations, this is automatically executed in the background. For SQL Server, you can set the recovery interval at the instance level. If the database engine detects a write latency of more than 50 milliseconds, automatic checkpoints are throttled.

Here is the script to set the CHECKPOINT value to 30 seconds automatically.

EXEC sp_configure'recovery interval','30'

Indirect Checkpoint-

When any SQL Server database satisfies certain requirements relating to the recovery time interval provided in the database configuration, this is run in the background. For SQL Server, you can set the recovery interval at the database level. This is enabled by default for your database in SQL Server 2016, and the value is 1 minute.

Here's how to set the Indirect CHECKPOINT value to 60 seconds with a script.

 ALTER DATABASE CURRENT

SET TARGET_RECOVERY_TIME = 60 SECONDS

Manual Checkpoint-

When the user runs the CHECKPOINT command on any database, this is run. With the aid of additional parameter checkpoint duration, the user can enhance or decrease the performance of this procedure as desired.

The script to run manual CHECKPOINT can be found here.

 CHECKPOINT

Internal Checkpoint-

When SQL Server completes specific tasks such as backups, shutdown, or maintaining isolations, it automatically runs this command to ensure that the disc contains the same database as the log.

This intrinsic operation is not done by a script.

Lazy writer-

The lazy writer is a system procedure that removes infrequently used pages from the buffer cache to keep free buffers available. Dirty pages are written to disc first. The eager write process publishes filthy data pages associated with actions that are only weakly logged, such as bulk insert and select into.