Casos do Dia a Dia – In-Memory OLTP – Use com Moderação – Problema Utilização Memória

Fala galera,

Vamos para o segundo post da série do In-Memory. Dessa vez, vou mostrar um caso real do dia a dia onde tivemos um problema com a utilização da memória do In-Memory.

Recomendo que veja também os outros posts da série que está bem legal! xD

Casos do Dia a Dia – In-Memory OLTP – Conceitos Básicos

Casos do Dia a Dia – In-Memory OLTP – Use com Moderação – Problema Utilização Memória

Casos do Dia a Dia – In-Memory OLTP – Mensagens de Erro

Simbora!!! #gogogo


Casos do Dia a Dia – Entendendo o Problema:

Após conhecer os Conceitos Básicos no post anterior (se você ainda não leu, recomendo que leia), agora vamos ver um problema na prática!

Algumas semanas atrás, recebemos um chamado de um cliente que informou que estava com lentidão no banco de dados. Conectamos no ambiente e iniciamos algumas validações.

Ao verificar o Error Log do SQL Server, nos deparamos com as mensagens de erro abaixo:

“There is insufficient system memory in resource pool “internal” to run this query.”

OBS: No próximo post, vou fazer algumas demos para explicar melhor esse erro.

Também verificamos o PLE (Page Life Expectancy) e o valor estava extremamente baixo!

  • PLE (Page Life Expectancy) – Valores de Referência:

Esse contador informa o tempo médio em segundos que as páginas estão permanecendo na memória.

Vale ressaltar aqui o conceito do NUMA Node (non-uniform memory access). Cada node possui seu próprio conjunto de processadores e um pool de memória. Com isso, o buffer pool é “quebrado” em áreas menores que serão gerenciadas por cada NUMA node. Por fim, imagine que cada um deles funciona como um “mini buffer pool”.

Segue abaixo dois links com mais detalhes sobre o NUMA node e o cálculo do PLE:

https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

https://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

Segue também uma classificação para os valores do PLE. Ou seja, o valor 5 está bem crítico!!!

< 10 : excessivamente baixo, podendo gerar erros, asserts e dumps

< 300 : baixo

1000 : razoável

5000 : bom

Quero que você entenda que: quanto menor o PLE, mais crítico! Ou seja, o SQL Server precisa buscar os dados no disco para depois colocar na memória e esse processo é demorado. Quando isso acontece, o valor do PLE diminui e você pode ter algum impacto na performance do seu ambiente.

“Vish! Só aí já deu pra notar que o trem estava bem feio!!!”

Após identificar esses erros, nós utilizamos a query abaixo (MUITO ÚTIL) para verificar como o SQL Server estava utilizando a memória.

“Caramba Luiz, mas que diabos é esse MEMORYCLERK_XTP???”

Pois é, a brincadeira começou por aí. Nunca tinha visto esse MEMORYCLERK e depois de fazer algumas buscas no São Google, conseguimos descobrir que essa utilização de memória estava relacionada ao In-Memory OLTP.

Referência: https://docs.microsoft.com/pt-br/sql/relational-databases/in-memory-oltp/sample-database-for-in-memory-oltp?view=sql-server-ver15

Repare que o In-Memory estava utilizando 26 GB de memória, ou seja, quase toda a memória do servidor que era 32 GB no total. Normalmente, o MEMORYCLERK_SQLBUFFERPOOL que é o TOP 1, pois ele contém as páginas de dados.

“Uai Luiz, o In-Memory alocou algumas tabelas na memória que estão ocupando um total de 26 GB, não é isso?”

Então, aqui é que as coisas começaram a ficar mais estranhas. A query abaixo retorna à utilização do “XTP” por cada database. O “DB_ID_X” informa o ID da database.

Usamos também a query abaixo para retornar as tabelas In-Memory da database atual (print abaixo apenas como exemplo):

Uma outra forma bem legal de verificar a utilização de memória do In-Memory é através de um report padrão do SQL Server. Basta gerar através do caminho abaixo:

Object Explorer -> Clicar com o botão direito na database -> Reports -> Standard Reports -> Memory Usage By Memory Optimized Objects

Criei algumas tabelas In-Memory na minha máquina e repare que uma delas estava consumindo quase 2 GB. Esse report é muito bacana porque mostra uma visão por cada objeto e uma visão resumida também.

