Tamanho da tabela e da linha em tabelas com otimização de memória

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do Azure

Antes do SQL Server 2016 (13.x), o tamanho dos dados na linha de uma tabela com otimização de memória não podia ter mais de 8.060 bytes. No entanto, a partir do SQL Server 2016 (13.x) e no Banco de Dados SQL do Azure, é possível criar uma tabela com otimização de memória com várias colunas grandes (por exemplo, várias colunas de varbinary(8000)) e colunas LOB (ou seja, varbinary(máx), varchar(máx) e nvarchar(máx)) e executar operações nela usando módulos Transact-SQL (T-SQL) e tipos de tabela em compilação nativa.

Colunas que não se ajustam ao limite de tamanho de linha de 8.060 bytes são colocadas fora de linha, em uma tabela interna separada. Cada coluna fora de linha tem uma tabela interna correspondente, que por sua vez, tem um único índice não clusterizado. Para obter detalhes sobre essas tabelas internas usadas para colunas fora da linha, veja sys.memory_optimized_tables_internal_attributes.

Existem alguns cenários em que é útil calcular o tamanho da linha e da tabela:

• Qual a quantidade de memória que uma tabela usa.

  • A quantidade de memória usada pela tabela não pode ser calculada exatamente. Muitos fatores afetam a quantidade de memória usada. Fatores como alocação de memória baseada em páginas, localidade, cache e preenchimento. Além disso, várias versões de linhas têm transações ativas associadas ou estão aguardando a coleta de lixo.

  • O tamanho mínimo necessário para os dados e índices na tabela é determinado pelo cálculo de <table size>, discutido posteriormente no artigo.

  • Calcular o uso da memória é, na melhor das hipóteses, uma aproximação; portanto, é recomendável incluir o planejamento de capacidade nos planos de implantação.

• Qual é o tamanho dos dados de uma linha, e ele cabe no limite de tamanho de linha de 8.060 bytes? Para responder a essas perguntas, use a computação para <row body size>, discutida mais adiante neste artigo.

Uma tabela com otimização de memória consiste em uma coleção de linhas e índices que contêm ponteiros para linhas. A figura a seguir ilustra uma tabela com índices e linhas que, por sua vez, têm cabeçalhos e corpos de linha:

Calcular o tamanho da tabela

O tamanho de uma tabela na memória, em bytes, é calculado da seguinte forma:

<table size> = <size of index 1> + ... + <size of index n> + (<row size> * <row count>)

O tamanho de um índice de hash é fixado no momento da criação da tabela e depende do número real de buckets. O bucket_count especificado com a definição de índice é arredondado até a potência mais próxima de 2 para obter o número real de buckets. Por exemplo, se o bucket_count especificado for 100000, o número real de buckets para o índice será 131072.

<hash index size> = 8 * <actual bucket count>

O tamanho de um índice não clusterizado está na ordem de <row count> * <index key size>.

O tamanho da linha é calculado adicionando-se o cabeçalho e o corpo:

<row size> = <row header size> + <actual row body size>
<row header size> = 24 + 8 * <number of indexes>

Calcular o tamanho do corpo da linha

As linhas em uma tabela com otimização de memória têm os seguintes componentes:

• O cabeçalho de linha contém o carimbo de data/hora necessário para implementar o controle de versão de linha. O cabeçalho de linha também contém o ponteiro de índice para implementar o encadeamento de linhas nos buckets de hash (descritos anteriormente).

• O corpo da linha contém os dados reais da coluna, que incluem algumas informações auxiliares como a matriz nula das colunas que permitem valor nulo e a matriz de deslocamento dos tipos de dados de comprimento variável.

A figura a seguir ilustra a estrutura de linha de uma tabela que tem dois índices:

As marcas de tempo de início e de término indicam o período durante o qual uma determinada versão de uma linha é válida. As transações que começam nesse intervalo podem ver essa versão de linha. Para obter mais informações, veja Transações com tabelas com otimização de memória.

Os ponteiros de índice apontam para a próxima linha da cadeia de caracteres que pertence ao bucket de hash. A figura a seguir ilustra a estrutura de uma tabela com duas colunas (nome, cidade), e com dois índices, um na coluna nome e outro na coluna cidade.

Nesta figura, os nomes John e Jane são transformados em hash para o primeiro bucket. Susan é mapeado para o segundo bucket. As cidades Beijing e Bogota são transformadas em hash para o primeiro bucket. Paris e Prague são mapeados para o segundo bucket.

Desse modo, as cadeias do índice de hash na coluna nome são as seguintes:

• Primeiro bucket: (John, Beijing); (John, Paris); (Jane, Prague)
• Segundo bucket: (Susan, Bogota)

As cadeias do índice de cidade são as seguintes:

• Primeiro bucket: (John, Beijing), (Susan, Bogota)
• Segundo bucket: (John, Paris), (Jane, Prague)

Um timestamp de término ∞ (infinito) indica que esta é a versão atualmente válida da linha. A linha não foi atualizada ou excluída desde que essa versão da linha foi gravada.

Para um tempo maior que 200, a tabela contém as seguintes linhas:

No entanto, qualquer transação ativa com hora de início 100, consulte a seguinte versão da tabela:

O cálculo do <row body size> é abordado na tabela a seguir.

