Tamaño de tabla y fila de las tablas con optimización para memoriaTable and Row Size in Memory-Optimized Tables

SE APLICA A: síSQL Server síAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Antes de SQL Server 2016 (13.x)SQL Server 2016 (13.x), el tamaño de los datos de una fila de una tabla optimizada para memoria no podía superar los 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. Pero a partir de SQL Server 2016 (13.x)SQL Server 2016 (13.x) y en Azure SQL Database es posible crear una tabla optimizada para memoria con varias columnas de gran tamaño (por ejemplo, varias columnas varbinary(8000)) y columnas LOB (es decir, varbinary(max), varchar(max) y nvarchar(max)) y realizar operaciones en ellas con módulos T-SQL compilados de forma nativa y tipos de tabla.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.

Las columnas que no quepan en el límite de tamaño de fila de 8060 bytes se colocan de forma no consecutiva, en una tabla interna aparte.Columns that do not fit in the 8060 byte row size limit are placed off-row, in a separate internal table. Cada columna no consecutiva tiene una tabla interna correspondiente que, a su vez, tiene un único índice no agrupado.Each off-row column has a corresponding internal table, which in turn has a single nonclustered index. Para obtener detalles sobre estas tablas internas usadas para columnas no consecutivas, vea 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).

Hay determinados escenarios donde resulta útil calcular el tamaño de la fila y la tabla:There are certain scenarios where it is useful to compute the size of the row and the table:

  • ¿Cuánta memoria usa una tabla?How much memory does a table use?

    • La cantidad de memoria utilizada por la tabla no se puede calcular con precisión.The amount of memory used by the table cannot be calculated exactly. Muchos factores afectan a la cantidad de memoria utilizada.Many factors affect the amount of memory used. Factores como la asignación de memoria basada en páginas, localidad, almacenamiento en caché y relleno.Factors such as page-based memory allocation, locality, caching, and padding. Además, varias versiones de fila que tengan transacciones activas asociadas o que estén esperando para la recolección de elementos no utilizados.Also, multiple versions of rows that either have active transactions associated or that are waiting for garbage collection.

    • El tamaño mínimo necesario para los datos y los índices de la tabla viene proporcionado por el cálculo del [tamaño de la tabla], descrito a continuación.The minimum size required for the data and indexes in the table is given by the calculation for [table size], discussed below.

    • El cálculo del uso de memoria es, en el mejor caso, una aproximación y es preferible incluir el planeamiento de la capacidad en los planes de implementación.Calculating memory use is at best an approximation and you are advised to include capacity planning in your deployment plans.

  • El tamaño de los datos de una fila y si cabe en la limitación de tamaño de fila 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 estas preguntas, utilice el cálculo del [tamaño del texto de la fila], descrito a continuación.To answer these questions, use the computation for [row body size], discussed below.

Una tabla optimizada para memoria consta de una colección de filas e índices que contienen punteros a las filas.A memory-optimized table consists of a collection of rows and indexes that contain pointers to rows. La ilustración siguiente muestra una tabla con índices y filas, que a su vez tienen encabezados de fila y cuerpos:The following figure illustrates a table with indexes and rows, which in turn have row headers and bodies:

Tabla optimizada en memoria.Memory optimized table.
La tabla con optimización para memoria, que consta de índices y filas.Memory-optimized table, consisting of indexes and rows.

Cálculo del tamaño de una tablaComputing Table Size

El tamaño en memoria de una tabla, en bytes, se calcula de la forma siguiente: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])  

El tamaño de un índice hash se fija en el momento de creación de la tabla y depende del número real de cubos.The size of a hash index is fixed at table creation time and depends on the actual bucket count. El valor bucket_count especificado con la especificación de índice se redondea a la potencia más cercana de 2 para obtener el número real de cubos.The bucket_count specified with the index definition is rounded up to the nearest power of 2 to obtain the actual bucket count. Por ejemplo, si el valor bucket_count especificado es 100 000, el número real de cubos para el índice es 131 072.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]  

