SQL Server persists data from durable memory-optimized tables in checkpoint file pairs. This appendix demonstrates how to analyze their states using the sys.db_dm_xtp_checkpoint_files view and shows the state transitions through the CFP lifetime.

Sys.db_dm_xtp_checkpoint_files View

The sys.db_dm_xtp_checkpoint_files view provides information about database checkpoint files, including their state, size, and physical location. We will use this view extensively in this appendix. Let’s look at the most important columns.

• The container_id and container_guid columns provide information about the FILESTREAM container to which the checkpoint file belongs. Container_id corresponds to the file_id column in the sys.database_files view.
• checkpoint_file_id is a GUID that represents the ID of the file.
• checkpoint_pair_file_id is the ID of the second, data or delta, file in the pair.
• relative_file_path shows the relative file path in the container.
• state and state_desc describe the state of the file. As you already know from Chapter 8, the checkpoint file pair can be in one of the following states (the number represents the state column value): (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.
• file_type and file_type_desc describe the type of file: (0) - DATA_FILE, (1) - DELTA_FILE. These columns return NULL if the CFP is in the TOMBSTONE state.
• lower_bound_tsn and upper_bound_tsn indicate the timestamp of the earliest and latest transactions covered by the data file. These columns are populated only for ACTIVE, MERGE TARGET, and MERGED SOURCE states.
• internal_storage_slot is the index of the file in the internal storage array. As you already know from Chapter 8, In-Memory OLTP persists the metadata information about checkpoint file pairs in an internal 8,192-slot array. This column of the view is populated only for UNDER CONSTRUCTION and ACTIVE CFPs.
• file_size_in_bytes and file_size_used_in_bytes provide information about file size and space used in the file. When the file is still being populated, file_size_used_in_bytes is updated at the time of the checkpoint event. These columns return NULL for files in the REQUIRED FOR BACKUP/HA, IN TRANSITION TO TOMBSTONE, and TOMBSTONE states.
• inserted_row_countand deleted_row_count provide the number of rows in the data and delta files. Drop_table_deleted_row_count shows the number of rows in the tables that were dropped.

Let’s use this view and analyze the state transitions of the checkpoint file pairs.

The Lifetime of Checkpoint File Pairs

As the first step in this test, let’s enable the undocumented trace flag TF9851 using the DBCC TRACEON(9851,-1) command. This trace flag disables the automatic merge process, which will allow you to have more control over your test environment.

Let’s create the database with an In-Memory OLTP file group and perform the full backup starting the backup chain, as shown in Listing C-1. I am doing it in the test environment and not following best practices (such as placing In-Memory OLTP and on-disk data on different drives, creating secondary file groups for on-disk data, and a few others). Obviously, you should remember to follow best practices when you design your real databases.

create database [AppendixC]
on primary
(
    name = N’AppendixC’
    ,filename = N’C:DataAppendixC.mdf’
),
filegroup HKData CONTAINS MEMORY_OPTIMIZED_DATA
(
    name = N’AppendixC_HKData’
    ,filename = N’C:DataHKDataAppendixC’
)
log on
(
    name = N’AppendixC_Log’
    ,filename = N’C:DataAppendixC_log.ldf’
)
go

backup database [AppendixC]
to disk = N’C:DataBackupsAppendixC.bak’
with noformat, init, name = ’AppendixC - Full’, compression;

The database is currently empty and, therefore, it does not have any checkpoint file pairs created. You can confirm this by querying the sys.dm_db_xtp_checkpoint_files view, as shown in Listing C-2.

use [AppendixC]
go

select
    checkpoint_file_id
    ,checkpoint_pair_file_id
    ,file_type_desc
    ,state_desc
    ,file_size_in_bytes
    ,relative_file_path
from
    sys.dm_db_xtp_checkpoint_files
order by
    state, file_type

Figure C-1 shows that result set is empty and that the sys.dm_db_xtp_checkpoint_files view does not return any data.

As the next step, let’s create a durable memory-optimized table, as shown in Listing C-3.

create table dbo.HKData
(
    ID int not null,
    Placeholder char(8000) not null,

    constraint PK_HKData
    primary key nonclustered hash(ID)
    with (bucket_count=10000),
)
with
(
    memory_optimized=on
    ,durability=schema_and_data
)

If you check the state of the checkpoint file pairs now and run the code from Listing C-2 again, you will see the output shown in Figure C-2. The total number of files and their size may be different in your environment and will depend on the hardware. My test virtual machine has four vCPU and 8GB of RAM, so I have eight checkpoint file pairs in the PRECREATED state with 16MB data and 1MB delta files. I also have one CFP in the UNDER CONSTRUCTION state.

Let’s enlarge the output for the files from the UNDER CONSTRUCTION CFP, as shown in Figure C-3. As you can see, the checkpoint_pair_file_id values reference the checkpoint_file_id of the second file in the pair.

Relative_file_path provides the path to the file relative to the FILESTREAM container in the In-Memory OLTP file group. Figure C-4 shows the checkpoint files in the folder on the disk.

Now, populate the dbo.HKData table with 1,000 rows and check the status of the checkpoint files, as shown in Listing C-4. The query filters out the checkpoint file pairs in the PRECREATED state from the output.

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as t1 cross join N1 as t2) -- 4 rows
,N3(C) as (select 0 from N2 as t1 cross join N2 as t2) -- 16 rows
,N4(C) as (select 0 from N3 as t1 cross join N3 as t2) -- 256 rows
,N5(C) as (select 0 from N4 as t1 cross join N4 as t2) -- 65,536 rows
,Ids(Id) as (select row_number() over (order by (select null)) from N5)
insert into dbo.HKData(Id, Placeholder)
    select Id, Replicate(’0’,8000)
    from ids
    where Id <= 1000;

