Tablas de montones en SQL Server

En el último post de esta serie de blogs, hablé de lo que era un índice de árbol B y expliqué brevemente su historia.

Antes de profundizar en los índices que utiliza SQL Server, es importante establecer la base de las estructuras de datos. La tabla más básica que puede crear es un montón. Un montón es una estructura sin clasificar de páginas que no están vinculadas entre sí.

¿Cuándo debe usar pilas?

En la mayoría de los casos, la recomendación de las mejores prácticas es evitar los montones de SQL Server. Kimberly Tripp explica algunas de las razones y el debate aquí, pero cubriré el de alto nivel. Los montones, al ser la estructura de almacenamiento más básica, carecen de características que mejoren el rendimiento de las cargas de trabajo normales (OLTP). Algunos argumentan que los montones son mejores para las operaciones de extracción, transformación y carga (ETL) porque hay menos páginas que mantener durante las operaciones de escritura (ACTUALIZAR/INSERTAR/ELIMINAR). Específicamente, los montones pueden ser más rápidos que un índice de árbol B si se producen divisiones de páginas y se deben actualizar muchos niveles del índice. Además, el identificador de fila (RID), que se analiza más adelante, es de solo 8 bytes. Esto puede funcionar mejor que un índice agrupado con muchas columnas de clave, porque los índices no agrupados deben almacenar la clave agrupada o ELIMINAR para cada fila indexada. Por lo general, recomiendo usar un índice agrupado (más en índices agrupados a medida que continuamos con esta serie) en lugar de un montón, pero, si los usa, úselos solo cuando haya demostrado que serán más eficientes que un índice agrupado. Después de discutir la estructura de un montón, describiré las preocupaciones de rendimiento del uso de montones.

Estructura de montón

Un montón es una agrupación de páginas sin clasificar que no están vinculadas. La anatomía de la página está fuera del alcance de esta serie, ya que todos los tipos de tablas indexadas y no indexadas utilizan la misma estructura de página, pero lo animo a que consulte aquí y aquí para obtener más información.

Un montón se compone de una o más páginas de mapa de asignación de índices (IAM) que apuntan a las páginas de datos que componen el montón. La única excepción a esto es cuando tiene una fila que se ha actualizado y ya no puede caber en su página. En ese caso, se obtiene un puntero de reenvío a la fila que se ha movido a una página existente con espacio o a una página nueva. Es posible que usted produzca una cadena de registros de reenvío si la fila continúa necesitando reubicación por operaciones posteriores.

heap-structure-1

referencia de Imagen: MSDN

UN montón tiene una fila en sys.las particiones por partición y su index_id serán iguales a cero. En este registro, la primera_página apunta a la primera de las páginas del mapa de asignación de índices (IAM). Una página de IAM asigna páginas para cada unidad de asignación y administra trozos de 4 GB de la tabla. Las unidades de asignación incluyen:

  1. Unidad de asignación IN_ROW_DATA
  2. Unidad de asignación LOB_DATA
  3. Unidad de asignación ROW_OVERFLOW_DATA

La página IAM contiene el encabezado estándar de 96 bytes seguido del encabezado IAM. El encabezado IAM comienza con ocho ranuras para asignaciones de extensión mixta y luego un mapa de bits de 8000 bytes para localizar extensiones uniformes asignadas a la tabla.

Una extensión mixta es aquella que contiene páginas de más de una tabla. Esto también se conoce como una extensión compartida. El propósito de una extensión mixta es ahorrar espacio con tablas de menos de 64 KB. Una extensión uniforme es aquella en la que todas las páginas de la extensión pertenecen a esa tabla única.

mixed-extent

Referencia de imagen: La Extensión

Para investigar nuestra página de IAM, primero debemos localizarla.

DBCC IND (‘demo’, ‘ dbo.demo’,1)

dbcc-ind

Con DBCC IND, tenemos 17 páginas de datos y 1 página de IAM. Las páginas IAM no son auto-referenciadas, por lo tanto el IAMFID (Id de archivo IAM) y el IAMPID (Id de página IAM) son NULOS. Esto significa que nuestra página IAM está en el Id de archivo (PageFID) 1 y es el Id de página (PagePID) 297. La PÁGINA DE DBCC nos permitirá ver nuestra página de IAM.

DBCC TRACEON(3604);

DBCC PAGE (‘demo’,1,297,3);

dbcc-page-iam

En AMARILLO hay ocho sola página asignaciones en nuestro extensión mixta. En NARANJA, las extensiones uniformes se muestran en rangos.

Implicaciones de rendimiento

