SQL Server 2016内存优化表–检查点操作

The SQL Server Database Engine stores data changes in the buffer pool, in memory, before applying it to the database files, for I/O performance reasons. After that, a special kind of background process, called Checkpoint, will write all of these not reflected pages, also known as Dirty Pages, to the database data and log files periodically.

由于I / O性能的原因，在将更改应用于数据库文件之前，SQL Server数据库引擎会将数据更改存储在缓冲池的内存中。 之后，一种称为Checkpoint的特殊后台进程会将所有这些未反映的页面（也称为“ 脏页面”）定期写入数据库数据和日志文件。

When a checkpoint is performed, the data will be marked by a recovery point, and the information about these recovery points will be saved to the database boot page, in order for it to be used in the case of database shutdown or crash, to roll forward all committed transactions that are written to the database transaction log file and not reflected to the database data files. This mark will be also used to rollback any data changes associated with uncommitted transaction, by reversing the operation written in the transaction log file. The purpose of the checkpoint process is to reduce the time required to recover the database in the event of crash, by writing the pages in memory to the data files, so that the SQL Engine will not apply these changes in the event of failure. This is how checkpoints help to guarantee the database consistency.

执行检查点时，数据将由恢复点标记，并且有关这些恢复点的信息将保存到数据库启动页面，以便在数据库关闭或崩溃时使用它转发所有已写入数据库事务日志文件但未反映到数据库数据文件的已提交事务。 通过反转事务日志文件中写入的操作，此标记还将用于回滚与未提交的事务相关的任何数据更改。 检查点过程的目的是通过将内存中的页面写到数据文件中来减少崩溃时恢复数据库所需的时间，以便SQL Engine在失败时不会应用这些更改。 这就是检查点如何帮助确保数据库一致性的方式。

There are four types of Checkpoints that the SQL Server Database Engine supports:

SQL Server数据库引擎支持四种类型的检查点：

• Internal
  内部
• Manual
  手册
• Automatic
  自动
• and Indirect
  和间接

For more information about these types, you can check out the earlier article, Database checkpoints – Enhancements in SQL Server 2016.

有关这些类型的更多信息，您可以查看前面的文章“ 数据库检查点– SQL Server 2016中的增强功能” 。

Within the scope of this article, we are interested in two types of the checkpoints for Memory-Optimized tables, the Manual Checkpoint that is performed by running the CHECKPOINT command and the Automatic Checkpoint that is performed automatically by the SQL Server Database Engine when meeting specific conditions.

在本文的范围内，我们对内存优化表的两种类型的检查点感兴趣，一种是通过运行CHECKPOINT命令执行的手动检查点 ，另一种是在满足特定条件时由SQL Server数据库引擎自动执行的自动检查点条件。

For disk-based tables, the checkpoint will flush all dirty pages to the database files and truncate the log for a database in simple recovery model. A log backup is required for a full recovery model database to truncate the transaction log. An automatic checkpoint is triggered based on the recovery interval configuration option value.

对于基于磁盘的表，检查点会将所有脏页刷新到数据库文件，并在简单恢复模型中截断数据库的日志。 完整备份模型数据库需要日志备份来截断事务日志。 根据恢复间隔配置选项值触发自动检查点。

A durable Memory-Optimized table with SCHEMA_AND_DATA durability records the transactions in the transaction log file for recovery purposes in case of failure or crash. These transactions are written to the transaction log only when the transaction is committed, different from the disk-based tables that use Write-Ahead Logging protocol to flush the dirty pages.

具有持久性SCHEMA_AND_DATA的持久性内存优化表将事务记录在事务日志文件中，以便在发生故障或崩溃时进行恢复。 这些事务仅在提交事务后才写入事务日志，这与使用预写日志记录协议刷新脏页的基于磁盘的表不同。

For Memory-Optimized tables, the checkpoint is performed by a background worker thread in the In-Memory OLTP Engine, different from the checkpoint threads for the disk-based tables. The Memory-Optimized table checkpoint flushes the data streams that contains all new inserted data to the data files and the flushes the delta stream that contains all deleted data to the delta files. These data and delta files, called the Checkpoint File Pairs (CFPs), are written sequentially opposite to the database data files that are written randomly. Remember that the UPDATE operations are considered as DELETE then INSERT operations.

对于内存优化表，检查点由内存中OLTP引擎中的后台工作线程执行，这与基于磁盘的表的检查点线程不同。 内存优化表检查点将包含所有新插入数据的数据流刷新到数据文件，并将包含所有已删除数据的增量流刷新到增量文件。 这些数据和增量文件（称为检查点文件对 （CFP））的写入顺序与随机写入的数据库数据文件相反。 请记住，UPDATE操作被认为是DELETE，然后是INSERT操作。

The Memory-Optimized table automatic checkpoint will be triggered when the database transaction log file becomes bigger than 1.5 GB since the last checkpoint, including the transaction log records for both the Memory-Optimized and disk-based tables. The Memory-Optimized tables checkpoint occurs periodically in order to advance the active part of the transaction log, allowing the tables to be recovered to the last successful checkpoint and applying the last active portion of the transaction log to complete the recovery process.

当数据库事务日志文件自上一个检查点以来大于1.5 GB时，将触发内存优化表自动检查点，包括针对内存优化表和基于磁盘的表的事务日志记录。 内存优化表检查点定期发生，以使事务日志的活动部分前进，从而允许将表恢复到最后一个成功的检查点，并应用事务日志的最后活动部分来完成恢复过程。

There is a special case in SQL Server 2016 in which the checkpoint characteristics for Memory-Optimized tables differs, called the Large Checkpoint, that is enabled on large machines with 16 or more logical processors, 128GB or greater memory or the ones that is capable of greater than 200MB/sec I/O measured for the IO subsystem of that database. The automatic large checkpoint is triggered only when 12GB of the transaction log file is filled up since the last checkpoint. The purpose of the large checkpoints is to ensure that