Pesquisa de áreas iniciais em OLTP in-memory

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Este artigo é para o desenvolvedor que está com pressa para aprender os fundamentos dos recursos de desempenho do OLTP in-memory do Microsoft SQL Server e do Banco de Dados SQL do Azure.

Para o OLTP in-memory, este artigo fornece o seguinte:

  • Explicações rápidas dos recursos.
  • Exemplos do código principal que implementam os recursos.

O SQL Server e o Banco de dados SQL têm apenas pequenas variações no suporte de tecnologia in-memory.

No mundo real, os blogueiros se referem ao OLTP in-memory como Hekaton.

Benefícios dos recursos In-Memory

O SQL Server fornece recursos In-Memory que podem melhorar muito o desempenho de vários sistemas de aplicativos. As considerações mais simples estão descritas nesta seção.

Recursos para OLTP (Processamento de Transações Online)

Sistemas que devem processar números grandes de INSERTs do SQL simultaneamente são excelentes candidatos para recursos do OLTP.

  • Nossos parâmetros de comparação mostram que é possível obter melhorias de velocidade de 5 a 20 vezes mais rápidas pela adoção de recursos In-Memory.

Sistemas que processam cálculos pesados no Transact-SQL são excelentes candidatos.

  • Um procedimento armazenado que é dedicado a cálculos pesados pode ser executado até 99 vezes mais rápido.

Posteriormente, você pode visitar os seguintes artigos que oferecem demonstrações de ganhos de desempenho do OLTP in-memory:

Recursos de análise operacional

A análise in-memory refere-se aos SELECTs do SQL, que agregam dados transacionais, normalmente pela inclusão de uma cláusula GROUP BY. O tipo de índice chamado columnstore é central para análise operacional.

Há dois cenários principais:

  • Análise operacional em lote refere-se aos processos de agregação que são executados após o horário comercial ou no hardware secundário, que tem cópias dos dados transacionais.
  • Análise operacional em tempo real refere-se aos processos de agregação executados durante o horário comercial e no hardware principal usado em cargas de trabalho transacionais.

O presente artigo se concentra em OLTP e não em Análises. Para obter informações sobre como os índices columnstore trazem as Análises para o SQL, confira:

columnstore

Uma sequência de postagens de blog excelente explica elegantemente os índices columnstore de várias perspectivas. A maioria das postagens descreve o conceito de análise operacional em tempo real, à qual o columnstore dá suporte. Essas postagens foram criadas por Sunil Agarwal, gerente de programa da Microsoft, em março de 2016.

Análise operacional em tempo real

  1. Análise operacional em tempo real usando a tecnologia in-memory
  2. Análise operacional em tempo real – Visão geral do NCCI (índice columnstore não clusterizado)
  3. Análise operacional em tempo real: exemplo simples usando NCCI (índice columnstore não clusterizado) no SQL Server 2016
  4. Análise operacional em tempo real: operações DML e NCCI (índice columnstore não clusterizado) no SQL Server 2016
  5. Análise operacional em tempo real: NCCI (índice columnstore não clusterizado) filtrado
  6. Análise operacional em tempo real: opção de atraso na compactação para NCCI (índice columnstore não clusterizado)
  7. Análise operacional em tempo real: opção de atraso na compactação com NCCI e desempenho
  8. Análise operacional em tempo real: tabelas com otimização de memória e índice columnstore

Desfragmentar um índice columnstore

  1. Desfragmentação de índice columnstore usando o comando REORGANIZE
  2. Política de mesclagem do índice columnstore para REORGANIZE

Importação em massa de dados

  1. Repositório de colunas clusterizado: carregamento em massa
  2. Índice columnstore clusterizado: otimizações de carregamento de dados - registro em log mínimo
  3. Índice columnstore clusterizado: otimização de carregamento de dados - importação paralela em massa

Recursos do OLTP in-memory

Vejamos os principais recursos do OLTP in-memory.

Tabelas com otimização de memória

A palavra-chave do T-SQL MEMORY_OPTIMIZED, na instrução CREATE TABLE, é como a tabela é criada para existir na memória ativa em vez de no disco.