Há dois cálculos diferentes para o tamanho do corpo da linha: tamanho calculado e o tamanho real:

• O tamanho calculado, marcado com tamanho calculado do corpo da linha, é usado para determinar se a limitação do tamanho da linha de 8.060 bytes foi excedida.

• O tamanho real, marcado com tamanho do corpo da linha real, é o tamanho real do armazenamento do corpo da linha na memória e nos arquivos do ponto de verificação.

Tanto o tamanho calculado do corpo da linha quanto o tamanho real do corpo da linha são calculados de forma semelhante. A única diferença é o cálculo do tamanho das colunas (n)varchar(i) e varbinary(i), como refletido na parte inferior da tabela a seguir. O tamanho do corpo da linha calculado usa o tamanho declarado i como o tamanho da coluna, enquanto o tamanho do corpo da linha real usa o tamanho real dos dados.

A tabela a seguir descreve o cálculo do tamanho do corpo da linha, dado como <actual row body size> = SUM(<size of shallow types>) + 2 + 2 * <number of deep type columns>.

Exemplo: cálculo do tamanho da tabela e da linha

Para índices de hash, o número de buckets real é arredondado até a potência mais próxima de 2. Por exemplo, se o bucket_count especificado for 100000, o número real de buckets do índice será 131072.

Considere uma tabela Orders com a seguinte definição:

CREATE TABLE dbo.Orders (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL INDEX IX_CustomerID HASH WITH (BUCKET_COUNT = 10000),
    OrderDate DATETIME NOT NULL,
    OrderDescription NVARCHAR(1000)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Esta tabela tem um índice de hash e um índice não clusterizado (a chave primária). Ele também tem três colunas de comprimento fixo e uma coluna de comprimento variável, com uma das colunas sendo anulável (OrderDescription). Vamos supor que a tabela Orders tenha 8.379 linhas e o tamanho médio dos valores na coluna OrderDescription seja de 78 caracteres.

Para determinar o tamanho da tabela, primeiro determine o tamanho dos índices. O bucket_count para ambos os índices é especificado como 10000. Isso é arredondado para a potência mais próxima de 2: 16384. Consequentemente, o tamanho total dos índices da tabela Orders é:

8 * 16384 = 131072 bytes

O que sobra é o tamanho dos dados da tabela, que é:

<row size> * <row count> = <row size> * 8379

(A tabela de exemplo tem 8.379 linhas.) Agora, temos:

<row size> = <row header size> + <actual row body size>
<row header size> = 24 + 8 * <number of indices> = 24 + 8 * 1 = 32 bytes

A seguir, vamos calcular <actual row body size>:

• Colunas de tipo raso:

  SUM(<size of shallow types>) = 4 <int> + 4 <int> + 8 <datetime> = 16
  

• O preenchimento da coluna superficial é 0, pois o tamanho total da coluna superficial é par.

• Matriz de deslocamento para colunas do tipo profundas:

  2 + 2 * <number of deep type columns> = 2 + 2 * 1 = 4
  

• NULL array = 1

• NULL preenchimento da matriz = 1, pois o tamanho da matriz NULL é ímpar e há uma coluna de tipo profundo.

• Preenchimento

  • 8 é o maior requisito de alinhamento
  • O tamanho até agora é 16 + 0 + 4 + 1 + 1 = 22
  • O múltiplo mais próximo de 8 é 24
  • O preenchimento total é 24 - 22 = 2 bytes

• Não há colunas do tipo profundas de comprimento fixo (Colunas do tipo profundas de comprimento fixo: 0.).

• O tamanho real da coluna do tipo profunda é 2 * 78 = 156. A única coluna do tipo profundo OrderDescription tem o tipo nvarchar.

<actual row body size> = 24 + 156 = 180 bytes

Para concluir o cálculo:

<row size> = 32 + 180 = 212 bytes
<table size> = 8 * 16384 + 212 * 8379 = 131072 + 1776348 = 1907420

O tamanho total da tabela na memória é de aproximadamente 2 megabytes. Isso não leva em conta a possível sobrecarga incorrida pela alocação de memória, e qualquer controle de versão de linha exigido para as transações que acessam essa tabela.

A memória real alocada para essa tabela, e usada por ela, bem como seus índices, podem ser obtidos por meio da seguinte consulta:

SELECT * FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id = object_id('dbo.Orders');

Limitações de coluna fora de linha

Certas limitações e restrições ao uso de colunas fora da linha em uma tabela com otimização de memória estão listadas a seguir:

• Se houver um índice columnstore em uma tabela com otimização de memória, todas as colunas deverão caber na linha.
• Todas as colunas de chave de índice devem ser armazenadas em linha. Se uma coluna de chave de índice não couber na linha, a adição do índice falhará.
• Advertências sobre alterar uma tabela com otimização de memória com colunas fora da linha.
• Para LOBs, a limitação de tamanho espelha-se na de tabelas baseadas em disco (limite de 2 GB em valores de LOB).
• Para otimizar o desempenho, é recomendável que a maioria das colunas caibam dentro de 8.060 bytes.
• Dados armazenados fora da linha podem causar uso excessivo de memória e/ou de disco.

Conteúdo relacionado

• Banco de dados de exemplo para OLTP na memória