Com isso, nós identificamos todas as tabelas que usavam o In-Memory na database. Além disso, o cliente informou que todos os dias a noite os registros das tabelas eram excluídos e no outro dia eles seriam populados novamente (elas utilizavam o DURABILITY = SCHEMA_AND_DATA, pois não podiam perder os dados durante o dia). Ou seja, repare que todo dia era executado um DELETE nas tabelas e isso gerava um “lixo” no In-Memory.

Dado esse comportamento, nós então excluímos manualmente os dados das tabelas, mas para nossa surpresa o MEMORYCLERK_XTP manteve o mesmo valor.

“Uai Luiz, mas se os registros foram excluídos, porque o SQL Server não liberou essa memória logo, mesmo com o PLE tão baixo (ou seja, tínhamos pressão na memória)?”

Acontece que o In-Memory utiliza um Garbage Collector (falei sobre ele no post anterior) para gerenciar essa liberação de memória. Contudo, ele não estava fazendo essa liberação e nesse momento nós ainda não tínhamos encontrado uma solução.

Fizemos mais algumas pesquisas e nossa próxima tentativa foi deixar a database OFFLINE e depois ONLINE novamente. Nesse caso, conseguimos fazer essa alteração pois era um horário com pouco movimento (no final do dia). Ou seja, teria um baixo impacto na produção.

Entretanto, imagine se isso acontecer em um ambiente que seja 24×7 com transações o tempo inteiro. Teríamos uma parada na produção e possíveis prejuízos ($$$) para o cliente.

Bom, depois disso, conseguimos enfim liberar 21 GB do MEMORYCLERK_XTP!!! Contudo, essa foi apenas uma SOLUÇÃO PALIATIVA e continuamos a pesquisar para descobrir como liberar AUTOMATICAMENTE essa memória utilizada pelo In-Memory. Depois disso, o MEMORYCLERK_SQLBUFFERPOOL e o PLE também voltaram a aumentar. Ufa!!!

Depois dessa jornada, explicamos para o cliente como o In-Memory funcionava realmente, as limitações, os cuidados que deveríamos ter e que ele requer uma boa quantidade de memória para não afetar a performance do ambiente como um todo. Como o servidor tinha apenas 32 GB de memória e bases com 50 a 200 GB, ele entendeu que não seria o cenário ideal para utilizar o In-Memory.

Por fim, recriamos a tabela, mas dessa vez do modo tradicional (disk-based) ao invés do In-Memory. Nosso maior receio com essa alteração seriam os possíveis locks, mas não tivemos problemas com isso e conseguimos liberar mais memória para os dados (MEMORYCLERK_SQLBUFFERPOOL) e isso também ajudou o PLE a crescer. Pode ser que no seu caso você tenha problemas com Locks, mas aí recomendo que faça um trabalho de Tuning para tentar melhorar as queries envolvidas.

Esse cliente utilizava a versão SQL Server Standard e por esse motivo não conseguia habilitar o Resource Governor para limitar os recursos das databases, pois ele está disponível apenas no SQL Server Enterprise. Talvez essa poderia ser mais uma opção para tentar amenizar os problemas no ambiente como um todo.

Resumindo:

O In-Memory não deve ser aplicado em qualquer ambiente. Ele deve ser muito bem estudado, testado e monitorado para evitar esse tipo de problema e prejuízos para o cliente. Portanto, tenha muito CUIDADO!!!


In-Memory – Utilização Espaço Disco:

Gostaria de alertá-los também para os devidos cuidados relacionados ao Espaço em Disco, pois o In-Memory precisa persistir algumas informações no disco (também falei um pouco sobre isso no post anterior) e devemos monitorar isso também!

Repare que no exemplo abaixo utilizei tabelas pequenas e foi gerado um arquivo com 2 GB no disco. Agora imagine um ambiente grande com tabelas maiores que 100 GB. Ou seja, além da memória também precisamos nos preocupar com o disco!

No caso do nosso cliente, o espaço em disco não era problema e ele estava utilizando tabelas pequenas no In-Memory.

Agora vamos para a parte mais legal que é fazer a nossa simulação do problema!  #gogogo


Teste – Simulação do Problema de Memória:

Recomendo fortemente que leiam o artigo abaixo para entender a configuração inicial do In-Memory:

Referência: https://www.red-gate.com/simple-talk/sql/sql-development/beginner-guide-to-in-memory-optimized-tables-in-sql-server/

CUIDADO: Uma vez criado em sua base de produção, o FILEGROUP NÃO PODERÁ SER REMOVIDO! Portanto, utilize uma base de teste primeiro para ter certeza se vai criar na produção mesmo! Vou falar melhor sobre esse erro no próximo post.

