Infra

En nuestro pasado artículo vimos como SQL Server organizaba el almacenamiento de los datos en páginas de datos y como lo administraba gracias a páginas de metadatos. En ese artículo, os prometí que hablaríamos más de estas páginas de asignación. Es muy importante entender estos conceptos pues son imprescindibles para poder hacer una optimización avanzada y enfrentar con solvencia problemas complejos de almacenamiento. 

Como ya vimos en el pasado artículo, SQL Server administra el almacenamiento mediante estructuras internas diseñadas para optimizar la asignación y reutilización del espacio. Dentro de estas estructuras, las páginas de asignación juegan un papel clave en la gestión de las páginas y extensiones dentro de los archivos de datos (.mdf o .ndf). Estas páginas permiten un acceso eficiente a los datos y minimizan la fragmentación, asegurando el mejor uso posible del espacio disponible.

En este artículo, profundizaremos en la función y el comportamiento de las páginas de asignación en SQL Server, complementando la información que vimos y ajustando los detalles donde sea necesario para proporcionarnos una visión más completa.

Estructuras de asignación de espacio en SQL Server

Resumamos un poco lo que vimos en el pasado artículo. SQL Server organiza el almacenamiento en páginas de 8 KB, que se agrupan en extents de 64 KB (8 páginas). Para administrar la asignación de estas páginas y extensiones, SQL Server utiliza las siguientes estructuras especializadas:

• PFS (Page Free Space) → Rastreo del uso del espacio en páginas individuales.
• GAM (Global Allocation Map) → Rastreo de extensiones asignadas y libres.
• SGAM (Shared Global Allocation Map) → Rastreo de extensiones mixtas con espacio libre.
• IAM (Index Allocation Map) → Mapeo de asignaciones de páginas y extensiones a objetos específicos.

Ahora que ya hemos refrescado ideas vamos a  ver en detalle cada una de estas estructuras. Pero antes una pequeña aclaración. Todos los archivos de datos (mdf y ndf) empiezan siempre con la misma estructura, una primera página (página 0) con cabeceras, y a continuación una página PFS, una GAM y una SGAM (página 1, 2 y 3 respectivamente)

Páginas PFS (Page Free Space)

Para gestionar correctamente la asignación de almacenamiento es imprescindible tener controlado en todo momento cuánto espacio tienes libre. Las PFS controlan cuánto espacio libre hay dentro de cada página individual en un archivo de base de datos. Son fundamentales para la inserción eficiente de datos, ya que SQL Server puede identificar rápidamente qué página tiene espacio disponible para nuevas filas sin necesidad de asignar nuevas páginas.

Características de las páginas PFS

Cada página PFS rastrea el estado de 8,088 de datos (~64 MB de datos). Es decir, si la página PFS es la página 1 del archivo de datos nos vamos a encontrar con una de estas cada 8088 páginas de datos, 

Estas PFS se actualizan cada vez que una página de datos cambia de estado, por ejemplo, cuando se insertan o eliminan filas.

La información se almacena en bits dentro de la página PFS, con los siguientes valores:

0x00 → Página vacía.
0x40 → Página 1-50% ocupada.
0x80 → Página 51-100% ocupada.
0xC0 → Página completamente llena.
0xE0 → Página reservada para IAM u otros propósitos.

Ubicación de las páginas PFS en el archivo de datos

SQL Server es capaz de registrar con un Byte de información la página y su estado por lo que en una página PFS que es de 8KB podremos almacenar información de 8 * 1024 páginas de datos. Esto significa que las PFS aparecen cada 8,088 páginas en un archivo de datos. Por ejemplo, si la primera página PFS sabemos que está en la página 1, la siguiente estará en la página 8,089, la siguiente en la página 16,177, y así sucesivamente.

Consulta para inspeccionar páginas PFS

Podemos analizar las PFS con DBCC PAGE pero para ello vamos a tener que activar primero la traza 3604 para ver el resultado en SSMS y que no vaya al log de errores de SQL Server. Al comando DBCC PAGE le vamos a pasar como parámetros primero el ID de la base de datos, seguido del ID del fichero de datos, el número de página que queremos ver y un 3.

