SQL Server – Conhecendo as tabelas In-Memory OLTP (Hekaton) Durable vs Non-durable

Visualizações: 879
Tempo de Leitura: 12 minutos

E aí, galera!
Prontos para mais uma dica?

Introdução

Nesse artigo eu gostaria de demonstrar pra vocês, algumas formas de carregar dados de forma rápida e eficiente no banco de dados, utilizando o mínimo de log possível. Isso é especialmente útil para cenários de staging em processos de BI/Data warehouse, onde os dados devem ser carregados rapidamente e uma possível perda de dados é aceitável (pois o processo pode ser refeito em caso de falha).

O intuito desse artigo é comparar a performance das mais diversas formas de inserção de dados, como tabelas temporárias, variáveis do tipo tabela, combinações de recovery_model, tipos de compactação e as tabelas otimizadas para memória (In-Memory OLTP), visando provar o quanto esse recurso é eficiente.

Vale ressaltar que o recurso In-Memory OLTP está disponível desde o SQL Server 2014 e possui significativas melhorias no SQL Server 2016 e SQL Server 2017.

Testes utilizando soluções disk-based

Visualizar conteúdo
Para os testes abaixo, vou inserir 100k registros em cada teste sem join e 10k registros em cada teste com alguns joins. Todos os databases utilizam o mesmo disco SSD (Samsung 850 EVO).

Script base utilizado:

Utilizando tabela física (recovery model FULL)

Nesse teste, vou utilizar uma tabela física com o Recovery Model no FULL.

Resultado:

Utilizando tabela física (recovery model SIMPLE)

Nesse teste, vou utilizar uma tabela física com o Recovery Model no SIMPLE, que gera menos informações na transaction log e teoricamente, deveria entregar uma carga mais rápida.

Resultado:

Utilizando tabela física (recovery model BULK-LOGGED)

Nesse teste, vou utilizar uma tabela física com o Recovery Model no BULK-LOGGED, que é otimizado para processos em lote e cargas de dados.

Resultado:

Utilizando tabela física e DELAYED_DURABILITY (recovery model BULK-LOGGED)

Nesse teste, vou utilizar uma tabela física com o Recovery Model no BULK-LOGGED, que é otimizado para processos em lote e cargas de dados e também o parâmetro DELAYED_DURABILITY = FORCED, que faz com que os eventos de log sejam gravados de força assíncrona (saiba mais sobre esse recurso acessando este link ou esse post aqui).

Resultado:

Utilizando tabela temporária (recovery model SIMPLE)

Muito utilizada para tabelas geradas dinamicamente e processos rápidos, vou realizar o teste inserindo os dados numa tabela #temporária (#Staging_Sem_JOIN e #Staging_Com_JOIN). A minha tempdb está utilizando o Recovery Model SIMPLE.

Resultado:

Utilizando variável do tipo tabela

Muito utilizada para tabelas geradas dinamicamente e processos rápidos, assim como a tabela #temporária, vou realizar o teste inserindo os dados em uma variável do tipo @tabela (@Staging_Sem_JOIN e @Staging_Com_JOIN).

Resultado:

In-Memory OLTP (IMO)

Visualizar conteúdo
Pois bem, durante os testes realizados, observamos que a forma mais rápida de carregar dados para o SQL Server é utilizando as variáveis do tipo tabela ou tabelas temporárias, certo ? Depende da versão do seu SQL Server..

A partir do SQL Server 2014, a Microsoft disponibilizou um recurso chamado In-Memory OLTP (IMO), também conhecido por Hekaton, que permite melhoria de desempenho do processamento de transações e redução do bloqueio de dados através do armazenamento dos dados apenas em memória física.

O motor In-Memory OLTP é projetado para concorrência extremamente alta nas operações OLTP. Para isso, o SQL usa estrutura de dados latch-free com multi-versionamento no controle de concorrência. O resultado é previsível: eliminação da contenção, baixa latência, alto rendimento com escala linear e tudo isso com a garantia de durabilidade do dado. O ganho de desempenho real depende de muitos fatores, mas comumente vemos melhorias na ordem de 5 a 20 vezes.

A maioria dos sistemas especializados, incluindo os de CEP (Complex Event Processing), DW / BI e OLTP, otimizam as estruturas de dados e algoritmos concentrando-se em estruturas na memória! Assim é importante estarmos ligados nas tecnologias que temos à nossa disposição para cada um desses cenários.

Uma forma bem prática de identificar potenciais tabelas ou SP’s candidatas a utilizar o In-Memory é utilizar o Memory Optimization Advisor, que informam sobre quais tabelas em seu banco de dados serão beneficiadas se forem movidas para usar esse recurso.

Para identificar quais tabelas na sua base estão utilizando o In-Memory, você pode utilizar essa consulta:

Para conhecer todas as DMV’s utilizadas pelo In-Memory OLTP, acesse este link.