Uma Tabela com otimização de memória tem uma representação de si mesma na memória ativa e na cópia secundária no disco.

  • A cópia em disco é destinada para a recuperação da rotina depois de uma reinicialização após o desligamento do servidor ou do banco de dados. Essa dualidade de memória mais disco é completamente oculta para você e para seu código.

Módulos compilados nativamente

A palavra-chave do T-SQL NATIVE_COMPILATION, na instrução CREATE PROCEDURE, é como um procedimento armazenado compilado de forma nativa é criado. As instruções T-SQL são compiladas para código de computador no primeiro uso do proc nativo toda vez que o banco de dados é alternado online. As instruções T-SQL não suportam mais a interpretação lenta de cada instrução.

  • Vimos o resultado da compilação nativa em durações que tem de uma a 100 vezes a duração interpretada.

Um módulo nativo pode fazer referência apenas às tabelas com otimização de memória e não podem fazer referência às tabelas baseadas em disco.

Há três tipos de módulos compilados nativamente:

Disponibilidade no Banco de Dados SQL do Azure

O OLTP in-memory e o Columnstore estão disponíveis no banco de dados SQL. Para obter detalhes, veja Otimizar o desempenho usando tecnologias in-memory no banco de dados SQL.

1. Garantir o nível de compatibilidade >= 130

Essa seção inicia uma sequência de sessões numeradas que, juntas, demonstram a sintaxe do Transact-SQL que pode ser usada para implementar recursos do OLTP in-memory.

Primeiro, é importante que seu banco de dados seja definido para um nível de compatibilidade de pelo menos 130. Next é o código do T-SQL para exibir o nível de compatibilidade efetivo para o qual seu banco de dados atual está definido.

SELECT d.compatibility_level
    FROM sys.databases as d
    WHERE d.name = Db_Name();

Next é o código do T-SQL para atualizar o nível, se necessário.

ALTER DATABASE CURRENT
    SET COMPATIBILITY_LEVEL = 130;

2. Elevar para o INSTANTÂNEO

Quando uma transação envolve tanto uma tabela baseada em disco quanto uma tabela com otimização de memória, chamamos esse procedimento de transação entre contêineres. Em tal transação, é essencial que a porção com otimização de memória da transação opere no nível de isolamento da transação chamado INSTANTÂNEO.

Para impor com segurança este nível para as tabelas com otimização de memória em uma transação entre contêineres, altere sua configuração de banco de dados executando o T-SQL a seguir.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Criar um GRUPO DE ARQUIVOS otimizado

No Microsoft SQL Server, antes de criar uma tabela com otimização de memória, você deve primeiro criar um grupo de arquivos que você declare como CONTAINS MEMORY_OPTIMIZED_DATA. O GRUPO DE ARQUIVOS é atribuído ao seu banco de dados. Para obter detalhes, confira:

No Banco de Dados SQL do Azure, você não precisa, e não pode, criar um GRUPO DE ARQUIVOS.

O exemplo de script T-SQL a seguir habilita um banco de dados para OLTP in-memory e define todas as configurações recomendadas. Ele funciona com o SQL Server e o Banco de Dados SQL do Azure: enable-in-memory-oltp.sql.

Observe que nem todos os recursos do SQL Server têm o suporte para os bancos de dados com um grupo de arquivos MEMORY_OPTIMIZED_DATA. Para obter mais detalhes sobre as limitações, consulte Recursos do SQL Server sem suporte no OLTP in-memory

4. Criar uma tabela com otimização de memória

A palavra-chave fundamental do Transact-SQL é MEMORY_OPTIMIZED.

CREATE TABLE dbo.SalesOrder
    (
        SalesOrderId   integer   not null   IDENTITY
            PRIMARY KEY NONCLUSTERED,
        CustomerId   integer    not null,
        OrderDate    datetime   not null
    )
        WITH
            (MEMORY_OPTIMIZED = ON,
            DURABILITY = SCHEMA_AND_DATA);

As instruções INSERT e SELECT do Transact-SQL em relação a uma tabela com otimização de memória são as mesmas que para uma tabela normal.

ALTER TABLE para tabelas com otimização de memória

