Visão geral do OLTP in-memory e cenários de uso

  • Artigo

Aplica-se a:banco de dados SQL Server SQL do Azure Instância Gerenciada de SQL do Azure

In-Memory OLTP é a principal tecnologia disponível em SQL Server e Banco de Dados SQL para otimizar o desempenho de processamento de transações, ingestão de dados, carregamento de dados e cenários de dados transitórios. Este artigo inclui uma visão geral da tecnologia e descreve os cenários de uso do OLTP in-memory. Use essas informações para determinar se o OLTP in-memory é adequado para seu aplicativo. O artigo termina com um exemplo que mostra objetos OLTP in-memory, referências a uma demonstração de desempenho e a recursos que você pode usar para as próximas etapas.

Este artigo aborda a tecnologia OLTP In-Memory em SQL Server e Banco de Dados SQL. Para obter mais informações específicas de dados na memória em SQL do Azure, consulte Otimizar o desempenho usando tecnologias na memória no Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure e Blog: In-Memory OLTP no Banco de Dados SQL do Azure.

Visão geral do OLTP in-memory

O OLTP in-memory pode fornecer excelentes ganhos de desempenho para as cargas de trabalho corretas. Os clientes observaram até 30 vezes de ganho de desempenho em alguns casos. O quanto de ganho você terá depende da carga de trabalho.

De onde vem esse ganho de desempenho? Em essência, In-Memory OLTP melhora o desempenho do processamento de transações tornando o acesso a dados e a execução de transações mais eficientes e removendo a contenção de bloqueio e trava entre transações em execução simultânea. In-Memory OLTP não é rápido porque está na memória; é rápido porque é otimizado em torno dos dados que estão na memória. Os algoritmos de processamento, o acesso e o armazenamento de dados foram reprojetados desde o início para tirar proveito dos aprimoramentos mais recentes da computação na memória e de alta simultaneidade.

Agora, só porque os dados residem na memória não significa que você os perde quando há uma falha. Por padrão, todas as transações são completamente duráveis, o que significa que você tem as mesmas garantias de durabilidade de qualquer outra tabela no SQL Server: como parte da confirmação da transação, todas as alterações são gravadas no log de transações no disco. Se houver uma falha a qualquer momento após a confirmação da transação, seus dados estarão lá quando o banco de dados voltar a ficar online. Além disso, In-Memory OLTP funciona com todos os recursos de alta disponibilidade e recuperação de desastre de SQL Server, como Always On grupos de disponibilidade, Always On Instâncias de Cluster de Failover (SQL Server), backup/restauração etc.

Para usar In-Memory OLTP em seu banco de dados, use um ou mais dos seguintes tipos de objetos:

  • Tabelas com otimização de memória são usadas para armazenar dados do usuário. Você declara uma tabela com otimização de memória no momento da criação.
  • Tabelas não duráveis são usadas para dados transitórios, para armazenar em cache ou para conjuntos de resultados intermediários (substituindo tabelas temporárias tradicionais). Uma tabela não durável é uma tabela com otimização de memória declarada com DURABILITY=SCHEMA_ONLY, o que significa que as alterações nessas tabelas não incorrem em nenhuma E/S. Isso evita o consumo de recursos de E/S de log para casos em que a durabilidade não é uma preocupação.
  • Tipos de tabela com otimização de memória são usados para parâmetros com valor de tabela (TVPs), bem como conjuntos de resultados intermediários em procedimentos armazenados. Eles podem ser usados em vez de tipos de tabela tradicionais. Variáveis de tabela e TVPs que são declarados usando um tipo de tabela com otimização de memória herdam os benefícios de tabelas com otimização de memória não duráveis: acesso a dados eficiente e nenhuma E/S.
  • Módulos T-SQL compilados nativamente são usados para reduzir ainda mais o tempo necessário para uma transação individual, reduzindo os ciclos de CPU necessários para processar as operações. Você declara um módulo Transact-SQL para ser compilado nativamente no momento da criação. No momento, os seguintes módulos T-SQL podem ser compilados nativamente: procedimentos armazenados, gatilhos e funções escalares definidas pelo usuário.

In-Memory OLTP é integrado a SQL Server e Banco de Dados SQL. Como esses objetos se comportam de forma semelhante aos seus equivalentes tradicionais, muitas vezes você pode obter benefícios de desempenho, fazendo apenas alterações mínimas no banco de dados e no aplicativo. Além disso, você pode ter tabelas com otimização de memória e tabelas tradicionais baseadas em disco no mesmo banco de dados, e executar consultas entre as duas. Você encontrará um script Transact-SQL com um exemplo para cada um desses tipos de objetos na parte inferior deste artigo.

