Montones (tablas sin índices clúster)

Se aplica a:SQL ServerAzure SQL DatabaseInstancia administrada de Azure SQLBase de datos SQL en Microsoft Fabric

Un montón es una tabla que no tiene un índice clúster. Puedes crear uno o más índices no agrupados en tablas almacenadas como un heap. El montón almacena datos sin especificar un orden. Normalmente, el heap almacena los datos inicialmente en el orden en que insertas las filas. Sin embargo, el motor de base de datos puede reorganizar los datos dentro del montón para almacenar las filas de forma eficiente. En los resultados de la consulta, no puedes predecir el orden de los datos. Para garantizar el orden de las filas que se devuelven de un montón, debe usar la cláusula ORDER BY. Para especificar un orden lógico permanente para almacenar las filas, crea un índice agrupado en la tabla, de modo que la tabla no sea un heap.

Cuándo se usa un montón

Un montón es ideal para tablas que frecuentemente truncas y recargas. El Motor de base de datos optimiza el espacio en un heap llenando el espacio más temprano disponible.

Ten en cuenta lo siguiente:

• Localizar espacio libre en un heap puede ser costoso, especialmente si se producen muchas eliminaciones o actualizaciones.
• Los índices agrupados ofrecen un rendimiento estable para tablas que no se truncan con frecuencia.

Para tablas que se truncan o recrean regularmente, como las temporales o las de staging, usar un heap suele ser más eficiente.

La elección entre usar un montón y un índice agrupado puede afectar significativamente al rendimiento y la eficacia de la base de datos.

Cuando almacenas una tabla como un heap, identificas filas individuales por referencia a un identificador de fila (RID) de 8 bytes que consiste en el número de archivo, el número de página de datos y la ranura en la página (FileID:PageID:SlotID). El identificador de fila es una estructura pequeña y eficaz.

Utiliza heaps como tablas de staging para operaciones de inserción grandes y no ordenadas. Como los heaps no aplican un orden de inserción estricto, la operación de inserción suele ser más rápida que una inserción equivalente en un índice agrupado. Si lees y procesas los datos del heap hasta convertirlos en un destino final, considera crear un índice estrecho y no agrupado que cubra el predicado de búsqueda que utiliza la consulta.

También puedes usar heaps cuando siempre accedes a datos a través de índices no agrupados y el RID es más pequeño que una clave de índice agrupada.

Si una tabla es un montón y no tiene índices no agrupados, entonces debes leer toda la tabla (un escaneo de tabla) para encontrar cualquier fila. SQL Server no puede buscar un RID directamente en el heap. Este comportamiento puede ser aceptable si la tabla es pequeña.

Cuándo no se usa un montón

No uses un heap cuando los datos se devuelven con frecuencia en orden ordenado. Un índice agrupado en la columna de ordenación puede evitar la operación de ordenación.

No uses un montón cuando los datos están frecuentemente agrupados. Los datos deben ordenarse antes de agruparse, y un índice agrupado en la columna de ordenación puede evitar la operación de ordenación.

No uses un heap cuando los rangos de datos se consultan frecuentemente desde la tabla. Un índice clúster en la columna de rango impedirá que se ordene el montón completo.

No uses un heap cuando no hay índices no agrupados y la tabla es grande. La única aplicación de este diseño es devolver todo el contenido de la tabla sin un orden especificado. En un heap, el Motor de base de datos lee todas las filas para encontrar una fila determinada.

No uses un heap si actualizas los datos con frecuencia. Si actualizas un registro y la actualización usa más espacio en las páginas de datos del que usa actualmente, el registro se mueve a una página de datos que tiene suficiente espacio libre. Este movimiento crea un registro reenviado que apunta a la nueva ubicación de los datos. El puntero de reenvío se escribe en la página que contenía los datos anteriormente, para indicar la nueva ubicación física. Este movimiento introduce fragmentación en el heap. Cuando el Motor de base de datos examina un heap, sigue estos punteros. Esta acción limita el rendimiento de lectura anticipada y puede generar e/s adicional, lo que reduce el rendimiento del escaneo.