El tamaño de un índice no agrupado está en el orden de [row count] * [index key size].The size of a nonclustered index is in the order of [row count] * [index key size].

El tamaño de fila se calcula agregando el encabezado y el cuerpo: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]  

Cálculo del tamaño del cuerpo de la filaComputing Row Body Size

Estructura de filas Las filas de una tabla optimizada para memoria tienen los componentes siguientes:Row Structure The rows in a memory-optimized table have the following components:

  • El encabezado de fila contiene la marca de tiempo necesaria para implementar las versiones de fila.The row header contains the timestamp necessary to implement row versioning. El encabezado de fila también contiene el puntero de índice para implementar el encadenamiento de filas en cubos de hash (descritos arriba).The row header also contains the index pointer to implement the row chaining in the hash buckets (described above).

  • El cuerpo de la fila contiene los datos de columna reales, lo que incluye cierta información auxiliar como la matriz NULL para las columnas que aceptan valores NULL y la matriz de desplazamiento para los tipos de datos de longitud variable.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.

La ilustración siguiente muestra la estructura de la fila de una tabla que tenga dos índices:The following figure illustrates the row structure for a table that has two indexes:

Estructura de fila para una tabla que tiene dos índices Row structure for a table that has two indexes.

Las marcas de tiempo de inicio y fin indican el periodo en el que una determinada versión de fila es válida.The begin and end timestamps indicate the period in which a particular row version is valid. Las transacciones que se inician en este intervalo pueden ver esta versión de fila.Transactions that start in this interval can see this row version. Para obtener más detalles, vea Transactions with Memory-Optimized Tables(Transacciones con tablas con optimización para memoria).For more details see Transactions with Memory-Optimized Tables.

Los punteros de índice señalan a la siguiente fila de la cadena que pertenece al cubo de hash.The index pointers point to the next row in the chain belonging to the hash bucket. La ilustración siguiente muestra la estructura de una tabla con dos columnas (name, city) y dos índices, uno en el nombre de columna y en otro en la ciudad de la columna.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.

Estructura de una tabla con dos columnas e índices.Structure of a table with two columns and indexes.

En esta ilustración, se aplica un algoritmo hash a los nombres John y Jane para el primer cubo.In this figure, the names John and Jane are hashed to the first bucket. Se aplica el algoritmo hash a Susan para el segundo cubo.Susan is hashed to the second bucket. Se aplica el algoritmo hash a las ciudades Beijing y Bogotá para el primer cubo.The cities Beijing and Bogota are hashed to the first bucket. Se aplica el algoritmo hash a París y Praga para el segundo cubo.Paris and Prague are hashed to the second bucket.

Por tanto, las cadenas para el índice hash en el nombre son las siguientes:Thus, the chains for the hash index on name are as follows:

  • Primer cubo: (John, Beijing); (John, París); (Jane, Praga)First bucket: (John, Beijing); (John, Paris); (Jane, Prague)

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

Las cadenas para el índice de la ciudad son las siguientes:The chains for the index on city are as follows:

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

  • Segundo cubo: (John, París), (Jane, Praga)Second bucket: (John, Paris), (Jane, Prague)

Una marca de tiempo de extremo ∞ (infinito) indica que esta es la versión no válida de la fila.An end timestamp ∞ (infinity) indicates that this is the currently valid version of the row. La fila no se ha actualizado ni se ha eliminado desde que esta versión de fila se escribió.The row has not been updated or deleted since this row version was written.

Para un tiempo mayor que 200, la tabla contiene las filas siguientes:For a time greater than 200, the table contains the following rows:

NombreName CityCity
JohnJohn BeijingBeijing
JaneJane PragaPrague

Sin embargo, cualquier transacción activa con el tiempo de inicio 100 verá la versión siguiente de la tabla:However, any active transaction with begin time 100 will see the following version of the table:

NombreName CityCity
JohnJohn ParisParis
JaneJane PragaPrague
SusanSusan BogotáBogata

