What’s the issue?
Memory-Optimized Tempdb Metadata is a SQL Server feature introduced in SQL Server 2019 as part of the In-Memory Database feature umbrella. When enabled, it moves the tempdb system metadata tables (used to track temporary tables, table variables, and similar objects) into memory-optimized, latch-free storage, eliminating the PAGELATCH contention that has historically been a tempdb bottleneck in high-concurrency workloads.For clarity, it does not memory-optimize user-created temporary tables or table variables, only the underlying system metadata that SQL Server maintains for those objects.
This finding identifies instances where the feature is currently enabled. It is not necessarily a problem, but it is worth noting in case the team is unaware that the feature is active or unaware of the considerations that come with it, particularly around SQL Server version differences.
Why is this a problem?
The feature provides significant benefit on workloads with heavy tempdb metadata activity, particularly those that create and drop large numbers of temporary tables. By moving metadata into latch-free in-memory structures, it eliminates a class of PAGELATCH waits tied to tempdb system-table metadata pages, though allocation-map related PAGELATCH waits (PFS/GAM/SGAM) may still remain.The most important consideration is memory consumption. The feature uses the In-Memory OLTP (Hekaton) infrastructure and consumes memory from the server’s memory pool, which can be substantial under heavy tempdb activity. Without proper memory planning, the in-memory tempdb metadata can grow to consume large portions of available memory, and certain workload patterns (long-running explicit transactions with DDL on temporary tables) can cause memory growth that does not release, eventually leading to out-of-memory errors and potential service crashes.
There are also functional limitations that vary by SQL Server version. In SQL Server 2019 specifically, columnstore indexes are not supported on temporary tables when the feature is enabled, and sp_estimate_data_compression_savings cannot estimate columnstore compression in tempdb. Transactions that access memory-optimized tables in user databases cannot also access tempdb metadata catalog views in the same transaction. SQL Server 2022 builds on the 2019 implementation with additional improvements including shared latches for GAM/SGAM pages and improved allocation logic.
What should you do about this?
No remediation is required if the feature is being used intentionally and the team is aware of the implications.Verify that memory planning accounts for the feature. Microsoft documents binding memory-optimized tempdb metadata to a Resource Governor resource pool as a mitigation to limit XTP memory growth and reduce the risk of instance-wide out-of-memory conditions; however, the pool must be sized carefully because hitting the cap can cause tempdb-dependent queries to fail. This is particularly important for instances with heavy or unpredictable tempdb workloads.
Review the workload for the limitations that apply to your SQL Server version. On SQL Server 2019, verify you don’t create columnstore indexes on temporary tables and avoid patterns where an explicit transaction touches memory-optimized objects in a user database and also queries tempdb catalog views. On SQL Server 2022+, tempdb scalability is improved via concurrent GAM/SGAM updates (separate from the metadata feature), but you should still validate feature-specific limitations against your exact build and test before enabling in production.
Monitor the MEMORYCLERK_XTP memory clerk through sys.dm_os_memory_clerks to track how much memory the feature is consuming over time. Sudden growth that does not release typically indicates a workload pattern (long-running DDL transactions on temp objects) that is incompatible with the feature, and should prompt a review of whether the workload can be adjusted or whether the feature should be disabled.