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 SQL do Azure

Antes do SQL Server 2016 (13.x), o tamanho dos dados em linha de uma tabela com otimização de memória não podia ser superior a 8.060 bytes. No entanto, a partir do SQL Server 2016 (13.x) e no Banco de Dados SQL do Azure, você pode criar uma tabela otimizada para memória com várias colunas grandes (por exemplo, várias colunas varbinary(8000)) e colunas LOB (ou seja, varbinary(max), varchar(max)e nvarchar(max)) e executar operações nelas usando módulos Transact-SQL (T-SQL) compilados nativamente e tipos de tabela.

As colunas que não se encaixam no limite de tamanho de linha de 8.060 bytes são colocadas fora da linha, em uma tabela interna separada. Cada coluna fora da 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 de linha, consulte sys.memory_optimized_tables_internal_attributes.

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

• Quanta memória 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ágina, localidade, cache e preenchimento. Além disso, existem várias versões de linhas que têm transações ativas associadas ou que estão à espera de coleta de lixo.

  • O tamanho mínimo exigido para os dados e índices na tabela é dado pelo cálculo para <table size>, discutido mais adiante neste artigo.

  • Calcular o uso de memória é, na melhor das hipóteses, uma aproximação e é aconselhável incluir o planejamento de capacidade em seus planos de implantação.

• Qual é o tamanho dos dados de uma linha e será que se enquadra na limitação de 8.060 bytes para o tamanho de linha? Para responder a essas perguntas, use o cálculo para <row body size>, discutido 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 na memória de uma tabela, 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 é fixo no momento da criação da tabela e depende da contagem real de buckets. O bucket_count especificado com a definição de índice é arredondado para a potência mais próxima de 2 para obter a contagem real de buckets. Por exemplo, se o bucket_count especificado for 100000, a contagem real de buckets para o índice será 131072.

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

O tamanho de um índice não agrupado é da ordem de <row count> * <index key size>.

O tamanho da linha é calculado adicionando 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 da linha contém o carimbo de data/hora necessário para implementar o controle de versão de linha. O cabeçalho da linha contém també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 para colunas anuláveis e a matriz de deslocamento para tipos de dados de comprimento variável.

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

As marcações de tempo de início e fim indicam o período em que uma determinada versão de linha é válida. As transações que começam neste intervalo podem visualizar esta versão da linha. Para obter mais informações, consulte Operações com Memory-Optimized Tabelas.

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

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

Assim, as cadeias para o índice de hash no nome são as seguintes:

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

As cadeias para o índice sobre a cidade são as seguintes:

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

Um marcador de data/hora final ∞ (infinito) indica que esta é a versão atualmente válida da linha de dados. A linha não foi atualizada ou excluída desde que esta versão da linha foi escrita.

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

Nome	Cidade
John	Pequim
Joana	Praga

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

Nome	Cidade
John	Paris
Joana	Praga
Susana	Bogotá

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

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

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

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

Tanto o tamanho do corpo da linha calculado quanto o tamanho real do corpo da linha são calculados de forma semelhante. A única diferença é o cálculo do tamanho de (n)varchar(i) e varbinary(i) colunas, conforme 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 real do corpo da linha 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>.

