Estimar requisitos de memória para tabelas com otimização de memória

Artigo
05/30/2023

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

As tabelas com otimização de memória requerem a existência de memória suficiente para manter todas as linhas e índices na memória. Como a memória é um recurso finito, é importante que você entenda e gerencie o uso de memória em seu sistema. Os tópicos nessa seção abordam os cenários comuns de uso e gerenciamento de memória.

Se você estiver criando uma nova tabela com otimização de memória ou migrando uma tabela baseada em disco existente para um In-Memory tabela com otimização de memória OLTP, é importante ter uma estimativa razoável das necessidades de memória de cada tabela para que você possa provisionar o servidor com memória suficiente. Esta seção descreve como estimar a quantidade de memória necessária para manter dados para uma tabela com otimização de memória.

Se você estiver pensando em migrar de tabelas baseadas em disco para tabelas com otimização de memória, antes de prosseguir neste tópico, consulte o tópico Determinando se uma tabela ou procedimento armazenado deve ser portado para In-Memory OLTP para obter diretrizes sobre quais tabelas são melhores para migrar. Todos os tópicos em Migrando para OLTP in-memory fornecem diretrizes sobre como migrar de tabelas baseadas em disco para tabelas com otimização de memória.

Diretrizes básicas para estimar os requisitos de memória

A partir do SQL Server 2016 (13.x), não há limite para o tamanho das tabelas com otimização de memória, embora as tabelas precisem caber na memória. No SQL Server 2014 (12.x), o tamanho dos dados com suporte é de 256 GB para tabelas SCHEMA_AND_DATA.

O tamanho de uma tabela com otimização de memória corresponde ao tamanho dos dados, além da sobrecarga para cabeçalhos de linha. Ao migrar uma tabela baseada em disco para uma tabela com otimização de memória, o tamanho da tabela com otimização de memória aproximadamente corresponderá ao tamanho do índice clusterizado ou do heap da tabela original baseada em disco.

Índices em tabelas com otimização de memória tendem a ser menores que os índices não clusterizados em tabelas baseadas em disco. O tamanho dos índices não clusterizados está na ordem de [primary key size] * [row count]. O tamanho dos índices de hash é [bucket count] * 8 bytes.

Quando há uma carga de trabalho ativa, a memória extra é necessária para considerar o controle de versão de linha e várias operações. A quantidade de memória necessária na prática depende na carga de trabalho, mas para segurança, a recomendação é iniciar com duas vezes o tamanho esperado de tabelas com otimização de memória e índices e observar quais são os requisitos de memória na prática. A sobrecarga de controle de versão de linha sempre depende das características da carga de trabalho – especialmente, transações de longa duração aumentam a sobrecarga. Para a maioria das cargas de trabalho que usam bancos de dados maiores (por exemplo, >100 GB), a sobrecarga tende a ser limitada (25% ou menos).

Computação detalhada dos requisitos de memória

Exemplo de tabela com otimização de memória

Considere o esquema de tabela com otimização de memória a seguir:

CREATE TABLE t_hk
(  
  col1 int NOT NULL  PRIMARY KEY NONCLUSTERED,  

  col2 int NOT NULL  INDEX t1c2_index   
      HASH WITH (bucket_count = 5000000),  

  col3 int NOT NULL  INDEX t1c3_index   
      HASH WITH (bucket_count = 5000000),  

  col4 int NOT NULL  INDEX t1c4_index   
      HASH WITH (bucket_count = 5000000),  

  col5 int NOT NULL  INDEX t1c5_index NONCLUSTERED,  

  col6 char (50) NOT NULL,  
  col7 char (50) NOT NULL,   
  col8 char (30) NOT NULL,   
  col9 char (50) NOT NULL  

)   WITH (memory_optimized = on)  ;
GO

Usando esse esquema, determinaremos a memória mínima necessária para essa tabela com otimização de memória.

Memória da tabela.

Uma linha de tabela com otimização de memória é composta de três partes:

Carimbos de data/hora
Cabeçalho de linha/carimbos de data/hora = 24 bytes.
Ponteiros de índice
Para cada índice de hash na tabela, cada linha tem um ponteiro de endereço de 8 bytes para a próxima linha no índice. Como há quatro índices, cada linha alocará 32 bytes para ponteiros de índice (um ponteiro de 8 bytes para cada índice).
Dados
O tamanho da parte dos dados da linha é determinado pela adição do tamanho de tipo para cada coluna de dados. Em nossa tabela temos cinco números inteiros de 4 bytes, três colunas de caracteres de 50 bytes e uma coluna de caracteres de 30 bytes. Como consequência, a parte de dados de cada linha tem 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50 ou 200 bytes.

Veja a seguir uma computação de tamanho para 5.000.000 (5 milhões) de linhas em uma tabela com otimização de memória. A memória total usada pelas linhas de dados é estimada como se segue:

Memória para linhas da tabela

Pelos cálculos acima, o tamanho de cada linha na tabela com otimização de memória é 24 + 32 + 200 ou 256 bytes. Como temos 5 milhões de linhas, a tabela consumirá 5.000.000 * 256 bytes ou 1.280.000.000 bytes – aproximadamente 1,28 GB.

Memória para índices

Memória para cada índice de hash

Cada índice de hash é uma matriz de hash de ponteiros de endereço de 8 bytes. O tamanho da matriz é melhor determinado pelo número de valores de índice exclusivos para esse índice – por exemplo, o número de valores exclusivos Col2 é um bom ponto de partida para o tamanho da matriz para o t1c2_index. Uma matriz de hash muito grande desperdiça memória. Uma matriz de hash que é muito pequena reduz o desempenho já que haverá muitas colisões pelos valores de índice que usam o hash para o mesmo índice.

