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进程，例如脏页，SQL Server CHECKPOINT，惰性编写器进程。 这是一个非常常见的问题，您可能会在SQL DBA技术面试中以及所有级别（例如初学者，中级和专家级）中遇到。
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.
SQL Server Checkpoint和惰性编写器将缓冲池中的脏页写入磁盘。 让我们探索本文中的过程和它们之间的差异。
什么是脏页？ (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.
在下图中，用户启动到Employee表的更新事务。 假设它更新数据库中的2页。
- 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尝试在缓冲区缓存中找到页面。 如果找不到该页面，则SQL Server从缓冲区缓存中的磁盘获取该页面。 此页面被称为干净页面，因为它不包含任何更改
- 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 SQL Server在页面上获取行级锁，并对记录执行更新。 修改后的页面称为脏页面。 在上图中，您可以看到绿色的脏页
- 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 它创建描述所做更改的日志记录。 SQL Server将日志记录写入事务日志。 它还将提交确认发送给用户。 更改后的页面仍在缓冲区缓存中
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.
我们可以使用DMV sys.dm_os_buffer_descriptors来检查实例中每个联机数据库的内存中的脏页。 我们可以使用is_modified列查看脏页。
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.
我们获取SQL Server实例中每个联机数据库的脏页数。
什么是预写日志记录？ (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).
一旦我们更改了缓冲区高速缓存中的干净页面，它就会生成包含修改细节的日志记录。 在将关联的脏页从缓冲区高速缓存刷新到磁盘（数据文件）之前，SQL Server将此日志记录写入事务日志中的磁盘。 此过程称为预写日志记录。 它确保了ACID（原子性-一致性-隔离性-耐久性）。
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).
在下图中，我们可以看到首先写入事务日志记录，然后将页面写入磁盘（数据文件）。 它可以确保在数据库恢复期间，如果发生任何问题并且服务器突然关闭，它将读取事务日志文件并准备恢复过程（UNDO，REDO）。
SQL Server CHECKPOINT和脏页概述 (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.
Checkpoint进程将所有脏页（在缓冲区高速缓存中可用）和事务日志记录写入磁盘。 它还将检查点信息记录在事务日志中。 它执行以下任务，如下图所示。
- 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) 它将所有脏页（自上一个检查点以来已修改的页）写入数据文件（MDF / NDF）
- It also writes the Checkpoint LSN to the database boot page 还将Checkpoint LSN写入数据库引导页面
SQL Server CHECKPOINTS的类型 (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.
它是SQL Server和默认（最常见）检查点中的后台进程。 我们在sp_configure命令中有一个恢复间隔服务器配置，您可以使用Show Advanced Option进行检查。
sp_configure 'show advanced options', 1
RECONFIGURE with override
go
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.
SQL Server不断检查日志记录的数量，一旦它估计日志记录的数量足以在按照恢复间隔配置选项指定的时间内处理，它就会发出Checkpoint命令。
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.
我们可以使用以下命令修改恢复间隔； 但是，只有当您是一名熟练的DBA并了解这样做的风险时，才可以使用它。
USE [master];
GO
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.
SQL Server还基于SQL Server中的一些关键事件发布内部SQL Server检查点。 这些事件如下。
- During database backup 在数据库备份期间
- Database Snapshots. It might be explicitly database snapshot or snapshot due to the DBCC CheckDB command 数据库快照。 由于DBCC CheckDB命令，它可能是数据库快照，也可能是快照
- SQL Server also performs Checkpoint for a clean shutdown of SQL Services SQL Server还执行检查点以彻底关闭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 在简单恢复模型中，如果日志文件已满70％
SQL Server controls these internal Checkpoint events. DBA cannot interfere in these operations.
SQL Server控制这些内部Checkpoint事件。 DBA不能干预这些操作。
手动检查点 (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.
我们还可以使用SQL Server CHECKPOINT命令在SQL Server中发出“手动检查点”。 该命令仅在当前数据库上下文下运行。
The command for the manual checkpoint is as below.
手动检查点的命令如下。
USE [master];
GO
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.
我们可以省略checkpoint_duration_in_Seconds参数。 SQL Server通过调整检查点持续时间来运行checkpoint命令，以最大程度地降低性能影响。
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.
如果我们以秒为单位指定检查点持续时间，则SQL Server尝试在请求的持续时间内执行检查点。 它可能会强制SQL Server使用其他资源并仅在指定时间内完成检查点操作。
For example, let’s say checkpoint takes typically 10 seconds to complete.
例如，假设检查点通常需要10秒钟才能完成。
Scenario 1: You execute the following SQL Server CHECKPOINT command to complete the CHECKPOINT in 5 seconds.
方案1：您执行以下SQL Server CHECKPOINT命令以在5秒钟内完成CHECKPOINT。
CHECKPOINT 5
It will cause SQL Server to assign more resources, and you might experience performance issues.
这将导致SQL Server分配更多资源，并且您可能会遇到性能问题。
Scenario 2: You execute the following checkpoint command to complete the CHECKPOINT in 5 seconds.
方案2：您执行以下检查点命令以在5秒钟内完成CHECKPOINT。
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.
与默认分配相比，它可能导致SQL Server对齐更少的资源，但是CHECKPOINT可能花费更长的时间，并且在任何不可预见的情况下可能会影响数据库的恢复。
间接检查点 (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.
间接检查点是从SQL Server 2012开始的新功能。它是SQL Server 2016的默认SQL Server检查点类型。在这种模式下，我们可以为特定数据库指定目标恢复时间。
We can specify the target recovery time using the following Alter database command.
我们可以使用下面的Alter database命令指定目标恢复时间。
ALTER DATABASE [Database name] SET TARGET_RECOVERY_TIME = Duration_InSeconds;
ALTER DATABASE [数据库名称] 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)
恢复间隔配置（使用sp_configure'恢复间隔）与特定于数据库的目标恢复时间（使用如上所示的alter database命令）之间存在关系。
| 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 |
ALTER DATABASE SET TARGET_RECOVERY_TIME | Sp_Configure'恢复间隔' | 使用的检查点类型 |
0 | 0 | 具有目标恢复间隔1的自动检查点 |
| 0 | > 0 | 自动检查点的目标恢复间隔等于使用'sp_configure'恢复间隔'选项指定的时间。 |
> 0 | 不适用 | 使用Target_recovery_time配置目标恢复时间的间接检查点 |
You can go through Database checkpoints – Enhancements in SQL Server 2016 to read about enhancements in SQL Server 2016 CHECKPOINT.
您可以遍历数据库检查点– SQL Server 2016中的增强功能，以了解有关SQL Server 2016 CHECKPOINT中的增强功能的信息。
如何在SQL Server中监视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.
我们可以使用未记录的系统函数fn_dblog来监视当前数据库中SQL Server CHECKPOINT事件。
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.
它提供了CHECKPOINT事件的详细信息，如下所示。
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.
您还可以使用跟踪标志3504将CHECKPOINT信息记录在错误日志中。 从SQL Server 2012开始，SQL Server在长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.
惰性写入器进程还会将脏页刷新到磁盘上。 SQL Server CHECKPOINT与惰性编写器进程之间有区别。
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.
CHECKPOINT不会从内存中删除脏页。 写入磁盘后的脏页被标记为“干净”，并保留在缓冲区高速缓存中。 它有助于SQL Server避免IO密集型任务来将页面从磁盘读取到内存。
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.
它可以删除干净的页面而无需任何额外的努力。 对于脏页，它需要先将这些页冲洗到磁盘上，然后再将其清除。 因此，您可能会发现懒惰的编写器删除的页数要比Checkpoint脏页要大。
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.
在健康的数据库系统中，您几乎看不到Lazy Writer的活动。 如果观察到Lazy Writer进程的持续使用，则表明它需要做很多工作，并且内存中的页面更少。 SQL Server必须频繁刷新页面，并且新查询需要首先从磁盘中获取内存中的页面。 在这种情况下，您会观察到很低的页面预期寿命值。 您应该分析内存需求，工作量并在需要时调整资源。
SQL Server CHECKPOINT和惰性编写器之间的区别 (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进程之间的区别。
| 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 |
检查点 | 懒惰的作家 |
SQL Server使用CHECKPOINT刷新磁盘中的脏页，以按照定义的阈值保持数据库恢复。 检查 | SQL Server使用惰性写入器进程刷新脏页和干净页，以在缓冲区高速缓存中保留可用空间以容纳新页 |
CHECKPOINT生成事务日志记录，并遵循预写日志记录过程 | 懒惰的编写者不在事务日志中进行任何输入 |
CHECKPOINT仅刷新脏页 | 它会冲洗脏页和干净页 |
我们可以通过设置适当的参数来手动执行CHECKPOINT或控制CHECKPOINT行为 | DBA无法控制惰性编写器进程 |
CHECKPOINT也取决于恢复模型 | Lazy Writer与数据库恢复模型没有任何关系 |
我们可以使用未记录的系统功能以及跟踪标志来监视CHECKPOINT事件。 从SQL Server 2012开始，它还会在错误日志中记录较长的CHECKPOINT条目 | 我们无法监视惰性编写器进程 |
我们可以在数据库启动页面中检查CHECKPOINT LSN | 我们无法检查懒惰作者进程 |
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.
您可以阅读本文， SQL Server内存性能指标–第5部分–了解惰性写入，空闲列表停顿数/秒和“内存授予中”以监视数据库性能指标。
您听说过渴望作家吗？ (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.
SQL Server将某些活动作为未记录的操作执行。 这些示例是：SELECT INTO，批量插入，WriteText和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.
Eager Writer进程同时处理页面创建和页面写入，因此查询在将所有页面写入磁盘之前不必等待完成整个批量处理。
结论 (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.
在本文中，我们探讨了内部过程，例如SQL Server CHECKPOINT，预写日志记录，惰性编写器和Eager Writer。 您应该注意这些过程。 如果您有任何意见或疑问，请随时将其留在下面的评论中。
1831
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
