Home > In-MemoryOLTP > In-Memory OLTP –Part 9

In-Memory OLTP –Part 9

How is my Data written?

 

The data in memory-optimized 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-optimized 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 filegroup, called a memory-optimized filegroup.

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-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f9-000000f8-0002
InsertFSV;0x01

Field m_typeFlagBits
Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f9-00000110-0002
InsertFSV;0x01

Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f9-00000118-0004
InsertFSV;0x01

Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f9-00000120-0002
InsertFSV;0x01

Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f9-00000188-0002
InsertFSV;0x01

Operation CLOSE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-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-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f7-00000038-0002

Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f7-00000050-0002

Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f8-000001b0-0002

Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f8-000001c8-0002

Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f8-00000100-0003

Operation DELETE;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f8-00000108-0003

GarbageCollector;0x

Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f7-00000210-0002;FileSize 16777216

Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f7-00000218-0002;FileSize 1048576

Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f7-00000350-0002;FileSize 16777216

Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f7-00000360-0002;FileSize 1048576

Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-31ca-4826-88b5-8966355c57d400004f8-00000070-0002;FileSize 16777216

Operation GARBAGE COLLECTED;File Id 65537;Name 83a190bd-22eb-4014-90f3-119be1bd3aa5\2031cae3-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

Categories: In-MemoryOLTP
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: