Problema

Habilitamos sp_configure 'tempdb metadata memory-optimized' = 1, e agora os metadados tempdb estão ocupando mais de 400 GB em um de nossos servidores e continuam crescendo. Há algumas quedas no uso de memória, mas geralmente continua crescendo o uso de memória. Tivemos algumas vezes em que o servidor realmente trava porque não há memória suficiente para os outros processos do sistema modificarem o tempdb e isso desativa todo o servidor.

Pergunta que estou fazendo

Como faço para impedir que os metadados tempdb otimizados na memória do SQL Server cresçam continuamente e travem meu servidor? Se alguma coisa, quais são algumas outras informações que posso analisar para descobrir o que está consumindo tanta memória?

Dados sobre o problema

sys.dm_os_memory_clerks

A consulta a seguir retorna atualmente 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

A consulta a seguir fornece os dados de que o maior uso de memória (290 GB) é memory_consumer_id de 113 - 'LOB Page Allocator'. Ele não tem object_id ou xtp_object_id, então estou supondo que é um objeto de banco de dados.

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

Meio Ambiente

Versão: SQL Server 2019 CU9 -
Memória corporativa na caixa: 3 TB
Tipo de instância: Instância em cluster de failover
SO: Windows Server 2019 Standard
Número de núcleos de CPU: 80 núcleos físicos (tivemos que aumentar esse número ultimamente) Número de Tempdb Arquivos: 64 Replicação: Este servidor é um editor e assinante de replicação transacional.

Padrões de uso

Somos usuários pesados ​​de tempdb. Estamos continuamente criando e descartando tabelas temporárias e variáveis ​​de tabela o tempo todo em procedimentos armazenados. Precisamos trabalhar com grandes quantidades de dados, passando listas de dados que depois transformamos em tabelas para trabalhar com dados do conjunto de resultados em vez de trabalhar com uma informação de cada vez. Devido à grande rotatividade no tempdb, tivemos que implementar o tempdb otimizado na memória para aliviar as esperas de PAGELATCH_* que estávamos enfrentando.

Tenho certeza de que NÃO existem tabelas de usuário otimizadas para memória. Quando executo a seguinte consulta, vejo apenas SYSTEM_TABLEobjetos de tipo:

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

Coisas que eu conheço e o que eu tentei

A coleta de lixo na memória só pode limpar itens mais antigos que a transação mais antiga.

Estou ciente de que a coleta de lixo só pode ocorrer em linhas mais antigas que a transação mais antiga e, portanto, parei todos os nossos trabalhos de longa execução do SQL Agent e quaisquer outros processos com mais de 5 minutos. Infelizmente, nosso uso de memória não voltou a cair. Temos sessões antigas, mas nenhuma delas tem transações abertas de acordo com a consulta a seguir.

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

Replicação

Para descartar a replicação, interrompi temporariamente os agentes de publicação e assinatura deste servidor. Nenhuma mudança no uso da memória.

Ponto de verificação

Eu executei 'CHECKPOINT' no tempdb. O uso de memória não caiu.

DBCC FREEPROCCACHE - Livrando-se de tabelas temporárias antigas

Para descartar tabelas temporárias antigas que estão sendo armazenadas em cache, limpei o cache do plano de consulta e verifiquei se as tabelas temporárias foram recriadas executando a consulta a seguir. Isso não resultou na queda do uso de memória em qualquer quantidade substancial.

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

Outras soluções possíveis

Reiniciando o SQL Server

Podemos reiniciar o SQL Server e isso atenua o problema por um tempo, mas o uso de memória aumenta novamente depois de um tempo. Embora seja uma solução alternativa, essa é uma solução terrível da qual não gostamos muito.

Desativando o 'Tempdb Otimizado na Memória'

Poderíamos desativar o 'tempdb otimizado na memória', mas então estaríamos sujeitos às esperas pesadas de PAGELATCH_* que estávamos experimentando antes. Temos 64 arquivos tempdb para ajudar a reduzir a contenção que estávamos vendo, mas mesmo isso não foi suficiente durante nossos tempos difíceis. Esta poderia ser uma opção, mas seria melhor se pudéssemos descobrir por que o uso de memória está crescendo continuamente.