Manage Limitations of Memory Optimized TempDB Feature

The Memory-Optimized TempDB feature was introduced in SQL Server 2019 to mitigate metadata contention in TempDB.

What is Metadata Contention?

For any CREATE, ALTER, or DROP operation on temp tables i.e., DDL operations, SQL Server must acquire a PAGELATCH on system objects. Under normal conditions, these latches are easily acquired. But, when too many concurrent DDL operations occur, these operations wait in line, resulting in contention on system (metadata) objects, hence the term metadata contention. More about metadata contention here.

Why This Feature Matters

This feature is specifically designed to reduce metadata contention. Note that adding data files will not resolve metadata contention, as that addresses a different type of contention.

You can learn more about enabling this feature and its benefits here.

Limitations of Memory-Optimized TempDB

While this feature is powerful, it comes with a few key limitations:

• Unexpected restarts
  Memory cleanup can be delayed due to open, sleeping transactions. When these persist, they prevent memory from being released causing unintended restart of SQL Services.

• Feature is NOT available in

  • Standard Edition

  • Azure SQL database or Azure SQL Managed Instance: Azure users have been looking for this feature, hopefully it comes out soon in those platforms.

• Uncontrolled XTP Memory Growth

  The XTP (eXtreme Transaction Processing) memory can grow without limits. To control it, you’ll need to use Resource Governor, otherwise, it may continue to consume max server memory.

• Temp tables with columnstore indexes won’t work.

Managing Memory-Optimized TempDB to Prevent Restarts

This feature does a good job of reducing metadata contention, but.. but.. you really need to strategically manage this feature to reap its maximum benefits and I’ll show you how.

Step 1: Monitor XTP Memory Usage

Use the following query to check XTP memory usage daily. If memory usage only increases and does not go down, memory cleanup isn’t happening.

--XTP memory consumed by engine
SELECT type AS ClerkType
	,sum(pages_kb) / 1024 AS SizeMB
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_XTP'
GROUP BY type
ORDER BY sum(pages_kb) DESC

Step 2: Identify Open Sleeping Transactions

For demo purpose, I’m going to create an explicit open transaction and then run a bunch of stored procedures which creates temp tables to mimic consuming memory. Now XTP memory that is consumed won’t be released since the earliest transaction is still open and not committed.

/*
Explicit open transaction
*/
BEGIN TRAN
    CREATE TABLE #hold(id int)

/*
Run below script on ostress that creates temp table accessing memory optimized tempdb to consume XTP memory

ostress.exe -S"HARIPRIYA\SQL2022" -Q"exec dbatest.dbo.PopulateTempTable" -n50 -r1000 -q
*/

Check XTP memory consumption using query from step 1, it is at 3.9GB

Run the query below to find if there are any open sleeping transactions.

--long open transaction
SELECT 
 DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) as tran_elapsed_time_mins,
 st.session_id,
 txt.text, 
 DB_NAME(sess.database_id) as database_name ,
 sess.program_name,
 *
FROM
 sys.dm_tran_active_transactions at
 INNER JOIN sys.dm_tran_session_transactions st ON st.transaction_id = at.transaction_id
 LEFT OUTER JOIN sys.dm_exec_sessions sess ON st.session_id = sess.session_id
 LEFT OUTER JOIN sys.dm_exec_connections conn ON conn.session_id = sess.session_id
   OUTER APPLY sys.dm_exec_sql_text(conn.most_recent_sql_handle)  AS txt
ORDER BY
 1 DESC;

As there is an idle transaction, memory cleanup will not take place, even if you try to cleanup manually as below. Only 500MB was released and 3.3GB is still held up.

/* 
Memory Cleanup
SQL 2019 - Run twice
SQL 2022 CU1 - Run Once
*/
EXEC sys.sp_xtp_force_gc 'tempdb'
GO
EXEC sys.sp_xtp_force_gc 'tempdb'
GO
EXEC sys.sp_xtp_force_gc
GO
EXEC sys.sp_xtp_force_gc

Step 3: Terminate open transaction and cleanup memory

Now, let’s kill the open transaction and run the query to cleanup memory.

KILL 56

/* 
Memory Cleanup
sys.sp_xtp_force_gc - Frees up allocated but unused bytes.
SQL 2019 - Run twice
SQL 2022 CU1 - Run Once
*/
EXEC sys.sp_xtp_force_gc 'tempdb'
GO
EXEC sys.sp_xtp_force_gc 'tempdb'
GO
EXEC sys.sp_xtp_force_gc
GO
EXEC sys.sp_xtp_force_gc

In your work environment, once you identify an idle transaction, based on what SPID it is, you can choose to:

• Terminate the SPID manually(if this belongs to database maintenance), or

• Reach out to the application team about the open transaction.

Step 4: Verify XTP memory is lower

Now that the open transaction has been terminated and memory has been cleaned up, XTP memory value is down to 73MB compared to 3.3GB before the cleanup.

So, the main idea here is to check for idle transactions and get rid of them so memory will be released.

Proactive Monitoring and Alerts

While we have done the memory release process manually, you can setup a monitoring solution to notify when XTP memory consumption crosses a threshold value.

Set up a SQL Agent job to:

• Alert when XTP memory exceeds 10% of max server memory.

• Check for idle transactions lingering for over 5 hours.

• Log session details to a table and send you a notification.

Github link to the script here.

Automate Cleanup

Once you’ve identified a consistent pattern of idle transactions, and you're confident in the behavior, you can automate SPID termination with enough filters like program name, login, or host name. Be very cautious if you have to automate spid termination.

Key point

Getting rid of idle transactions periodically should help release XTP memory to make the best use of the feature.

Conclusion

Memory-Optimized TempDB does a great job reducing metadata contention, but it must be strategically managed. With regular monitoring and cleanup of idle transactions, you can prevent restarts and fully leverage the benefits of this feature.

In my next article, I’ll cover how to use Resource Governor to manage memory usage for this feature more effectively.

Additional Reading

• https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-ver17#memory-optimized-tempdb-metadata

• https://gohigh.substack.com/p/metadata-contention-on-tempdb

• https://gohigh.substack.com/p/in-memory-tempdb-metadata-feature

• https://gohigh.substack.com/p/flowchart-to-guide-you-resolve-tempdb

Thanks for reading,
Haripriya.
My LinkedIn.