ALTER TABLE...ADD/DROP pode adicionar ou remover uma coluna de uma tabela com otimização de memória ou de um índice.

Planejar suas tabelas com otimização de memória

5. Criar um procedimento armazenado compilado de modo nativo (proc. nativo)

A palavra-chave fundamental é NATIVE_COMPILATION.

CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
        @_CustomerId   INT  
        WITH  
            NATIVE_COMPILATION,  
            SCHEMABINDING  
    AS  
    BEGIN ATOMIC  
        WITH  
            (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
            LANGUAGE = N'us_english')  
      
        DECLARE @SalesOrderId int, @OrderDate datetime;
      
        SELECT TOP 1  
                @SalesOrderId = s.SalesOrderId,  
                @OrderDate    = s.OrderDate  
            FROM dbo.SalesOrder AS s  
            WHERE s.CustomerId = @_CustomerId  
            ORDER BY s.OrderDate DESC;  
      
        RETURN @SalesOrderId;  
    END;

A palavra-chave SCHEMABINDING indica que as tabelas referidas no procedimento nativo não podem ser descartadas, a menos que o proc. nativo seja descartado primeiro. Para obter detalhes, confira Criando procedimentos armazenados compilados de modo nativo.

Observe que você não precisa criar um procedimento armazenado compilado de modo nativo para acessar uma tabela com otimização de memória. Você também pode fazer referência a tabelas com otimização de memória dos procedimentos armazenados e lotes ad hoc tradicionais.

6. Executar o proc. nativo

Preencha a tabela com duas linhas de dados.

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),
        ( 42, '2015-01-15 15:35:59' );

Uma chamada do tipo EXECUTAR para o procedimento armazenado compilado de modo nativo é reproduzida.

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);
      
EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;
      
SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);
PRINT @mesg;

Esta é a saída real de PRINT:

-- 2 = Latest SalesOrderId, for CustomerId = 42

Guia para a documentação e próximas etapas

Os exemplos simples anteriores oferecem uma base para aprender os recursos mais avançados do OLTP in-memory. As seções a seguir são um guia para as considerações especiais que talvez você precise saber e onde você pode conferir os detalhes sobre cada uma.

Como os recursos do OLTP in-memory funcionam com muito mais rapidez?

As subseções a seguir descrevem brevemente como os recursos do OLTP in-memory funcionam internamente para fornecer um desempenho aprimorado.

Como as tabelas com otimização de memória têm um desempenho mais rápido?

Natureza dupla: uma tabela com otimização de memória tem uma natureza dupla: uma representação na memória ativa e a outra no disco rígido. Cada transação está comprometida com ambas as representações da tabela. As transações operam contra a muito mais rápida representação de memória ativa. As tabelas com otimização de memória se beneficiam da maior velocidade da memória ativa em comparação ao disco. Além disso, a maior agilidade da memória ativa torna prática uma estrutura de tabela mais avançada, que é otimizada para a velocidade. A estrutura avançada também não tem páginas, por isso evita a sobrecarga e a contenção de travas e spinlocks.

Nenhum bloqueio: a tabela com otimização de memória se baseia em uma abordagem otimista para os objetivos concorrentes de integridade de dados em relação à simultaneidade e alta taxa de transferência. Durante a transação, a tabela não coloca bloqueios em qualquer versão de linhas atualizadas dos dados. Isso pode reduzir significativamente a contenção em alguns sistemas de alto volume.

Versões de linha: em vez de bloqueios, a tabela com otimização de memória adiciona uma nova versão de uma linha atualizada na tabela em si, não no tempdb. A linha original é mantida até que a transação seja confirmada. Durante a transação, outros processos podem ler a versão original da linha.

  • Quando várias versões de uma linha são criadas para uma tabela baseada em disco, as versões de linha são armazenadas temporariamente no tempdb.

Menos registros em log: as versões anteriores e posteriores das linhas atualizadas são mantidas na tabela com otimização de memória. O par de linhas fornece grande parte das informações que tradicionalmente são gravadas no arquivo de log. Isso permite que o sistema grave menos informações, e com menos frequência, no log. No entanto, a integridade transacional é garantida.

