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
SELECT *
FROM sys.dm_os_performance_counters
WHERE 
counter_name = 'Page life expectancy'
	AND object_name LIKE '%Buffer Manager%'

-- PLE - Page Life Expectancy

SELECT *

FROM sys.dm_os_performance_counters

WHERE

counter_name = 'Page life expectancy'

AND object_name LIKE '%Buffer Manager%'

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.

-- TOP CLERKS - Ordenado pelo Consumo de Memória
-- SQL Server 2012 version
SELECT TOP(10) [type] as [Memory Clerk Name], SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

-- TOP CLERKS - Ordenado pelo Consumo de Memória

-- SQL Server 2012 version

SELECT TOP(10) [type] as [Memory Clerk Name], SUM(pages_kb)/1024 AS [SPA Memory (MB)]

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY SUM(pages_kb) DESC;

“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.

-- MEMORYCLERK_XTP - Por Database
SELECT type, name, memory_node_id, pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks
where [type] like '%xtp%'

-- MEMORYCLERK_XTP - Por Database

SELECT type, name, memory_node_id, pages_kb/1024 AS pages_MB

FROM sys.dm_os_memory_clerks

where [type] like '%xtp%'

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

-- Retorna as tabelas In-Memory
SELECT *
FROM sys.tables
WHERE is_memory_optimized = 1

-- Retorna as tabelas In-Memory

SELECT *

FROM sys.tables

WHERE is_memory_optimized = 1

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!!!):

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'10000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'max server memory (MB)', N'10000'

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

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:

USE master
GO

--	CRIA A DATABASE - ALTERAR O CAMINHO para um local existente no seu servidor.
CREATE DATABASE Teste 
	ON  PRIMARY ( 
		NAME = N'Teste', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste.mdf' , 
		SIZE = 102400KB , FILEGROWTH = 102400KB 
	)
	LOG ON ( 
		NAME = N'Teste_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_log.ldf' , 
		SIZE = 30720KB , FILEGROWTH = 30720KB 
	)
GO

-- BASE DE TESTE - Alterar o Recovery Model para SIMPLE
ALTER DATABASE Teste SET RECOVERY SIMPLE

ALTER DATABASE Teste 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

ALTER DATABASE Teste 
ADD FILEGROUP Teste_In_Memory CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE Teste 
ADD FILE (
	name='Teste_In_Memory', 
	filename='C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_In_Memory'
) 
TO FILEGROUP Teste_In_Memory

-- Valida a Configuração da Database
SELECT database_id, name, recovery_model_desc, is_memory_optimized_elevate_to_snapshot_on
FROM sys.databases
where name = 'teste'

USE master

-- CRIA A DATABASE - ALTERAR O CAMINHO para um local existente no seu servidor.

CREATE DATABASE Teste

ON PRIMARY (

NAME = N'Teste', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste.mdf' ,

SIZE = 102400KB , FILEGROWTH = 102400KB

)

LOG ON (

NAME = N'Teste_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_log.ldf' ,

SIZE = 30720KB , FILEGROWTH = 30720KB

)

-- BASE DE TESTE - Alterar o Recovery Model para SIMPLE

ALTER DATABASE Teste SET RECOVERY SIMPLE

ALTER DATABASE Teste

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

ALTER DATABASE Teste

ADD FILEGROUP Teste_In_Memory CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE Teste

ADD FILE (

name='Teste_In_Memory',

filename='C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_In_Memory'

)

TO FILEGROUP Teste_In_Memory

-- Valida a Configuração da Database

SELECT database_id, name, recovery_model_desc, is_memory_optimized_elevate_to_snapshot_on

FROM sys.databases

where name = 'teste'

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.

USE Teste

CREATE TABLE dbo.InMemoryExample (
    OrderID   INTEGER   NOT NULL   IDENTITY PRIMARY KEY NONCLUSTERED,
    Nome   VARCHAR(4000)    NOT NULL
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA);

USE Teste

CREATE TABLE dbo.InMemoryExample (

OrderID INTEGER NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED,

Nome VARCHAR(4000) NOT NULL

)

WITH