DBCC TRACEON(3604);
DBCC PAGE(6, 1, 1, 3);

Páginas GAM (Global Allocation Map)

Las GAM rastrean la asignación de extensiones dentro del archivo de base de datos. Permiten a SQL Server identificar rápidamente qué extensiones están libres y cuáles han sido asignadas a objetos.

Características de las páginas GAM

Cada página GAM cubre 511,232 de datos (unos 3 GB de datos).

Utiliza 1 bit por extensión:
1 → Extensión libre.
0 → Extensión asignada.

Como sabéis, SQL Server no libera el espacio cuando los datos se borran sino que se lo queda y lo marca como disponible. Pues bien, GAM se usa cuando SQL Server necesita encontrar espacio disponible y no asignado para nuevos objetos. 

Cada página GAM es una página de 8 KB (como todas) que contiene información de 63,904 extents (ya que cada bit representa un extent). Como cada extent tiene 8 páginas, esto significa que una página GAM puede rastrear 511,232 páginas, lo que equivale a aproximadamente 3 GB de almacenamiento. Pero no tienes por qué creer ciegamente en lo que yo te diga, vamos a hacer los números.

1 GAM = 8 KB = 8 * 1024 bytes = 8192  bytes
De estos 8192 bytes, 204 bytes están reservados por lo que quedan 7988 en una página  GAM. ¿Cuántas páginas de datos pueden cubrir 7988 bytes?
1 GAM = 7988 bytes = 7988 * 8 bits = 63904 bits
Entonces si una página GAM tiene 63904 bits y necesita 1 bit para registrar 1 extent eso significa que puede almacenar información de 63904 extents. Convirtamos eso en tamaño.
1 GAM = 63904 extents = 63904 * 8 páginas = 511232 páginas
11232 páginas * 8 KB = 4,089,856 KB
4,089,856 KB / 1024 = 3994 MB / 1024 = 3,3144 GB

Ubicación de las páginas GAM en el archivo de datos

Si ya sabemos que las GAM aparecen cada 511,232 páginas y que la primera GAM está en la página 2, podemos saber que la siguiente en la página 511,234, etc.

Consulta para inspeccionar GAM

Igual que hemos hecho antes podemos usar DBCC PAGE para ver las GAM.

DBCC TRACEON(3604);
DBCC PAGE(6, 1, 2, 3);

Páginas SGAM (Shared Global Allocation Map)

Las SGAM funcionan de manera complementaria a las GAM y se utilizan para rastrear extents mixtos que aún tienen espacio disponible para nuevos datos.

Características de SGAM

Cada página SGAM cubre 511,232 páginas ( unos 3 GB de datos), igual que GAM. También utiliza 1 bit por extensión:
1 → Extensión mixta con espacio libre.
0 → Extensión completamente ocupada o no mixta.

SGAM se usa cuando SQL Server necesita encontrar espacio en extensiones mixtas para insertar nuevos datos sin asignar nuevas páginas.

Ubicación de las SGAM en el archivo de datos

Las SGAM aparecen cada 511,232 páginas, comenzando en la página 3, luego en la página 511,235, y así sucesivamente.

Consulta para inspeccionar SGAM

DBCC TRACEON(3604);
DBCC PAGE(6,1,3.,3);

Páginas IAM (Index Allocation Map)

Las IAM rastrean qué páginas y extensiones pertenecen a un objeto específico, como una tabla o un índice. Cada página IAM cubre un solo objeto (una tabla o un índice) en un solo archivo. Incluso puede abarcar múltiples páginas y archivos si el objeto está distribuido. 

Las IAM almacenan la relación entre los objetos y sus páginas de datos distribuidas en el archivo de datos. A diferencia de las GAM y SGAM, las IAM no tienen una ubicación fija dentro del archivo, ya que se crean dinámicamente para cada objeto.

Consulta para inspeccionar IAM

En este caso es mucho más complicado porque las IAM no tienen ubicación fija pero si supiesemos el número de página podremos consultarlo igual con DBCC PAGE y en el resultado podríamos ver que páginas y extents pertenecen a ese objeto. 