Os índices de hash atingem muito rápido pesquisas de igualdade como:

SELECT * FROM t_hk  
   WHERE Col2 = 3;

Os índices não clusterizados são mais rápidos para pesquisas de intervalo como:

SELECT * FROM t_hk  
   WHERE Col2 >= 3;

Se você estiver migrando uma tabela baseada em disco, poderá usar o seguinte para determinar o número de valores exclusivos para o índice t1c2_index.

SELECT COUNT(DISTINCT [Col2])  
  FROM t_hk;

Se você estiver criando uma nova tabela, precisará estimar o tamanho da matriz ou coletar dados de seus testes antes da implantação.

Para obter informações sobre como os índices de hash funcionam em tabelas com otimização de memória OLTP na memória , veja Índices de hash.

Definindo o tamanho da matriz de índice de hash

O tamanho da matriz de hash é definido por (bucket_count= value) , em que value é um valor inteiro maior que zero. Se value não for uma potência de 2, o bucket_count real será arredondado para a próxima potência mais próxima de 2. Em nossa tabela de exemplo, (bucket_count = 5000000), uma vez que 5.000.000 não é uma potência de 2, a contagem real de buckets arredonda até 8.388.608 (2^23). Você deve usar esse número, e não 5.000.000 quando calcular a memória necessária à matriz de hash.

Assim, em nosso exemplo, a memória necessária para cada matriz de hash é:

8.388.608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67.108.864 ou aproximadamente 64 MB.

Como temos três índices de hash, a memória necessária para os índices de hash é de 3 * 64 MB = 192 MB.

Memória para índices não clusterizados

Os índices não clusterizados são implementados como árvores Bw com os nós internos que contêm o valor e ponteiros de índice aos nós subsequentes. Os nós folha contêm o valor de índice e um ponteiro para a linha da tabela na memória.

Ao contrário dos índices de hash, os índices não clusterizados não têm um tamanho de bucket fixo. O índice aumenta e diminui dinamicamente com os dados.

A memória necessária pelos índices não clusterizados pode ser computada da seguinte forma:

Memória alocada a nós que não são nós folha
Para uma configuração comum, a memória alocada para nós não folha é uma porcentagem muito pequena da memória total usada pelo índice. Ela é tão pequena que pode seguramente ser ignorada.
Memória para nós folha
Os nós folha têm uma linha para cada chave exclusiva na tabela que aponta para as linhas de dados com essa chave exclusiva. Se você tiver várias linhas com a mesma chave (ou seja, você tem um índice não clusterizado não exclusivo), há apenas uma linha no nó folha de índice que aponta para uma das linhas com as outras linhas vinculadas umas às outras. Assim, a memória total necessária pode ser aproximado por:
- memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys

Os índices não clusterizados são os melhores quando usado para pesquisas de intervalo, como exemplificadas pela seguinte consulta:

SELECT * FROM t_hk  
   WHERE c2 > 5;

Memória para o controle de versão de linha

Para evitar bloqueios, OLTP de memória usa a simultaneidade otimista ao atualizar ou excluir linhas. Isso significa que, quando uma linha é atualizada, outra versão da linha é criada. Além disso, as exclusões são lógicas – a linha existente é marcada como excluída, mas não é removida imediatamente. O sistema mantém as versões de linhas antigas (incluindo as linhas excluídas) disponíveis até que todas as transações que possivelmente poderiam usar a versão tenham concluído a execução.

Como pode haver muito mais linhas na memória a qualquer momento aguardando o ciclo de coleta de lixo liberar a memória, você deve ter memória suficiente para acomodar essas outras linhas.

O número de linhas extras pode ser estimado calculando o número máximo de atualizações e exclusões de linha por segundo e multiplicando-o pelo número de segundos que a transação mais longa leva (mínimo de 1).

Esse valor é então multiplicado pelo tamanho da linha para obter o número de bytes necessários para o controle de versão de linha.

rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond

A necessidade de memória para linhas obsoletas é estimada pela multiplicação do número de linhas obsoletas pelo tamanho de uma linha da tabela com otimização de memória (Veja Memória para a tabela acima).

memoryForRowVersions = rowVersions * rowSize

Memória para variáveis de tabela

A memória usada para uma variável de tabela é liberada apenas quando a variável de tabela sai do escopo. As linhas excluídas, incluindo linhas excluídas como parte de uma atualização, de uma variável de tabela não estão sujeitas à coleta de lixo. Nenhuma memória é liberada até a variável de tabela sair do escopo.

As variáveis de tabela definidas em um lote SQL grande, e não em um escopo do procedimento, que são usadas em muitas transações podem consumir bastante memória. Como não são coletas de lixo, as linhas excluídas em uma variável de tabela podem consumir muita memória e degradar o desempenho, pois as operações de leitura precisam examinar as linhas excluídas.

Memória para o crescimento

Os cálculos acima estima suas necessidades de memória para a tabela como existe atualmente. Além dessa memória, você precisa estimar o aumento da tabela e fornecimento de memória suficiente para acomodar esse crescimento. Por exemplo, se você antecipar o crescimento de 10% no múltiplo da necessidade dos resultados acima por 1,1 para obter a memória total necessária para a tabela.

Confira também

Migrando para OLTP na memória