Realizar varias operaciones de lenguaje de manipulación de datos (DML) en un montón tiene estos efectos.

  • INSERTAR: Se pueden colocar nuevas filas en la primera página disponible con espacio suficiente. Por lo tanto, cada vez que se inserta una nueva fila, es probable que se agregue a la última página.
  • ACTUALIZAR – Filas puede permanecer en la misma página si cabe en la página después de la actualización, si no, será eliminado de la página actual y la coloca en la primera página con espacio suficiente y una nueva puntero está escrito en su página original.
  • ELIMINAR: Los datos no se sobrescriben, el espacio solo se marca como disponible para reutilizar. Esto puede hacer que su tabla consuma significativamente más espacio en disco del necesario.SELECCIÓN
  • : Para la mayoría de las consultas, se deberá leer un análisis de tabla en toda la tabla. La excepción a esto es cuando hay disponible un índice adecuado no agrupado. Discutiremos los índices no agrupados más adelante en esta serie.

Lee

Sin ningún índice de soporte, un montón requiere un análisis completo de la tabla para cualquier consulta. Esto se debe a que la estructura física no está ordenada y no hay enlaces de página para admitir exploraciones de rango.

ESTABLECER IO DE ESTADÍSTICAS EN

DBCC IND (‘demo’, ‘ dbo.demo’,1)

SELECCIONE COUNT(*)
DE dbo.demo

Repetir nuestro comando DBCC IND desde arriba nos recuerda que tenemos 18 páginas para esta tabla. 17 páginas de datos y 1 página IAM.

– Resultados DBCC IND eliminados porque ya se muestra arriba.
-18 resultados de fila, indica 18 páginas.

(18 filas afectadas)
Ejecución de DBCC completada. Si DBCC imprimió mensajes de error, póngase en contacto con el administrador del sistema.

Recuento de filas
—-
5031
(1 fila(s) afectada (s)

Tabla ‘Demo’. Examinar el número 1, lecturas lógicas 17, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de lob 0, lecturas físicas de lob 0, lecturas anticipadas de lob 0.
(1 fila (s) afectada (s)

Tenga en cuenta las 17 lecturas lógicas de nuestra consulta. Esto se debe a que la página IAM no se cuenta en la métrica lecturas lógicas y realizamos un análisis completo de la tabla con 18 páginas.

Escritura y punteros de avance

Como se mencionó anteriormente, las inserciones y eliminaciones son bastante sencillas. Se produce una INSERCIÓN en la primera página con espacio disponible, incluso si eso significa la creación de una nueva página. Las eliminaciones se realizan desasignando el espacio que ocupaba la fila.

Las actualizaciones son donde las cosas van cuesta abajo por montones. Cuando se actualiza un tipo de datos de longitud fija, la actualización se realizará in situ. Si se actualiza un tipo de datos de longitud variable y la longitud ha aumentado, existe la posibilidad de que la fila ya no quepa en la misma página. Cuando la fila ya no encaja, se coloca un puntero de reenvío en el espacio donde estaba la fila original y la fila se reubica en una nueva página. Este proceso tiene un impacto negativo en el rendimiento de lectura. Vamos a demostrarlo.

Originalmente todos nuestros registros en el dbo.la tabla de demostración contenía VARCHARs con 9 cadenas de caracteres.

SELECCIONE TOP 5 *
DE dbo.demo

 datos de la tabla de demostración

Para crear algunos punteros hacia adelante, actualizaremos todas nuestras filas y aumentaremos la longitud de la columna varLen.

actualizar dbo.demo
set varLen = replicate (‘a’,100)

Ahora tenemos 95 páginas en nuestro montón porque todas nuestras filas se han reubicado en páginas nuevas con punteros delanteros dejados atrás.

DBCC IND (‘demo’, ‘ dbo.demo’,1)

dbcc-output-1

Como se puede imaginar, con montones que requieren escaneos de tablas completos, necesitaremos leer muchas más páginas para la misma cantidad de registros ahora.

ESTABLECER IO DE ESTADÍSTICAS EN

SELECCIONAR COUNT(*)
DESDE dbo.demo
(1 fila (s) afectada (s)

Tabla ‘Demo’. Examinar el número 1, lecturas lógicas 4405, lecturas físicas 0, lecturas anticipadas 0, lecturas lógicas de lob 0, lecturas físicas de lob 0, lecturas anticipadas de lob 0.

Nuestras lecturas aumentaron drásticamente porque se tuvieron que leer las páginas originales y luego se tuvieron que seguir los punteros delanteros. El impacto en el rendimiento y el desperdicio de espacio en memoria y en disco es la razón por la que necesitamos índices agrupados.

La próxima vez

La estructura de datos del montón no es un índice y a menudo es la estructura de datos de peor rendimiento para sus tablas en SQL Server. Lo cubrimos hoy como una base de entendimiento que será útil al discutir los índices. En la siguiente parte de esta serie, se tratarán los índices agrupados, una estructura de árbol B+para los datos de la tabla.

Deja una respuesta

Tu dirección de correo electrónico no será publicada.