Otimizar o desempenho usando tecnologias In-Memory no Banco de Dados SQL do Azure

Aplica-se a: Banco de Dados SQL do Azure

As tecnologias In-Memory permitem melhorar o desempenho do seu aplicativo e reduzem potencialmente o custo do banco de dados.

Quando usar tecnologias In-Memory

Ao usar tecnologias In-Memory, obtenha melhorias de desempenho com várias cargas de trabalho:

• Transacional (OLTP [processamento transacional online]) em que a maioria das solicitações lê ou atualiza o menor conjunto de dados, por exemplo, operações de criação/leitura/atualização/exclusão (CRUD).
• Analítico (OLAP [processamento analítico online]) em que a maioria das consultas tem cálculos complexos para fins de relatório e também processos agendados regularmente que executam operações de carregamento (ou carregamento em massa) e/ou gravam alterações de dados em tabelas existentes. Muitas vezes, as cargas de trabalho OLAP são atualizadas periodicamente a partir de cargas de trabalho OLTP.
• Mista (HTAP (processamento analítico/transacional híbrido)) em que as consultas OLTP e OLAP são executadas no mesmo conjunto de dados.

Tecnologias In-Memory podem melhorar o desempenho dessas cargas de trabalho, mantendo os dados que devem ser processados na memória, usando a compilação nativa das consultas, ou processamento avançado, tal como processamento em lotes e instruções SIMD que estão disponíveis no hardware subjacente.

Visão geral

O Banco de Dados SQL do Azure oferece suporte às seguintes tecnologias in-memory:

• OLTP In-Memory aumenta o número de transações por segundo o e reduz a latência para o processamento de transações. Os cenários que se beneficiam do OLTP In-Memory são: processamento de transações de alta taxa de transferência, como comércio e jogos, ingestão de dados de eventos ou dispositivos IoT, cache, carregamento de dados e cenários de variáveis de tabela e tabelas temporárias.
• Os índices columnstore clusterizados reduzem seu volume de armazenamento (em até 10 vezes) e melhoram o desempenho de relatórios e consultas de análise. Você pode usá-lo com tabelas de fatos em data marts para colocar mais dados no banco de dados e melhorar o desempenho. Além disso, também é possível usá-lo com os dados históricos no banco de dados operacional para arquivar e conseguir consultar até 10 vezes mais dados.
• Índices columnstore não clusterizados para HTAP ajudam a obter análises em tempo real sobre seus negócios consultando o banco de dados operacional diretamente, sem a necessidade de executar um processo ETL (extração, transformação e carregamento) caro e aguardar o data warehouse ser populado. Índices columnstore não clusterizados permitem a execução rápida das consultas de análise no banco de dados OLTP, enquanto reduzem o impacto sobre a carga de trabalho operacional.
• Índices columnstore clusterizados com otimização de memória para HTAP permitem a você executar o processamento de transações com muita rapidez e executar simultaneamente consultas de análise muito rapidamente sobre os mesmos dados.

Os índices columnstore e o OLTP in-memory foram introduzidos no SQL Server em 2012 e 2014, respectivamente. O Banco de Dados SQL do Azure, a Instância Gerenciada de SQL do Azure e o SQL Server compartilham a mesma implementação de tecnologias In-Memory.

Benefícios da tecnologia In-Memory

Devido ao processamento de transações e consulta mais eficientes, as tecnologias In-Memory também ajudam a reduzir os custos. Você normalmente não precisa atualizar o tipo de preço do banco de dados para obter ganhos de desempenho. Em alguns casos, você mesmo poderá até mesmo reduzir o tipo de preço e ainda continuar a ver melhorias de desempenho com as tecnologias na memória.

Usando o OLTP In-Memory, a Quorum Business Solutions foi capaz de duplicar a carga de trabalho, melhorando as DTUs em 70%. Para obter mais informações, confira OLTP in-memory no Banco de Dados SQL do Azure.

Este artigo descreve aspectos do OLTP In-Memory e dos índices Columnstore específicos ao Banco de Dados SQL do Azure, além de incluir exemplos que permitem perceber:

• O impacto dessas tecnologias no armazenamento e dos limites de tamanho dos dados.
• Como gerenciar a movimentação dos bancos de dados que utilizam essas tecnologias entre os diferentes tipos de preço.
• Um uso ilustrativo do OLTP in-memory e os índices columnstore.

Para saber mais sobre tecnologias in-memory no SQL Server, confira:

• Visão geral e cenários de uso do OLTP In-Memory (incluindo referências a estudos de caso de cliente e informações para começar)
• Documentação para OLTP in-memory
• Guia de Índices columnstore
• HTAP (Processamento Transacional e Analítico Híbrido), também conhecido como análise operacional em tempo real

OLTP na memória

A tecnologia OLTP in-memory fornece operações de acesso de dados, extremamente rápidas, mantendo todos os dados na memória. Ela também usa índices especializados, compilação nativa de consultas e acesso de dados sem bloqueio para melhorar o desempenho da carga de trabalho OLTP. Há duas maneiras de organizar seus dados OLTP In-Memory:

• Formato rowstore com otimização de memória , em que cada linha é um objeto de memória separado. Esse é um formato clássico de OLTP In-Memory otimizado para cargas de trabalho OLTP de alto desempenho. Há dois tipos de tabelas com otimização de memória que podem ser usados no formato rowstore com otimização de memória:

  • Tabelas duráveis (SCHEMA_AND_DATA) em que as linhas colocadas na memória são preservadas após reiniciar o servidor. Esse tipo de tabelas se comporta como uma tabela rowstore tradicional com os benefícios adicionais de otimizações de memória.
  • Tabelas não duráveis (SCHEMA_ONLY) em que as linhas não são preservadas após o reinício. Esse tipo de tabela foi projetado para dados temporários (por exemplo, substituição de tabelas temporárias), ou tabelas em que você precisa carregar rapidamente os dados antes de movê-los para alguma tabela persistente (denominadas tabelas de preparo).

• Formato columnstore com otimização de memória em que os dados são organizados em um formato de coluna. Essa estrutura é projetada para cenários HTAP em que você precisa executar consultas analíticas na mesma estrutura de dados onde a carga de trabalho OLTP está em execução.

• Um guia rápido sobre In-Memory OLTP: Início Rápido 1: tecnologias In-Memory OLTP para desempenho mais rápido de T-SQL.

Tamanho dos dados e limite de armazenamento do OLTP in-memory

O OLTP in-memory inclui tabelas com otimização de memória, que são usadas para armazenar dados do usuário. Essas tabelas precisam caber na memória. Cada objetivo de serviço tem uma cota ou limite de memória para tabelas com otimização de memória. Esse limite é conhecido como armazenamento OLTP in-memory.

Cada objetivo de serviço de banco de dados individual e cada objetivo de serviço de pool elástico incluem determinada quantidade de armazenamento OLTP in-memory.

• Limites de recursos baseados em DTU – bancos de dados individuais
• Limites de recurso baseados em DTU – pools elásticos
• Limites de modelo de recursos baseado em vCore – Banco de dados individual
• Limites de recurso baseados em vCore – pools elásticos

Os itens a seguir contam para seu limite de armazenamento do OLTP in-memory:

• Linhas de dados de usuário ativo em tabelas com otimização de memória e variáveis de tabela. As versões de linha antigas não entram na contagem do limite.
• Índices em tabelas com otimização de memória.
• Custo operacional das operações ALTER TABLE.

Se atingir o limite, você receberá um erro de limite de cota atingido e não conseguirá inserir ou atualizar os dados. Para atenuar esse erro, exclua dados ou aumente o objetivo de serviço preço do banco de dados ou do pool elástico.

Para obter detalhes sobre como monitorar a utilização do armazenamento do OLTP in-memory e configurar alertas quando estiver perto de atingir o limite, consulte Monitorar o armazenamento in-memory.

Sobre pools elásticos

Com os pools elásticos, o armazenamento do OLTP in-memory é compartilhado entre todos os bancos de dados no pool. Portanto, o uso de um banco de dados pode afetar outros bancos de dados. As duas mitigações para esse problema são:

• Configure um Max eDTU ou Max vCore para bancos de dados que sejam inferiores à contagem eDTU ou vCore para o pool como um todo. Esse máximo também limita proporcionalmente a utilização do armazenamento OLTP in-memory em qualquer banco de dados no pool.
• Configure um Min eDTU ou Min vCore que seja maior que 0. Esse mínimo garante que cada banco de dados no pool tenha a quantidade de armazenamento de OLTP In-Memory disponível que corresponde ao configurado Min eDTU ou Min vCore.

Alterar camadas de serviço dos bancos de dados que usam tecnologias de OLTP in-memory

Não há suporte para o OLTP In-Memory em bancos de dados nas camadas de serviço Uso Geral, Standard e Básico do Banco de Dados SQL do Azure. Além disso, não é possível escalar um banco de dados que tenha objetos OLTP In-Memory para uma dessas camadas. Para dimensionar um banco de dados para uma dessas camadas de serviço, remova todas as tabelas e os tipos de tabela com otimização de memória, bem como todos os módulos T-SQL compilados nativamente, ou converta-os em objetos baseados em disco e módulos T-SQL regulares.

Ao reduzir verticalmente um banco de dados Comercialmente Crítico ou Premium, os dados nas tabelas com otimização de memória precisam caber no armazenamento OLTP in-memory disponível no objetivo de serviço de destino do banco de dados ou pool elástico. Se você tentar reduzir o banco de dados ou o pool elástico verticalmente, ou mover um banco de dados para um pool elástico, e o objetivo de serviço de destino não tiver armazenamento de OLTP in-memory suficiente, a operação vai falhar.

Determinar se existem objetos OLTP in-memory

Há uma maneira programática de descobrir se determinado banco de dados dá suporte ao OLTP in-memory. Execute a seguinte consulta Transact-SQL:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Se a consulta retornar 1, há suporte para o OLTP in-memory nesse banco de dados.

As seguintes consultas identificam todos os objetos que precisam ser removidos antes de escalar um banco de dados para a camada de serviço Hiperescala, Uso Geral, Standard ou Básico:

SELECT * FROM sys.tables WHERE is_memory_optimized = 1;
SELECT * FROM sys.table_types WHERE is_memory_optimized = 1;
SELECT * FROM sys.sql_modules WHERE uses_native_compilation = 1;

Columnstore In-memory

A tecnologia columnstore In-memory permite armazenar e consultar uma grande quantidade de dados nas tabelas. A tecnologia Columnstore usa o formato de armazenamento de dados com base em coluna e processamento de consulta em lote para obter ganho de até 10 vezes o desempenho da consulta em cargas de trabalho OLAP sobre o armazenamento tradicional orientado por linha. Também é possível obter ganhos de até 10 vezes na compactação de dados sobre o tamanho dos dados descompactados.

Há dois tipos de índices columnstore úteis para organizar seus dados:

• Columnstore clusterizado em que todos os dados na tabela são organizadas em formato colunar. Nesse tipo de índice, todas as linhas na tabela são colocadas em formato colunar que compacta bastante os dados e permite que você execute rápidas consultas analíticas e relatórios na tabela. Dependendo da natureza dos seus dados, o tamanho dos dados pode ser reduzido de 10x a 100x. Os índices columnstore clusterizados também permitem a ingestão rápida de uma grande quantidade de dados (carregamento em massa), já que grandes lotes de dados maiores que 100.000 linhas são compactados antes de serem armazenadas no disco. Esse tipo de índice é uma boa escolha para os cenários clássicos de data warehouse.
• Columnstore não clusterizado em que os dados são armazenados na tabela rowstore tradicional e há um índice adicional no formato columnstore que é usado para as consultas analíticas. Esse tipo de índice permite HTAP (Hybrid Transactional-Analytic Processing): a capacidade de executar a análise rápida em tempo real em uma carga de trabalho transacional. Consultas OLTP são executadas na tabela rowstore que é otimizada para acessar um pequeno conjunto de linhas, enquanto as consultas OLAP são executadas em um índice columnstore que é a melhor opção para exames e análises. O otimizador de consulta escolhe dinamicamente o formato rowstore ou columnstore com base na consulta. Os índices columnstore não clusterizados não diminuem o tamanho dos dados, pois o conjunto de dados original é mantido na tabela rowstore original sem qualquer alteração. No entanto, o tamanho do índice columnstore adicional está em ordens de magnitude menores do que o índice de árvore B equivalente.

