SQL Server 2019 – Managing In-Memory Optimized TempDB Metadata Growth

memorymemory-optimized-tablessql serversql-server-2019tempdb

Problem

We have enabled sp_configure 'tempdb metadata memory-optimized' = 1, and now the tempdb meta data is taking over 400 GB on one of our servers and continues to grow. There are some drops in memory usage, but generally it keeps growing it's memory usage. We've had a couple times where the server actually crashes because there's not enough memory for the other system processes to modify tempdb and it brings down the whole server.

Question I am asking

How do I keep the SQL Server in-memory optimized tempdb metadata from growing continually and crashing my server? If anything, what are some other pieces of information that I can look into to find what's consuming so much memory?

Data about the problem

sys.dm_os_memory_clerks

The following query currently returns 438 GB.

SELECT SUM(domc.pages_kb / 1024.0 / 1024.0) AS pages_gb
FROM sys.dm_os_memory_clerks AS domc
WHERE domc.type LIKE 'MEMORYCLERK_XTP'

sys.dm_db_xtp_memory_consumers

The following query provides the data that the biggest usage of memory (290 GB) is memory_consumer_id of 113 – 'LOB Page Allocator'. It has no object_id or xtp_object_id, so I'm guessing that it's a database wide object.

SELECT ddxmc.memory_consumer_id
     , ddxmc.memory_consumer_type_desc
     , ddxmc.memory_consumer_desc
     , ddxmc.object_id
     , ddxmc.xtp_object_id
     , ddxmc.used_bytes / 1024.0 / 1024.0 / 1024.0 AS used_gb
FROM sys.dm_db_xtp_memory_consumers AS ddxmc
ORDER BY ddxmc.allocated_bytes DESC

Environment