El recálculo de [tamaño del cuerpo de la fila] se describe en la siguiente tabla.The calculation of [row body size] is discussed in the following table.

Hay dos cálculos diferentes para el tamaño del cuerpo de la fila: el tamaño calculado y el tamaño real:There are two different computations for row body size: computed size and the actual size:

  • El tamaño calculado, indicado mediante el tamaño del cuerpo calculado de la fila, se utiliza para determinar si la limitación de tamaño de fila de 8.060 bytes se supera.The computed size, denoted with computed row body size, is used to determine if the row size limitation of 8,060 bytes is exceeded.

  • El tamaño real, que se denomina tamaño del texto real de la fila, es el tamaño de almacenamiento real del cuerpo de la fila en memoria y en los archivos de puntos de comprobación.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.

Tanto tamaño del cuerpo calculado de la fila y tamaño del texto real de la fila se calculan de la misma forma.Both computed row body size and actual row body size are calculated similarly. La única diferencia es el cálculo de tamaño de las columnas (n)varchar(i) y varbinary(i), como se refleja en la parte inferior de la tabla siguiente.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. El tamaño del cuerpo calculado de la fila utiliza el tamaño i declarado como tamaño de columna, mientras que el tamaño del cuerpo real de la fila utiliza el tamaño real de los datos.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.

En la tabla siguiente se describe el cálculo del tamaño del cuerpo de fila, indicado como tamaño real del cuerpo de fila = SUM(tamaño de tipos superficiales) + 2 + 2 * número de columnas de 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.

SecciónSection TamañoSize ComentariosComments
Columnas de tipo superficialShallow type columns SUM([tamaño de tipos superficiales])SUM([size of shallow types]). El tamaño en bytes de los tipos individuales es el siguiente: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 (precisión <=18): 8Numeric (precision <=18): 8

Time: 8Time: 8

Numeric (precisión >18): 16Numeric(precision>18): 16

Uniqueidentifier: 16Uniqueidentifier: 16
Relleno superficial de la columnaShallow column padding Los valores posibles son:Possible values are:

1, si hay columnas de tipo profundo y el tamaño total de datos de las columnas superficiales es un número impar.1 if there are deep type columns and the total data size of the shallow columns is as odd number.

