Common misconceptions about SQL Server checkpoint

The concept of checkpoint is not unfamiliar to most SQL Server development or DBA personnel. However, including myself, we all have more or less some misunderstandings about checkpoint, recently and Gao Wenjia classmate (thanks to the discussion of high school students) about the department conducted some discussion, sorted out a few misunderstandings.

1.CheckPoint instance level, not database level

Although the checkpoint time can be set at the instance level, the checkpoint process is based on the database granularity. From the role of checkpoint in redo and undo, checkpoint is designed to optimize IO and reduce recovery time, while recovery requires log support, so the log is a database-level concept, It is therefore possible to know that the checkpoint is in the database unit.

Let's do a simple experiment, set up two connections A and b,a and B to use different databases and modify data to produce dirty data, after a checkpoint, a connected database dirty pages are all written to disk, and B connection generated dirty pages still reside in buffer, Therefore, you can determine that checkpoint is a database level, not a server level.

Figure 1. Checkpoint is database level

2. Automatic checkpoint caused by log growth writes dirty pages of all databases to disk

As it turns out, this is also a mistake, and automatic checkpoint will simply write some dirty pages or log too many database dirty pages to disk. You can also do this by using the example in Figure 1.

3.CheckPoint writes only dirty data that has been submitted to disk

This is also wrong, regardless of whether the transaction is committed, the resulting dirty data will be written to disk by checkpoint. Examples can be shown in my blog post: Talk about checkpoint in the role of log in SQL Server.

4. If there is more than one database on an instance, the checkpoint is parallel

Error, through the 3502 tracking tag, checkpoint is serial, that is, a database checkpoint will continue to the next. As shown in Figure 2.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

Figure 2. Serial Checkpoint

We can note that checkpoint is using the same SPID.