Where is my data? Cont…
So, As we know the data is saved in checkpoint (data)files , In this post we will go over monitoring the checkpoint files and how data is really saved.
The checkpoint files were created at the location which was provided at the time of adding MEMORY_OPTIMIZED DATA file.
Before we start inserting the data, let check if there are active files.
select df.name [FileName],df.physical_name,/*cf.checkpoint_file_id,cf.relative_file_path,*/cf.file_type, cf.file_type_desc,/*cf.checkpoint_pair_file_id,*/cf.file_size_in_bytes,cf.file_size_used_in_bytes, cf.inserted_row_count,cf.deleted_row_count,cf.drop_table_deleted_row_count,convert(nvarchar(500), cf.last_checkpoint_recovery_lsn) last_checkpoint_recovery_lsn,cf.tombstone_operation_lsn from sys.dm_db_xtp_checkpoint_files cf join sys.database_files df on cf.container_id=df.file_id and cf.state= 1
Insert data into In-Memory Table. Query the sys.dm_db_xtp_checkpoint dmv Meta data and it’s Mapping to physical Checkpoint files on the disk:
Column inserted_row_count,deleted_row_count,drop_table_deleted_row_count are of interest to understand how basically data is inserted or deleted in In-Memory Table
inserted_row_count : No of rows inserted or updated
deleted_row_count : No of rows deleted
drop_table_deleted_row_count : How many rows were deleted when the In-Memory Table is dropped.
I have updated a million rows, Updates are treated as insert.inserted_row_count is updated with the number of records updated.As this is done in a single transaction all the records will be written in a single DATA file (A data file contains inserted rows while a delta file contains deleted rows. Each data file is pre-allocated to a size of 128 MBs but can get larger if there is a long running transaction or when a manual merge forces the resultant target file to be larger than 128MB.)