select
    checkpoint_file_id
    ,file_type_desc
    ,state_desc
    ,lower_bound_tsn
    ,upper_bound_tsn
    ,file_size_in_bytes
    ,inserted_row_count
    ,deleted_row_count
from
    sys.dm_db_xtp_checkpoint_files
where
    state_desc <> ’PRECREATED’
order by
    state, file_type;

As you can see in Figure C-5, SQL Server populates the data file in the UNDER CONSTRUCTION CFP by inserting 1,000 rows there. The NULL value in the lower_bound_tsn column indicates that this CFP covers transactions from the time of database creation. Similarly, the NULL value in the upper_bound_tsn column indicates that this CFP covers current transactions.

Let’s run a manual CHECKPOINT and check the status of checkpoint file pairs, as shown in Listing C-5.

checkpoint
go

select
    checkpoint_file_id
    ,file_type_desc
    ,state_desc
    ,lower_bound_tsn
    ,upper_bound_tsn
    ,file_size_in_bytes
    ,file_size_used_in_bytes
    ,inserted_row_count
    ,deleted_row_count
from
    sys.dm_db_xtp_checkpoint_files
where
    state_desc <> ’PRECREATED’
order by
    state, file_type;

As you can see in Figure C-6, the CHECKPOINT operation transitions the UNDER CONSTRUCTION CFP to the ACTIVE state. The upper_bound_tsn columns are now populated, indicating the maximum timestamp for transactions covered by the checkpoint file pair.

Let’s insert another 1,000 rows to the dbo.HKData table and check the status of the CFPs. Listing C-6 shows the code to perform this.

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as t1 cross join N1 as t2) -- 4 rows
,N3(C) as (select 0 from N2 as t1 cross join N2 as t2) -- 16 rows
,N4(C) as (select 0 from N3 as t1 cross join N3 as t2) -- 256 rows
,N5(C) as (select 0 from N4 as t1 cross join N4 as t2) -- 65,536 rows
,Ids(Id) as (select row_number() over (order by (select null)) from N5)
insert into dbo.HKData(Id, Placeholder)
    select 1000 + Id, Replicate(’0’,8000)
    from ids
    where Id <= 1000;

select
    checkpoint_file_id
    ,file_type_desc
    ,state_desc
    ,lower_bound_tsn
    ,upper_bound_tsn
    ,file_size_in_bytes
    ,inserted_row_count
    ,deleted_row_count
from
    sys.dm_db_xtp_checkpoint_files
where
    state_desc <> ’PRECREATED’
order by
    state, file_type;

Figure C-7 shows the states of the checkpoint file pairs after the second insert. As you can see, SQL Server creates another CFP in the UNDER CONSTRUCTION state with lower_bound_tsn = 4.

Another CHECKPOINT would transition the UNDER CONSTRUCTION CFP to the ACTIVE state, as shown in Figure C-8. You can force it by running the code from Listing C-5 again. At this point, you have two ACTIVE checkpoint file pairs covering different ranges of transaction timestamps.

As the next step, let’s delete 66.7% of the rows from the table, as shown in Listing C-7. In this listing, you are also running the query that combines the information about the data and delta files, and demonstrates that both checkpoint file pairs are mostly empty.

