patternsqlMinor
SQL Server in-memory optmized tempdb metadata memory usage continually growing
Viewed 0 times
tempdbsqloptmizedusagegrowingmemoryservercontinuallymetadata
Problem
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.
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.
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
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 DESCEnvironment
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
Solution
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.
According to Klaus Ondrich this issue is fixed in Cumulative Update 13 for SQL Server 2019.
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.
According to Klaus Ondrich this issue is fixed in Cumulative Update 13 for SQL Server 2019.
Context
StackExchange Database Administrators Q#292370, answer score: 9
Revisions (0)
No revisions yet.