SQL Server CHECKPOINT, Lazy Writer, Eager Writer and Dirty Pages in SQL Server

Database administrators should be aware of the internal SQL Server processes such as the dirty pages, SQL Server CHECKPOINT, Lazy writer process. This is a very common question that you might come across in SQL DBA technical interviews as well on all levels such as beginner, intermediate and expert level.

SQL Server Checkpoint and Lazy writer write the dirty pages from the buffer pool to the disk. Let’s explore both the processes and differences between them in this article.

What are Dirty pages?

In the following image, a user starts an update transaction to an Employee table. Suppose it updates 2 pages in the database.

First, SQL Server tries to locate the page in the buffer cache. If it does not find the page, SQL Server gets that page from the disk in the buffer cache. This page is known as a clean page because it does not contain any changes
SQL Server acquires locks on the pages, row-level and performs an update to the records. The modified page is known as a Dirty page. In the above image, you can see the dirty pages in green color
It creates a log record describing the changes made. SQL Server writes the log records to the transaction log. It also sends commit acknowledgment to the user. The changed page is still in the buffer cache

We can use DMV sys.dm_os_buffer_descriptors to check the dirty pages in memory for each online database in the instance. We can use the column is_modified to see the dirty pages.

SELECT db_name(database_id) AS 'Database',

count(page_id) AS 'Dirty Pages'

FROM sys.dm_os_buffer_descriptors

WHERE is_modified =1

GROUP BY db_name(database_id)

ORDER BY count(page_id) DESC

We get the dirty pages count for each online database in SQL Server instance.

What is Write-ahead logging?

Once we make a change to a clean page in the buffer cache, it generates log records containing details of the modification. SQL Server writes this log record to the disk in the transaction log before the associated dirty page is flushed to the disk (data files) from the buffer cache. This process is known as write-ahead logging. It ensures the ACID (Atomicity – Consistency – Isolation – Durability).

In the following image, we can see that the transaction log record is written first, and later the page is written to the disk (data file). It ensures that if any issues happen and the server suddenly shuts down, during database recovery, it reads the transaction log file and prepares the recovery process (UNDO, REDO).

SQL Server CHECKPOINT and Dirty pages overview

A Checkpoint process writes all dirty pages (available in the buffer cache) and transaction log records to the disk. It also logs checkpoint information in the transaction log. It performs the following tasks as shown in the following image.

It writes the log records from the buffer cache to the disk ( transaction log file)
It writes all dirty pages ( modified pages since the last checkpoint) to the data file ( MDF/NDF)
It also writes the Checkpoint LSN to the database boot page

Types of SQL Server CHECKPOINTS

We have several types of checkpoints. Let’s explore them in this section.

Automatic CHECKPOINT

It is a background process in SQL Server and default, most common checkpoint. We have a recovery interval server configuration in the sp_configure command, and you can check it using the Show Advanced Option.

sp_configure 'show advanced options', 1

RECONFIGURE with override

sp_configure

The default value for the recovery interval is set to zero, which means the target recovery interval is one minute.

SQL Server continuously checks the number of the log record, and once it estimates that the number of log records is sufficient to process in the time specified as per the recovery interval configuration option, it issues Checkpoint command.

We can modify the recovery interval using the following command; however, you should not play with it until you are a highly skilled DBA and understand the risk of doing it.

USE [master];

EXEC [sp_configure] '[recovery interval]', 'seconds'

GO;

Internal CHECKPOINT

SQL Server also issues internal SQL Server checkpoint based on a few key events in SQL Server. These events are as follows.

During database backup
Database Snapshots. It might be explicitly database snapshot or snapshot due to the DBCC CheckDB command
SQL Server also performs Checkpoint for a clean shutdown of SQL Services
Add\Remove database files using the Alter database command
Switching recovery models from FULL or Bulk-logged to Simple
In the simple recovery model, if the log file becomes 70% full

SQL Server controls these internal Checkpoint events. DBA cannot interfere in these operations.

Manual CHECKPOINT

We can also issue Manual Checkpoint in SQL Server using the SQL Server CHECKPOINT command. This command runs under the current database context only.

The command for the manual checkpoint is as below.

USE [master];

CHECKPOINT [checkpoint_duration_in_Seconds]

GO;

We can omit the checkpoint_duration_in_Seconds parameter. SQL Server runs the checkpoint command by adjusting the checkpoint duration to minimize the performance impact.

If we specify the checkpoint duration in seconds, SQL Server tries to perform the Checkpoint in the requested duration. It might force SQL Server to use additional resources and complete the checkpoint operation during the specified time only.

For example, let’s say checkpoint takes typically 10 seconds to complete.

Scenario 1: You execute the following SQL Server CHECKPOINT command to complete the CHECKPOINT in 5 seconds.

1	CHECKPOINT 5

It will cause SQL Server to assign more resources, and you might experience performance issues.

Scenario 2: You execute the following checkpoint command to complete the CHECKPOINT in 5 seconds.

1	CHECKPOINT 20