Version: SQL Server 2019 CU9 – Enterprise
Memory on box: 3 TB
Instance Type: Fail-Over Clustered Instance
OS: Windows Server 2019 Standard
Number of CPU Cores: 80 Physical Cores (we've had to increase this number lately)
Number Of Tempdb Files: 64
Replication: This server is a publisher and subscriber to transactional replication.

Usage patterns

We are heavy users of tempdb. We are continually creating and dropping temp tables and table variables all the time in stored procedures. We need to work with bulk amounts of data, passing in lists of data that we then turn into tables to work with result set data instead of working with one piece of information at a time. Because of the heavy churn on tempdb we have had to implement the in-memory optimized tempdb to relieve PAGELATCH_* waits we were experiencing.

I am pretty sure there are NOT any user tables that are memory optimized. When I run the following query I only see SYSTEM_TABLE type objects:

SELECT *
FROM sys.dm_db_xtp_index_stats AS ddxis
    JOIN sys.objects AS o ON o.object_id = ddxis.object_id

Things I'm aware of and what I've tried

In-memory garbage collection can only clean up items older then the oldest transaction.

I'm quite aware that garbage collection can only happen on rows that are older then the oldest transaction, and so I have stopped all of our long running SQL Agent jobs and any other processes longer then 5 minutes. Unfortunately our memory usage didn't come back down. We do have old sessions, but none of them have any transactions open according to the following query.

SELECT *
FROM sys.dm_exec_sessions AS des
WHERE des.open_transaction_count > 0

Replication

To rule out replication I did temporarily stop the publishing and subscription agents for this server. No change in memory usage.

Checkpoint

I have run 'CHECKPOINT' in tempdb. Memory usage didn't come down.

DBCC FREEPROCCACHE – Getting rid of old temp tables

To drop old temp tables that are being cached I cleared the query plan cache and verified that the temp tables were re-created by running the following query. This did not result in the memory usage dropping any substantial amount.

SELECT *
FROM sys.tables AS t
WHERE t.name LIKE '#%'
    AND t.create_date < 'TimeOfClearingPlanCache'

Other Possible Solutions

Restarting SQL Server

We can restart SQL Server, and it does mitigate the problem for a while, but the memory usage does increase again after a while. While this is a work around, this is a terrible solution that we are not very fond of.

Turning Off 'In-Memory Optimized Tempdb'

We could turn off 'in-memory optimized tempdb', but we then would be subject to the heavy PAGELATCH_* waits we were experiencing before. We have 64 tempdb files to help reduce the contention that we were seeing, but even that wasn't enough during our heavy times. This could be an option, but it would be best if we could find why the memory usage is continually growing.

Best Answer

Pam Lahoud SQL Enterprise Team Principal Program Manager at Microsoft said on twitter:

and contributed via comment here:

There are a few known issues that could be coming into play here. It's best to open a case with support if you can, the more data we have on these issues the more likely we'll be able to find a fix.

Related Solutions

SQL Server – TempDB Internal Space Usage Growing from Service Broker

TLDR; Check for conversations being left open completely.

In our system we re-use conversations and have a table dedicated to holding these conversations that are usable, however the dev team setup a new service broker without my knowledge ages ago while I was off, didn't set up these conversation points and didn't set any thresholds on the alerting.

When the new system has been turned on the conversations are being opened but not closed properly and since there aren't any in the pool it is just creating a new conversation (we reached 7.1 million conversations for one service broker)

My steps for fixing was to create and record the 20 conversation handlers that I require for that service broker and record them into our table. This stopped the growth of the tempDB to stop the risk of the DB going down.

Then came the long process of closing off all the un-used conversations

select 
    se.[conversation_handle] 
from 
    sys.conversation_endpoints se with (nolock) 
    left outer join [queue].[maintained_Conversations] qh with (nolock) on qh.[conversation_handle] = se.[conversation_handle]
where 
    qh.service_name is not null 
    and se.far_service = @service

Gives the list of all the IDs cursor through those values and simply run END CONVERSATION @id; on each of them.

After the process has finished the temp DB space will loosen up (Note it does not do it as you close them, it seems to do it in large chunks when you're no longer working on creating / ending them (I cant guarantee this is how it works, just what I observed after stopping mid process and tempDB getting some space back)

SQL Server Tempdb on RAM Disk – Feasibility and Performance

First, patch: make sure you're on 2012 Service Pack 1 Cumulative Update 10 or newer. In SQL 2014, Microsoft changed TempDB to be less eager to write to disk, and they awesomely backported it to 2012 SP1 CU10, so that can alleviate a lot of TempDB write pressure.

Second, get exact numbers on your latency. Check sys.dm_io_virtual_file_stats to see the average write stall for your TempDB files. My favorite way to do this is either:

sp_BlitzFirst @ExpertMode = 1, @Seconds = 30 /* Checks for 30 seconds */
sp_BlitzFirst @SinceStartup = 1 /* Shows data since startup, but includes overnights */

Look at the file stats section, and focus on the physical writes. The SinceStartup data can be a little misleading since it also includes times when CHECKDB is running, and that can really hammer your TempDB.

If your average write latency is over 3ms, then yes, you might have solid state storage in your SAN, but it's still not fast.

Consider local SSDs for TempDB first. Good local SSDs (like Intel's PCIe NVMe cards, which are under $2k USD especially at the sizes you're describing) have extremely low latency, lower than you can achieve with shared storage. However, under virtualization, this comes with a drawback: you can't vMotion the guest from one host to another to react to load or to hardware issues.

Consider a RAM drive last. There are two big gotchas with this approach:

First, if you really do have heavy TempDB write activity, the change rate on memory may be so high that you won't be able to vMotion the guest from one host to another without everyone noticing. During vMotion, you have to copy the contents of RAM from one host to another. If it's really changing that fast, faster than you can copy it over your vMotion network, you can run into issues (especially if this box is involved with mirroring, AGs, or a failover cluster.)

Second, RAM drives are software. In the load testing that I've done, I haven't been all that impressed with their speed under really heavy TempDB activity. If it's so heavy that an enterprise-grade SSD can't keep up, then you're going to be taxing the RAM drive software, too. You'll really want to load test this heavily before going live - try things like lots of simultaneous index rebuilds on different indexes, all using sort-in-tempdb.