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

E aí Jhow,

Vamos para o último post da série do In-Memory. Hoje vou mostrar alguns erros que podemos encontrar ao utilizar o 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

Vamos nessa!!! #gogogo

In-Memory – Mensagens de Erro:

Vou recapitular alguns erros que já citei nos posts anteriores dessa série do In-Memory. Lembra das limitações que citei no post dos Conceitos Básicos do In-Memory? Vamos ver aqui também alguns erros sobre isso.

Versão SQL Server:

Vou utilizar a seguinte versão para realizar 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: )

In-Memory – CREATE TABLE:

Se você tentar criar uma tabela In-Memory com o DURABILITY = SCHEMA_ONLY e sem um índice ou uma primary key, você vai receber um ERRO. Segue um exemplo:

USE Teste

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

CREATE TABLE dbo.InMemorySchemaWithoutIndex (
    [ID]   INTEGER   NOT NULL ,--  IDENTITY PRIMARY KEY NONCLUSTERED,
    [Message]   VARCHAR(4000)    NOT NULL
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_ONLY);

USE Teste

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

DROP TABLE InMemorySchemaWithoutIndex

CREATE TABLE dbo.InMemorySchemaWithoutIndex (

[ID] INTEGER NOT NULL ,-- IDENTITY PRIMARY KEY NONCLUSTERED,

[Message] VARCHAR(4000) NOT NULL

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_ONLY);

Msg 41327, Level 16, State 7, Line 6

The memory optimized table ‘InMemorySchemaWithoutIndex’ must have at least one index or a primary key.

Msg 1750, Level 16, State 0, Line 6

Could not create constraint or index. See previous errors.

Da mesma forma, se você tentar criar uma tabela In-Memory com o DURABILITY = SCHEMA_AND_DATA e sem uma primary key, você também vai receber um ERRO, mas repare que ele é diferente do anterior. Segue um exemplo:

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

CREATE TABLE dbo.InMemoryDataWithoutIndex (
    [ID]   INTEGER   NOT NULL ,--  IDENTITY PRIMARY KEY NONCLUSTERED,
    [Message]   VARCHAR(4000)    NOT NULL
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA);

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

DROP TABLE InMemoryDataWithoutIndex