DBCC TRACEON(3604); 
DBCC PAGE(6, 1, NúmeroDePáginaIAM, 3);

Conclusión

SQL Server administra el almacenamiento de manera eficiente utilizando páginas de asignación especializadas como PFS, GAM, SGAM e IAM. Estas estructuras permiten al motor de base de datos optimizar el uso del espacio, minimizar la fragmentación y mejorar el rendimiento. Comprender su funcionamiento es clave para administrar bases de datos de manera óptima y solucionar problemas de asignación de espacio en SQL Server.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima! 

¿Alguna vez te has preguntado cómo hace SQL Server para gestionar los datos en disco? Y no sólo SQL Server, la administración eficiente del almacenamiento es fundamental para garantizar un rendimiento óptimo y una gestión eficaz de los datos en cualquier sistema gestor de bases de datos. El motor de base de datos tiene que utilizar estructuras bien definidas, como páginas y extensiones, para organizar y controlar el espacio en disco. Para nosotros, como DBAs o usuarios avanzados, comprender en profundidad estas estructuras y sus mecanismos asociados es esencial para diseñar, implementar y mantener bases de datos de alto rendimiento.

Páginas, la unidad atómica de almacenamiento en SQL Server 

Una página es la unidad básica de almacenamiento en SQL Server, en estas páginas se guardan nuestros datos y tienen un tamaño fijo de 8 KB, siempre tienen el mismo tamaño.

Cada página comienza con un encabezado de 96 bytes que contiene información crucial, como el número de página, el tipo de página, la cantidad de espacio libre y el ID de la unidad de asignación del objeto propietario. Las filas de datos se almacenan secuencialmente después del encabezado, y al final de la página se encuentra una tabla de desplazamiento de filas que facilita el acceso rápido a cada fila.

Tipos de Páginas en función su almacenamiento

SQL Server utiliza diversos tipos de páginas para almacenar diferentes tipos de información.

Aunque el usuario medio podría pensar que todo lo que hay son páginas de datos lo cierto es que este es solo un tipo de página. En concreto, son las páginas que almacenan las filas de datos de las tablas pero, excluyendo los datos de tipos como text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml cuando la opción «text in row» está activada. Esos datos veremos que van a otro tipo especial de página.

En concreto a páginas de texto o imagen que son las encargadas de almacenar datos de objetos grandes (LOB), incluyendo text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml.

Para cerrar con las páginas que almacenan datos tenemos que nombrar las páginas de índice que contienen esas entradas de índices que facilitan el acceso rápido a los datos.

Pero esto no es todo, no sólo hay páginas con nuestros datos, SQL Server necesita una serie de metadatos para poder gestionar todo esto y los almacena en las páginas de asignación. En este tipo de páginas incluimos las páginas GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), IAM (Index Allocation Map) y PFS (Page Free Space), que gestionan la asignación y el seguimiento del espacio en la base de datos.

Extensiones, como realmente SQL Server gestiona el almacenamiento

Una extensión (lo encontrarás también como extent) es un conjunto de ocho páginas físicamente contiguas, totalizando 64 KB. Las extensiones son la unidad básica mediante la cual SQL Server gestiona el espacio de almacenamiento. 

Permitidme hacer un parón en este punto para interiorizar esto, vuelve a leer el párrafo anterior y grábatelo a fuego así, la próxima vez que tengas que preparar un nuevo disco para almacenar datos de SQL Server entenderás por qué es una buena práctica formatear los discos con un tamaño de asignación de bloques de 64 KB.

Ahora sé, volvemos con los extent y, nuevamente, nos encontramos con que existen varios tipos. En este caso, no dependen del tipo de los datos que estamos almacenando sino de si son o no dedicados para un objeto. 

De esta manera, podemos encontrar extensiones uniformes donde las ocho páginas son propiedad de un único objeto o extensiones mixtas donde las ocho páginas pueden ser compartidas por hasta ocho objetos diferentes, permitiendo una asignación eficiente para objetos pequeños. Es importante destacar que aunque el extent puede ser mixto, las páginas siempre son dedicadas al 100% para el mismo objeto.