Secção	Tamanho	Observações
Colunas de tipo raso	SUM(<size of shallow types>). O tamanho em bytes dos tipos individuais é o seguinte: bit: 1 Tinyint: 1 smallint:2 Int: 4 reais: 4 SmallDateTime: 4 pequeno_dinheiro : 4 BIGINT:8 Data:8 datahora2: 8 flutuar: 8 dinheiro: 8 numérico (precisão <= 18): 8 tempo: 8 numérico (precisão > 18): 16 identificador único: 16
Preenchimento reduzido da coluna	Os valores possíveis são: 1 se houver colunas de tipo profundo e o tamanho total dos dados das colunas rasas for um número ímpar. 0 contrário	Tipos profundos são os tipos (var)binário e (n)(var)char.
matriz de deslocamento para colunas de tipo profundo	Os valores possíveis são: 0 se não houver colunas de tipo profundo 2 + 2 * <number of deep type columns> contrário	Tipos profundos são os tipos (var)binário e (n)(var)char.
Matriz nula	<number of nullable columns> / 8 arredondado para bytes completos.	A matriz tem 1 bit por coluna anulável. Isso é arredondado para o número inteiro de bytes.
preenchimento de matriz nula	Os valores possíveis são: 1 se houver colunas de tipo profundo e o tamanho da matriz NULL for um número ímpar de bytes. 0 contrário	Tipos profundos são os tipos (var)binário e (n)(var)char.
Preenchimento	Se não houver colunas de tipo profundo: 0 Se houver colunas de tipo profundo, 0 a 7 bytes de preenchimento serão adicionados, com base no maior alinhamento exigido por uma coluna superficial. Cada coluna superficial requer alinhamento igual ao seu tamanho, conforme documentado anteriormente, exceto que as colunas GUID precisam de alinhamento de 1 byte (não 16) e as colunas numéricas sempre precisam de alinhamento de 8 bytes (nunca 16). O maior requisito de alinhamento entre todas as colunas superficiais é usado. 0 - 7 bytes de preenchimento são adicionados de tal forma que o tamanho total até agora (sem as colunas de tipo profundo) é um múltiplo do alinhamento necessário.	Tipos profundos são os tipos (var)binário e (n)(var)char.
Colunas de tipo profundo de comprimento fixo	SUM(<size of fixed length deep type columns>) O tamanho de cada coluna é o seguinte: i para char(i) e binário(i). 2 * i para nchar(i)	Colunas de tipo profundo de comprimento fixo são colunas do tipo char(i), nchar(i)ou binary(i).
Colunas de tipo profundo de comprimento variável tamanho calculado	SUM(<computed size of variable length deep type columns>) O tamanho calculado de cada coluna é o seguinte: i para varchar(i) e varbinary(i) 2 * i para nvarchar(i)	Esta linha só se aplicava a tamanho de corpo de linha calculado. Colunas de tipo profundo de comprimento variável são colunas do tipo varchar(i), nvarchar(i), ou varbinary(i). O tamanho calculado é determinado pelo comprimento máximo (i) da coluna.
Colunas de tipo profundo de comprimento variável tamanho real	SUM(<actual size of variable length deep type columns>) O tamanho real de cada coluna é o seguinte: n, onde n é o número de caracteres armazenados na coluna, para varchar(i). 2 * n, onde n é o número de caracteres armazenados na coluna, para nvarchar(i). n, onde n é o número de bytes armazenados na coluna, para varbinary(i).	Esta linha só se aplicava ao tamanho real do corpo da linha . O tamanho real é determinado pelos dados armazenados nas colunas da linha.

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

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

Considere uma tabela Pedidos 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 comprimento 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. Este valor é arredondado para a potência mais próxima de 2: 16384. Portanto, o tamanho total dos índices para a tabela Orders é:

8 * 16384 = 131072 bytes

O que resta é 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

Em seguida, vamos calcular <actual row body size>:

• Colunas de tipo raso:

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

• O espaçamento da coluna rasa é 0, pois o tamanho total da coluna rasa é par.

• Matriz de deslocamento para colunas de tipo profundo:

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

• NULL matriz = 1

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

• Acolchoamento

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

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

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

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

Para completar 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 é, portanto, de aproximadamente 2 megabytes. Isso não leva em conta a sobrecarga potencial incorrida pela alocação de memória e qualquer controle de versão de linha necessário para as transações que acessam esta tabela.

A memória real alocada e usada por esta tabela e seus índices podem ser obtidos através da seguinte consulta:

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

Limitações da coluna fora da linha

Certas limitações e advertências ao uso de colunas fora de linha em uma tabela com otimização de memória estão listadas da seguinte maneira:

• Se houver um índice columnstore numa 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 de linha.
• Para LOBs, a limitação de tamanho espelha a de tabelas baseadas em disco (limite de 2 GB em valores de LOB).
• Para um desempenho ideal, recomendamos que a maioria das colunas caiba dentro de 8.060 bytes.
• Dados fora da linha de registros podem causar uso excessivo de memória e/ou disco.

Conteúdo relacionado

• base de dados de exemplo para In-Memory OLTP