Como os proc. nativos têm um desempenho mais rápido?

A conversão de um procedimento regular armazenado e interpretado para um procedimento armazenado compilado de modo nativo reduz significativamente o número de instruções a serem realizadas durante o tempo de execução.

Vantagens e desvantagens dos recursos In-Memory

Como é comum na ciência da computação, os ganhos de desempenho fornecidos pelos recursos de memória são uma compensação. Os melhores recursos trazem benefícios que são mais valiosos do que os custos extras do recurso. Você pode encontrar diretrizes abrangentes sobre as vantagens e desvantagens em:

O restante desta seção lista algumas das principais considerações sobre planejamento e vantagens e desvantagens.

Vantagens e desvantagens das tabelas com otimização de memória

Estimar memória: você deve estimar a quantidade de memória ativa que sua tabela com otimização de memória consumirá. Seu sistema de computador deve ter capacidade de memória suficiente para hospedar uma tabela com otimização de memória. Para obter detalhes, confira:

Dividir a tabela grande em partes: uma forma de atender à demanda de muita memória ativa é dividir a tabela grande em partes na memória, que armazenam as linhas de dados ativas recentes em relação às outras partes do disco que armazenam as linhas herdadas inativas (como pedidos de venda que foram totalmente enviados e concluídos). Esse particionamento é um processo manual de design e implementação. Consulte:

Vantagens e desvantagens dos proc. nativos

  • Um procedimento armazenado compilado de modo nativo não pode acessar uma tabela baseada em disco. Um proc. nativo pode acessar somente as tabelas com otimização de memória.
  • Quando um processo nativo for executado pela primeira vez depois que o servidor ou o banco de dados tiver sido colocado online novamente, o proc. nativo deverá ser recompilado uma vez. Isso causa um atraso antes do proc. nativo começar a ser executado.

Considerações avançadas das tabelas com otimização de memória

OsÍndices para as tabelas com otimização de memória são diferentes de algumas maneiras dos índices em tabelas em disco tradicionais. Os Índices de Hash estão disponíveis apenas em tabelas com otimização de memória.

Você deve planejar para garantir que haverá memória ativa suficiente para sua tabela planejada de otimização de memória e seus índices. Consulte:

Uma tabela com otimização de memória pode ser declarada com DURABILITY = SCHEMA_ONLY:

  • Essa sintaxe informa ao sistema para descartar todos os dados da tabela com otimização de memória quando o banco de dados é colocado offline. Somente a definição da tabela é persistida.
  • Quando o banco de dados for colocado online novamente, a tabela com otimização de memória será carregada outra vez na memória ativa, vazia de dados.
  • Tabelas SCHEMA_ONLY podem ser uma excelente alternativa às tabelas de #temporary em tempdb, quando vários milhares de linhas estão envolvidos.

Variáveis de tabela também podem ser declaradas como com otimização de memória. Consulte:

Considerações avançadas dos módulos compilados de modo nativo

Os tipos de módulos compilados de modo nativo disponíveis por meio do Transact-SQL são:

Uma UDF (função definida pelo usuário) compilada nativamente é executada muito mais rapidamente do que uma UDF interpretada. Aqui estão alguns pontos a considerar com UDFs:

  • Quando um T-SQL SELECT usa um UDF, o UDF é sempre chamado uma vez por linha retornada.
    • As UDFs nunca são executadas de forma embutida, em vez disso, sempre são chamadas.
    • A diferença compilada será menos significativa do que a sobrecarga de chamadas repetidas inerentes a todas as UDFs.
    • Ainda assim, a sobrecarga de chamadas a UDF geralmente é aceitável no nível de prática.

Para dados de teste e explicação sobre o desempenho dos UDFs nativos, consulte:

Guia de documentação das tabelas com otimização de memória

Consulte estes outros links que abordam considerações especiais para tabelas com otimização de memória:

Guia de documentação dos proc. nativos

O seguinte artigo e seus artigos filho no sumário, explicam os detalhes sobre procedimentos armazenados compilados nativamente.

Aqui estão os artigos que oferecem o código para demonstrar os ganhos de desempenho que você pode obter usando OLTP in-memory: