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

  • Artigo

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.

Observação

Para obter um tutorial passo a passo detalhado para demonstrar as vantagens de desempenho da tecnologia OLTP in-memory usando o banco de dados de exemplo AdventureWorksLT e o ostress.exe, consulte Exemplo de in-memory no Banco de Dados SQL do Azure.

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.

Observação

As tecnologias In-Memory estão disponíveis nas camadas Premium e Comercialmente Crítico do 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 também incluir exemplos:

  • Você verá o impacto dessas tecnologias no armazenamento e dos limites de tamanho dos dados.
  • Você verá como gerenciar a movimentação dos bancos de dados que utilizam essas tecnologias entre os diferentes tipos de preço.
  • Você verá dois exemplos que ilustram o uso do OLTP In-Memory, bem como dos índices columnstore.

Para saber mais sobre In-Memory no SQL Server, confira:

OLTP in-memory

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) onde 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.

Observação

A tecnologia de OLTP In-Memory foi projetada para as estruturas de dados que podem residir totalmente na memória. Como os dados na memória não podem ser descarregados para o disco, certifique-se de estar usando um banco de dados que tenha memória suficiente. Para obter mais informações, consulte Tamanho dos dados e limite de armazenamento do OLTP In-Memory.

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. Como você gerencia a memória diretamente no Banco de Dados SQL, temos o conceito de uma cota para dados de usuário. Esse conceito é conhecido como Armazenamento de OLTP In-Memory.

Cada tipo de preço de banco de dados individual e de pool elástico compatível inclui determinada quantidade de armazenamento do OLTP in-memory.

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 tipo de preço do banco de dados ou do pool.

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, confira 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 MaxvCore para bancos de dados que sejam inferiores à contagem eDTU ou vCore para o pool como um todo. Isso proporciona um limite máximo à utilização no armazenamento do OLTP in-memory em qualquer banco de dados no pool ao tamanho que corresponde à contagem de eDTUs.
  • Configure um Min-eDTU ou MinvCore 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 vCore.

Alterar camadas de serviço dos bancos de dados que usam tecnologias de OLTP In-Memory

Você pode sempre atualizar o banco de dados para uma camada superior, como de Uso Geral (vCore) para Comercialmente Crítico (ou Standard [DTU] para Premium). Os recursos e funcionalidades disponíveis só aumentam.

Porém, fazer downgrade da camada pode afetar negativamente o banco de dados. O impacto é especialmente aparente quando você faz o downgrade de Comercialmente Crítico para Uso Geral (ou Premium para Standard ou Básico) quando o banco de dados contém objetos OLTP In-Memory. Você pode encontrar facilmente objetos in-memory em seu banco de dados.

As tabelas otimizadas para memória não ficam disponíveis após o downgrade (mesmo se permanecerem visíveis). As mesmas considerações se aplicam ao reduzir o tipo de preço de um pool elástico ou ao mover um banco de dados com tecnologias In-Memory para um pool elástico de Uso Geral, Standard ou Básico.

Importante

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

Recursos par reduzir verticalmente na camada Comercialmente Crítico: os dados em tabelas com otimização de memória devem ajustar-se ao armazenamento OLTP In-Memory associado à camada do banco de dados ou estar disponíveis no pool elástico. Se você tentar reduzir a camada ou mover o banco de dados para um pool que não tem armazenamento OLTP In-Memory suficiente disponível, a operação falhará.

Determinar se existem objetos in-memory

Há uma maneira programática de entender 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 retorna 1, há suporte para o OLTP in-memory neste banco de dados.

As seguintes consultas identificam todos os objetos que precisam ser removidos antes do downgrade de um banco de dados para Uso Geral, Standard ou Basic:

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 modelos de columnstore que você pode usar para organizar seus dados:

  • Columnstore clusterizado em que todos os dados na tabela são organizadas em formato colunar. Nesse modelo, 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. O modelo de columnstore clusterizado também permite 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 modelo é 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 no formato columnstore que é usado para as consultas analíticas. Esse modelo permite HTAP (Hybrid Transactional-Analytic Processing): a capacidade de executar a análise em tempo real de alto desempenho 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 deve ser em ordem de magnitude menor do que o índice de árvore B equivalente.

Observação

A tecnologia columnstore In-memory mantém apenas os dados que são necessários para processamento na memória, enquanto os dados que ajustam-se à memória são armazenados em disco. Portanto, a quantidade de dados em estruturas columnstore In-memory pode exceder a quantidade de memória disponível.

Tamanho dos dados e armazenamento para índices columnstore

Os índices columnstore não precisam caber 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. E a 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.

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

Fazer downgrade do banco de dados individual para Básico ou Standard talvez não seja possível se a camada de destino estiver abaixo de S3. Os índices columnstore só têm suporte no tipo de preço Comercialmente Crítico/Premium e na camada Standard, S3 e superior, e não na camada Básico. Ao fazer o downgrade de seu banco de dados para um tipo ou nível sem suporte, seu índice columnstore fica indisponível. O sistema mantém seu índice columnstore, mas nunca usa o índice. Se, mais tarde, você atualizar de volta 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 após o downgrade. Solte todos os índices columnstore clusterizados (e substitua por índices clusterizados rowstore) antes de fazer o downgrade do banco de dados para uma camada ou nível sem suporte.

Conteúdo relacionado