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'
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.
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
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.
EXEC [sp_configure] '[recovery interval]', 'seconds'
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.
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.
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.
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.
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 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
Automatic Checkpoint with target recovery interval 1
Automatic Checkpoint with target recovery interval equal to the time specified using the ‘sp_configure ‘recovery interval’ option.
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.
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.
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.
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.
While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at email@example.com
View all posts by Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
- Query Amazon Athena external tables using SQL Server - November 15, 2019
- Read SQL Server error logs using the xp_readerrorlog command - November 14, 2019
- Overview of DBCC INPUTBUFFER and sys.dm_exec_input_buffer DMF - November 7, 2019