Busy workloads can spend time contending on the metadata used to create temporary objects. Memory-optimized tempdb metadata targets that bottleneck, but enabling it is a planned instance change with a restart.
Identify the Tempdb Bottleneck Before Choosing a Feature
PAGELATCH waits describe contention on pages already in memory. They are different from PAGEIOLATCH waits for page I/O. A tempdb latch wait can involve allocation or another hot page rather than temporary-object metadata. Confirm the page and object before choosing the feature.
The feature changes selected temporary-object system metadata to non-durable memory-optimized structures. It does not move every temporary table's rows into memory or remove the need for tempdb storage. It also does not eliminate every latch, lock, spill, or capacity problem in that database. I identify the contested metadata object before proposing the restart.
Capture the workload's temporary-object creation rate and the timing of the contention. A short snapshot can miss a recurring burst. Preserve observations from the busy period along with throughput and application latency. The server does not reserve a bottleneck for the moment the diagnostic query happens to arrive.
Inspect the Waiting Pages and Their Objects
These page-inspection functions arrived in SQL Server 2019, the same release as the feature. The request's page_resource can be decoded and inspected to identify the database, page, and associated object. Run the bounded query during the observed latch contention with approved diagnostic permissions.
SELECT TOP (50) r.session_id,r.wait_type,r.wait_time,
pr.db_id,pr.file_id,pr.page_id,pi.object_id,
OBJECT_NAME(pi.object_id,pi.database_id) AS PageObjectName,
pi.page_type_desc
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pr
CROSS APPLY sys.dm_db_page_info(pr.db_id,pr.file_id,pr.page_id,'LIMITED') AS pi
WHERE pr.db_id=2 AND r.page_resource IS NOT NULL
AND r.wait_type LIKE N'PAGELATCH[_]%'
ORDER BY r.wait_time DESC;Inspect the identified system metadata objects against the documented diagnostic scope. A NULL object name or a different page type needs additional review. Do not label every row from this query a qualifying metadata bottleneck. Keep allocation contention and user-object pages separate from the system-table contention addressed by the feature.
Correlate the page evidence with the relevant operation. Frequent temporary-table creation and deletion can be part of the pattern, but an application can use many temporary tables without suffering this specific bottleneck. The accepted justification should show the contention and its service effect rather than only count temporary objects.
Check Whether Memory-Optimized Tempdb Metadata Is Active
Memory-optimized tempdb metadata was introduced in SQL Server 2019. Inspect the installed build and runtime property before planning a change. Keep the instance start time with wait-statistics comparisons because a restart resets important cumulative evidence.
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS MetadataMemoryOptimized;
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
SELECT wait_type,waiting_tasks_count,wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE N'PAGELATCH[_]%';Those wait totals are instance-wide and do not identify tempdb metadata by themselves. Combine them with the page observations and workload timings. Retain a pre-change baseline outside the instance if a restart will clear the evidence. Compare equivalent workload windows rather than raw totals accumulated over different durations.
Record CPU, memory, tempdb allocation, and application behavior as well. A reduction in one latch category does not prove the service improved if another resource becomes constrained. I keep the acceptance criteria attached to throughput and latency rather than treating a property value of one as the performance result.
Turn On Memory-Optimized Tempdb Metadata With a Restart
The administrative command enables the configuration, but the service restart is required for activation. Arrange the maintenance window, application reconnection, dependencies, and verification procedure before executing it. This is not a query to run casually during a busy period. It changes a server-level setting, so it also needs the ALTER SETTINGS server permission.
ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA=ON;Use the approved Windows service maintenance process for the correct instance. This article does not hide the restart inside a diagnostic script. Afterward, verify the runtime property and the new instance start time, then run the accepted representative workload.
SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized') AS MetadataMemoryOptimized;
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;A property value of one after the restart confirms activation. It does not establish that the original contention was removed or that every workload remains supported. Repeat the page-based observation and service measurements under the same accepted test conditions. Keep the configuration result separate from the workload result.
Monitor Memory and Long Metadata Transactions
The feature has documented memory considerations, including cases where XTP memory grows substantially. Inspect the memory clerks and relevant tempdb memory consumers during the trial. Long explicit transactions involving temporary-object DDL can retain metadata-related memory and need application-side review.
SELECT type,SUM(pages_kb)/1024.0 AS ClerkPagesMB
FROM sys.dm_os_memory_clerks
GROUP BY type ORDER BY ClerkPagesMB DESC;
SELECT committed_kb/1024.0 AS CommittedMB,
committed_target_kb/1024.0 AS TargetMB
FROM sys.dm_os_sys_info;
SELECT memory_consumer_type_desc,allocated_bytes,used_bytes
FROM tempdb.sys.dm_db_xtp_memory_consumers;MEMORYCLERK_XTP is not exclusively a measurement of this feature when the instance uses other memory-optimized workloads. Interpret it with the more specific consumers and active transaction evidence. A retained allocation and a process-wide total answer different questions. Record collection scope instead of attributing all XTP memory to tempdb automatically.
Review the current documented feature limitations for the deployed build and exercise the application's temporary-object patterns. Resource-pool binding can constrain the metadata's memory in supported deployments, but an undersized pool can cause allocation failures. Choose any bound through a reviewed capacity trial rather than copying a percentage from another server.
Turn Off Memory-Optimized Tempdb Metadata Safely
Which workload behavior would make you reverse the change? Define that condition before the first restart. Memory pressure, an unsupported interaction, or no measurable benefit can justify returning to the baseline. Record the responsible decision and include the second restart in the maintenance plan.
ALTER SERVER CONFIGURATION
SET MEMORY_OPTIMIZED TEMPDB_METADATA=OFF;Disabling also requires a restart. If an instance cannot start normally after the change, follow the documented minimal-configuration recovery procedure with the qualified operating team. Do not improvise unrelated startup changes. Keep the current configuration, approved recovery steps, and instance identity available outside the affected server.
Accept the Change From Workload Evidence
Compare metadata-page contention, throughput, latency, CPU, and memory over matched workload periods. Recheck the representative temporary-object operations and normal application behavior. Retain the configuration, build, measured outcomes, and reversal criteria with the change record.
Memory-optimized tempdb metadata is useful when confirmed metadata contention limits the workload and the trial meets its service criteria. It is one targeted option in tempdb performance work. Choose it for demonstrated need, then verify its operating cost and limitations after activation.
Related reading on this blog: Spotting tempdb Contention and Watching the tempdb Version Store.
A metadata setting is not a general tempdb cure, it is a targeted change that needs contention evidence and a planned restart.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
