Tamanho da tabela e da linha em tabelas com otimização de memóriaTable and Row Size in Memory-Optimized Tables

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure SQL nãoAzure Synapse Analytics (SQL DW) nãoData Warehouse Paralelo APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Antes do SQL Server 2016 (13.x)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.Prior to SQL Server 2016 (13.x)SQL Server 2016 (13.x) the in-row data size of a memory-optimized table couldn't be longer than 8,060 bytes. No entanto, no SQL Server 2016 (13.x)SQL Server 2016 (13.x) e no Banco de Dados SQL do Azure, agora é 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(max), varchar(max) e nvarchar(max)] e executar operações nela usando módulos T-SQL e tipos de tabela compilados nativamente.However, starting SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in Azure SQL Database it is now possible to create a memory-optimized table with multiple large columns (e.g., multiple varbinary(8000) columns) and LOB columns (i.e., varbinary(max), varchar(max), and nvarchar(max)) and perform operations on them using natively compiled T-SQL modules and table types.

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.Columns that do not fit in the 8060 byte row size limit are placed off-row, in a separate internal table. Cada coluna fora de linha tem uma tabela interna correspondente, que por sua vez, tem um único índice não clusterizado.Each off-row column has a corresponding internal table, which in turn has a single nonclustered index. Para obter detalhes sobre essas tabelas internas usadas para colunas fora da linha, consulte sys.memory_optimized_tables_internal_attributes (Transact-SQL).For details about these internal tables used for off-row columns see sys.memory_optimized_tables_internal_attributes (Transact-SQL).

Existem alguns cenários em que é útil calcular o tamanho da linha e da tabela:There are certain scenarios where it is useful to compute the size of the row and the table:

  • Qual a quantidade de memória que uma tabela usa?How much memory does a table use?

    • A quantidade de memória usada pela tabela não pode ser calculada exatamente.The amount of memory used by the table cannot be calculated exactly. Muitos fatores afetam a quantidade de memória usada,Many factors affect the amount of memory used. como alocação de memória baseada na página, localidade, cache e preenchimento.Factors such as page-based memory allocation, locality, caching, and padding. Além disso, várias versões de linhas têm transações ativas associadas ou estão aguardando a coleta de lixo.Also, multiple versions of rows that either have active transactions associated or that are waiting for garbage collection.

    • O tamanho mínimo necessário para os dados e índices na tabela é determinado pelo cálculo de [tamanho da tabela], discutido a seguir.The minimum size required for the data and indexes in the table is given by the calculation for [table size], discussed below.

    • 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.Calculating memory use is at best an approximation and you are advised to include capacity planning in your deployment plans.

  • O tamanho dos dados de uma linha; ele se ajusta à limitação de tamanho de linha de 8.060 bytes?The data size of a row, and does it fit in the 8,060 byte row size limitation? Para responder a essas perguntas, use a computação para [tamanho do corpo da linha], discutida abaixo.To answer these questions, use the computation for [row body size], discussed below.

Uma tabela com otimização de memória consiste em uma coleção de linhas e índices que contêm ponteiros para linhas.A memory-optimized table consists of a collection of rows and indexes that contain pointers to rows. A figura a seguir ilustra uma tabela com índices e linhas que, por sua vez, têm cabeçalhos e corpos de linha:The following figure illustrates a table with indexes and rows, which in turn have row headers and bodies:

Tabela com otimização de memória.Memory optimized table.
Tabela com otimização de memória composta por índices e linhas.Memory-optimized table, consisting of indexes and rows.

Calculando o tamanho da tabelaComputing Table Size

O tamanho de uma tabela na memória, em bytes, é calculado da seguinte forma:The in-memory size of a table, in bytes, is computed as follows:

[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.The size of a hash index is fixed at table creation time and depends on the actual bucket count. 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.The bucket_count specified with the index definition is rounded up to the nearest power of 2 to obtain the actual bucket count. Por exemplo, se o bucket_count especificado for 100000, o número real de buckets do índice será 131072.For example, if the specified bucket_count is 100000, the actual bucket count for the index is 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].The size of a nonclustered index is in the order of [row count] * [index key size].

O tamanho da linha é calculado adicionando-se o cabeçalho e o corpo:The row size is computed by adding the header and the body:

[row size] = [row header size] + [actual row body size]  
[row header size] = 24 + 8 * [number of indexes]  

Calculando o tamanho do corpo da linhaComputing Row Body Size

Estrutura de Linha As linhas em uma tabela com otimização de memória têm os seguintes componentes:Row Structure The rows in a memory-optimized table have the following components:

  • O cabeçalho de linha contém o carimbo de data/hora necessário para implementar o controle de versão de linha.The row header contains the timestamp necessary to implement row versioning. O cabeçalho da linha também contém o ponteiro de índice para implementar o encadeamento de linhas nos buckets de hash (descrito acima).The row header also contains the index pointer to implement the row chaining in the hash buckets (described above).

  • 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.The row body contains the actual column data, which includes some auxiliary information like the null array for nullable columns and the offset array for variable-length data types.

A figura a seguir ilustra a estrutura de linha de uma tabela que tem dois índices:The following figure illustrates the row structure for a table that has two indexes:

Estrutura de linha para uma tabela que tem dois índices.Row structure for a table that has two indexes.

Os carimbos de data/hora de início e de término indicam o período em que uma determinada versão de linha é válida.The begin and end timestamps indicate the period in which a particular row version is valid. As transações que começam nesse intervalo podem ver essa versão de linha.Transactions that start in this interval can see this row version. Para obter mais detalhes, veja Transações com tabelas com otimização de memória.For more details see Transactions with Memory-Optimized Tables.

Os ponteiros de índice apontam para a próxima linha da cadeia de caracteres que pertence ao bucket de hash.The index pointers point to the next row in the chain belonging to the hash bucket. 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.The following figure illustrates the structure of a table with two columns (name, city), and with two indexes, one on the column name, and one on the column city.

Estrutura de uma tabela com duas colunas e índices.Structure of a table with two columns and indexes.

Nesta figura, os nomes John e Jane são transformados em hash no primeiro bucket.In this figure, the names John and Jane are hashed to the first bucket. Susan é transformado em hash no segundo bucket.Susan is hashed to the second bucket. As cidades Beijing e Bogotá são transformadas em hash no primeiro bucket.The cities Beijing and Bogota are hashed to the first bucket. Paris e Praga são transformadas em hash no segundo bucket.Paris and Prague are hashed to the second bucket.

Desse modo, as cadeias do índice de hash na coluna nome são as seguintes:Thus, the chains for the hash index on name are as follows:

  • Primeiro bucket: (John, Beijing); (John, Paris); (Jane, Praga)First bucket: (John, Beijing); (John, Paris); (Jane, Prague)

  • Segundo bucket: (Susan, Bogotá)Second bucket: (Susan, Bogota)

As cadeias do índice de cidade são as seguintes:The chains for the index on city are as follows:

  • Primeiro bucket: (John, Beijing), (Susan, Bogotá)First bucket: (John, Beijing), (Susan, Bogota)

  • Segundo bucket: (John, Paris), (Jane, Praga)Second bucket: (John, Paris), (Jane, Prague)

Um carimbo de data/hora de término ∞ (infinito) indica que essa é a versão atualmente válida da linha.An end timestamp ∞ (infinity) indicates that this is the currently valid version of the row. A linha não foi atualizada nem excluída desde que essa versão de linha foi gravada.The row has not been updated or deleted since this row version was written.

Para um tempo maior que 200, a tabela contém as seguintes linhas:For a time greater than 200, the table contains the following rows:

NomeName CityCity
JohnJohn PequimBeijing
JaneJane PragaPrague

Entretanto, qualquer transação ativa com tempo inicial igual a 100 resultará na seguinte versão da tabela:However, any active transaction with begin time 100 will see the following version of the table:

NomeName CityCity
JohnJohn ParisParis
JaneJane PragaPrague
SusanSusan BogotáBogata

O cálculo do [tamanho do corpo da linha] é abordado na tabela a seguir.The calculation of [row body size] is discussed in the following table.

Há dois cálculos diferentes para o tamanho do corpo da linha: tamanho calculado e o tamanho real:There are two different computations for row body size: computed size and the actual size:

  • 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.The computed size, denoted with computed row body size, is used to determine if the row size limitation of 8,060 bytes is exceeded.

  • 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.The actual size, denoted with actual row body size, is the actual storage size of the row body in memory and in the checkpoint files.

