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

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

O OLTP in-memory é a principal tecnologia disponível no SQL Server e no Banco de Dados SQL para otimizar o desempenho de processamento de transações, de ingestão de dados, de carregamento de dados e de 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 no SQL Server e no Banco de Dados SQL. Para obter mais informações específicas sobre dados na memória no SQL do Azure, confira Otimizar o desempenho usando tecnologias na memória no Banco de Dados SQL do Azure e na Instância Gerenciada de SQL do Azure e o Blog: OLTP in-memory 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, o OLTP in-memory melhora o desempenho do processamento de transações ao tornar o acesso a dados e a execução de transações mais eficientes e ao remover a contenção de bloqueios e travas entre transações com execução simultânea. O OLTP in-memory não é rápido por estar na memória, ele é rápido por ser 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.

Porém, só porque os dados residem na memória, isso não significa que você os perderá quando houver 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, o OLTP in-memory funciona com todas as capacidades de alta disponibilidade e recuperação de desastre do SQL Server, como grupos de disponibilidade Always On, Instâncias de Cluster de Failover Always On (SQL Server), backup/restauração etc.

Para usar o OLTP in-memory no 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 que é declarada com DURABILITY=SCHEMA_ONLY, o que significa que as alterações nessas tabelas não incorrerão em E/S alguma. 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.

O OLTP in-memory é incorporado ao SQL Server e ao Banco de Dados SQL. Além disso, como esses objetos se comportam de forma semelhante a seus equivalentes tradicionais, você muitas vezes pode obter benefícios de desempenho fazendo somente 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 de cada um desses tipos de objetos no final deste artigo.

Cenários de uso para o OLTP in-memory

O OLTP in-memory não é um botão mágico de ação rápida 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.

Veja uma lista de cenários e padrões de aplicativo em que observamos o sucesso de clientes com o OLTP in-memory.

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 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. Com frequência, ele também é útil na ingestão de dados em um banco de dados do SQL Server em comparação com outros destinos, pois o SQL Server agiliza a execução de consultas nos dados 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 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 regularmente descarregar lotes de dados em 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 do SQL Server ou do SQL do Azure uma plataforma atraente para manter o estado de sessão (por exemplo, para um aplicativo ASP.NET) e para armazenamento em cache.

O estado de sessão do ASP.NET é um caso de uso bem-sucedido do OLTP in-memory. 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 a bwin está usando o OLTP in-memory do SQL Server 2016 (13.x) para atingir um desempenho e uma escala sem precedentes

Considerações sobre 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 JSON no SQL Server e no 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.

Comece a usar o estado de sessão do ASP.NET com otimização de memória, com os scripts publicados no GitHub para substituir os objetos criados pelo provedor de estado de sessão interno do SQL Server: aspnet-session-state

Estudos de caso de cliente

Substituição do objeto tempdb

Use tabelas não duráveis e os tipos de tabela com otimização de memória para substituir as estruturas tradicionais baseadas no tempdb, 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 implementação

Para começar, veja: Melhorando 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 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 exemplo de script a seguir ilustra os objetos OLTP in-memory que você pode criar no banco de dados.

Primeiro, comece configurando o banco de dados do OLTP in-memory.

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