Para conhecer mais sobre o In-Memory OLTP, recomendo esses dois links:
Documentação oficial
Post da Érika Madeira, parte do time de produto do SQL Server

Implementando o In-Memory OLTP

Visualizar conteúdo

Adicionando suporte ao In-Memory no seu database

Visando testar o quão rápido o In-Memory OLTP pode ser em comparação aos outros métodos utilizados, precisamos antes, adicionar um filegroup ao nosso database otimizado para dados em memória. Isso é um pré-requisito para utilizar o In-Memory.

Para adicionar esse filegroup “especial”, você pode utilizar a interface do SQL Server Management Studio:

Mas na hora de adicionar um arquivo ao filegroup, a interface do SSMS (versão 17.5) não possui suporte a isso ainda, não me mostrando o filegroup de In-Memory que já havia criado, tendo que adicionar o arquivo utilizando comandos T-SQL.

Para adicionar o filegroup e também os arquivos, você pode utilizar o seguinte comando T-SQL:

Reparem que no In-Memory, diferente de um filegroup comum, você não especifica a extensão do arquivo, porque, na verdade, um diretório é criado com vários arquivos hospedados nele.

IMPORTANTE: Até a versão atual (2017), não é possível remover um filegroup do tipo MEMORY_OPTIMIZED_DATA, ou seja, uma vez criado, ele só poderá ser apagado se o banco inteiro for dropado. Por isso, recomendo a criação de um novo database só para as tabelas In-Memory.

In-Memory OLTP: Durable vs Non-durable

Agora que criamos nosso filegroup e adicionamos pelo menos 1 arquivo nele, podemos começar a criar nossas tabelas In-Memory. Antes de começarmos, preciso explicar que existem 2 tipos de tabelas In-Memory:
Durable (DURABILITY = SCHEMA_AND_DATA): Dados e estruturas persistidos no disco. Isso quer dizer que se o servidor ou serviço do SQL for reiniciado, os dados da sua tabela em memória continuarão disponíveis para consulta. Esse é o comportamento padrão das tabelas In-Memory.
Non-durable (DURABILITY = SCHEMA_ONLY): Apenas a estrutura da tabela é persistida no disco e operações de LOG não são geradas. Isso quer dizer que as operações de escrita nesse tipo de tabelas são MUITO mais rápidas. Entretanto, se o servidor ou serviço do SQL for reiniciado, a sua tabela continuará disponível para consultas, para ela estará vazia, pois os dados ficam disponíveis apenas em memória e foram perdidos durante o crash/restart.

Restrições do In-Memory OLTP

Visualizar conteúdo
Uma das restrições de tabelas In-Memory, é a necessidade explítica de existir uma PRIMARY KEY definida na tabela. Caso contrário, você irá receber essa mensagem de erro:

Msg 41321, Level 16, State 7, Line 5
The memory optimized table ‘Staging_Sem_JOIN’ with DURABILITY=SCHEMA_AND_DATA must have a primary key.
Msg 1750, Level 16, State 0, Line 5
Could not create constraint or index. See previous errors.

Outra restrição óbvia do In-Memory OLTP é com relação à quantidade de memória do servidor. Para que esse recurso funcione bem, você precisa avaliar muito bem quais tabelas são boas candidatas para ficarem armazenadas em memória. Se você tem uma tabela de 50 GB e 32 GB de RAM, não vai ser possível colocar uma tabela dessas armazenada em memória né.. Além disso, se você começar a armazenar tabelas muito grandes em memória, isso pode prejudicar a performance geral da instância e do servidor como um todo. O ideal para utilização de In-Memory é para cargas de DW ou tabelas pequenas e muito acessadas.

Caso você tente alocar tabelas muito grandes em memória, poderá se deparar com essa mensagem de erro:

The statement has been terminated.
Msg 701, Level 17, State 103, Line 63
There is insufficient system memory in resource pool ‘default’ to run this query.

Ainda nas restrições para se utilizar tabelas In-Memory, podemos listar as triggers de DDL. Caso você tenha criado server triggers ou database triggers para auditoria, como demonstrei no post Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server, você irá receber essa mensagem de erro:

Msg 12332, Level 16, State 111, Line 5
Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

A solução para esse caso, é remover a server triggers e criar essa triggers em todos os databases que não contenham tabelas In-Memory. De preferência, crie um database só para suas tabelas In-Memory.

Na versão 2014, todas as colunas string que faziam parte do índice non-clustered deveriam possuir a collation *_BIN2. A partir do SQL Server 2016, essa restrição não existe mais.

Outras restrições:

Recurso / Limitação
SQL Server 2014SQL Server 2016 CTP2
Tamanho máximo de memória utilizado
Recomentação (não é um limite definido): 256 GB
Recomentação (não é um limite definido): 2TB
Suporte a collation
Colunas do tipo string que fazem parte do índice ou comparações/ordenações em módulos natively-compiled devem utilizar a collation *_BIN2.Todas as collations são suportadas
Alterações em tabelas memory-optimized após a criaçãoNão suportadoSuportado
Alterações em natively-compiled stored procedures
Não suportadoSuportado
Plano paralelo para operações acessando tabelas memory-optimized
Não suportadoSuportado
Transparent Data Encryption (TDE)Não suportadoSuportado
Uso dos comandos abaixo em stored procedures natively-compiled:
  • LEFT e RIGHT OUTER JOIN

  • SELECT DISTINCT

  • operadores OR e NOT

  • Subqueries

  • Chamadas aninhadas de Stored procedures (Nested stored procedure calls)

  • UNION e UNION ALL

  • Funções matemáticas

Não suportadoSuportado
Triggers de DML em tabelas memory-optimized
Não suportadoSuportado (AFTER triggers, natively-compiled)
Multiple Active Result Sets (MARS)
Não suportadoSuportado
Large Objects (LOBs):
  • varchar(max)

  • nvarchar(max)

  • varbinary(max)

Não suportadoSuportado
Offline Checkpoint Threads
1Várias threads
Natively-compiled, scalar user-defined functions
Não suportadoSuportado
Índices em colunas que aceitam valor NULL (NULLable columns)
Não suportadoSuportado

Para visualizar todas as restrições das tabelas In-Memory, dê uma olhada nesse link aqui.

Testes com tabelas In-Memory OLTP

Visualizar conteúdo
O script que utilizei para criar os testes utilizando as tabelas In-Memory OLTP é esse:

Notem que especifiquei o BUCKET_COUNT da Primary Key HASH do mesmo tamanho da quantidade de registros. Essa informação é importante para avaliar a quantidade de memória necessária para alocar uma tabela, conforme podemos aprender mais no artigo desse link aqui. O número ideal é igual a quantidade registros distintos da tabela original para processos temporários de carga (ETL) ou de 2x a 5x esse número para tabelas transacionais.

In-Memory OLTP: Durable

Nesse exemplo, vou criar as tabelas utilizando o tipo Durable (DURABILITY = SCHEMA_AND_DATA) e veremos se o ganho de performance na inserção dos dados é tão eficiente assim.

Resultado:

No print acima, ficou demonstrado que o tempo medido para o insert sem join não foi muito satisfatório, ficando bem atrás do insert em tabela @variavel e tabela #temporaria, enquanto o tempo com os JOINS foi o melhor medido até agora, mas não foi nada surpreendente.

In-Memory OLTP: Non-Durable

Nesse exemplo, vou criar as tabelas utilizando o tipo Non-Durable (DURABILITY = SCHEMA_ONLY) e veremos se o ganho de performance na inserção dos dados é tão eficiente assim.

Resultado:

Aqui conseguimos encontrar um resultado bem interessante, com o menor tempo com o JOIN e o 2º menor tempo sem o JOIN.

In-Memory OLTP: Non-Durable (tudo em memória)

Por fim, vou tentar diminuir o tempo da carga com os joins, criando todas as tabelas envolvidas para memória e testar se isso vai nos dar um bom ganho de performance.

Script utilizado:

Resultado:

Conclusão

Nos testes acima, ficou claro que, para esse cenário, a In-Memory OLTP acaba sendo a melhor solução, tanto para o exemplo mais simples, somente inserindo os dados, quanto inserindo dados com joins.

Resumo dos testes:

* Teste 1 = Apenas INSERT / Teste 2 = INSERT com JOINS

Se compararmos os resultados com as tabelas físicas, o resultado é bem expressivo e um grande incentivo para a sua utilização em cenários de BI, até porque, nos exemplos apresentados, as tabelas eram de apenas 100k registros numa VM com 4 cores e 8GB de RAM.

A tendência é que quanto melhor o hardware e maior o volume de dados, maior será a diferença de performance entre as tabelas físicas e tabelas em memória. Entretanto, o resultado não foi tão expressivo quando se comparado à variável do tipo tabela, por exemplo, o que até faz sentido, pois as 2 são armazenadas completamente na memória.

É claro que uma tabela In-Memory possui várias vantagens sobre a variável do tipo tabela, especialmente a vida útil, já que a variável do tipo tabela só está disponível durante a execução do batch e a tabela In-Memory fica disponível enquanto o serviço do SQL Server continuar ativo.

Como não fiquei convencido com os resultados dos testes, resolvi aumentar o volume dos dados. Ao invés de 100k, que tal inserir lotes de 20 registros, totalizando 1 milhão de registros inseridos por teste e repetindo mais 2x para cada forma de avaliação?

Vamos aos resultados:

Nesse artigo, eu demonstrei apenas o seu potencial para escrita, mas o In-Memory OLTP possui uma performance muito boa para leitura também, especialmente se a tabela é muito acessada.

Espero que vocês tenham gostado desse post. Se você não conhecia o In-Memory OLTP, espero ter demonstrado um pouco do potencial dessa excelente feature do SQL Server.

Um abraço e até a próxima!