It might cause SQL Server to align fewer resources compared to the default allocation, but CHECKPOINT might take a longer time, and it might impact the database recovery in case of any unforeseen circumstances.

Indirect CHECKPOINT

Indirect Checkpoint is the new feature starting from SQL Server 2012. It is the default SQL Server Checkpoint type from SQL Server 2016. In this mode, we can specify target recovery time for a specific database.

We can specify the target recovery time using the following Alter database command.

ALTER DATABASE [Database name] SET TARGET_RECOVERY_TIME = Duration_InSeconds;

There is a relation between the recovery interval configuration (using the sp_configure ‘Recovery Interval) and the Database specific target recovery time ( using the alter database command as shown above)

ALTER DATABASE SET TARGET_RECOVERY_TIME	Sp_Configure ‘Recovery Interval’	Type of Checkpoint Used
0	0	Automatic Checkpoint with target recovery interval 1
0	> 0	Automatic Checkpoint with target recovery interval equal to the time specified using the ‘sp_configure ‘recovery interval’ option.
>0	NA	Indirect checkpoints with target recovery time configured using the Target_recovery_time

You can go through Database checkpoints – Enhancements in SQL Server 2016 to read about enhancements in SQL Server 2016 CHECKPOINT.

How do you monitor CHECKPOINT events in SQL Server?

We can use the undocumented system function, fn_dblog to monitor the SQL Server CHECKPOINT event in the current database.

1 2	select [Current LSN] , Operation, Context, LogBlockGeneration,[Checkpoint Begin],[Checkpoint End],Description,[Log Record] from ::fn_dblog(null,null) WHERE [Operation] like '%CKPT'

It gives the details of the CHECKPOINT event as shown below.

You can also use the trace flag 3504 to log the CHECKPOINT information in the error log. Starting from SQL Server 2012, SQL Server logs information in the error log for the long CHECKPOINT.

What is Lazy Writer?

The Lazy writer process also flushes out the dirty pages to the disk. There is a difference between the SQL Server CHECKPOINT and the Lazy writer process.

CHECKPOINT does not remove the dirty pages from the memory. The dirty pages after written to disk are marked as Clean and stay in the buffer cache. It helps SQL Server to avoid IO intensive task to fetch pages from the disk to memory.

The Lazy writer process checks for the pages in the buffer pool and flushes them to the disk. It removes both the clean and dirty pages from the buffer cache. Its job is to keep a certain number of free pages available inside the buffer pool so that other queries do not suffer. It checks out the least recently used pages and removes the pages not being used actively.

It can remove the clean pages without any additional efforts. For the dirty pages, it needs to flush out these pages to the disk before removing out. Due to this, you might find lazy writer removing the higher pages count than the Checkpoint dirty pages flushing out.

You can see very little activity of Lazy Writer in a healthy database system. If you observe continuous use of the Lazy Writer process, it shows that it has to do much work and you have fewer pages in the memory. SQL Server has to flush out pages frequently, and new queries need to fetch the pages in the memory first from the disk. You can observe very low page life expectancy values in this case. You should analyze the memory requirement, workload and tune the resources if required.

Difference between the SQL Server CHECKPOINT and Lazy Writer

Let’s have a quick summary to see the difference between the CHECKPOINT and the Lazy Writer process.

CHECKPOINT	LAZY WRITER
SQL Server uses CHECKPOINT to flush dirty pages in the disk to keep database recovery as per the defined threshold. check	SQL Server uses the Lazy Writer process to flush the dirty and clean pages for keeping free space in the buffer cache to accommodate new pages
CHECKPOINT generates the transaction log records and follows the write-ahead logging process	A lazy writer does not make any entry in the transaction log
CHECKPOINT flushes only the dirty pages	It flushes both dirty and clean pages
We can manually execute the CHECKPOINT or control the CHECKPOINT behavior by setting the appropriate parameters	DBA cannot control on the Lazy Writer process
CHECKPOINT is also dependent on the recovery model	Lazy Writer does not have any relationship with the database recovery model
We can monitor the CHECKPOINT event by using the undocumented system function as well as the trace flags. Starting from SQL Server 2012, it also logs an entry in the error log for the long CHECKPOINT	We cannot monitor the Lazy Writer process
We can check CHECKPOINT LSN in the database boot page	We cannot check the Lazy writer process

You can read this article, SQL Server memory performance metrics – Part 5 – understanding Lazy Writes, Free List Stalls/sec, and Memory Grants Pending to monitor the database performance metrics.

Have you heard about Eager Writer?

SQL Server performs certain activities as non-logged operations. The examples are: SELECT INTO, Bulk Insert, WriteText and UpdateText.

The Eager Writer process handles both the page creation and page writing in parallel so that queries do not wait to complete the entire bulk process before writing all pages to the disk.

Conclusion

In this article, we explored the internal processes such as SQL Server CHECKPOINT, Write-Ahead Logging, Lazy writer and Eager Writer. You should be aware of these processes. If you have any comments or questions, feel free to leave them in the comments below.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023