O tamanho calculado do corpo da linha e o tamanho real do corpo da linha real são calculados de modo semelhante.Both computed row body size and actual row body size are calculated similarly. A única diferença é o cálculo do tamanho das colunas (n)varchar(i) e varbinary(i), como refletido na parte inferior da tabela seguinte.The only difference is the calculation of the size of (n)varchar(i) and varbinary(i) columns, as reflected at the bottom of the following table. 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.The computed row body size uses the declared size i as the size of the column, while the actual row body size uses the actual size of the data.

A tabela a seguir descreve o cálculo do tamanho do corpo da linha, fornecido como tamanho do corpo da linha real = SUM(tamanho dos tipos superficiais) + 2 + 2 * número de colunas do tipo profundo.The following table describes the calculation of the row body size, given as actual row body size = SUM(size of shallow types) + 2 + 2 * number of deep type columns.

SeçãoSection TamanhoSize ComentáriosComments
Colunas do tipo superficialShallow type columns SUM([tamanho dos tipos superficiais]).SUM([size of shallow types]). O tamanho dos tipos individuais em bytes é o seguinte:Size in bytes of the individual types is as follows:

Bit: 1Bit: 1

Tinyint: 1Tinyint: 1

Smallint: 2Smallint: 2

Int: 4Int: 4

Real: 4Real: 4

Smalldatetime: 4Smalldatetime: 4

Smallmoney: 4Smallmoney: 4

Bigint: 8Bigint: 8

Datetime: 8Datetime: 8

Datetime2: 8Datetime2: 8

Float: 8Float: 8

Money: 8Money: 8

Numeric (precisão <=18): 8Numeric (precision <=18): 8

Time: 8Time: 8

Numeric(precisão>18): 16Numeric(precision>18): 16

Uniqueidentifier: 16Uniqueidentifier: 16
Preenchimento da coluna superficialShallow column padding Os valores possíveis são:Possible values are:

1 se houver colunas do tipo profundas e o tamanho total dos dados das colunas superficiais for como um número ímpar.1 if there are deep type columns and the total data size of the shallow columns is as odd number.