A partir de SQL Server 2016, veremos que el comportamiento predeterminado es asignar extensiones uniformes para la mayoría de las operaciones. En principio para optimizar el rendimiento pero bueno, eso es un tema discutible. 

Gestión del almacenamiento

Como hemos comentado antes, SQL Server emplea varias estructuras internas (páginas de asignación) para gestionar la asignación y el uso del almacenamiento. Aquí va un pequeño resumen aunque ya os adelanto que a este tema le vamos a dedicar un artículo completo porque tiene mucho de lo que hablar.

Páginas GAM: mapa global de asignación de almacenamiento

Las páginas GAM (Global Allocation Map) registran qué extents han sido asignadas en el archivo de datos. Cada bit en una página GAM representa una extensión: un bit en 1 indica que la extensión está libre, mientras que un bit en 0 señala que está asignada. Cada página GAM puede rastrear aproximadamente 4 GB de datos, cubriendo 64,000 extents.

Páginas SGAM: mapa global de almacenamiento compartido

Las páginas SGAM (Shared Global Allocation Map) indican qué extents mixtos tienen al menos una página disponible para su uso. Un bit en 1 significa que la extensión mixta tiene espacio libre, mientras que un bit en 0 indica que todas sus páginas están ocupadas o que no es una extensión mixta. Al igual que las páginas GAM, cada página SGAM cubre cerca de 4 GB de datos.

Páginas PFS: almacenamiento disponible

Las páginas PFS (Page Free Space) rastrean el estado de cada página individual en términos de asignación y espacio libre. Cada bit en una página PFS representa una página de datos y señala si está libre, parcialmente llena o completamente ocupada. Cada página PFS puede rastrear 8,088 páginas, lo que equivale aproximadamente a 64 MB de datos.

Páginas IAM: Mapa de asignación de índices

Las páginas IAM (Index Allocation Map) mapean las extensiones utilizadas por un objeto específico, como una tabla o un índice, en una unidad de asignación particular. Estas páginas permiten a SQL Server identificar rápidamente qué páginas y extensiones pertenecen a cada objeto, facilitando operaciones eficientes de lectura y escritura.

Consideraciones Avanzadas

Antes de cerrar este artículo quería remarcar unos conceptos sobre los que he podido pasar de manera tangencial antes. 

De cara a entender la compatibilidad con filas largas, aunque una fila de datos no puede dividirse entre múltiples páginas, SQL Server maneja columnas de longitud variable que pueden exceder los 8 KB moviendo parte de los datos a páginas especiales denominadas ROW_OVERFLOW_DATA. Esto permite almacenar filas que, en conjunto, superan el tamaño de una página estándar.

Otra cosa que hemos comentado pero que quería matizar es que en versiones anteriores a SQL Server 2016, las tablas e índices pequeños comenzaban utilizando extensiones mixtas y, al crecer, se les asignaban extensiones uniformes. Desde SQL Server 2016, el comportamiento predeterminado es asignar extensiones uniformes desde el inicio para la mayoría de las operaciones, mejorando el rendimiento en la gestión del almacenamiento. Sin embargo, ahora mismo, con los discos SSD modernos, las lecturas secuenciales ya no representan tanta ventaja como cuando los discos eran mecánicos y esta supuesta ventaja en rendimiento puede ser un problema de desaprovechamiento del almacenamiento. Tampoco es que importe mucho, los discos duros, a la vez que se hacían más rápidos, también han visto crecer su capacidad total y el coste del almacenamiento se ha reducido mucho.

Conclusión

Una comprensión profunda de las estructuras de almacenamiento en SQL Server, incluyendo páginas, extensiones y mapas de asignación, es esencial para administradores y desarrolladores que buscan optimizar el rendimiento y la eficiencia de sus bases de datos. Al dominar estos conceptos, es posible diseñar estrategias de almacenamiento que maximicen el uso de los recursos y aseguren operaciones de base de datos robustas y escalables.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!