SQL SERVER – List All Memory Optimized Tables in TempDB

Based on yesterday’s blog post on Memory-Optimized TempDB and RESOURCE_SEMAPHORE Wait Type, I received a very interesting question from my client of Comprehensive Database Performance Health Check. The question was about how to list all the Memory Optimized Tables in TempDB.

First of all, we must remember that while memory optimization is possible for the TempDB, it is a bit different from other regular databases. In TempDB the optimization happens for the selected meta-data tables and not the user tables or temporary tables which we create. Additionally, TempDB when restarted always is created from scratch and almost all the metadata tables are empty so it does not take a lot of time to be recreated.

Now here are three questions that I received from my client DBA.

Q1: How do I know if my TempDB is enabled for memory optimization or not?

A1: Run the script below to know the status of your TempdB:

SELECT IIF(SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') = 1, 'Enabled','Disabled') 

The script above will give you status.

Q2: How to list all the metadata tables which are enabled for the memory optimization in TempDB?

A2: Here is the script that lists all the metadata memory-optimized tables in TempDB:

SELECT TempTabs.[object_id], aobj.name
FROM tempdb.sys.all_objects AS aobj 
INNER JOIN tempdb.sys.memory_optimized_tables_internal_attributes AS TempTabs
ON aobj.[object_id] = TempTabs.[object_id]

The script above will give the following output if you are running SQL Server 2019.

object_id name
object_id name
———– ——————-
3 sysrscols
5 sysrowsets
7 sysallocunits
9 sysseobjvalues
34 sysschobjs
40 sysmultiobjvalues
41 syscolpars
54 sysidxstats
55 sysiscols
60 sysobjvalues
74 syssingleobjrefs
75 sysmultiobjrefs

Q3: What are the disadvantages of enabling this option for TempDB?

A3: First of all, you should enable this feature only if you are facing issues like RESOURCE_SEMAPHORE, in your system and you are sure that it is because of TempDB contention.

However, if you enable this feature on the TempDB you can’t use any of the tables which are memory-optimized in the same transactions where you use the regular tables. However, in the case of the TempDB, I would not be worried much as there is very little chance of those tables to be used in daily routine procedures. Additionally, columnstore indexes are also not possible to be created in the TempDB after memory-optimized metadata is enabled.

In reality, I have never needed that for my client, so I am currently not much concerned about it.

Reference: Pinal Dave (https://blog.sqlauthority.com)