Hello Geeks and welcome to the Day 67 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Yesterday I have covered how to check the transaction stats using sys.dm_xtp_transaction_stats. Today I will cover sys.dm_db_xtp_checkpoint_files. Before showing how to use this DMV I will give an overview of how data is saved in In-Memory optimized tables.
The In-Memory tables consists of two types of files as checkpoint file pairs. The data file is used to save the inserted records. The delta file includes the deleted rows. Over time using garbage collection principle data files or delta files are merged. This will gain time when saving durable tables to disk. You can see the whole concept of how it works in my presentation deck.
So sys.dm_db_xtp_checkpoint_files lists down each file with details. Let us see a sample output and I will explain few important columns from the DMV.
SELECT file_type_desc, internal_storage_slot, file_size_in_bytes, file_size_used_in_bytes, inserted_row_count, deleted_row_count, drop_table_deleted_row_count, state_desc, lower_bound_tsn, upper_bound_tsn, delta_watermark_tsn FROM sys.dm_db_xtp_checkpoint_files
From the output if you observe, you will see 5000 rows in most of data and delta files. That is because I had run update on 5000 rows. That will be considered as 5000 deletes and inserts. Most of the columns in sys.dm_db_xtp_checkpoint_files are self-explanatory. I will explain them briefly. There are two columns which needs to be put in detail.
file_type_desc – Data or Delta
internal_storage_slot – In index storage array this is the index of the file.
inserted_row_count – the number of rows inserted in this data file.
deleted_row_count – the number of rows deleted in this delta file.
drop_table_deleted_row_count – row count of a dropped table after the checkpoint occurs. It is not updated till checkpoint occurs and garbage collection is completed.
state_desc – This explains the state of the file. Possible values are as below.
PRECREATED – These are allocated when In-Memory is enabled. This is created on similar principals of tempdb. You will have equal number of data and delta files equal to number of cores. Each pair of data and delta files is called Checkpoint File Pairs (CFP). The minimum is 8 files each. This will save time in making new allocations during transactions.
UNDER CONSTRUCTION – These are CFPs which hold new inserted or deleted rows after last checkpoint.
ACTIVE – This contains the rows from last closed checkpoint. This contains the rows which are needed when applying active part of transaction log at database restart.
MERGE_TARGET – This is marked during the merge operation. Once merge is complete they turn to ACTIVE.
MERGE_SOURCE – This is the source to which the target will be merged.
REQUIRED FOR BACKUP/HA – Once merge is complete and the table is durable, the source is marked to this state. This allows database consistency with backups.
IN TRANSITION TO TOMBSTONE – These are CFPs which are no longer needed and can be garbage collected.
TOMBSTONE – These are marked for garbage collection and waiting for filestream garbage collector.
lower_bound_tsn – The lower bound of the transaction sequence number. Used for merging the files.
upper_bound_tsn – The upper bound of the transaction sequence number. Used for merging the files.
delta_watermark_tsn – It is like an lsn for the delta file. The last transaction which has written to the delta file.
To understand the DMVs related to In-Memory you need to be familiar with the architecture of In-Memory OLTP. Today I have shown using sys.dm_db_xtp_checkpoint_files. Tomorrow I will be covering another In-Memory optimized tables related DMV. So stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
