Memory-Optimized Files: List Logical and Physical Names

To list memory-optimized files, join sys.dm_db_xtp_checkpoint_files to sys.database_files. The first view lists the checkpoint files. The second gives the logical and physical name of the container that holds them.

Where Memory-Optimized Data Lives on Disk

One client had a strict documentation process. It needed a list of every memory-optimized file, with its logical name and physical name. The request sounds odd, because a memory-optimized table lives in memory. A durable table still writes to disk, so a restart can bring it back. Those writes go to memory-optimized files.

The files sit in a container. A container is a folder, and it belongs to a filegroup of the type MEMORY_OPTIMIZED_DATA. In sys.database_files the container looks like a file of the type FILESTREAM. Ordinary FILESTREAM containers use the same type. Its name is the logical name, and its folder is the physical name. Inside the folder, SQL Server keeps data files, delta files and root files, each with its own state.

The demo creates a database named XtpFilesDemo with a container in the default data folder. It adds a durable table, loads 50,000 rows and runs a checkpoint, so the files exist. The demo needs about 1 GB of free disk space in the default data folder, and the cleanup removes it. Memory-optimized tables need SQL Server 2014 or later, and every edition supports them from SQL Server 2016 SP1.

IF DB_ID(N'XtpFilesDemo') IS NULL CREATE DATABASE XtpFilesDemo;
GO
USE XtpFilesDemo;
GO
IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = N'FX')
BEGIN
    ALTER DATABASE XtpFilesDemo ADD FILEGROUP XtpData CONTAINS MEMORY_OPTIMIZED_DATA;
    DECLARE @container nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath')) + N'XtpFilesDemo_container';
    DECLARE @sql nvarchar(max) = N'ALTER DATABASE XtpFilesDemo ADD FILE (NAME = N''XtpFilesDemo_container'', FILENAME = N''' + @container + N''') TO FILEGROUP XtpData;';
    EXEC (@sql);
END;
GO
DROP TABLE IF EXISTS dbo.SessionState;
CREATE TABLE dbo.SessionState (
    SessionKey int           NOT NULL PRIMARY KEY NONCLUSTERED,
    Payload    nvarchar(200) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
INSERT INTO dbo.SessionState (SessionKey, Payload)
SELECT TOP (50000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), REPLICATE(N'x', 100)
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CHECKPOINT;

The Logical and the Physical Name

The container is the first answer. Filter sys.database_files on the type FILESTREAM.

SELECT name AS LogicalName, physical_name AS PhysicalName, type_desc AS FileType
FROM sys.database_files
WHERE type_desc = N'FILESTREAM';

The physical name is a folder, not a file. The path is the one on this test server, and yours will differ. The file counts and sizes below also vary by machine. The checkpoint files live inside it, in a subfolder, under names made of GUIDs.

Count and Size the Checkpoint Files

The checkpoint file view has a column container_id. It equals the file_id of the container in sys.database_files. That join connects each checkpoint file to its logical and physical name. The next query adds up the memory-optimized files of each container.

SELECT df.name AS LogicalName, df.physical_name AS PhysicalName,
       COUNT(*) AS CheckpointFiles,
       CAST(SUM(x.file_size_in_bytes) / 1048576.0 AS decimal(9,1)) AS SizeMB,
       CAST(SUM(ISNULL(x.file_size_used_in_bytes, 0)) / 1048576.0 AS decimal(9,1)) AS UsedMB
FROM sys.dm_db_xtp_checkpoint_files AS x
JOIN sys.database_files AS df ON df.file_id = x.container_id
GROUP BY df.name, df.physical_name;

The query needs the VIEW DATABASE STATE permission, or VIEW DATABASE PERFORMANCE STATE on SQL Server 2022 and later. The column file_size_used_in_bytes is NULL for precreated files. ISNULL turns those NULLs into zero before the sum, so the total stays a number.

The container holds 18 files and 952 MB on this server, and the table uses 10.9 MB of that. The sizes are in megabytes, because the file size is in bytes and the query divides by 1,048,576. On this server, the disk space of the database is far larger than its data. Plan for it.

Why So Much Space Is Idle

The next query groups the same files by type and state. A state of PRECREATED means that SQL Server created the file ahead of time. The next checkpoint then doesn’t wait for the disk.

SELECT file_type_desc AS FileType, state_desc AS State, COUNT(*) AS Files,
       CAST(SUM(file_size_in_bytes) / 1048576.0 AS decimal(9,1)) AS SizeMB
FROM sys.dm_db_xtp_checkpoint_files
GROUP BY file_type_desc, state_desc
ORDER BY file_type_desc, state_desc;

Only the ACTIVE files hold your rows. The DATA file keeps the inserted rows, and the DELTA file keeps the identifiers of deleted rows. The ROOT file keeps checkpoint metadata. Twelve FREE files wait for use and account for 648 MB. The ROOT file in the state WAITING FOR LOG TRUNCATION isn’t needed once the log is truncated. The precreated sizes depend on the memory of the machine.

Run It in Every Database

Both views describe the current database. To document a whole server, run the query in each database that has a MEMORY_OPTIMIZED_DATA filegroup. Add DB_NAME() as a column, so the rows can be pasted into one list. The memory-optimized files belong to SQL Server. Don’t move, rename or delete them by hand. Let SQL Server change their states.

Why Not Use Database Properties?

You could argue that the Files page of Database Properties already lists the container. It does, as one FILESTREAM row with its path. It can’t show the checkpoint files inside, their states or their sizes. The query shows them, and it can be saved in a script and run on every database with memory-optimized tables.

What to Remember

Use sys.database_files for the logical and physical name of the container, and sys.dm_db_xtp_checkpoint_files for the files inside it. Join them on container_id and file_id. Read the states before you worry about the size, because precreated files make a new container look large. The view needs the permission to see database state. Date the list when you document it, because the states change with every checkpoint.

When you finish, drop the demo database. SQL Server removes the container folder with it.

USE master;
GO
IF DB_ID(N'XtpFilesDemo') IS NOT NULL
BEGIN
    ALTER DATABASE XtpFilesDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE XtpFilesDemo;
END;

A memory-optimized table is not a table without files, it is a table with files you see only on request.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.