Cannot drop the last memory-optimized container ‘Teste_In_Memory’.

Msg 5042, Level 16, State 8, Line 153

The filegroup ‘Teste_In_Memory’ cannot be removed because it is not empty.

  • Configuração do SQL Server:

Vou utilizar a seguinte versão para os testes:

Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) – 14.0.3048.4 (X64)   Nov 30 2018 12:57:58   Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Home 10.0 <X64> (Build 18362: )

O MAX SERVER MEMORY da instância está configurado com aproximadamente 10 GB. Caso você queira fazer esse ajuste também, basta executar o script abaixo (CUIDADO PARA NÃO EXECUTAR ISSO NA PRODUÇÃO!!!):

Nossos primeiros passos serão:

  • Criar uma database “Teste”;
  • Alterar o Recovery Model para SIMPLE (por ser uma base de teste);
  • Habilitar o MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT;
  • Criar um FILEGROUP;
  • Criar um arquivo para o In-Memory.

Feito isso, vamos ter uma database com a seguinte configuração:

Ao criar a database, se você conferir o Error Log do SQL Server você vai encontrar a mensagem abaixo que é apenas informativa e não precisamos nos preocupar OK.

Message

[INFO] HkHostDbCtxt::Initialize(): Database ID: [5] ‘Teste’. XTP Engine version is 2.11.

Agora vamos criar a nossa tabela In-Memory utilizando o DURABILITY = SCHEMA_AND_DATA.

Inicialmente, esse é o consumo de memória do SQL Server (fiz um Restart antes de iniciar os testes):

OBS: Vou utilizar esses dois SELECTs para validar cada uma das próximas etapas OK.

Vamos começar a brincadeira inserindo 500 mil registros para aumentar a utilização do MEMORYCLERK_XTP.

O MEMORYCLERK_XTP passou a utilizar 4 GB.

Lembra do report do In-Memory do SQL Server? Observe que nossa tabela está utilizando 2 GB, mas o MEMORYCLERK_XTP está com 4 GB. Que loucura né!

Vamos inserir também 1 milhão de registros em uma tabela normal para aumentar a utilização do MEMORYCLERK_SQLBUFFERPOOL.

Repare que o XTP e o PLE não foram reduzidos e o MEMORYCLERK_SQLBUFFERPOOL aumentou.

Agora vamos criar mais uma tabela normal, com o mesmo tamanho da anterior e ver o que vai acontecer.

O XTP não foi alterado, o SQLBUFFERPOOL aumentou um pouco e o PLE foi zerado. Ou seja, tivemos uma pressão na memória e ela reduziu o valor do PLE.

Vamos agora excluir todos os registros da tabela In-Memory. Lembra das limitações que citei no post anterior? Pois é, não é possível utilizar o TRUNCATE TABLE com o In-Memory.

Msg 10794, Level 16, State 96, Line 98

The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.

Já que o TRUNCATE não funciona, vamos com o DELETE mesmo.

Mesmo não possuindo nenhum dado nas tabelas do In-Memory, o XTP ainda está utilizando 4 GB!

Vamos criar novamente as duas tabelas normais e analisar o comportamento (executar cada trecho separadamente).

O SQLBUFFERPOOL não aumentou e ainda tivemos uma pressão de memória e mais uma vez o PLE foi zerado.

“Caramba Luiz, e como vamos tirar esse maldito XTP daí?”

Ah não, agora eu fiquei nervoso e vou fazer um teste mais sinistro kkk. Você lembra que comentei anteriormente que o MAX SERVER MEMORY está com 10 GB? Então agora eu vou criar uma tabela normal que vai utilizar 10 GB na memória. Quero ver esse XTP não sair daí!!!

Ufaa, finalmente o SQL Server liberou um pouco da memória do MEMORYCLERK_XTP que não estava sendo utilizada! Uhhuull!!! Mas mesmo assim ele ainda manteve mais de 2 GB no XTP.

Nesse processo de liberação da memória do XTP, o SQL Server registra as mensagens abaixo no Error Log:

Message

[INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row (id: 1).

Agora sendo bem mais apelão, vou excluir a tabela do In-Memory e verificar a utilização da memória.

OBS: Nem sempre você deve conseguir fazer isso na produção, pois os dados da tabela serão perdidos. Contudo, pode ser uma solução mais rápida para liberar a memória e depois você recria a tabela com o In-Memory novamente. Dessa forma, apenas essa tabela seria impactada.

Conseguimos reduzir ainda mais o XTP, de 2 GB para 600 MB. Por fim, vamos executar mais uma vez o INSERT da tabela normal de 10 GB.

Dessa vez conseguimos utilizar mais de 8 GB com o MEMORYCLERK_SQLBUFFERPOOL!

Repare que mesmo sem nenhum objeto utilizando o In-Memory, o SQL Server ainda manteve mais de 600 MB com o MEMORYCLERK_XTP.

O que podemos concluir foi que para forçar o SQL Server a liberar a memória que não estava sendo utilizada pelo In-Memory (MEMORYCLERK_XTP), nós precisamos gerar uma pressão muito grande na memória e mesmo assim ainda não conseguimos liberar tudo. Ou seja, é uma luta para tentar recuperar essa memória que não está sendo utilizada pelo In-Memory.

No caso do nosso cliente, o In-Memory segurou 26 GB da memória, deixando “quase nada” para os dados que são realmente importantes e o PLE ficava extremamente baixo. Como o ambiente não conseguia gerar uma pressão gigantesca na memória, o In-Memory tomou conta dela e não liberou mais (#miseraviii kkkkk).

“Luiz, mas se eu tentar criar uma tabela In-Memory com 10 GB para utilizar toda a memória, isso vai dar certo?”

Já adianto que isso VAI DAR MERDA!! Kkkk

Mas esse teste vai ficar para o próximo post. =)


Teste – Solução Paliativa – Database OFFLINE / ONLINE:

Por fim, vou mostrar também como funciona a SOLUÇÃO PALIATIVA.

De forma resumida, vamos fazer o seguinte:

  • Criar a database;
  • Configurar e criar a tabela In-Memory;
  • Popular a tabela e excluir os registros;
  • Por fim, deixar a database OFFLINE e ONLINE novamente (SEM PERDA DE DADOS).

OBS: Se você fizer um Restart no Serviço do SQL Server ele também vai liberar a memória não utilizada pelo In-Memory. Contudo, essa é uma solução mais drástica, pois vai afetar TODO O AMBIENTE!

Nesse momento, a utilização de memória estava da seguinte forma:

Agora iremos excluir todos os registros da tabela.

Após o DELETE, o XTP aumentou um pouco mais.

Por fim, vamos deixar a database OFFLINE e ONLINE e conferir novamente.

Como era esperado, o MEMORYCLERK_XTP liberou a memória que não estava sendo utilizada!

Com essa solução nós conseguimos liberar a memória não utilizada pelo In-Memory e afetamos a base inteira ao fazer o OFFLINE -> ONLINE. Portanto, tenha muito cuidado ao utilizar essa estratégia e alinhe bem com o cliente antes de executar esse procedimento para minimizar os prejuízos ($$$).


Conclusão:

Minhas considerações finais são:

  • Podemos ver os “estragos” que o In-Memory pode fazer quando não for bem utilizado;
  • Não recomendo que o In-Memory seja utilizado em um servidor com pouca memória;
  • O SQL Server pode demorar a liberar a memória não utilizada pelo In-Memory e isso pode afetar a performance do ambiente inteiro;
  • O SQL Server possui o report padrão “Memory Usage By Memory Optimized Objects” que é muito útil para identificar os objetos que estão utilizando o In-Memory;
  • O In-Memory gera algumas mensagens no Error Log do SQL Server e algumas delas podem ser úteis para entender o que está acontecendo no banco de dados;
  • Vimos também algumas formas paliativas de liberar essa memória não utilizada.
  • Utilizar o In-Memory com o SQL Server Standard pode ser mais perigoso, pois não conseguimos habilitar o Resource Governor (disponível apenas no SQL Server Enterprise) para limitar os recursos das databases.

Espero que você tenha gostado dos testes e te espero no próximo post da série! =)


Download – Scripts:

Segue abaixo os scripts utilizados nesse post:

https://github.com/luizvitorf/SQLServer/blob/master/Scripts/In-Memory/Casos%20do%20Dia%20a%20Dia%20-%20In-Memory%20OLTP%20-%20Use%20com%20Modera%C3%A7%C3%A3o%20-%20Problema%20Utiliza%C3%A7%C3%A3o%20Mem%C3%B3ria.sql


Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!

Me siga no LinkedIn e YouTube para ficar por dentro das novidades.

Abraço,

Luiz Vitor França Lima

Consultor SQL Server

9 comentários em “Casos do Dia a Dia – In-Memory OLTP – Use com Moderação – Problema Utilização Memória

  1. Pingback: Casos do Dia a Dia – In-Memory OLTP – Conceitos Básicos – Luiz Lima

Deixe uma resposta