Administración de montones

Para crear un montón, cree una tabla sin un índice clúster. Si la tabla ya tiene un índice clúster, quite el índice clúster para convertir de nuevo la tabla en un montón.

Para quitar un montón, cree un índice clúster en el montón.

Para volver a crear un montón a fin de recuperar el espacio desaprovechado:

• Cree un índice agrupado en el heap y luego elimine ese índice agrupado.
• Use el comando ALTER TABLE ... REBUILD para reconstruir el montículo.

Identificación de montones

La siguiente consulta devuelve la lista de montones de la base de datos actual. La lista incluye lo siguiente:

• Nombres de tabla
• Nombres de esquema
• Número de filas
• Tamaño de tabla en KB
• Tamaño del índice en KB
• Espacio sin usar
• Una columna para identificar un montículo

SELECT t.name AS 'Your TableName',
       s.name AS 'Your SchemaName',
       p.rows AS 'Number of Rows in Your Table',
       SUM(a.total_pages) * 8 AS 'Total Space of Your Table (KB)',
       SUM(a.used_pages) * 8 AS 'Used Space of Your Table (KB)',
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 'Unused Space of Your Table (KB)',
       CASE
           WHEN i.index_id = 0 THEN 'Yes'
           ELSE 'No'
       END AS 'Is Your Table a Heap?'
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units AS a
         ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas AS s
         ON t.schema_id = s.schema_id
WHERE i.index_id <= 1 -- 0 for Heap, 1 for Clustered Index
GROUP BY t.name, s.name, i.index_id, p.rows
ORDER BY 'Your TableName';

Estructuras de montón

Un montón es una tabla que no tiene un índice clúster. Los montones tienen una fila en sys.partitions, con index_id = 0 para cada partición que usa el montón. De forma predeterminada, un montón contiene una sola partición. Cuando un montón tiene varias particiones, cada partición tiene una estructura de montón que contiene los datos de esa partición en concreto. Por ejemplo, si un montón tiene cuatro particiones, existirán cuatro estructuras de montón, una en cada partición.

Dependiendo de los tipos de datos en el heap, cada estructura de heap tiene una o más unidades de asignación para almacenar y gestionar los datos de una partición específica. Como mínimo, cada heap tiene una IN_ROW_DATA unidad de asignación por partición. La estructura de heap también tiene una LOB_DATA unidad de asignación por partición, si contiene columnas de objetos grandes (LOB). También tiene una ROW_OVERFLOW_DATA unidad de asignación por partición, si contiene columnas de longitud variable que superan el límite de fila de 8.060 bytes.

La columna first_iam_page en la sys.system_internals_allocation_units vista del sistema apunta a la primera página del Mapa de Asignación de Índices (IAM) en la cadena de páginas IAM que gestionan el espacio asignado al montón en una partición específica. SQL Server usa las páginas IAM para desplazarse por el montón. Las páginas de datos y las filas dentro de ellas no están en un orden específico ni están enlazadas. La única conexión lógica entre las páginas de datos es la información registrada en las páginas IAM.

Puedes realizar escaneos de tablas o lecturas en serie de un heap escaneando las páginas IAM para encontrar las extensiones que contienen las páginas del heap. Como el IAM representa las extensiones en el mismo orden en que existen en los archivos de datos, esta estructura significa que los escaneos de heap serial avanzan secuencialmente a través de cada archivo. Usar las páginas IAM para establecer la secuencia de escaneo también significa que las filas del heap normalmente no se devuelven en el orden en que fueron insertadas.

La siguiente ilustración muestra cómo el motor de la base de datos de SQL Server usa las páginas IAM para recuperar las filas de datos en un solo montón de partición.

Contenido relacionado

• CREATE INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Índices agrupados y no agrupados