In-Memory OLTP: Checkpoint, Data & Delta Files

In earlier versions of SQL Server, CHECKPOINT known as a process which writes dirty pages (modified pages) and Transaction Log information from memory to disk. Also it is similar to Memory Optimized Tables. If a Memory Optimized Table created with durability (SCHEMA_AND_DATA) option, the engine writes data to the Transaction Log when it is committed. In the process of CHECKPOINT fires, it moves the data from the Transaction Log to the Data and Delta files in the Checkpoint Container. During crash recovery or system restart, In-Memory OLTP engine requests CHECKPOINT to load Data & Delta files and active part of the Transaction Log to bring Memory Optimized Table to the current point-in-time. This is the exact explanation of how Memory Optimized Tables perform durability.

CHECKPOINT files (Data & Delta Files) are created into MEMORY_OPTIMIZED_DATA filegroup which is CHECKPOINT container. Data and Delta Files are only accessed in the events of crash recovery, restore operation, offline CHECKPOINT thread and merge operation.


Data Files Delta Files
Data Files size is approximately 128MB and contain only inserted rows. The rows are only appended to the data file while exploiting the sequential IO. Once the data file is full, the rows inserted by new transactions are stored in another data file. Delta Files track the deleted rows ID’s. Delta Files are accessed sequentially like Data Files and size of these are not constant. When a row is deleted, the row is not removed from the data file but a reference to the row is appended to the delta file.


Let’s have a look at to these files.










In the example, only one pair of files are highlighted. So, let’s try to insert some rows to check what happens to Data Files (Data File contains only inserted rows). Firstly, create a clean database that contains Memory Optimized Table.

Now, we can insert 1000 rows to Memory Optimized Table.

After inserting the rows, let’s check the active part of the Transaction Log with an fn_dblog_xtp() which is undocumented function







Now, it is time to examine Checkpoint Files.

When you execute the code shown as above, you will not get any results because of the CHECKPOINT. Therefore, we need to trigger CHECKPOINT to move committed data transactions to Data Files. After executing CHECKPOINT, send the same query and check the results one more time.



Check the inserted rows on Data Files.



As it is stated at the beginning of this article, Data Files only accept inserted rows. So, on the screenshot nothing inserted to Delta File! Now, try to delete some rows.




As you can see now we have 3 files and the deleted data ID’s which were written to the Delta File.

Posted In:
Post Details

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.