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

In-Memory OLTP –Part 10

Merging of Checkpoint Files

The set of files involved in a checkpoint grows with each check-point. However the active content of a data file decreases as more and more of its versions are marked as deleted in the corresponding delta file. Since the recovery process will read the contents of all data and delta files in the checkpoint, performance of crash recovery degrades as the relevant number of rows in each data file decreases

The checkpoint system hands this through an automatic process called merge. SQL Server 2014 implements the following merge policy:

· A merge is scheduled if 2 or more consecutive CFPs can be consolidated, after accounting for deleted rows, such that the resultant rows can fit into 1 CFP of ideal size. The ideal size of CFP is determined as follows

  • For machines < 16GB. The data file is 16MB and delta file is 1MB
  • For machines > 16GB, the data file is 128MB and delta file is 8MB

· A single CFP can be self-merged the data file exceeds 256 MB and over half of the rows are deleted. A data file can grow larger than 128MB if, for example, a single transaction or multiple concurrent transactions insert/update large amount of data forcing the data file to grow beyond its ideal size because a transaction cannot span multiple CFPs.

There are 2 ways to merge CPF files.

  • Automatic Merge

Will cover this later.

  • Manual Merge (sys.sp_xtp_merge_checkpoint_files)
In most cases, the automatic merging of checkpoint files will be sufficient to keep the number of files to a manageable number. However, in rare situations, or for testing purposes, you might want to use a manual merge.

To manual merge find the min and max tsn, use sys.sp_xtp_merge_checkpoint_files with database name,min and max tsn

select min(lower_bound_tsn),max(upper_bound_tsn) from sys.dm_db_xtp_checkpoint_files cf

image

 

exec sys.sp_xtp_merge_checkpoint_files adventureworks2014, 379, 398

Once the Request is placed , check the status on the merge request using sys.dm_db_xtp_merge_requests

select * from sys.dm_db_xtp_merge_requests

image

 

  • Requested means that a merge request exists.

  • Pending means that the merge is being processing.(Request can be in pending state,if a checkpoint is required.)

  • Installed means that the merge is complete.

  • Abandoned means that the merge could not complete, perhaps due to lack of storage.

 

select file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='PRECREATED'
order by container_id, file_type_desc, upper_bound_tsn

There are 16 files under ‘PRECREATED’ state.The demo was run on machine with 4 logical processors with 16GB memory

  • PRECREATED – A small set of Checkpoint File Pairs (CFPs) are kept pre-allocated to minimize or eliminate any waits to allocate new files as transactions are being executed. These are full sized with data file size of 128MB and delta file size of 8 MB, but contain no data. The number of CFPs is computed as the number of logical processors or schedulers, with a minimum of 8. This is a fixed storage overhead in databases with memory-optimized tables.

select file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='PRECREATED'
order by container_id, file_type_desc, upper_bound_tsn

 

image

 

There are 3 data files marked ‘UNDER CONSTRUCTION’ as checkpoint has not been taken but these files together store 2700000 rows that was inserted. If database is restarted, the data rows will loaded using transaction log and NOT from these files as they are not part of a durable checkpoint

select file_type_desc, state_desc, relative_file_path,internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='UNDER CONSTRUCTION'
order by container_id, file_type_desc, upper_bound_tsn

 

image

 

image

 

You will notice that there will be 3 active files as soon as  the checkpoint occur and data will be written to these file

  • ACTIVE – These contain the inserted/deleted rows from previous closed checkpoints. These CFPs contain all required inserted/deleted rows required before applying the active part of the transaction log at the database restart. We expect that size of these CFPs to be approximately 2x of the in-memory size of memory-optimized tables, assuming the merge operation is keeping up with the transactional workload.

select file_type_desc, state_desc,relative_file_path, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count 
from sys.dm_db_xtp_checkpoint_files where state_desc='Active'
order by container_id, file_type_desc, upper_bound_tsn

 

image

Now, execute a manual checkpoint by executing the following command and see the state transition in the CFPs. You will note that UNDER CONSTRUCTION CFPs are now marked ACTIVE as they are part of a durable checkpoint that we have just completed. The manual checkpoint closed the data file with internal_storage_slot . These data file can potentially be merged in future as it qualifies under the merge policy.

image

 

Let’s issue the CHECKPOINT

 

CHECKPOINT

select file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,
inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count
from sys.dm_db_xtp_checkpoint_files where state = 2 order by container_id, file_type_desc, upper_bound_tsn

 

image

Let’s force a manual merge by executing the below command, the Merge will merge the active files

select min(lower_bound_tsn),max(upper_bound_tsn) from sys.dm_db_xtp_checkpoint_files

image

exec sys.sp_xtp_merge_checkpoint_files adventureworks2014, 392, 415

Check the Merge Request

select * from sys.dm_db_xtp_merge_requests

 

image

 

As we have forced the Merge  you will see the file marked [MERGE TARGET].

 

image

 

The Merge request will remain in Pending state if any of the file is pending for Backup or for a checkpoint, In this case a backup is required to install the Merge Request

 

image

 

Lets do a diff backup, as the backup kicks in Merge Request will  be installed, and CPF files will be marked as [MERGED SOURCE]

image 

image

 

Here is the output of the DMVs. It shows that the MERGE TARGET is now changed to ACTIVE state and all the CFPs that were used as source of the MERGE are now marked as MERGED SOURCE.

image

 

***state REQUIRED FOR BACKUP/HA meaning that these CFPs can’t be garbage collected as they are needed for operational correctness of the database with memory-optimized table

 

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: