How is my Data written?
The data in memory-optimised tables is stored as free-form data rows that are linked through one or more in-memory indexes, in memory. There are no page structures for data rows, such as those used for disk-based tables. When the application is ready to commit the transaction, the In-Memory OLTP generates the log records for the transaction. The persistence of memory-optimised tables is done with a set of data and delta files using a background thread. The data and delta files are located in one or more containers (using the same mechanism used for FILESTREAM data). These containers are mapped to a new type of file group, called a memory-optimised file group.
Data is written to these files in a strictly sequential fashion, which minimizes disk latency for spinning media. You can use multiple containers on different disks to distribute the I/O activity. Data and delta files in multiple containers on different disks will increase recovery performance when data is read from the data and delta files on disk, into memory.
An application does not directly access data and delta files. All data reads and writes use in-memory data.
At this moment I don’t have any pending checkpoints to be written to Data and Delta files , so it is as good as a clean In-Memory Table
Forced checkpoint manually.
Lets insert couple of records,900 records inserted
As we know , system default checkpoint occurs when the log has growth up to 512 MB after the last checkpoint.Let check how much log has been generated from out last insert.
Since last checkpoint the log has grown less then 1 MB, no checkpoint will occurs as it does not qualify the 512 MB mark.So the data remains in the log.To check that we will see the use of undocumented function sys.fn_dblog_xtp which is similar to sys.fn_dblog but specifically for In-Memory objects.
So, we have done 900 inserts in the table which can been seen in the log.these transactions are still waiting to be written to the DETA File.
Issues a manual checkpoint to force the log records to flush out and be written on the DETA files.Below are set of events gets captured in the database log,
All the log records will be flushed out
Description
XTP chained record ver 3: log_Discriminator = 0x00000001, log_prevRec = 00000000:00000000:0000
XTP chained record ver 3: log_Discriminator = 0x00000003, log_prevRec = 00000000:00000000:0000
XTP complete checkpoint ver 4: { LSN = ;000004f9:00000040:0003;, previous checkpoint completion LSN = ;000004f9:00000068:0003;, close LSN = ;000004f9:000000c8:0005;, CkptTs = 0x0000016d, SerializeTs = 0x0000016f };, { CkptDir ==> GUIDs = { Rowset 83a190b
HkFsStgCreateNewFile;0x01
Operation CREATE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-000000f8-0002
InsertFSV;0x01
Field m_typeFlagBits
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-000000f8-0002;CrLSN 000004f9-000000f8-0002;OpLSN 000004f9-00000100-0009;OldFileSz 0;FileSz 0;Off 0;Sz 0;Flg 0007
HkFsStgCreateNewFile;0x01
Operation CREATE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000110-0002
InsertFSV;0x01
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000110-0002;CrLSN 000004f9-00000110-0002;OpLSN 000004f9-00000110-0008;OldFileSz 0;FileSz 0;Off 0;Sz 0;Flg 0007
HkFsStgCreateNewFile;0x01
Operation CREATE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000118-0004
InsertFSV;0x01
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000118-0004;CrLSN 000004f9-00000118-0004;OpLSN 000004f9-00000118-000a;OldFileSz 0;FileSz 0;Off 0;Sz 0;Flg 0007
HkFsStgCreateNewFile;0x01
Operation CREATE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000120-0002
InsertFSV;0x01
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000120-0002;CrLSN 000004f9-00000120-0002;OpLSN 000004f9-00000120-0008;OldFileSz 0;FileSz 0;Off 0;Sz 0;Flg 0007
Action 0 (HOBTCOUNT) on rowset 72057594038321152. Leaf page count: 2, Reserved page count: 4, Used page count: 4
Action 1 (ROWSETCOUNT) on rowset 72057594038321152. Row count: 6.
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 1, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 2, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 3, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 4, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 5, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 6, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 7, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 8, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 9, mod count: 1390
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594038321152. Column Id: 10, mod count: 1390
Action 0 (HOBTCOUNT) on rowset 72057594038386688. Leaf page count: 1, Reserved page count: 2, Used page count: 2
Action 1 (ROWSETCOUNT) on rowset 72057594038386688. Row count: 6.
Action 0 (HOBTCOUNT) on rowset 72057594060144640. Leaf page count: 2, Reserved page count: 3, Used page count: 3
Action 1 (ROWSETCOUNT) on rowset 72057594060144640. Row count: 30.
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594060144640. Column Id: 1, mod count: 478
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594060144640. Column Id: 2, mod count: 702
Action 2 (ROWSETCOLUMNCOUNT) on rowset 72057594060144640. Column Id: 3, mod count: 478
Action 0 (HOBTCOUNT) on rowset 72057594060210176. Leaf page count: 1, Reserved page count: 2, Used page count: 2
Action 1 (ROWSETCOUNT) on rowset 72057594060210176. Row count: 30.
File Id 65537; Old Header Len = 447; New HeaderLen = 447; Old Container Size = 303038464; New Container Size = 338690048
log_minRecoveryLsn 000004f9:00000128:0017;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000
XTP chained record ver 3: log_Discriminator = 0x00000001, log_prevRec = 00000000:00000000:0000
HkFsStgCreateNewFile;0x01
Operation CREATE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000170-0002
InsertFSV;0x01
XTP chained record ver 3: log_Discriminator = 0x00000000, log_prevRec = 00000000:00000000:0000
XTP chained record ver 3: log_Discriminator = 0x00000003, log_prevRec = 00000000:00000000:0000
XTP complete checkpoint ver 4: { LSN = ;000004f9:000000c8:0003;, previous checkpoint completion LSN = ;000004f9:000000f0:0002;, close LSN = ;000004f9:00000148:0005;, CkptTs = 0x00000173, SerializeTs = 0x00000176 };, { CkptDir ==> GUIDs = { Rowset 83a190b
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000170-0002;CrLSN 000004f9-00000170-0002;OpLSN 000004f9-00000178-0006;OldFileSz 0;FileSz 0;Off 0;Sz 0;Flg 0007
HkFsStgCreateNewFile;0x01
Operation CREATE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000188-0002
InsertFSV;0x01
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f9-00000188-0002;CrLSN 000004f9-00000188-0002;OpLSN 000004f9-00000188-0008;OldFileSz 0;FileSz 0;Off 0;Sz 0;Flg 0007
HkFsStgDeleteExistingFile;0x
GarbageCollector;0x
HkFsStgDeleteExistingFile;0x
HkFsStgDeleteExistingFile;0x
HkFsStgDeleteExistingFile;0x
HkFsStgDeleteExistingFile;0x
HkFsStgDeleteExistingFile;0x
HkFsStgCleanupStorageTable;0x
Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f7-00000038-0002
Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f7-00000050-0002
Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f8-000001b0-0002
Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f8-000001c8-0002
Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f8-00000100-0003
Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f8-00000108-0003
GarbageCollector;0x
Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f7-00000210-0002;FileSize 16777216
Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f7-00000218-0002;FileSize 1048576
Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f7-00000350-0002;FileSize 16777216
Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f7-00000360-0002;FileSize 1048576
Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f8-00000070-0002;FileSize 16777216
Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa52031cae3-31ca-4826-88b5-8966355c57d400004f8-00000088-0002;FileSize 1048576
Field m_typeFlagBits
select * from sys.fn_dblog_xtp(NULL, NULL) where [Transaction ID]='0000:0007ba6e'