(MEMORY_OPTIMIZED = ON,

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.

-- TOP CLERKS - Ordenado pelo Consumo de Memória
-- SQL Server 2012 version
SELECT TOP(10) [type] as [Memory Clerk Name], SUM(pages_kb)/1024 AS [SPA Memory (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC;

-- PLE (Page Life Expectancy)
SELECT cntr_value AS PLE_Value
FROM sys.dm_os_performance_counters
WHERE  
 counter_name = 'Page life expectancy'
 and object_name like '%Buffer Manager%'

-- TOP CLERKS - Ordenado pelo Consumo de Memória

-- SQL Server 2012 version

SELECT TOP(10) [type] as [Memory Clerk Name], SUM(pages_kb)/1024 AS [SPA Memory (MB)]

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY SUM(pages_kb) DESC;

-- PLE (Page Life Expectancy)

SELECT cntr_value AS PLE_Value

FROM sys.dm_os_performance_counters

WHERE

counter_name = 'Page life expectancy'

and object_name like '%Buffer Manager%'

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

SET NOCOUNT ON

INSERT INTO InMemoryExample
SELECT TOP 500000
	REPLICATE('A',4000) 
FROM	Master.sys.All_Columns ac1,
		Master.sys.All_Columns ac2,
Master.sys.All_Columns ac3;

SET NOCOUNT ON

INSERT INTO InMemoryExample

SELECT TOP 500000

REPLICATE('A',4000)

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

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.

-- Insere os dados na tabela normal para utilizar o MEMORYCLERK_SQLBUFFERPOOL
IF(OBJECT_ID('MemoryPressure') IS NOT NULL)
	DROP TABLE 'MemoryPressure'

SELECT TOP 1000000
	REPLICATE('A',4000) AS Name
INTO MemoryPressure
FROM	Master.sys.All_Columns ac1,
		Master.sys.All_Columns ac2,
Master.sys.All_Columns ac3;

EXEC sp_spaceused 'MemoryPressure'

-- Insere os dados na tabela normal para utilizar o MEMORYCLERK_SQLBUFFERPOOL

IF(OBJECT_ID('MemoryPressure') IS NOT NULL)

DROP TABLE 'MemoryPressure'

SELECT TOP 1000000

REPLICATE('A',4000) AS Name

INTO MemoryPressure

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

EXEC sp_spaceused 'MemoryPressure'

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.

IF(OBJECT_ID('MemoryPressure2') IS NOT NULL)
	DROP TABLE MemoryPressure2

SELECT TOP 1000000
	REPLICATE('A',4000) AS Name
INTO MemoryPressure2
FROM	Master.sys.All_Columns ac1,
		Master.sys.All_Columns ac2,
Master.sys.All_Columns ac3;

EXEC sp_spaceused 'MemoryPressure2'

IF(OBJECT_ID('MemoryPressure2') IS NOT NULL)

DROP TABLE MemoryPressure2

SELECT TOP 1000000

REPLICATE('A',4000) AS Name

INTO MemoryPressure2

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

EXEC sp_spaceused 'MemoryPressure2'

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.

-- Tenta excluir todos os registros da tabela
TRUNCATE TABLE InMemoryExample

1 2	-- Tenta excluir todos os registros da tabela TRUNCATE TABLE InMemoryExample

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.

-- Exclui os registos da tabela In-Memory
DELETE FROM InMemoryExample

1 2	-- Exclui os registos da tabela In-Memory DELETE FROM InMemoryExample

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).

-- Insere os dados na tabela normal para utilizar o MEMORYCLERK_SQLBUFFERPOOL
IF(OBJECT_ID('MemoryPressure') IS NOT NULL)
	DROP TABLE MemoryPressure

SELECT TOP 1000000
	REPLICATE('A',4000) AS Name
INTO MemoryPressure
FROM	Master.sys.All_Columns ac1,
		Master.sys.All_Columns ac2,
Master.sys.All_Columns ac3;

EXEC sp_spaceused MemoryPressure

-- Insere os dados na tabela normal 2 para utilizar o MEMORYCLERK_SQLBUFFERPOOL
IF(OBJECT_ID('MemoryPressure2') IS NOT NULL)
	DROP TABLE MemoryPressure2

SELECT TOP 1000000
	REPLICATE('A',4000) AS Name
INTO MemoryPressure2
FROM	Master.sys.All_Columns ac1,
		Master.sys.All_Columns ac2,
Master.sys.All_Columns ac3;

EXEC sp_spaceused MemoryPressure2

-- Insere os dados na tabela normal para utilizar o MEMORYCLERK_SQLBUFFERPOOL

IF(OBJECT_ID('MemoryPressure') IS NOT NULL)

DROP TABLE MemoryPressure

SELECT TOP 1000000

REPLICATE('A',4000) AS Name

INTO MemoryPressure

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

EXEC sp_spaceused MemoryPressure

-- Insere os dados na tabela normal 2 para utilizar o MEMORYCLERK_SQLBUFFERPOOL

IF(OBJECT_ID('MemoryPressure2') IS NOT NULL)

DROP TABLE MemoryPressure2

SELECT TOP 1000000

REPLICATE('A',4000) AS Name

INTO MemoryPressure2

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

EXEC sp_spaceused MemoryPressure2

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í!!!

-- GERA UMA PRESSAO NA MEMORIA
-- Up to 3 minutes to run...
-- 10416MB table size...
USE Teste

IF(OBJECT_ID('MemoryPressure10GB') IS NOT NULL)
	DROP TABLE MemoryPressure10GB

CREATE TABLE MemoryPressure10GB(Col1 CHAR(500));

INSERT INTO MemoryPressure10GB
SELECT TOP 20000000
       CONVERT(CHAR(100), 'Test, fixed data to be used on test') AS Col1
FROM master.dbo.sysobjects A,
     master.dbo.sysobjects B,
     master.dbo.sysobjects C,
     master.dbo.sysobjects D
OPTION (MAXDOP 1);
GO

EXEC sp_spaceused 'MemoryPressure10GB'

-- GERA UMA PRESSAO NA MEMORIA

-- Up to 3 minutes to run...

-- 10416MB table size...

USE Teste

IF(OBJECT_ID('MemoryPressure10GB') IS NOT NULL)

DROP TABLE MemoryPressure10GB

CREATE TABLE MemoryPressure10GB(Col1 CHAR(500));

INSERT INTO MemoryPressure10GB

SELECT TOP 20000000

CONVERT(CHAR(100), 'Test, fixed data to be used on test') AS Col1

FROM master.dbo.sysobjects A,

master.dbo.sysobjects B,

master.dbo.sysobjects C,

master.dbo.sysobjects D

OPTION (MAXDOP 1);

EXEC sp_spaceused 'MemoryPressure10GB'

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.

-- Exclui a Tabela do In-Memory
DROP TABLE InMemoryExample

1 2	-- Exclui a Tabela do In-Memory DROP TABLE InMemoryExample

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.

-- 2 MINUTOS
-- Executa novamente o INSERT da tabela de 10 GB
USE Teste

IF(OBJECT_ID('MemoryPressure10GB') IS NOT NULL)
	DROP TABLE MemoryPressure10GB

CREATE TABLE MemoryPressure10GB(Col1 CHAR(500));

INSERT INTO MemoryPressure10GB
SELECT TOP 20000000
       CONVERT(CHAR(100), 'Test, fixed data to be used on test') AS Col1
FROM master.dbo.sysobjects A,
     master.dbo.sysobjects B,
     master.dbo.sysobjects C,
     master.dbo.sysobjects D
OPTION (MAXDOP 1);
GO

-- 2 MINUTOS

-- Executa novamente o INSERT da tabela de 10 GB

USE Teste

IF(OBJECT_ID('MemoryPressure10GB') IS NOT NULL)

DROP TABLE MemoryPressure10GB

CREATE TABLE MemoryPressure10GB(Col1 CHAR(500));

INSERT INTO MemoryPressure10GB

SELECT TOP 20000000

CONVERT(CHAR(100), 'Test, fixed data to be used on test') AS Col1

FROM master.dbo.sysobjects A,

master.dbo.sysobjects B,

master.dbo.sysobjects C,

master.dbo.sysobjects D

OPTION (MAXDOP 1);

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!

USE master
GO

-- DROP DATABASE Teste

--	CRIA A DATABASE - ALTERAR O CAMINHO para um local existente no seu servidor.
CREATE DATABASE Teste 
	ON  PRIMARY ( 
		NAME = N'Teste', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste.mdf' , 
		SIZE = 102400KB , FILEGROWTH = 102400KB 
	)
	LOG ON ( 
		NAME = N'Teste_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_log.ldf' , 
		SIZE = 30720KB , FILEGROWTH = 30720KB 
	)
GO

-- BASE DE TESTE - Alterar o Recovery Model para SIMPLE
ALTER DATABASE Teste SET RECOVERY SIMPLE

ALTER DATABASE Teste 
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

ALTER DATABASE Teste 
ADD FILEGROUP Teste_In_Memory CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE Teste 
ADD FILE (
	name='Teste_In_Memory', 
	filename='C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_In_Memory'
) 
TO FILEGROUP Teste_In_Memory

GO

USE Teste
GO

IF(OBJECT_ID('InMemoryExample') IS NOT NULL)
	DROP TABLE InMemoryExample

CREATE TABLE InMemoryExample (
    OrderID   INTEGER   NOT NULL   IDENTITY PRIMARY KEY NONCLUSTERED,
    Nome   VARCHAR(4000)    NOT NULL
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA);

-- Insere os dados na tabela In_Memory para utilizar o MEMORYCLERK_XTP
INSERT INTO InMemoryExample
SELECT TOP 500000
	REPLICATE('A',4000) 
FROM	Master.sys.All_Columns ac1,
		Master.sys.All_Columns ac2,
Master.sys.All_Columns ac3;

EXEC sp_spaceused 'InMemoryExample'

USE master

-- DROP DATABASE Teste

-- CRIA A DATABASE - ALTERAR O CAMINHO para um local existente no seu servidor.

CREATE DATABASE Teste

ON PRIMARY (

NAME = N'Teste', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste.mdf' ,

SIZE = 102400KB , FILEGROWTH = 102400KB

)

LOG ON (

NAME = N'Teste_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_log.ldf' ,

SIZE = 30720KB , FILEGROWTH = 30720KB

)

-- BASE DE TESTE - Alterar o Recovery Model para SIMPLE

ALTER DATABASE Teste SET RECOVERY SIMPLE

ALTER DATABASE Teste

SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

ALTER DATABASE Teste

ADD FILEGROUP Teste_In_Memory CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE Teste

ADD FILE (

name='Teste_In_Memory',

filename='C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\Teste_In_Memory'

)

TO FILEGROUP Teste_In_Memory

USE Teste

IF(OBJECT_ID('InMemoryExample') IS NOT NULL)

DROP TABLE InMemoryExample

CREATE TABLE InMemoryExample (

OrderID INTEGER NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED,

Nome VARCHAR(4000) NOT NULL

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

-- Insere os dados na tabela In_Memory para utilizar o MEMORYCLERK_XTP

INSERT INTO InMemoryExample

SELECT TOP 500000

REPLICATE('A',4000)

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

EXEC sp_spaceused 'InMemoryExample'

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

Agora iremos excluir todos os registros da tabela.

DELETE InMemoryExample

EXEC sp_spaceused 'InMemoryExample'

DELETE InMemoryExample

EXEC sp_spaceused 'InMemoryExample'

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

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

USE master
GO

ALTER DATABASE Teste SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE Teste SET ONLINE

USE master

ALTER DATABASE Teste SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE Teste SET ONLINE

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”

Pingback: Casos do Dia a Dia – In-Memory OLTP – Conceitos Básicos – Luiz Lima
Edvaldo Castro 26 de maio de 2020 Responder

Bem legal e bem completo o post. Parabens… keep up the good work.
- Luiz Lima Autor do post26 de maio de 2020 Responder
  
  Nice! Thanks Edvaldo! =)
  
  Luiz Vitor
Walisson Alkmim 26 de maio de 2020 Responder

Que post !!!
Parabéns, realmente muito completo..
- Luiz Lima Autor do post26 de maio de 2020 Responder
  
  Obrigado Walisson! Fico feliz que tenha gostado!
  
  Abraço,
  Luiz Vitor
Marcos Blasques 26 de maio de 2020 Responder

Parabéns Luiz !!! Muito obrigado por compartilhar conhecimento .
- Luiz Lima Autor do post26 de maio de 2020 Responder
  
  Valeu Marcos! Obrigado e volte sempre =)
  
  Abraço,
  Luiz Vitor
Rodrigo Martin 23 de junho de 2020 Responder

Post top!!!
- Luiz Lima Autor do post24 de junho de 2020 Responder
  
  Obrigado Rodrigo! Abraço =)
  
  Luiz Vitor

Casos do Dia a Dia – Entendendo o Problema:

Resumindo:

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

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

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

Conclusão:

Download – Scripts:

Compartilhe isso:

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

Deixe uma resposta para Luiz LimaCancelar resposta