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

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstâ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 compreender e gerenciar o uso de memória no sistema. Os tópicos nessa seção abordam os cenários comuns de uso e gerenciamento de memória.

É importante ter uma estimativa razoável das necessidades de memória de cada tabela com otimização de memória para que você possa provisionar o servidor com memória suficiente. Isso se aplica a novas tabelas e tabelas migradas de tabelas baseadas em disco. 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 considerando uma migração de tabelas baseadas em disco para tabelas otimizadas para memória, consulte Determinando se uma Tabela ou Procedimento Armazenado Deve ser Portado para OLTP em Memória para obter orientações 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

No SQL Server 2016 (13.x) e versões posteriores, não há limite para o tamanho de 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. O tamanho da tabela com otimização de memória corresponde aproximadamente ao tamanho do índice clusterizado ou heap da tabela baseada em disco original.

Í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 houver uma carga de trabalho ativa, será necessária memória adicional para a conta para controle de versão de linha e várias operações. A quantidade necessária de memória depende da carga de trabalho, mas para ser segura, a recomendação é começar com duas vezes o tamanho esperado de tabelas e índices com otimização de memória e observar o consumo real de memória. 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, mais de 100 GB), a sobrecarga tende a ser limitada (25% ou menos).

Para obter mais informações sobre a possível sobrecarga de memória no mecanismo OLTP In-Memory, consulte a fragmentação de memória.

Computação detalhada dos requisitos de memória

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

• Memória da tabela

• Memória para índices

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

• Memória para variáveis de tabela

• Memória para o crescimento

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, vamos determinar 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 tem 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 aloca 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 consome 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. No exemplo atual, o número de valores únicos de Col2 é um bom ponto de partida para o tamanho da matriz para o t1c2_index. Uma matriz de hash que é muito grande desperdiça memória. Uma matriz de hash muito pequena reduz o desempenho, pois há muitas colisões por valores de índice que fazem hash para a mesma entrada de í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 do OLTP in-memory, confira Í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 até a potência seguinte mais próxima de 2. Na nossa tabela de exemplo, (bucket_count = 5000000), uma vez que 5.000.000 não é a uma potência de 2, o número de buckets real é arredondado para 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 é 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.

Diferentemente 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 para nós não-folha
  Para uma configuração típica, a memória alocada a nós não-folha é uma pequena porcentagem 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 entre si. Assim, a memória total necessária pode ser aproximado por:

  • tamanhoMemóriaParaÍndiceNãoAgrupado = (tamanhoPonteiro + soma(dimensõesDosTiposDeDadosDasColunasChave)) * linhasComChavesÚnicas

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 versões antigas de linhas (incluindo linhas excluídas) disponíveis até que todas as transações que possam usar a versão tenham sido completamente executadas.

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

O número de linhas adicionais pode ser estimado pelo cálculo do número máximo de atualizações e exclusões de linha por segundo e, em seguida, multiplicando este número pelo número de segundos da duração da transação mais longa (no mínimo, 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

As necessidades de memória para linhas obsoletas são estimadas multiplicando o número de linhas obsoletas pelo tamanho de uma linha de tabela com otimização de memória. Para obter mais informações, consulte Memória para a tabela.

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, inclusive as 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.

Variáveis de tabela definidas em um lote SQL grande em vez de em um procedimento armazenado e usadas em muitas transações podem consumir uma grande quantidade de memória. Como elas não são submetidas a coleta de lixo, as linhas excluídas em uma variável de tabela podem consumir muita memória e diminuir o desempenho, pois as operações de leitura precisam verificar além das linhas excluídas.

Memória para o crescimento

Os cálculos anteriores estimam suas necessidades de memória para a tabela como ela 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ê prever um crescimento de 10%, precisará multiplicar os resultados anteriores por 1,1 para obter a quantidade total de memória necessária para sua tabela.

Fragmentação de memória

Para evitar a sobrecarga de chamadas de alocação de memória e melhorar o desempenho, o mecanismo In-Memory OLTP sempre solicita memória do SQL Server Operating System (SQLOS) usando blocos de 64 KB, conhecidos como superblocos.

Cada superbloco contém alocações de memória somente dentro de um intervalo de tamanho específico, conhecido como sizeclass. Por exemplo, o superblock A pode ter alocações de memória na classe de tamanho de 1 a 16 bytes, enquanto o superblock B pode ter alocações de memória na classe de tamanho de 17 a 32 bytes e assim por diante.

Por padrão, os superblocks também são particionados pela CPU lógica. Isso significa que, para cada CPU lógica, há um conjunto separado de superbloques, divididos ainda mais por classe de tamanho. Isso reduz a contenção de alocação de memória entre as solicitações em execução em CPUs diferentes.

Quando o mecanismo OLTP In-Memory faz uma nova alocação de memória, ele primeiro tenta encontrar memória livre em um superblock existente para a classe de tamanho solicitada e para a CPU que processa a solicitação. Se essa tentativa for bem-sucedida, o valor na used_bytes coluna em sys.dm_xtp_system_memory_consumers para um consumidor de memória específico aumentará pelo tamanho de memória solicitado, mas o valor na allocated_bytes coluna permanecerá o mesmo.

Se não houver memória livre em superblocos existentes, um novo superbloco será alocado e o valor em used_bytes será aumentado pelo tamanho de memória solicitado, enquanto o valor na coluna allocated_bytes aumentará em 64 KB.

Com o tempo, à medida que a memória em superblocks é alocada e desalocada, a quantidade total de memória consumida pelo mecanismo OLTP In-Memory pode se tornar significativamente maior do que a quantidade de memória usada. Em outras palavras, a memória pode ficar fragmentada.

A coleta de lixo pode reduzir a memória usada, mas só reduz a memória alocada se um ou mais blocos ficarem vazios e forem desalocados. Isso se aplica à coleta de lixo automática e forçada usando o procedimento armazenado do sistema sys.sp_xtp_force_gc.

Se a fragmentação de memória do mecanismo In-Memory OLTP e o uso de memória alocada se tornarem maiores do que o esperado, você poderá habilitar o sinalizador de rastreamento 9898. Isso altera o esquema de particionamento de superbloco de por CPU para por nó NUMA, reduzindo o número total de superblocos e o potencial de fragmentação de memória alta.

Essa otimização é mais relevante para computadores grandes com muitas CPUs lógicas. A compensação dessa otimização é um potencial aumento na contenção de alocação de memória resultante da redução no número de superblocos, o que pode reduzir o throughput geral da carga de trabalho. Dependendo dos padrões de carga de trabalho, a redução da taxa de transferência devido ao uso de particionamento de memória por NUMA pode ou não ser perceptível.

Conteúdo relacionado

• Migrando para OLTP na memória