Tamanho dos dados e armazenamento para índices columnstore

Os índices columnstore não precisam caber totalmente na memória. Portanto, o único limite para o tamanho dos índices é o tamanho máximo do banco de dados geral, que está documentado nos artigos modelo de compra baseado em DTU e modelo de compra baseado em vCore.

Ao usar os índices columnstore clusterizados, a compactação vertical é usada para o armazenamento de tabelas base. Essa compactação pode reduzir consideravelmente o volume de armazenamento dos dados do usuário, o que significa que você pode colocar mais dados no banco de dados. A taxa de compactação pode ser ainda maior com a compactação de arquivamento vertical. A quantidade de compactação que pode ser obtida depende da natureza dos dados, mas uma compactação de 10 vezes não é incomum.

Por exemplo, se você tiver um banco de dados com tamanho máximo de 1 TB (terabyte) e obter uma compactação de 10 vezes usando índices columnstore, você poderá colocar um total de 10 TB de dados de usuário no banco de dados.

Quando você usa os índices columnstore não clusterizado, a tabela base ainda é armazenada no formato rowstore tradicional. Portanto, a economia de armazenamento não é tão significante quanto com os índices columnstore clusterizados. No entanto, se você estiver substituindo vários índices não clusterizados tradicionais por um único índice columnstore, você ainda poderá observar uma economia geral no espaço de armazenamento da tabela. Também é possível usar a compactação de dados rowstore para a tabela base.

Alterar camadas de serviço dos bancos de dados contendo índices columnstore

Se você usar o modelo de compra de DTU e seu banco de dados contiver índices columnstore, seu aplicativo poderá parar de funcionar se você escalar seu banco de dados abaixo do objetivo de serviço do S3. Os índices columnstore têm suporte apenas nas camadas de serviço Hiperescala, Comercialmente Crítico e Premium, bem como na camada de serviço Standard se estiver usando o S3 e superior. Não há suporte para índices Columnstore em bancos de dados na camada de serviço Básica. Ao escalar seu banco de dados para um nível de serviço ou um objetivo de serviço sem suporte, seu índice columnstore fica indisponível. O sistema mantém o índice quando você executa instruções DML, mas nunca usa o índice. Se, mais tarde, você dimensionar para um tipo ou nível com suporte, o índice columnstore será imediatamente disponibilizado para uso novamente.

Se você tiver um índice columnstore clusterizado, a tabela inteira ficará indisponível se o banco de dados for dimensionado para uma camada de serviço ou objetivo de serviço sem suporte. Descarte todos os índices columnstore clusterizados, substituindo-os por índices clusterizados rowstore ou heaps, antes da operação de dimensionamento.

Conteúdo relacionado

• Início Rápido 1: Tecnologias OLTP In-Memory para um desempenho mais rápido do T-SQL
• Usar o OLTP In-Memory em um aplicativo existente do SQL Azure
• Visão geral e cenários de uso do OLTP in-memory
• Monitorar o armazenamento OLTP in-memory
• Exemplo de in-memory no Banco de Dados SQL do Azure
• Blog: OLTP in-memory no Banco de Dados SQL do Azure
• Saiba mais sobre o OLTP in-memory
• Saiba mais sobre os índices columnstore
• Saiba mais sobre a análise operacional em tempo real
• Artigo técnico: OLTP in-memory – Padrões comuns de carga de trabalho e considerações de migração no SQL Server 2014