Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
For more information, see Creating and Managing Storage for Memory-Optimized Objects.
| 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. |
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;