Export (0) Print
Expand All

sys.dm_db_xtp_checkpoint_files (Transact-SQL)

SQL Server 2014

Displays information about checkpoint files, including file size, physical location and the transaction ID.

Note 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 the transaction log grows 512MB since the last checkpoint, or if you issue the CHECKPOINT command (CHECKPOINT (Transact-SQL)).

A memory-optimized file group internally uses filestream 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 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.

For more information, see Creating and Managing Storage for Memory-Optimized Objects.

Applies to: SQL Server (SQL Server 2014 through current version).

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 6.

file_type_desc

nvarchar(60)

The type of file: DATA_FILE, DELTA_FILE, or NULL if the state column is set to 6.

internal_storage_slot

int

The index of the file in the internal storage array. NULL if the state column is set to 2 or 3.

NULL if a checkpoint file pair has state 1 -- UNDER CONSTRUCTION.

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 4, 5, or 6.

file_size_used_in_bytes

bigint

Used size of the file that is used. NULL if the state column is set to 4, 5, or 6.

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 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 (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 2 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 may 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 1.

upper_bound_tsn

bigint

The upper bound of transactions contained in the file. Null if the state column is other than 1.

last_backup_page_count

int

Logical page count that is determined at last backup. Applies when the state column is set to 0, 1, or 2. NULL if page count 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

Null, unless the state column is 6.

Requires VIEW DATABASE STATE permission on the server.

You can estimate the storage used by in-memory tables as follows:

-- total storage used by in-memory tables
select sum (file_size_in_bytes)/(1024*1024) as file_size_in_MB
   from sys.dm_db_xtp_checkpoint_files 
   where internal_storage_slot is not NULL

You can estimate the free space in each file with the following query.

Notice the percent_full column. In-Memory OLTP uses a heuristic to identify the last transaction for the data file. Depending upon the number of rows changed by transaction, the percent full may vary. The filled space of a data file can also be affected if a checkpoint was done that caused this file to close. You may also see a data file with no rows. This can be caused by a manual checkpoint after deleting rows and before any rows were added.

select *,
str((convert 
(float, (file_size_used_in_bytes * (1 - convert (float, deleted_rows)/inserted_rows)))/file_size_in_bytes),
25, 2) as percent_full
from
( 
  select t.internal_storage_slot, file_size_in_bytes, file_size_used_in_bytes, 
  (case when inserted_row_count= 0 then 1
         when inserted_row_count > 0 then inserted_row_count end) as inserted_rows,
(select deleted_row_count 
 from sys.dm_db_xtp_checkpoint_files 
 where internal_storage_slot = t.internal_storage_slot and file_type=1) as deleted_rows
from sys.dm_db_xtp_checkpoint_files as t
where internal_storage_slot is not NULL and file_type=0) as t_t
order by internal_storage_slot

SQL Server allows up to 8,192 data and delta file pairs. To see the number of active data and delta file pairs, using the following query.

-- total number of data and delta file pairs
select count (*)
from sys.dm_db_xtp_checkpoint_files
where internal_storage_slot is not NULL and file_type = 0

To estimate the % of total storage used:

declare @deleted_row_count int; 
declare @inserted_row_count int;
declare @effective_row_percentage float

-- get the total deleted row counts by looking at active delta files
select @deleted_row_count = SUM (deleted_row_count)
from sys.dm_db_xtp_checkpoint_files 
where state = 2 and file_type = 1

-- get total inserted row count by looking at active data files
select @inserted_row_count = SUM (inserted_row_count)
from sys.dm_db_xtp_checkpoint_files 
where state = 2 and file_type = 0

-- get the effective % of active rows after accounting for the deleted rows
-- This number represents the potential space that can be freed up if deleted are removed from storage
select @effective_row_percentage =  (1 - convert (float, @deleted_row_count)/@inserted_row_count)

-- Compute the effective usage fill factor for the storage. 
-- Effective fill factor computes the effective free space in data files
-- on average after accounting for the deleted rows 
-- This should be >= 50% otherwise it is an indication that auto-merge is not keeping up
select 
str (convert (varchar(100), ((SUM (file_size_used_in_bytes)*@effective_row_percentage)/SUM (file_size_in_bytes)) *100 ),5, 2)
as [storage usage fill factor]
from sys.dm_db_xtp_checkpoint_files
where state = 2 and file_type = 0

Community Additions

ADD
Show:
© 2014 Microsoft