sys.dm_db_xtp_checkpoint_files (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Displays information about In-Memory OLTP checkpoint files, including file size, physical location and the transaction ID.
Note
For the current checkpoint that has not closed, the state column of sys.dm_db_xtp_checkpoint_files will be UNDER CONSTRUCTION for new files. A checkpoint closes automatically when there is sufficient transaction log growth since the last checkpoint, or if you issue the CHECKPOINT command. For more information, see CHECKPOINT (Transact-SQL).
A memory-optimized file group internally uses append-only files to store inserted and deleted rows for in-memory tables. There are two types of files. A data file contains inserted rows while a delta file contains references to deleted rows.
SQL Server 2014 (12.x) is substantially different from more recent versions and is discussed in SQL Server 2014.
For more information, see Creating and Managing Storage for Memory-Optimized Objects.
SQL Server 2016 (13.x) and later
The following table describes the columns for sys.dm_db_xtp_checkpoint_files, beginning with SQL Server 2016 (13.x).
| Column name | Type | Description |
|---|---|---|
| container_id | int | The ID of the container (represented as a file with type FILESTREAM in sys.database_files) that the data or delta file is part of. Joins with file_id in sys.database_files (Transact-SQL). |
| container_guid | uniqueidentifier | GUID of the Container, which the root, data or delta file is part of. Joins with file_guid in the sys.database_files table. |
| checkpoint_file_id | uniqueidentifier | GUID of the checkpoint file. |
| relative_file_path | nvarchar(256) | Path of the file relative to container it is mapped to. |
| file_type | smallint | -1 for FREE 0 for DATA file. 1 for DELTA file. 2 for ROOT file 3 for LARGE DATA file |
| file_type_desc | nvarchar(60) | FREE- All files maintained as FREE are available for allocation. Free files can vary in size depending on anticipated needs by the system. The maximum size is 1 GB. DATA - Data files contain rows that have been inserted into memory-optimized tables. DELTA - Delta files contain references to rows in data files that have been deleted. ROOT - Root files contain system metadata for memory-optimized and natively compiled objects. LARGE DATA - Large data files contain values inserted in (n)varchar(max) and varbinary(max) columns, as well as the column segments that are part of columnstore indexes on memory-optimized tables. |
| internal_storage_slot | int | The index of the file in the internal storage array. NULL for ROOT or for state other than 1. |
| checkpoint_pair_file_id | uniqueidentifier | Corresponding DATA or DELTA file. NULL for ROOT. |
| file_size_in_bytes | bigint | Size of the file on the disk. |
| file_size_used_in_bytes | bigint | For checkpoint file pairs that are still being populated, this column will be updated after the next checkpoint. |
| logical_row_count | bigint | For Data, number of rows inserted. For Delta, number of rows deleted after accounting for drop table. For Root, NULL. |
| state | smallint | 0 - PRECREATED 1 - UNDER CONSTRUCTION 2 - ACTIVE 3 - MERGE TARGET 8 - WAITING FOR LOG TRUNCATION |
| state_desc | nvarchar(60) | PRECREATED - A number of checkpoint files are preallocated to minimize or eliminate any waits to allocate new files as transactions are being executed. These files can vary in size, and are created depending on the estimated needs of the workload. They contain no data. This is a storage overhead in databases with a MEMORY_OPTIMIZED_DATA filegroup. UNDER CONSTRUCTION - These checkpoint files are under construction, meaning they are being populated based on the log records generated by the database, and are not yet part of a checkpoint. ACTIVE - These contain the inserted/deleted rows from previous closed checkpoints. They contain the contents of the tables that area read into memory before applying the active part of the transaction log at the database restart. We expect that size of these checkpoint files to be approximately 2x of the in-memory size of memory-optimized tables, assuming the merge operation is keeping up with the transactional workload. MERGE TARGET - The target of merge operations - these checkpoint files store the consolidated data rows from the source files that were identified by the merge policy. Once the merge is installed, the MERGE TARGET transitions into ACTIVE state. WAITING FOR LOG TRUNCATION - Once the merge has been installed and the MERGE TARGET CFP is part of durable checkpoint, the merge source checkpoint files transition into this state. Files in this state are needed for operational correctness of the database with memory-optimized table. For example, to recover from a durable checkpoint to go back in time. |
| lower_bound_tsn | bigint | Lower bound of the transaction in the file; NULL if state not in (1, 3). |
| upper_bound_tsn | bigint | Upper bound of the transaction in the file; NULL if state not in (1, 3). |
| begin_checkpoint_id | bigint | ID of the begin checkpoint. |
| end_checkpoint_id | bigint | ID of the end checkpoint. |
| last_updated_checkpoint_id | bigint | ID of the last checkpoint that updated this file. |
| encryption_status | smallint | 0, 1, 2 |
| encryption_status_desc | nvarchar(60) | 0 => UNENCRYPTED 1 => ENCRYPTED WITH KEY 1 2 => ENCRYPTED WITH KEY 2. Valid only for active files. |
SQL Server 2014 (12.x)
The following table describes the columns for sys.dm_db_xtp_checkpoint_files, for SQL Server 2014 (12.x).
| Column name | Type | Description |
|---|---|---|
| container_id | int | The ID of the container (represented as a file with type FILESTREAM in sys.database_files) that the data or delta file is part of. Joins with file_id in sys.database_files (Transact-SQL). |
| container_guid | uniqueidentifier | The GUID of the container that the data or delta file is part of. |
| checkpoint_file_id | GUID | ID of the data or delta file. |
| relative_file_path | nvarchar(256) | Path to the data or delta file, relative to the location of the container. |
| file_type | tinyint | 0 for data file. 1 for delta file. NULL if the state column is set to 7. |
| file_type_desc | nvarchar(60) | The type of file: DATA_FILE, DELTA_FILE, or NULL if the state column is set to 7. |
| internal_storage_slot | int | The index of the file in the internal storage array. NULL if the state column is not 2 or 3. |
| checkpoint_pair_file_id | uniqueidentifier | The corresponding data or delta file. |
| file_size_in_bytes | bigint | Size of the file that is used. NULL if the state column is set to 5, 6, or 7. |
| file_size_used_in_bytes | bigint | Used size of the file that is used. NULL if the state column is set to 5, 6, or 7.For checkpoint file pairs that are still being populated, this column will be updated after the next checkpoint. |
| inserted_row_count | bigint | Number of rows in the data file. |
| deleted_row_count | bigint | Number of deleted rows in the delta file. |
| drop_table_deleted_row_count | bigint | The number of rows in the data files affected by a drop table. Applies to data files when the state column equals 1. Shows deleted row counts from dropped table(s). The drop_table_deleted_row_count statistics are compiled after the memory garbage collection of the rows from dropped table(s) is complete and a checkpoint is taken. If you restart SQL Server before the drop tables statistics are reflected in this column, the statistics will be updated as part of recovery. The recovery process does not load rows from dropped tables. Statistics for dropped tables are compiled during the load phase and reported in this column when recovery completes. |
| state | int | 0 - PRECREATED 1 - UNDER CONSTRUCTION 2 - ACTIVE 3 - MERGE TARGET 4 - MERGED SOURCE 5 - REQUIRED FOR BACKUP/HA 6 - IN TRANSITION TO TOMBSTONE 7 - TOMBSTONE |
| state_desc | nvarchar(60) | PRECREATED - A small set of data and delta file pairs, also known as checkpoint file pairs (CFPs) are kept preallocated to minimize or eliminate any waits to allocate new files as transactions are being executed. They are created with a data file size of 128 MB 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 (one per core, no maximum) with a minimum of 8. This is a fixed storage overhead in databases with memory-optimized tables. UNDER CONSTRUCTION - Set of CFPs that store newly inserted and possibly deleted data rows since the last checkpoint. ACTIVE - These contain the inserted and deleted rows from previous closed checkpoints. These CFPs contain all required inserted and deleted rows required before applying the active part of the transaction log at the database restart. The size of these CFPs will be approximately two times the in-memory size of memory-optimized tables, assuming the merge operation is current with the transactional workload. MERGE TARGET - The CFP stores the consolidated data rows from the CFP(s) that were identified by the merge policy. Once the merge is installed, the MERGE TARGET transitions into ACTIVE state. MERGED SOURCE - Once the merge operation is installed, the source CFPs are marked as MERGED SOURCE. Note, the merge policy evaluator might identify multiple merges but a CFP can only participate in one merge operation. REQUIRED FOR BACKUP/HA - Once the merge has been installed and the MERGE TARGET CFP is part of durable checkpoint, the merge source CFPs transition into this state. CFPs in this state are needed for operational correctness of the database with memory-optimized table. For example, to recover from a durable checkpoint to go back in time. A CFP can be marked for garbage collection once the log truncation point moves beyond its transaction range. IN TRANSITION TO TOMBSTONE - These CFPs are not needed by the In-Memory OLTP engine and can they can be garbage collected. This state indicates that these CFPs are waiting for the background thread to transition them to the next state, which is TOMBSTONE. TOMBSTONE - These CFPs are waiting to be garbage collected by the filestream garbage collector. (sp_filestream_force_garbage_collection (Transact-SQL)) |
| lower_bound_tsn | bigint | The lower bound of transactions contained in the file. NULL if the state column is other than 2, 3, or 4. |
| upper_bound_tsn | bigint | The upper bound of transactions contained in the file. NULL if the state column is other than 2, 3, or 4. |
| last_backup_page_count | int | Logical page count that is determined at last backup. Applies when the state column is set to 2, 3, 4, or 5. NULL if the page count is not known. |
| delta_watermark_tsn | int | The transaction of the last checkpoint that wrote to this delta file. This is the watermark for the delta file. |
| last_checkpoint_recovery_lsn | nvarchar(23) | Recovery log sequence number of the last checkpoint that still needs the file. |
| tombstone_operation_lsn | nvarchar(23) | The file will be deleted once the tombstone_operation_lsn falls behind the log truncation log sequence number. |
| logical_deletion_log_block_id | bigint | Applies only to state 5. |
Permissions
Requires VIEW DATABASE STATE permission on the database.
Permissions for SQL Server 2022 and later
Requires VIEW DATABASE PERFORMANCE STATE permission on the database.
Use Cases
You can estimate the total storage used by In-Memory OLTP as follows:
-- total storage used by In-Memory OLTP
SELECT SUM (file_size_in_bytes)/(1024*1024) as file_size_in_MB
FROM sys.dm_db_xtp_checkpoint_files;
To see a breakdown of storage utilization by state and file type run the following query:
SELECT state_desc
, file_type_desc
, COUNT(*) AS [count]
, SUM(file_size_in_bytes) / 1024 / 1024 AS [on-disk size MB]
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY state, state_desc, file_type, file_type_desc
ORDER BY state, file_type;