{"id":1593,"date":"2014-01-23T12:45:00","date_gmt":"2014-01-23T20:45:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/dataplatforminsider\/2014\/01\/23\/state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables\/"},"modified":"2024-01-22T22:49:03","modified_gmt":"2024-01-23T06:49:03","slug":"state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables","status":"publish","type":"post","link":"https:\/\/www.microsoft.com\/en-us\/sql-server\/blog\/2014\/01\/23\/state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables\/","title":{"rendered":"State Transition of Checkpoint Files in Databases with Memory-Optimized Tables"},"content":{"rendered":"

In the previous blog Merge Operations<\/a>, we described that how a CFP transitions across various stages in its life cycle. In this blog, we will walk through an actual example to show the state transition. Note, some of the states shown for CFPs are only visible in RTM bits (not in CTP2) but internally the state transitions is the same. Basing this example using RTM bits adds lot more clarity in how storage is managed. Another note is that RTM bits allocate CFPs (16MB of data file and 1 MB of delta file) on machines with physical memory <= 16GB. This example was run on a machine with 8GB of RAM using pre-RTM bits.<\/p>\n

CREATE DATABASE<\/span> hkdb1 ON\u00a0<\/span><\/span><\/p>\n

\u00a0PRIMARY<\/span> (NAME = [hkdb1_hk_fs_data], FILENAME<\/span> = ‘C:\\hekaton_test\\data\\hkdb1_data.mdf’<\/span>),<\/span><\/p>\n

\u00a0FILEGROUP<\/span> [hkdb1_hk_fs_fg] CONTAINS<\/span> MEMORY_OPTIMIZED_DATA<\/span><\/p>\n

\u00a0(NAME = [hkdb1_hk_fs_dir],\u00a0 FILENAME<\/span> = ‘C:\\hekaton_test\\data\\hkdb1_hk_fs_dir’<\/span>)<\/span><\/p>\n

\u00a0LOG<\/span> ON<\/span> (name = [hktest_log], Filename<\/span>=‘C:\\hekaton_test\\data\\hkdb1_log.ldf’<\/span>, size=100MB)<\/span><\/p>\n

go<\/span><\/p>\n

— disable auto-merge so that we can show the merge<\/span><\/p>\n

— in a predictable way<\/span><\/p>\n

dbcc<\/span> traceon (9851, -1)<\/span><\/p>\n

— set the database to full recovery. This is a common practice for production scenario<\/span><\/p>\n

alter database<\/span> hkdb1 set recovery full<\/span><\/span><\/p>\n

go<\/span><\/p>\n

use<\/span> hkdb1<\/span><\/p>\n

go<\/span><\/p>\n

— create a memory-optimized table with each row of size > 8KB<\/span><\/p>\n

CREATE TABLE<\/span> dbo.t_memopt (<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c1 int<\/span> NOT NULL,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c2 char<\/span>(40) NOT NULL,<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c3 char<\/span>(8000) NOT NULL,<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT<\/span> [pk_t_memopt_c1] PRIMARY KEY NONCLUSTERED HASH<\/span> (c1) WITH<\/span> (BUCKET_COUNT<\/span> = 100000)<\/span><\/p>\n

) WITH<\/span> (MEMORY_OPTIMIZED<\/span> = ON<\/span>, DURABILITY<\/span> = SCHEMA_AND_DATA)<\/span><\/p>\n

go<\/span><\/p>\n

— do the database backup. We do this so that we can do transaction log backups<\/span><\/p>\n

BACKUP DATABASE<\/span> [hkdb1] TO\u00a0 DISK<\/span> = N’C:\\hekaton_test\\data\\hk-empty-data.bak’<\/span><\/span><\/p>\n

WITH NOFORMAT<\/span>, INIT<\/span>,\u00a0 NAME = N’hkdb1-Full Database Backup’<\/span>, SKIP<\/span>, NOREWIND<\/span>, NOUNLOAD<\/span>,\u00a0 STATS<\/span> = 10<\/span><\/p>\n

GO<\/span><\/p>\n

— load 8000 rows. This should load 5 16MB data files on a machine with <= 16GB of storage<\/span><\/p>\n

declare<\/span> @i int<\/span> = 0<\/span><\/p>\n

while<\/span> (@i < 8000)<\/span><\/p>\n

begin<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 insert<\/span> t_memopt values<\/span> (@i, ‘a’<\/span>, replicate<\/span> (‘b’<\/span>, 8000))<\/span><\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set<\/span> @i += 1;<\/span><\/p>\n

end<\/span><\/p>\n

go<\/span><\/p>\n

In this example, we will show the output of DMV sys.dm_db_xtp_checkpoint_files using the following query.<\/p>\n

select<\/span> file_type_desc, state_desc, internal_storage_slot,file_size_in_bytes, file_size_used_in_bytes,<\/span><\/p>\n

inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn, last_backup_page_count, drop_table_deleted_row_count\u00a0<\/span><\/p>\n

from<\/span> sys<\/span>.dm_db_xtp_checkpoint_files<\/span><\/p>\n

order by<\/span> container_id, file_type_desc, upper_bound_tsn<\/span><\/p>\n

Here is the output after removing some columns and rows (just showing rows with DATA file).<\/p>\n