CREATE TABLE dbo.InMemoryDataWithoutIndex (

[ID] INTEGER NOT NULL ,-- IDENTITY PRIMARY KEY NONCLUSTERED,

[Message] VARCHAR(4000) NOT NULL

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

Msg 41321, Level 16, State 7, Line 23

The memory optimized table ‘InMemoryDataWithoutIndex’ with DURABILITY=SCHEMA_AND_DATA must have a primary key.

Msg 1750, Level 16, State 0, Line 23

Could not create constraint or index. See previous errors.

Se você tentar criar uma tabela In-Memory em uma database onde o In-Memory não está habilitado, você vai receber outro ERRO. Segue um exemplo:

USE Traces

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

CREATE TABLE dbo.DatabaseWithoutInMemory (
    [ID]   INTEGER   NOT NULL  IDENTITY PRIMARY KEY NONCLUSTERED,
    [Message]   VARCHAR(4000)    NOT NULL
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_ONLY);

USE Traces

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

DROP TABLE DatabaseWithoutInMemory

CREATE TABLE dbo.DatabaseWithoutInMemory (

[ID] INTEGER NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED,

[Message] VARCHAR(4000) NOT NULL

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_ONLY);

Msg 41337, Level 16, State 100, Line 6

Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.

In-Memory – TRUNCATE TABLE:

Se você precisar limpar uma tabela In-Memory e tentar fazer um TRUNCATE TABLE, você vai receber um ERRO, pois essa operação não é suportada no In-Memory. Segue um exemplo:

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

In-Memory – ALTER INDEX – REBUILD / COMPRESSION:

Nesse exemplo, vou criar uma tabela In-Memory chamada “InMemoryExampleCompression” com um índice também.

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

CREATE TABLE dbo.InMemoryExampleCompression (
    [ID]   INTEGER   NOT NULL   IDENTITY,
    [Message]   VARCHAR(4000)    NOT NULL,
 CONSTRAINT PK_InMemoryExampleCompression PRIMARY KEY NONCLUSTERED([ID])
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA);

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

DROP TABLE InMemoryExampleCompression

CREATE TABLE dbo.InMemoryExampleCompression (

[ID] INTEGER NOT NULL IDENTITY,

[Message] VARCHAR(4000) NOT NULL,

CONSTRAINT PK_InMemoryExampleCompression PRIMARY KEY NONCLUSTERED([ID])

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

Depois, vou tentar fazer a Compressão de Dados do meu índice, mas vou receber uma mensagem de erro informando que a operação ALTER INDEX não é suportada no In-Memory.

ALTER INDEX PK_InMemoryExampleCompression ON dbo.InMemoryExampleCompression REBUILD WITH(DATA_COMPRESSION = PAGE)

1	ALTER INDEX PK_InMemoryExampleCompression ON dbo.InMemoryExampleCompression REBUILD WITH(DATA_COMPRESSION = PAGE)

Msg 10794, Level 16, State 12, Line 390

The operation ‘ALTER INDEX’ is not supported with memory optimized tables.

Fazendo uma rápida pesquisa no google, verifiquei que o In-Memory até consegue fazer algumas operações nos índices, mas de uma forma diferente. Segue uma breve explicação:

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

Agora vamos fazer mais um teste. Repare que agora recebemos uma mensagem diferente e ela informa que a opção “data_compression” não é suportada no In-Memory.

ALTER TABLE InMemoryExampleCompression
ALTER INDEX PK_InMemoryExampleCompression REBUILD WITH(DATA_COMPRESSION = PAGE)

1 2	ALTER TABLE InMemoryExampleCompression ALTER INDEX PK_InMemoryExampleCompression REBUILD WITH(DATA_COMPRESSION = PAGE)

Msg 10794, Level 16, State 89, Line 17

The index option ‘data_compression’ is not supported with indexes on memory optimized tables.

In-Memory – CHECKDB / CHECKTABLE

No In-Memory você também não consegue validar a Corrupção de Dados com o CHECKDB. Ao executar o comando abaixo, ele não retorna nenhum erro, contudo, se você procurar o retorno dos objetos que utilizam o In-Memory, você vai encontrar uma mensagem informando que essa operação não é suportada e que o objeto foi ignorado no processamento:

DBCC CHECKDB('Teste')

1	DBCC CHECKDB('Teste')

Object ID 50099219 (object ‘InMemoryExample’): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘Teste’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Agora, se você executar um CHECKTABLE o comando vai falhar e você vai receber uma mensagem parecida com a anterior:

DBCC CHECKTABLE ('InMemoryExample')

1	DBCC CHECKTABLE ('InMemoryExample')

Msg 5296, Level 16, State 1, Line 399

Object ID 50099219 (object ‘InMemoryExample’): The operation is not supported with memory optimized tables. This object has been skipped and will not be processed.

In-Memory – FOREIGN KEY

Se você não sabe o que é uma FOREIGN KEY, segue abaixo uma referência da documentação da Microsoft:

https://docs.microsoft.com/pt-br/sql/relational-databases/tables/create-foreign-key-relationships?view=sql-server-ver15

De forma resumida, FOREIGN KEY são chaves estrangeiras que utilizamos para garantir a integridade dos dados entre duas tabelas.

“Como assim Luiz??? Entendi nada!!! Traduz por favor kkk”

Calma que você vai entender com o próximo exemplo. Já já chegaremos lá.

No SQL Server 2016, algumas limitações do In-Memory foram removidas. Uma delas foi a possibilidade de criar uma Foreign Key entre tabelas In-Memory.

Referências:

https://www.mssqltips.com/sqlservertip/4751/foreign-key-support-in-sql-server-2016-memory-optimized-tables/

https://docs.microsoft.com/pt-br/sql/relational-databases/in-memory-oltp/transact-sql-constructs-not-supported-by-in-memory-oltp?view=sql-server-ver15

Ou seja, no SQL Server 2014 isso ainda não era possível e você teria o erro abaixo:

The feature ‘FOREIGN KEY’ is not supported with memory optimized tables.

FOREIGN KEY -> TESTE

Certo, chega de teoria e vamos testar isso na prática!

Nesse exemplo, vou criar duas tabelas In-Memory, depois uma Foreign Key entre elas e por fim vou inserir alguns dados e testar se vai manter a integridade entre as tabelas.

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

CREATE TABLE dbo.InMemoryTable1 (
    [ID_1]   INTEGER   NOT NULL   IDENTITY,
    [Message]   VARCHAR(4000)    NOT NULL,
	CONSTRAINT PK_InMemoryTable1 PRIMARY KEY NONCLUSTERED([ID_1])
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA);

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

CREATE TABLE dbo.InMemoryTable2 (
    [ID_2]   INTEGER   NOT NULL   IDENTITY,
	[ID_1]   INTEGER   NOT NULL,
    [Message]   VARCHAR(4000)    NOT NULL,
	CONSTRAINT PK_InMemoryTable2 PRIMARY KEY NONCLUSTERED([ID_2])
)
WITH
    (MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA);
	
-- CRIANDO A FOREIGN KEY
ALTER TABLE dbo.InMemoryTable2
ADD CONSTRAINT FK_InMemoryTable2
FOREIGN KEY([ID_1])
REFERENCES InMemoryTable1

INSERT INTO dbo.InMemoryTable1
VALUES('TESTE TABELA 1')

SELECT * FROM dbo.InMemoryTable1

INSERT INTO dbo.InMemoryTable2
VALUES(1, 'TESTE TABELA 2')

SELECT * FROM dbo.InMemoryTable2

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

DROP TABLE InMemoryTable1

CREATE TABLE dbo.InMemoryTable1 (

[ID_1] INTEGER NOT NULL IDENTITY,

[Message] VARCHAR(4000) NOT NULL,

CONSTRAINT PK_InMemoryTable1 PRIMARY KEY NONCLUSTERED([ID_1])

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

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

DROP TABLE InMemoryTable2

CREATE TABLE dbo.InMemoryTable2 (

[ID_2] INTEGER NOT NULL IDENTITY,

[ID_1] INTEGER NOT NULL,

[Message] VARCHAR(4000) NOT NULL,

CONSTRAINT PK_InMemoryTable2 PRIMARY KEY NONCLUSTERED([ID_2])

)

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

-- CRIANDO A FOREIGN KEY

ALTER TABLE dbo.InMemoryTable2

ADD CONSTRAINT FK_InMemoryTable2

FOREIGN KEY([ID_1])

REFERENCES InMemoryTable1

INSERT INTO dbo.InMemoryTable1

VALUES('TESTE TABELA 1')

SELECT * FROM dbo.InMemoryTable1

INSERT INTO dbo.InMemoryTable2

VALUES(1, 'TESTE TABELA 2')

SELECT * FROM dbo.InMemoryTable2

Show! Até aqui tudo certo! Nós conseguimos criar a Foreign Key e inserir os dados normalmente.

Agora vamos fazer um teste para validar a integridade entre as tabelas. Vou tentar inserir um valor “2” para a coluna “ID_1” na tabela “dbo.InMemoryTable2”. Essa coluna referencia a outra tabela “dbo.InMemoryTable1”, contudo, esse valor não existe lá. Então vamos validar se o SQL Server vai permitir ou não essa operação.

INSERT INTO dbo.InMemoryTable2
VALUES(2, 'TESTE INCONSISTENTE TABELA 2')

1 2	INSERT INTO dbo.InMemoryTable2 VALUES(2, 'TESTE INCONSISTENTE TABELA 2')

Msg 547, Level 16, State 0, Line 441

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_DiskBased1”. The conflict occurred in database “Teste”, table “dbo.DiskBased1”, column ‘ID_1’.

Show! Ele deu um erro porque o valor não existe na outra tabela e com isso conseguimos manter a integridade entre as tabelas In-Memory! =)

FOREIGN KEY -> IN-MEMORY x DISK BASED

Agora vamos testar se é possível criar uma FOREIGN KEY entre uma tabela normal e outra com o In-Memory.

-- TABLE DISK-BASED
IF (OBJECT_ID('DiskBasedTable') IS NOT NULL)
	DROP TABLE DiskBasedTable

CREATE TABLE dbo.DiskBasedTable (
    [ID_2]   INTEGER   NOT NULL   IDENTITY,
	[ID_1]   INTEGER   NOT NULL,
    [Message]   VARCHAR(4000)    NOT NULL,
	CONSTRAINT PK_DiskBasedTable PRIMARY KEY NONCLUSTERED([ID_2])
)

-- CRIANDO A FOREIGN KEY
ALTER TABLE dbo.DiskBasedTable
ADD CONSTRAINT FK_DiskBasedTable
FOREIGN KEY([ID_1])
REFERENCES InMemoryTable1

-- TABLE DISK-BASED

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

DROP TABLE DiskBasedTable

CREATE TABLE dbo.DiskBasedTable (

[ID_2] INTEGER NOT NULL IDENTITY,

[ID_1] INTEGER NOT NULL,

[Message] VARCHAR(4000) NOT NULL,

CONSTRAINT PK_DiskBasedTable PRIMARY KEY NONCLUSTERED([ID_2])

)

-- CRIANDO A FOREIGN KEY

ALTER TABLE dbo.DiskBasedTable

ADD CONSTRAINT FK_DiskBasedTable

FOREIGN KEY([ID_1])

REFERENCES InMemoryTable1

Msg 10778, Level 16, State 0, Line 468

Foreign key relationships between memory optimized tables and non-memory optimized tables are not supported.

Msg 1750, Level 16, State 1, Line 468

Could not create constraint or index. See previous errors.

EITA! Não tem como! =(

Pois é! Infelizmente não vamos conseguir manter a integridade dos dados entre uma tabela normal e outra com o In-Memory! Então vamos provar isso!

INSERT INTO dbo.DiskBasedTable
VALUES(666, 'FURANDO A INTEGRIDADE DE DADOS!!!')

SELECT * FROM dbo.InMemoryTable1

SELECT * FROM dbo.DiskBasedTable

INSERT INTO dbo.DiskBasedTable

VALUES(666, 'FURANDO A INTEGRIDADE DE DADOS!!!')

SELECT * FROM dbo.InMemoryTable1

SELECT * FROM dbo.DiskBasedTable

Como podemos ver, conseguimos inserir um dado inconsistente, pois ele não existe na outra tabela! Portanto, tome cuidado com isso! =(

Vamos ver se isso também será corrigido no futuro.

In-Memory – Insufficient Memory:

Agora vamos ver um dos erros mais perigosos que podemos encontrar no In-Memory!

No post anterior “Casos do Dia a Dia – In-Memory OLTP – Use com Moderação – Problema Utilização Memória”, mostrei um caso real do dia a dia onde o In-Memory gerou uma série de problemas na utilização de memória.

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

Traduzindo, o SQL Server está informando que não existe memória suficiente para executar a query. Ferrou!!!

Agora imagine se isso acontecer no seu ambiente de produção no horário de maior movimento, seria um CAOS!!! Por isso eu disse que esse é um dos erros mais perigosos do In-Memory, pois aqui o cliente pode ter prejuízos se não conseguir finalizar as vendas por exemplo.

No primeiro post da série “Casos do Dia a Dia – In-Memory OLTP – Conceitos Básicos” eu falei um pouco sobre o Resource Governor e ele pode ajudar a reduzir um pouco o impacto nesse caso também.

Segue mais algumas informações sobre esse erro de memória:

Referência: http://sqlmagu.blogspot.com/2015/02/erro-there-is-insufficient-system.html

Bora simular isso então!

Inicialmente, vou ajustar o “Max server memory” para 5 GB.

-- AJUSTA O MAX SERVER MEMORY PARA 5 GB APENAS!
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'5000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

-- AJUSTA O MAX SERVER MEMORY PARA 5 GB APENAS!

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

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

RECONFIGURE WITH OVERRIDE

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

Agora vou criar uma tabela In-Memory e depois validar a utilização de memória.

USE Teste

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

CREATE TABLE dbo.InMemoryExample1 (
    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
SET NOCOUNT ON

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

GO

-- 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 
FROM sys.dm_os_performance_counters
WHERE 	
	counter_name = 'Page life expectancy'
	and object_name like '%Buffer Manager%'

GO

USE Teste

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

DROP TABLE InMemoryExample1

CREATE TABLE dbo.InMemoryExample1 (

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

SET NOCOUNT ON

INSERT INTO InMemoryExample1

SELECT TOP 400000

REPLICATE('A',4000)

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

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

FROM sys.dm_os_performance_counters

WHERE

counter_name = 'Page life expectancy'

and object_name like '%Buffer Manager%'

Como podemos ver, estamos utilizando um pouco mais de 3 GB de memória com o In-Memory (MEMORYCLERK_XTP).

Agora vou criar uma segunda tabela e verificar o que vai acontecer.

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

CREATE TABLE dbo.InMemoryExample2 (
    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
SET NOCOUNT ON

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

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

DROP TABLE InMemoryExample2

CREATE TABLE dbo.InMemoryExample2 (

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

SET NOCOUNT ON

INSERT INTO InMemoryExample2

SELECT TOP 300000

REPLICATE('A',4000)

FROM Master.sys.All_Columns ac1,

Master.sys.All_Columns ac2,

Master.sys.All_Columns ac3;

Msg 701, Level 17, State 137, Line 329

There is insufficient system memory in resource pool ‘default’ to run this query.

Como podemos ver, o SQL Server não conseguiu executar a query porque não tinha memória suficiente!

Se você consultar o Error Log também irá encontrar esse erro lá.

Por fim, vou excluir as tabelas In-Memory.

-- EXCLUI AS TABELAS IN-MEMORY
IF(OBJECT_ID('InMemoryExample1') IS NOT NULL)
	DROP TABLE InMemoryExample1

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

-- EXCLUI AS TABELAS IN-MEMORY

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

DROP TABLE InMemoryExample1

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

DROP TABLE InMemoryExample2

In-Memory – Remove Filegroup

Por último, vou mostrar um erro que recebemos ao tentar remover o In-Memory. Como eu mencionei nos posts anteriores, uma vez que o In-Memory é configurado, você não consegue removê-lo!

“Putz Luiz, é sério isso mesmo?”

Até a versão que estou utilizando (citei no início do post) isso realmente não é possível. Vamos testar!

USE [Teste]
GO
ALTER DATABASE [Teste]  REMOVE FILE [Teste_In_Memory]
GO
ALTER DATABASE [Teste] REMOVE FILEGROUP [Teste_In_Memory]
GO

USE [Teste]

ALTER DATABASE [Teste] REMOVE FILE [Teste_In_Memory]

ALTER DATABASE [Teste] REMOVE FILEGROUP [Teste_In_Memory]

Msg 41802, Level 16, State 1, Line 151

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.

O mais estranho é que mesmo excluindo todos os objetos que utilizam o In-Memory, ainda assim você não vai conseguir removê-lo! Que loucura! Pois é, mas a própria Microsoft nos informa que você só consegue remover o In-Memory dropando a database. #medo #chatiado

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/the-memory-optimized-filegroup?view=sql-server-2017

Se você conferir os Filegroups da database, você irá encontrar um para o In-Memory.

Object Explorer -> Botão direito na Database -> Properties -> Filegroups -> Memory Optimized Data

Então, se você dropar a database você vai conseguir remover os arquivos do In-Memory, mas vai perder também TODOS os seus outros dados. =(

USE master
GO
DROP DATABASE Teste

USE master

DROP DATABASE Teste

Commands completed successfully.

Uma outra solução para você se livrar de vez do In-Memory seria:

Criar uma nova database;
Migrar todos os seus dados para essa database (sem utilizar o in-memory);
Dropar a database antiga;
Renomear a nova database com o nome antigo;
Ou seja, você vai ter um trabalho do cão!!!

Portanto, tenha muito cuidado ao implantar isso em produção! Faça testes primeiro e crie quando for realmente útil e necessário, pois é um caminho sem volta. =(

Referências:

https://www.sqlpassion.at/archive/2019/03/19/removing-an-in-memory-oltp-file-group/

https://feedback.azure.com/forums/908035-sql-server/suggestions/32896801-in-memory-oltp-allow-removal-of-filegroup-for-me

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-%20Mensagens%20de%20Erro.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

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

In-Memory – Mensagens de Erro:

In-Memory – CREATE TABLE:

In-Memory – TRUNCATE TABLE:

In-Memory – ALTER INDEX – REBUILD / COMPRESSION:

In-Memory – CHECKDB / CHECKTABLE

In-Memory – FOREIGN KEY

FOREIGN KEY -> TESTE

FOREIGN KEY -> IN-MEMORY x DISK BASED

In-Memory – Insufficient Memory:

In-Memory – Remove Filegroup

Download – Scripts:

2 comentários em “Casos do Dia a Dia – In-Memory OLTP – Mensagens de Erro”

Deixe uma respostaCancelar resposta

In-Memory – Mensagens de Erro:

In-Memory – CREATE TABLE:

In-Memory – TRUNCATE TABLE:

In-Memory – ALTER INDEX – REBUILD / COMPRESSION:

In-Memory – CHECKDB / CHECKTABLE

In-Memory – FOREIGN KEY

FOREIGN KEY -> TESTE

FOREIGN KEY -> IN-MEMORY x DISK BASED

In-Memory – Insufficient Memory:

In-Memory – Remove Filegroup

Download – Scripts:

Compartilhe isso:

2 comentários em “Casos do Dia a Dia – In-Memory OLTP – Mensagens de Erro”

Deixe uma respostaCancelar resposta