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.
|
|
SELECT checkpoint_file_id, checkpoint_pair_file_id, file_type_desc, internal_storage_slot, relative_file_path FROM sys.dm_db_xtp_checkpoint_files WHERE checkpoint_file_id IS NOT NULL AND internal_storage_slot IS NOT NULL AND state_desc = 'ACTIVE' ORDER BY internal_storage_slot |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
USE [master] GO CREATE DATABASE IMODB2 ON PRIMARY (NAME = [IMODB2_data], FILENAME = 'D:\Workload\Databases\SQL2014\IMODB2_data.mdf', SIZE = 500MB), FILEGROUP [IMODB2_fg] CONTAINS MEMORY_OPTIMIZED_DATA (NAME = [IMODB2_mod], FILENAME = 'D:\Workload\Databases\SQL2014\IMODB2_mod') LOG ON (NAME = [IMODB2_log], FILENAME = 'D:\Workload\Databases\SQL2014\IMODB2_log.ldf', SIZE = 500MB) COLLATE Turkish_CI_AS GO USE [IMODB2] GO CREATE TABLE imo_Address ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), Address_Line1 VARCHAR(100) NOT NULL, Address_Line2 VARCHAR(100) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON) GO |
Now, we can insert 1000 rows to Memory Optimized Table.
|
|
DECLARE @AddNew INT SET @AddNew = 0 WHILE @AddNew <= 1000 BEGIN INSERT INTO imo_Address VALUES (@AddNew, 'SQL Server Onculeri', 'www.sqlserveronculeri.com') SET @AddNew = @AddNew + 1 END |
After inserting the rows, let’s check the active part of the Transaction Log with an fn_dblog_xtp() which is undocumented function
|
|
SELECT [Current LSN], [Transaction ID], [operation_desc], OBJECT_NAME(table_id) AS TableName FROM fn_dblog_xtp(NULL, NULL) WHERE OBJECT_NAME(table_id) = 'imo_Address' |
Now, it is time to examine Checkpoint Files.
|
|
SELECT checkpoint_file_id, checkpoint_pair_file_id, file_type_desc, internal_storage_slot, Files = REVERSE(SUBSTRING(REVERSE(relative_file_path),0,CHARINDEX('\',REVERSE(relative_file_path)))) FROM sys.dm_db_xtp_checkpoint_files WHERE checkpoint_file_id IS NOT NULL AND internal_storage_slot IS NOT NULL AND state_desc = 'ACTIVE' ORDER BY internal_storage_slot |
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.
|
|
SELECT internal_storage_slot, checkpoint_file_id, checkpoint_pair_file_id, file_type_desc, file_size_in_bytes / 1024 / 1024 AS file_size_in_mb, file_size_used_in_bytes / 1024.0 / 1024.0 AS file_size_used_in_mb, inserted_row_count FROM sys.dm_db_xtp_checkpoint_files WHERE checkpoint_file_id IS NOT NULL AND internal_storage_slot IS NOT NULL AND state_desc = 'ACTIVE' ORDER BY internal_storage_slot |
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.
|
|
DELETE TOP(500) FROM dbo.imo_Address GO CHECKPOINT |
As you can see now we have 3 files and the deleted data ID’s which were written to the Delta File.
Post Details
