In-Memory OLTP –Part 9

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.

image

Lets insert couple of records,900 records inserted

 

image

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.

image

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.

image

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

image

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'


image


image

					


	




	



				
	
			



	
	
	
		

		
Leave a Reply 
Your email address will not be published.