Memory-Optimized TempDB Tables: List Them and Check Status

Memory-optimized TempDB tables are the system tables that SQL Server 2019 and later can keep in memory. Two short queries tell you whether the feature is on and which tables it covers.

What Moves to Memory

The feature does not touch your temporary tables. It moves selected metadata tables of TempDB to memory-optimized storage. Those are the system tables that record every object, column and index. TempDB starts empty at every restart. Most of those tables start empty with it, so rebuilding them in memory is quick.

A client asked three questions about it. How do I know whether TempDB uses it? Which tables are in memory? What does it cost? The three sections below answer them in that order. The reasons to turn it on at all are in Metadata Contention in TempDB: When Memory-Optimized Metadata Helps.

Is the Feature On?

The server property IsTempdbMetadataMemoryOptimized answers with 1 or 0. It is NULL on versions that do not have the property, so the query says so. The configuration table holds the same setting as a second opinion. The subquery returns NULL when the option does not exist.

SELECT CASE CONVERT(int, SERVERPROPERTY('IsTempdbMetadataMemoryOptimized'))
            WHEN 1 THEN N'Enabled'
            WHEN 0 THEN N'Disabled'
            ELSE N'Not available on this version'
       END AS TempdbMetadata,
       (SELECT value_in_use FROM sys.configurations WHERE name = N'tempdb metadata memory-optimized') AS ConfigValueInUse;

The test instance runs SQL Server 2025, and the feature is still off there. It is not on by default in any version, so a result of Disabled is normal.

The query returns one row, so it suits a check across many servers. A query window against a server group in Central Management Server runs it on every instance and merges the rows. Servers older than SQL Server 2019 answer Not available on this version, which is the right answer for them.

List the Tables That Are in Memory

The second query joins the objects of TempDB to the view that holds the internal attributes of memory-optimized tables. A table that appears in both is one of the memory-optimized TempDB tables.

SELECT attrs.object_id, o.name AS TableName
FROM tempdb.sys.all_objects AS o
JOIN tempdb.sys.memory_optimized_tables_internal_attributes AS attrs ON attrs.object_id = o.object_id
ORDER BY o.name;

On the test instance this returns no rows, because the feature is off. A client ran the same query on SQL Server 2019 with the feature on. It returned the twelve system tables below. The object ids are the ones that instance reported.

Read the names as a map of what a temporary table needs. The columns of the table sit in syscolpars. Its indexes sit in sysidxstats and sysiscols. Its pages are tracked in sysallocunits and sysrowsets. All of them are bookkeeping, and none holds your data.

What the Feature Costs

Turn it on only when you have a real contention problem and you are sure it comes from TempDB metadata. It needs a restart of the service. Memory-optimized TempDB metadata uses memory too, so watch the memory of the server after the change. Test the change on a copy of the workload first. Keep the way back in mind. Switching the feature off needs the same restart.

Two limits matter to code. A transaction cannot touch a memory-optimized table of a user database and the TempDB metadata at the same time. Creating a temporary table inside such a transaction is the common case. The second limit is that columnstore indexes cannot be created on temporary tables. Most daily procedures need neither. Check yours before the change.

Check Your Code Before the Change

Two quick checks show whether the limits can touch you. The first counts the memory-optimized tables in a database. Run it in each user database. A count of 0 means the first limit cannot apply there.

SELECT COUNT(*) AS MemoryOptimizedTables FROM sys.tables WHERE is_memory_optimized = 1;

The second check searches modules for the word COLUMNSTORE and for a pound sign. The pound sign marks a temporary table. It is a text search, so it returns candidates and not proof. Read every module it returns.

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS SchemaName, OBJECT_NAME(m.object_id) AS ModuleName
FROM sys.sql_modules AS m
WHERE m.definition LIKE N'%COLUMNSTORE%' AND m.definition LIKE N'%#%'
ORDER BY SchemaName, ModuleName;

In msdb on the test instance, the count is 0 and the search returns no rows. Your user databases decide the real answer.

Is the List Worth Running?

You could argue that the status check is enough. It is, for most days. The list of memory-optimized TempDB tables adds proof. After a restart it confirms that the tables moved. Keep both queries in your health check for a server that has the setting on.

What to Remember

Check the status with the server property. List the memory-optimized TempDB tables with the second query when you need proof. Remember that only metadata moves to memory. Your temporary tables stay as they are.

A feature flag is not a fix, it is a change you still have to measure.

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