Cenários de uso para In-Memory OLTP

In-Memory OLTP não é um botão magic go-fast e não é adequado para todas as cargas de trabalho. Por exemplo, as tabelas com otimização de memória não reduzirão a utilização da CPU se a maioria das consultas estiver realizando agregação em grandes intervalos de dados. Os índices Columnstore ajudam nesse cenário.

Aqui está uma lista de cenários e padrões de aplicativo em que vimos os clientes serem bem-sucedidos com In-Memory OLTP.

Processamento de transações de baixa latência e alta taxa de transferência

Este é o cenário principal para o qual criamos o OLTP in-memory: suporte a grandes volumes de transações, com baixa latência consistente para transações individuais.

Cenários comuns de carga de trabalho são: troca de instrumentos financeiros, apostas esportivas, jogos móveis e entrega de anúncios. Outro padrão comum que vimos é um "catálogo" que é frequentemente lido e/ou atualizado. Um exemplo é quando você tem arquivos grandes, cada um distribuído em vários nós de cluster, e cataloga o local de cada fragmento de cada arquivo em uma tabela com otimização de memória.

Considerações sobre a implementação

Use tabelas com otimização de memória para suas tabelas de transações principais, ou seja, as tabelas com as transações mais críticas de desempenho. Use procedimentos armazenados compilados nativamente para otimizar a execução da lógica associada à transação comercial. Quanto mais da lógica você inserir em procedimentos armazenados no banco de dados, mais benefícios obterá do OLTP in-memory.

Para começar a usar em um aplicativo existente:

  1. Use o relatório de análise de desempenho de transação para identificar os objetos que você deseja migrar.
  2. Use a otimização de memória e consultores de compilação nativa para ajudar na migração.

Ingestão de dados, incluindo a IoT (Internet das coisas)

O OLTP in-memory é bom na ingestão de grandes volumes de dados de várias fontes diferentes ao mesmo tempo. E geralmente é benéfico ingerir dados em um banco de dados SQL Server em comparação com outros destinos, pois SQL Server faz consultas em execução nos dados rapidamente e permite que você obtenha insights em tempo real.

Os padrões comuns de aplicativo são:

  • Ingestão de eventos e leituras do sensor e permissão de notificações, bem como da análise histórica.
  • Gerenciamento de atualizações em lotes, até mesmo de várias fontes, minimizando o impacto sobre a carga de trabalho de leitura simultânea.

Considerações sobre a implementação

Use uma tabela com otimização de memória para a ingestão de dados. Se a ingestão consistir principalmente de inserções (em vez de atualizações) e o espaço de armazenamento do OLTP in-memory dos dados for uma preocupação,

  • Use um trabalho para descarregar regularmente dados em lote para uma tabela baseada em disco com um índice columnstore clusterizado, usando um trabalho que faz INSERT INTO <disk-based table> SELECT FROM <memory-optimized table>; ou
  • Use uma tabela com otimização de memória temporária para gerenciar dados históricos. Neste modo, os dados históricos residem no disco e a movimentação de dados é gerenciada pelo sistema.

O repositório de exemplos do SQL Server contém um aplicativo de grade inteligente que usa uma tabela com otimização de memória temporária, um tipo de tabela com otimização de memória e um procedimento armazenado compilado nativamente, para agilizar a ingestão de dados, ao gerenciar o espaço de armazenamento do OLTP in-memory dos dados de sensor:

Estado de sessão e cache

A tecnologia OLTP In-Memory torna o mecanismo de banco de dados em bancos de dados SQL Server ou SQL do Azure uma plataforma atraente para manter o estado da sessão (por exemplo, para um aplicativo ASP.NET) e para cache.

ASP.NET estado de sessão é um caso de uso bem-sucedido para In-Memory OLTP. Com o SQL Server, um cliente estava prestes a atingir 1,2 milhão de solicitações por segundo. Enquanto isso, eles começaram a usar o OLTP in-memory para fins de armazenamento em cache de todos os aplicativos de camada intermediária na empresa. Detalhes: como o bwin está usando SQL Server 2016 (13.x) In-Memory OLTP para alcançar desempenho e escala sem precedentes

Considerações sobre a implementação

Você pode usar tabelas não duráveis com otimização de memória como um repositório de chave-valor simples armazenando um BLOB em uma coluna varbinary(max). Como alternativa, você pode implementar um cache semiestruturado com suporte a JSON em SQL Server e Banco de Dados SQL. Por fim, você pode criar um cache relacional completo por meio de tabelas não duráveis com um esquema relacional completo, incluindo vários tipos de dados e restrições.

Introdução à otimização de memória ASP.NET estado de sessão usando os scripts publicados no GitHub para substituir os objetos criados pelo provedor de estado de sessão de SQL Server interno: aspnet-session-state

Estudos de caso de cliente

substituição de objeto tempdb

Use tabelas não duráveis e tipos de tabela com otimização de memória para substituir suas estruturas tradicionais tempdb baseadas, como tabelas temporárias, variáveis de tabela e TVPs (parâmetros com valor de tabela).

Variáveis de tabela com otimização de memória e tabelas não duráveis normalmente reduzem a CPU e removem completamente a E/S de log, em comparação a variáveis de tabela tradicionais e à tabela #temp.

Considerações sobre a implementação

Para iniciar, confira: Melhorar o desempenho da tabela temporária e da variável de tabela usando a otimização de memória.

Estudos de caso de cliente

ETL (Extrair, Transformar e Carregar)

Fluxos de trabalho ETL normalmente incluem o carregamento de dados em uma tabela de preparo, transformações de dados e carregamento nas tabelas finais.

Use tabelas com otimização de memória não duráveis para a preparação de dados. Elas removem completamente toda E/S e tornam o acesso aos dados mais eficiente.

Considerações sobre a implementação

Caso realize transformações na tabela de preparo como parte do fluxo de trabalho, você poderá usar procedimentos armazenados compilados nativamente para acelerar essas transformações. Caso possa fazer essas transformações em paralelo, você obterá benefícios adicionais de dimensionamento da otimização de memória.

Exemplo de script

Antes de começar a usar o OLTP in-memory, você precisa criar um grupo de arquivos MEMORY_OPTIMIZED_DATA. Além disso, recomendamos o uso do nível de compatibilidade do banco de dados 130 (ou superior) e a definição da opção de banco de dados MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT como ON.

Você pode usar o script que se encontra no local a seguir para criar o grupo de arquivos na pasta de dados padrão e definir as configurações recomendadas:

O script de exemplo a seguir ilustra In-Memory objetos OLTP que você pode criar em seu banco de dados.

Primeiro comece configurando o banco de dados para In-Memory OLTP.

-- configure recommended DB option
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON;
GO

Você pode criar tabelas com diferentes durabilidades:

-- memory-optimized table
CREATE TABLE dbo.table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON);
GO
-- non-durable table
CREATE TABLE dbo.temp_table1
( c1 INT IDENTITY PRIMARY KEY NONCLUSTERED,
  c2 NVARCHAR(MAX))
WITH (MEMORY_OPTIMIZED=ON,
      DURABILITY=SCHEMA_ONLY);
GO

Você pode criar um tipo de tabela voltada para memória.

-- memory-optimized table type
CREATE TYPE dbo.tt_table1 AS TABLE
( c1 INT IDENTITY,
  c2 NVARCHAR(MAX),
  is_transient BIT NOT NULL DEFAULT (0),
  INDEX ix_c1 HASH (c1) WITH (BUCKET_COUNT=1024))
WITH (MEMORY_OPTIMIZED=ON);
GO

Você pode criar um procedimento armazenado compilado nativamente. Para obter mais informações, confira Como chamar Procedimentos Armazenados Compilados Nativamente de Aplicativos de Acesso a Dados.

-- natively compiled stored procedure
CREATE PROCEDURE dbo.usp_ingest_table1
  @table1 dbo.tt_table1 READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT,
          LANGUAGE=N'us_english')

  DECLARE @i INT = 1

  WHILE @i > 0
  BEGIN
    INSERT dbo.table1
    SELECT c2
    FROM @table1
    WHERE c1 = @i AND is_transient=0

    IF @@ROWCOUNT > 0
      SET @i += 1
    ELSE
    BEGIN
      INSERT dbo.temp_table1
      SELECT c2
      FROM @table1
      WHERE c1 = @i AND is_transient=1

      IF @@ROWCOUNT > 0
        SET @i += 1
      ELSE
        SET @i = 0
    END
  END

END
GO
-- sample execution of the proc
DECLARE @table1 dbo.tt_table1;
INSERT @table1 (c2, is_transient) VALUES (N'sample durable', 0);
INSERT @table1 (c2, is_transient) VALUES (N'sample non-durable', 1);
EXECUTE dbo.usp_ingest_table1 @table1=@table1;
SELECT c1, c2 from dbo.table1;
SELECT c1, c2 from dbo.temp_table1;
GO

Recursos para saber mais

Confira também

Próximas etapas