delete from dbo.HKData
where ID % 3 <> 0;

select
    data.checkpoint_file_id
    ,data.state_desc
    ,data.lower_bound_tsn
    ,data.upper_bound_tsn
    ,data.inserted_row_count
    ,delta.deleted_row_count
    ,convert(decimal(5,2),
        100. - 100. * delta.deleted_row_count /
            IIF(data.inserted_row_count = 0,1,data.inserted_row_count)
        ) as [% Full]
from
    sys.dm_db_xtp_checkpoint_files data join
        sys.dm_db_xtp_checkpoint_files delta on
            data.checkpoint_pair_file_id =
                delta.checkpoint_file_id
where
    data.file_type_desc = ’DATA’ and
    data.state_desc <> ’PRECREATED’;

As you can see in Figure C-9, both files are just 33.3% full so they are perfect candidates for the merge.

You can trigger the merge by calling the sys.sp_xtp_merge_checkpoint_files system stored procedure. This procedure requires you to provide the lower and upper bounds for the merge and it does not accept NULL as the parameter value. You can use any tsn, which is covered by the CFP file participating in the merge.

As already discussed, in most cases you can rely on the automatic merge and do not need to call this procedure manually. One of the cases when manual merge is beneficial is the situation when the size of the data in the durable memory-optimized tables is close to 256GB and you want granular control over the merge process, avoiding situations when you do not have enough space in the checkpoint file pairs to store the data. Listing C-8 shows the code that calls the stored procedure.

exec sys.sp_xtp_merge_checkpoint_files
    @database_name = ’AppendixC’
    ,@transaction_lower_bound = 1
    ,@transaction_upper_bound = 8;

You can check the status of merge requests by examining the sys.dm_db_xtp_merge_requests view, as shown in Listing C-9. Figure C-10 illustrates the output of the query.

select
    request_state_desc
    ,destination_file_id
    ,lower_bound_tsn
    ,upper_bound_tsn
    ,source0_file_id
    ,source1_file_id
from
    sys.dm_db_xtp_merge_requests;

Figure C-11 illustrates the state of checkpoint file pairs after the merge is initiated. As you can see, SQL Server creates the new checkpoint file pair in the MERGE TARGET state and merges data from ACTIVE CFPs there. You can also see the correlation between the checkpoint_file_id of CFPs with destination_file_id and source_file_id columns in sys.dm_db_xtp_merge_requests view.

The next CHECKPOINT will transition the checkpoint file pairs that participated in merge from ACTIVE to MERGED SOURCE and from MERGE TARGET CFP to ACTIVE states. Figure C-12 demonstrates this. Now the merge is considered to be complete and the request state value from the sys.dm_db_xtp_merge_requests view is changed to INSTALLED.

After the next CHECKPOINT, the MERGED SOURCE CFPs will be transitioned to the REQUIRED FOR BACKUP/HA state, as shown in Figure C-13.

After the transaction log backup is taken, log records are transmitted to secondary nodes, and the checkpoint event occurs, these CFPs are eventually picked up by the garbage collection thread and moved to IN TRANSITION TO TOMBSTONE and TOMBSTONE states and eventually deallocated. You can also force manual garbage collection by calling the sys.sp_xtp_checkpoint_force_garbage_collection stored procedure. Listing C-10 illustrates this.

backup log [AppendixC]
to disk = N’C:DataBackupsAppendixC.bak’
with noformat, noinit, name = ’AppendixC - Log’, compression
go

checkpoint
go

exec sys.sp_xtp_checkpoint_force_garbage_collection;

Figure C-14 illustrates CFPs in the TOMBSTONE state. Eventually, they will be cleared from the result sets and deallocated.

Summary

Every checkpoint file pair transitions through various states during its lifetime. You can analyze these states using the sys.dm_db_xtp_checkpoint_files data management view. This view returns information about individual checkpoint files, including their type, size, state, number of inserted and deleted rows, and quite a few other properties.

The merge process merges information from the ACTIVE checkpoint file pairs that have a large percent of deleted rows, creating a new CFP. In most cases, you can rely on the automatic merge process; however, you can trigger a manual merge using the sys.sp_xtp_merge_checkpoint_files stored procedure. You can monitor the status of merge requests using the sys.dm_db_xtp_merge_requests view.

Merged checkpoint file pairs should be included in the log backup before they are deallocated. As with the merge, you can rely on the automatic garbage collection process in most cases. However, you can trigger the manual garbage collection process using the sys.sp_xtp_checkpoint_force_garbage_collection stored procedure.