Caso contrário, será 00 otherwise
Os tipos profundos são os tipos (var)binary e (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
Matriz de deslocamento para colunas do tipo profundasOffset array for deep type columns Os valores possíveis são:Possible values are:

0 se não houver nenhuma coluna do tipo profunda0 if there are no deep type columns

Caso contrário, 2 + 2 * [número de colunas do tipo profundas]2 + 2 * [number of deep type columns] otherwise
Os tipos profundos são os tipos (var)binary e (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
Matriz NULLNULL array [número de colunas que permitem valor nulo] / 8, arredondado para bytes completos.[number of nullable columns] / 8, rounded up to full bytes. A matriz tem um bit por coluna que permite valor nulo.The array has one bit per nullable column. Ele é arredondado para bytes completos.This is rounded up to full bytes.
Preenchimento da matriz NULLNULL array padding Os valores possíveis são:Possible values are:

1 se houver colunas do tipo profundas e o tamanho da matriz NULL é um número ímpar de bytes.1 if there are deep type columns and the size of the NULL array is an odd number of bytes.

Caso contrário, será 00 otherwise
Os tipos profundos são os tipos (var)binary e (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
PreenchimentoPadding Se não houver colunas de tipo profunda: 0If there are no deep type columns: 0

Se houver colunas do tipo profundas, de 0 a 7 bytes de preenchimento serão adicionados, com base no maior alinhamento exigido por uma coluna superficial.If there are deep type columns, 0-7 bytes of padding is added, based on the largest alignment required by a shallow column. Cada coluna superficial exige alinhamento igual a seu tamanho, como documentado acima, exceto pelo fato de que as colunas GUID precisam de alinhamento de 1 byte (e não 16) e as colunas numéricas sempre precisam de alinhamento de 8 bytes (nunca 16).Each shallow column requires alignment equal to its size as documented above, except that GUID columns need alignment of 1 byte (not 16) and numeric columns always need alignment of 8 bytes (never 16). O requisito de maior alinhamento entre todas as colunas superficiais é usado, e de 0 a 7 bytes de preenchimento são adicionados, de modo que o tamanho total até agora (sem as colunas do tipo profundas) é um múltiplo do alinhamento requerido.The largest alignment requirement among all shallow columns is used, and 0-7 bytes of padding is added in such a way that the total size so far (without the deep type columns) is a multiple of the required alignment.
Os tipos profundos são os tipos (var)binary e (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
Colunas do tipo profundas de comprimento fixoFixed-length deep type columns SUM(tamanho das colunas do tipo profundo de tamanho fixo)SUM(size of fixed length deep type columns)

O tamanho de cada coluna é o seguinte:The size of each column is as follows:

i para char(i) e binary(i).i for char(i) and binary(i).

2 * i para nchar(i)2 * i for nchar(i)
As colunas do tipo profundas de comprimento fixo são colunas do tipo char(i), nchar(i) ou binary(i).Fixed-length deep type columns are columns of type char(i), nchar(i), or binary(i).
Tamanho calculado das colunas do tipo profundo de tamanho variávelVariable length deep type columns computed size SUM(tamanho calculado das colunas do tipo profundo de tamanho variável)SUM(computed size of variable length deep type columns)

O tamanho calculado de cada coluna é o seguinte:The computed size of each column is as follows:

i para varchar(i) e varbinary(i)i for varchar(i) and varbinary(i)

2 * i para nvarchar(i)2 * i for nvarchar(i)
Essa linha é aplicada somente ao tamanho calculado do corpo da linha.This row only applied to computed row body size.

As colunas do tipo profundas de comprimento variável são colunas do tipo varchar(i), nvarchar(i) ou varbinary(i).Variable-length deep type columns are columns of type varchar(i), nvarchar(i), or varbinary(i). O tamanho calculado é determinado pelo comprimento máximo (i) da coluna.The computed size is determined by the max length (i) of the column.
Tamanho real das colunas do tipo profundo de tamanho variávelVariable length deep type columns actual size SUM(tamanho real das colunas do tipo profundo de tamanho variável)SUM(actual size of variable length deep type columns)

O tamanho real de cada coluna é o seguinte:The actual size of each column is as follows:

n, onde n é o número de caracteres armazenados na coluna, para varchar(i).n, where n is the number of characters stored in the column, for varchar(i).

2 * n, onde n é o número de caracteres armazenados na coluna, para nvarchar(i).2 * n, where n is the number of characters stored in the column, for nvarchar(i).

n, onde n é o número de bytes armazenados na coluna, para varbinary(i).n, where n is the number of bytes stored in the column, for varbinary(i).
Essa linha é aplicada somente ao tamanho real do corpo da linha.This row only applied to actual row body size.

O tamanho real é determinado pelos dados armazenados nas colunas da linha.The actual size is determined by the data stored in the columns in the row.

Exemplo: cálculo do tamanho da tabela e da linhaExample: Table and Row Size Computation

Para índices de hash, o número de buckets real é arredondado até a potência mais próxima de 2.For hash indexes, the actual bucket count is rounded up to the nearest power of 2. Por exemplo, se o bucket_count especificado for 100000, o número real de buckets do índice será 131072.For example, if the specified bucket_count is 100000, the actual bucket count for the index is 131072.

Considere uma tabela Orders com a seguinte definição:Consider an Orders table with the following definition:

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  

Observe que esta tabela tem um índice de hash e um índice não clusterizado (a chave primária).Notice that this table has one hash index and a nonclustered index (the primary key). Ela também tem três colunas de tamanho fixo e uma coluna de tamanho variável, além disso, uma das colunas é NULLable (OrderDescription).It also has three fixed-length columns and one variable-length column, with one of the columns being NULLable (OrderDescription). Vamos supor que a tabela Orders tenha 8379 linhas e o tamanho médio dos valores na coluna OrderDescription seja de 78 caracteres.Let's assume the Orders table has 8379 rows, and the average length of the values in the OrderDescription column is 78 characters.

Para determinar o tamanho da tabela, primeiro determine o tamanho dos índices.To determine the table size, first determine the size of the indexes. O bucket_count para ambos os índices é especificado como 10000.The bucket_count for both indexes is specified as 10000. Isso é arredondado para a potência mais próxima de 2: 16384.This is rounded up to the nearest power of 2: 16384. Consequentemente, o tamanho total dos índices da tabela Orders é:Therefore, the total size of the indexes for the Orders table is:

8 * 16384 = 131072 bytes  

O que sobra é o tamanho dos dados da tabela, que éWhat remains is the table data size, which is,

[row size] * [row count] = [row size] * 8379  

(A tabela de exemplo tem 8379 linhas.) Agora, temos:(The example table has 8379 rows.) Now, we have:

[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 o [tamanho real do corpo da linha]:Next, let's calculate [actual row body size]:

  • Colunas do tipo superficial:Shallow type columns:

    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.Shallow column padding is 0, as the total shallow column size is even.

  • Matriz de deslocamento para colunas do tipo profundas:Offset array for deep type columns:

    2 + 2 * [number of deep type columns] = 2 + 2 * 1 = 4  
    
  • Matriz NULL = 1NULL array = 1

  • Preenchimento da matriz NULL = 1, pois o tamanho da matriz é ímpar e há uma coluna do tipo profunda.NULL array padding = 1, as the NULL array size is odd and there is a deep type column.

  • PreenchimentoPadding

    • 8 é o maior requisito de alinhamento.8 is the largest alignment requirement.

    • O tamanho até agora é 16 + 0 + 4 + 1 + 1 = 22.Size so far is 16 + 0 + 4 + 1 + 1 = 22.

    • O múltiplo mais próximo de 8 é 24.Nearest multiple of 8 is 24.

    • O preenchimento total é 24 - 22 = 2 bytes.Total padding is 24 - 22 = 2 bytes.

  • Não há colunas de tipo profundas de comprimento fixo (colunas de tipo profundas de comprimento fixo: 0.).There are no fixed-length deep type columns (Fixed-length deep type columns: 0.).

  • O tamanho real da coluna do tipo profunda é 2 * 78 = 156.The actual size of deep type column is 2 * 78 = 156. A única coluna do tipo profundo OrderDescription tem o tipo nvarchar.The single deep type column OrderDescription has type nvarchar.

[actual row body size] = 24 + 156 = 180 bytes  

Para concluir o cálculo:To complete the calculation:

[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.Total table size in memory is thus approximately 2 megabytes. Isso não conta para a possível sobrecarga incorrida pela alocação de memória, bem como para qualquer controle de versão de linha exigido para as transações que acessam essa tabela.This does not account for potential overhead incurred by memory allocation as well as any row versioning required for the transactions accessing this table.

A memória real alocada para essa tabela, e usada por ela, bem como seus índices, podem ser obtidos por meio da seguinte consulta:The actual memory allocated for and used by this table and its indexes can be obtained through the following query:

select * from sys.dm_db_xtp_table_memory_stats  
where object_id = object_id('dbo.Orders')  

Limitações de coluna fora da linhaOff-row Column Limitations

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 abaixo:Certain limitations and caveats to using off-row columns in a memory-optimized table are listed below:

  • Se houver um índice columnstore em uma tabela com otimização de memória, todas as colunas deverão caber na linha.If there is a columnstore index on a memory-optimized table, then all the columns must fit in-row.
  • Todas as colunas de chave de índice devem ser armazenadas na linha.All index key columns must be stored in-row. Se uma coluna de chave de índice não couber na linha, a adição do índice falhará.If an index key column doesn't fit in-row, adding the index fails.
  • Advertências sobre alterar uma tabela com otimização de memória com colunas fora da linha.Caveats on altering a memory-optimized table with off-row columns.
  • Para LOBs, a limitação de tamanho espelha-se na de tabelas baseadas em disco (limite de 2 GB em valores de LOB).For LOBs the size limitation mirrors that of disk based tables (2GB limit on LOB values).
  • Para otimizar o desempenho, é recomendável que a maioria das colunas caibam dentro de 8060 bytes.For optimal performance, it is recommended to have most columns fit within 8060 bytes.

A postagem no blog Novidades para OLTP in-memory do SQL Server 2016 desde o CTP3 oferece mais detalhes sobre algumas dessas complexidades.What's new for In-Memory OLTP in SQL Server 2016 since CTP3 blog post further details some of these intricacies.

Consulte TambémSee Also

Memory-Optimized TablesMemory-Optimized Tables