De lo contrario, es 00 otherwise
Los tipos profundos son (var)binary y (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
Matriz de desplazamiento para las columnas de tipo profundoOffset array for deep type columns Los valores posibles son:Possible values are:

0, si no hay columnas de tipos profundos0 if there are no deep type columns

2 + 2 * [número de columnas de tipo profundo], en caso contrario2 + 2 * [number of deep type columns] otherwise
Los tipos profundos son (var)binary y (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
Matriz NULLNULL array [número de columnas que admiten valores NULL] / 8, redondeado a bytes completos.[number of nullable columns] / 8, rounded up to full bytes. La matriz tiene un bit por cada columna que admite valores NULL.The array has one bit per nullable column. Se redondea a bytes completos.This is rounded up to full bytes.
Relleno de matriz NULLNULL array padding Los valores posibles son:Possible values are:

1, si hay columnas de tipo profundo y el tamaño de la matriz NULL es un número de bytes impar.1 if there are deep type columns and the size of the NULL array is an odd number of bytes.

De lo contrario, es 00 otherwise
Los tipos profundos son (var)binary y (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
RellenoPadding Si no hay columnas de tipos profundos: 0If there are no deep type columns: 0

Si hay columnas de tipo profundo, se agregan los bytes de relleno 0-7, según la alineación mayor requerida por una columna 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 columna superficial requiere una alineación igual a su tamaño según se documentó anteriormente, salvo en que las columnas GUID necesitan la alineación de 1 byte (no 16) y las columnas numéricas necesitan siempre la alineación 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). Se usa el requisito de alineación mayor entre todas las columnas superficiales y se agregan los bytes 0 a 7 de relleno de forma que el tamaño total (sin las columnas de tipo profundo) sea un múltiplo de la alineación requerida.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.
Los tipos profundos son (var)binary y (n)(var)char.Deep types are the types (var)binary and (n)(var)char.
Columnas de tipo profundo de longitud fijaFixed-length deep type columns SUM(tamaño de columnas de tipo profundo de longitud fija)SUM(size of fixed length deep type columns)

El tamaño de cada columna es el siguiente:The size of each column is as follows:

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

2 * i para nchar(i)2 * i for nchar(i)
Las columnas de tipo profundo de longitud fija son de tipo char(i), nchar(i) o binary(i).Fixed-length deep type columns are columns of type char(i), nchar(i), or binary(i).
Columnas de tipo profundo de longitud variable tamaño calculadoVariable length deep type columns computed size SUM(tamaño calculado de columnas de tipo profundo de longitud variable)SUM(computed size of variable length deep type columns)

El tamaño calculado de cada columna es el siguiente:The computed size of each column is as follows:

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

2 * i para nvarchar(i)2 * i for nvarchar(i)
Esta fila solo se aplica al tamaño del texto calculado de la fila.This row only applied to computed row body size.

Las columnas de tipo profundo de longitud variable son de tipo varchar(i), nvarchar(i) o varbinary(i).Variable-length deep type columns are columns of type varchar(i), nvarchar(i), or varbinary(i). El tamaño calculado se determina mediante la longitud máxima (i) de la columna.The computed size is determined by the max length (i) of the column.
Columnas de tipo profundo de longitud variable tamaño realVariable length deep type columns actual size SUM(tamaño real de columnas de tipo profundo de longitud variable)SUM(actual size of variable length deep type columns)

El tamaño real de cada columna es el siguiente:The actual size of each column is as follows:

n, donde n es el número de caracteres almacenados en la columna, para varchar(i).n, where n is the number of characters stored in the column, for varchar(i).

2 * n, donde n es el número de caracteres almacenados en la columna, para nvarchar(i).2 * n, where n is the number of characters stored in the column, for nvarchar(i).

n, donde n es el número de bytes almacenados en la columna, para varbinary(i).n, where n is the number of bytes stored in the column, for varbinary(i).
Esta fila solo se aplica al tamaño del texto real de la fila.This row only applied to actual row body size.

El tamaño real se determina con los datos almacenados en las columnas de la fila.The actual size is determined by the data stored in the columns in the row.

Ejemplo: cálculo del tamaño de fila y tablaExample: Table and Row Size Computation

Para los índices hash, el número de cubos real se redondea a la potencia más cercana de 2.For hash indexes, the actual bucket count is rounded up to the nearest power of 2. Por ejemplo, si el valor bucket_count especificado es 100 000, el número real de cubos para el índice es 131 072.For example, if the specified bucket_count is 100000, the actual bucket count for the index is 131072.

Considere una tabla Orders con la definición siguiente: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 tabla tiene un índice hash y un índice no clúster (la clave principal).Notice that this table has one hash index and a nonclustered index (the primary key). También tiene tres columnas de longitud fija y una columna de longitud variable, y una de las columnas admite valores NULL (OrderDescription).It also has three fixed-length columns and one variable-length column, with one of the columns being NULLable (OrderDescription). Imaginemos que la tabla Orders tiene 8 379 filas y la longitud promedio de los valores de la columna OrderDescription es 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 el tamaño de la tabla, primero determine el tamaño de los índices.To determine the table size, first determine the size of the indexes. El bucket_count para ambos índices se especifica como 10000.The bucket_count for both indexes is specified as 10000. Se redondea a la potencia más cercana de 2: 16384.This is rounded up to the nearest power of 2: 16384. Por consiguiente, el tamaño total de los índices de la tabla Orders es:Therefore, the total size of the indexes for the Orders table is:

8 * 16384 = 131072 bytes  

Lo que permanece en el tamaño de los datos de la tabla, que esWhat remains is the table data size, which is,

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

(La tabla de ejemplo tiene 8379 filas.) Ahora, tenemos:(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  

A continuación, vamos a calcular [tamaño del cuerpo real de la fila]:Next, let's calculate [actual row body size]:

  • Columnas de tipo superficial:Shallow type columns:

    SUM([size of shallow types]) = 4 [int] + 4 [int] + 8 [datetime] = 16  
    
  • El relleno superficial de la columna es 0, ya que el tamaño total de la columna es uniforme.Shallow column padding is 0, as the total shallow column size is even.

  • Matriz de desplazamiento para las columnas de tipo profundo:Offset array for deep type columns:

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

  • El relleno NULL de matriz = 1, como el tamaño de la matriz NULL es impar y hay una columna de tipo profundo.NULL array padding = 1, as the NULL array size is odd and there is a deep type column.

  • RellenoPadding

    • 8 es el requisito mayor de alineación.8 is the largest alignment requirement.

    • El tamaño es hasta ahora 16 + 0 + 4 + 1 + 1 = 22.Size so far is 16 + 0 + 4 + 1 + 1 = 22.

    • El múltiplo más cercano de 8 es 24.Nearest multiple of 8 is 24.

    • El relleno total es 24 - 22 = 2 bytes.Total padding is 24 - 22 = 2 bytes.

  • No hay columnas de tipo profundo de longitud fija (columnas de tipo profundo de longitud fija: 0.).There are no fixed-length deep type columns (Fixed-length deep type columns: 0.).

  • El tamaño real de la columna de tipo profundo es 2 * 78 = 156.The actual size of deep type column is 2 * 78 = 156. La columna de tipo profundo único OrderDescription tiene el tipo nvarchar.The single deep type column OrderDescription has type nvarchar.

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

Para completar el cálculo:To complete the calculation:

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

El tamaño total de la tabla en memoria es de aproximadamente 2 megabytes.Total table size in memory is thus approximately 2 megabytes. Esto no tiene en cuenta la sobrecarga potencial provocada por la asignación de memoria junto con las versiones de fila necesarias para las transacciones que tienen acceso a esta tabla.This does not account for potential overhead incurred by memory allocation as well as any row versioning required for the transactions accessing this table.

La memoria real asignada a esta tabla y usada por ella y sus índices se pueden obtener con la consulta siguiente: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')  

Limitaciones de las columnas no consecutivasOff-row Column Limitations

A continuación se muestran varias limitaciones y advertencias relacionadas con el uso de columnas no consecutivas en una tabla optimizada para memoria:Certain limitations and caveats to using off-row columns in a memory-optimized table are listed below:

  • Si hay un índice de almacén de columnas en una tabla optimizada para memoria, todas las columnas deben ajustarse de forma consecutiva.If there is a columnstore index on a memory-optimized table, then all the columns must fit in-row.
  • Todas las columnas de clave de índice se deben almacenar de forma consecutiva.All index key columns must be stored in-row. Si una columna de clave de índice no se ajusta de forma consecutiva, se produce un error al agregar el índice.If an index key column doesn't fit in-row, adding the index fails.
  • Advertencias sobre la modificación de una tabla optimizada para memoria con columnas no consecutivas.Caveats on altering a memory-optimized table with off-row columns.
  • En el caso de los LOB, la limitación de tamaño es igual a la de las tablas basadas en disco (límite de 2 GB sobre los valores de LOB).For LOBs the size limitation mirrors that of disk based tables (2GB limit on LOB values).
  • Para obtener un rendimiento óptimo, se recomienda que la mayoría de las columnas se ajusten a 8060 bytes.For optimal performance, it is recommended to have most columns fit within 8060 bytes.

La entrada de blog What's new for In-Memory OLTP in SQL Server 2016 since CTP3 (Novedades de OLTP en memoria en SQL Server 2016 desde CTP3) detalla algunas de estas particularidades.What's new for In-Memory OLTP in SQL Server 2016 since CTP3 blog post further details some of these intricacies.

Consulte tambiénSee Also

Tablas con optimización para memoriaMemory-Optimized Tables