Guía de arquitectura de páginas y extensiones

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

La página es la unidad fundamental del almacenamiento de datos en SQL Server. Una extensión es una colección de ocho páginas físicamente contiguas. Las extensiones ayudan a administrar las páginas con eficacia. En esta guía se describen las estructuras de datos que se utilizan para administrar páginas y extensiones en todas las versiones de SQL Server. Comprender la arquitectura de las páginas y las extensiones es importante para diseñar y desarrollar bases de datos que funcionen eficazmente.

Páginas y extensiones

La unidad fundamental del almacenamiento de datos en SQL Server es la página. El espacio en disco asignado a un archivo de datos (.mdf o .ndf) en una base de datos se divide lógicamente en páginas numeradas de forma contigua de 0 a n. Las operaciones de E/S de disco se realizan en el nivel de página. Es decir, SQL Server lee o escribe páginas de datos enteras.

Las extensiones son una colección de ocho páginas físicamente contiguas; se utilizan para administrar las páginas de forma eficaz. Todas las páginas se organizan en extensiones.

Páginas

En un libro normal, todo su contenido está escrito en las páginas. De forma similar a un libro, SQL Server escribe todas las filas de datos en páginas y todas las páginas de datos tienen el mismo tamaño: 8 KB. En un libro, la mayoría de las páginas contienen los datos, el contenido principal del libro, y algunas páginas contienen metadatos sobre el contenido (por ejemplo, tabla de contenido e índice). De nuevo, SQL Server no es distinto: la mayoría de las páginas contienen filas de datos reales almacenadas por los usuarios. Se denominan páginas de datos y páginas de texto e imagen (para casos especiales). Las páginas de índice contienen referencias de índice sobre dónde están los datos. Por último, hay páginas del sistema que almacenan varios metadatos sobre la organización de los datos.

Cada página empieza con un encabezado de 96 bytes, que se utiliza para almacenar la información del sistema acerca de la página. Esta información incluye el número de página, el tipo de página, el espacio disponible en la página y el Id. de unidad de asignación del objeto propietario de la página.

En la siguiente tabla se muestran los tipos de página utilizados en los archivos de datos de una base de datos de SQL Server.

Las filas de datos se almacenan en las páginas una a continuación de otra, empezando inmediatamente después del encabezado. Al final de la página, comienza una tabla de desplazamiento de fila y cada una de esas tablas contiene una entrada para cada fila de la página. Cada entrada de desplazamiento de fila almacena la distancia del primer byte de la fila desde el inicio de la página. Por lo tanto, la función de la tabla de desplazamiento de filas es ayudar a SQL Server buscar filas en una página rápidamente. Las entradas de la tabla de desplazamiento de fila están en orden inverso a la secuencia de las filas de la página.

Compatibilidad con filas largas

Las filas no pueden abarcar páginas; pero se pueden alejar de la página de la fila ciertas partes de la fila para que esta pueda tener un tamaño mucho mayor. La cantidad máxima de datos y de sobrecarga que está contenida en una única fila de una página es de 8060 bytes. Esto no incluye los datos almacenados en el tipo de página Texto o imagen.

Esta restricción es menos estricta para tablas que contienen columnas varchar, nvarchar, varbinary o sql_variant. Cuando el tamaño de fila total de todas las columnas variables y fijas de una tabla excede el límite de 8060 bytes, SQL Server mueve dinámicamente una o más columnas de longitud variable a páginas de la unidad de asignación ROW_OVERFLOW_DATA, empezando por la columna con el mayor ancho.

Esto se realiza cuando una operación de inserción o actualización aumenta el tamaño total de la fila más allá del límite de 8060 bytes. Cuando una columna se mueve a una página de la unidad de asignación ROW_OVERFLOW_DATA, se mantiene un puntero de 24 bytes de la página original de la unidad de asignación IN_ROW_DATA. Si una operación posterior reduce el tamaño de la fila, SQL Server vuelve a mover las columnas dinámicamente a la página de datos original.

Consideraciones acerca del desbordamiento de fila

Una fila no puede residir en varias páginas y puede desbordarse si el tamaño combinado de los campos de tipo de datos de longitud variable supera el límite de 8060 bytes. Para ilustrarlo, se puede crear una tabla con dos columnas: una de tipo varchar(7000) y otra de tipo varchar(2000). De forma individual, ninguna de las columnas excede los 8060 bytes, pero si se combinan sí que podrían hacerlo, si se rellena todo el ancho de cada columna. Es posible que SQL Server traslade dinámicamente la columna de longitud variable varchar (7000) a las páginas de la unidad de asignación ROW_OVERFLOW_DATA. Al combinar las columnas varchar, nvarchar, varbinary o sql_variant o de tipo definido por el usuario CLR que superen los 8060 bytes por fila, tenga en cuenta lo siguiente:

• El movimiento de registros grandes a otra página tiene lugar de forma dinámica a medida que la longitud de los registros aumenta en función de las operaciones de actualización. Las operaciones de actualización que reducen os registros pueden hacer que los registros se vuelvan a colocar en la página original de la unidad de asignación IN_ROW_DATA.

  El hecho de consultar y realizar otras operaciones de selección, como puedan ser ordenaciones o combinaciones en registros grandes que contengan datos de desbordamiento de fila, ralentiza el tiempo de procesamiento, ya que estos registros se procesan sincrónicamente en lugar de asincrónicamente.

  Por lo tanto, al diseñar una tabla con varias columnas varchar, nvarchar, varbinary o sql_variant o de tipo definido por el usuario CLR, tenga en cuenta el porcentaje de filas que tienen posibilidades de desbordarse y la frecuencia con la que se consultarán estos datos de desbordamiento. Si es muy probable que se realicen consultas con frecuencia en muchas filas de los datos de desbordamiento de fila, considere la posibilidad de normalizar la tabla para que algunas columnas se muevan a otra tabla. Más adelante, puede realizar la consulta en una operación JOIN asincrónica.

• La longitud de las columnas individuales debe estar comprendida en el límite de 8000 bytes para las columnas varchar, nvarchar, varbinary o sql_variant y de tipo definido por el usuario CLR. Solo la combinación de sus longitudes puede superar el límite de fila de 8.060 bytes de una tabla.

• La suma de otras columnas de tipos de datos, incluidos los datos char y nchar, debe estar comprendida en el límite de fila de 8060 bytes. Los datos de objeto grande no están sometidos al límite de fila de 8.060 bytes.

• La clave de índice de un índice agrupado no puede contener columnas varchar con datos existentes en la unidad de asignación ROW_OVERFLOW_DATA. Si se crea un índice agrupado en una columna varchar y los datos existentes están en la unidad de asignación IN_ROW_DATA, no se realizarán correctamente las siguientes acciones de inserción o actualización en la columna que intenten insertar los datos de manera no consecutiva. Para más información sobre las unidades de asignación, vea Guía de diseño y arquitectura de índices de SQL Server y Azure SQL.

• Puede incluir columnas que contengan datos de desbordamiento de fila como columnas de clave o sin clave de un índice no clúster.

• El tamaño máximo del registro para las tablas que usan columnas dispersas es de 8.018 bytes. Si los datos convertidos más los datos de registro existentes superan los 8018 bytes, se devolverá el error MSSQLSERVER ERROR 576. Si las columnas pasan de dispersas a no dispersas y viceversa, el Motor de base de datos mantendrá una copia de los datos del registro actuales. Esto duplica temporalmente el almacenamiento necesario para el registro.

• Para obtener más información sobre las tablas o los índices que pueden contener datos de desbordamiento de fila, use la función de administración dinámica sys.dm_db_index_physical_stats.

Extents

Las extensiones son la unidad básica en la que se administra el espacio. Una extensión consta de ocho páginas contiguas físicamente, es decir 64 KB. Esto significa que las bases de datos de SQL Server tienen 16 extensiones por megabyte.

SQL Server tiene dos tipos de extensiones:

• Las extensiones uniformes son propiedad de un único objeto; solo el objeto propietario puede usar las ocho páginas de la extensión.
• Las extensiones mixtas, que pueden estar compartidas por hasta ocho objetos. Cada una de las 8 páginas de la extensión puede ser propiedad de un objeto diferente.

Hasta SQL Server 2014 (12.x), el motor de base de datos no asigna extensiones completas a tablas con pequeñas cantidades de datos. A las tablas o índices nuevos se les suelen asignar páginas de extensiones mixtas. Cuando la tabla o el índice crecen hasta el punto de ocupar ocho páginas, se pasa al uso de extensiones uniformes para las asignaciones posteriores. Si crea un índice de una tabla existente que dispone de filas suficientes para generar ocho páginas en el índice, todas las asignaciones del índice están en extensiones uniformes.

A partir de SQL Server 2016 (13.x), el valor predeterminado para la mayoría de asignaciones en una base de datos de usuario y tempdb es usar extensiones uniformes, salvo en el caso de las asignaciones que pertenezcan a las primeras ocho páginas de una cadena IAM. Las asignaciones relativas a bases de datos master, msdb y model conservarán el comportamiento anterior.

A partir de SQL Server 2012 (11.x), la función del sistema sys.dm_db_database_page_allocations puede notificar información de asignación de páginas relativa a una base de datos, tabla, índice o partición.

A partir de SQL Server 2019 (15.x), la función del sistema sys.dm_db_page_info está disponible y devuelve información sobre una página de una base de datos. La función devuelve una fila que contiene la información de encabezado en la página, incluidos object_id, index_id y partition_id. Esta función reemplaza la necesidad de usar DBCC PAGE en la mayoría de los casos.

Administración de las asignaciones de extensiones y el espacio disponible

Las estructuras de datos de SQL Server que administran asignaciones de extensiones y realizan un seguimiento del espacio disponible tienen una estructura relativamente sencilla. Esto tiene las siguientes ventajas:

• La información del espacio libre está densamente empaquetada, de modo que esta información está contenida en relativamente pocas páginas.

  Esto aumenta la velocidad al reducir el número de lecturas del disco necesarias para recuperar la información de asignación. También incrementa la posibilidad de que las páginas de asignación permanezcan en la memoria, lo que elimina aún más lecturas.

• La mayor parte de la información de asignación no está encadenada. Esto simplifica el mantenimiento de la información de asignación.

  La asignación o cancelación de asignación de las páginas se puede hacer con más rapidez. Esto reduce el conflicto entre las tareas simultáneas que necesiten asignar o cancelar la asignación de páginas.

Administración de las asignaciones de extensiones

SQL Server utiliza dos tipos de mapas de asignación para registrar la asignación de las extensiones:

• Mapa de asignación global (GAM)

  Las páginas GAM registran las extensiones que han sido asignadas. Cada GAM cubre 64 000 extensiones o casi 4 GB de datos. La página GAM tiene 1 bit por cada extensión del intervalo que cubre. Si el bit es 1, la extensión está disponible; si el bit es 0, la extensión está asignada.

• Mapa de asignación global compartido (SGAM)

  Las páginas SGAM registran las extensiones que actualmente se están utilizando como extensiones mixtas y además tienen al menos una página sin utilizar. Cada SGAM cubre 64.000 extensiones o casi 4 GB de datos. La SGAM tiene 1 bit por cada extensión del intervalo que cubre. Si el bit es 1, la extensión se está utilizando como extensión mixta y tiene una página disponible. Si el bit es 0, la extensión no se usa como extensión mixta, o bien se trata de una extensión mixta y se usan todas sus páginas.

Todas las extensiones tienen establecidos los siguientes patrones de bits en las página GAM y SGAM, basados en su uso actual.

Esto hace que los algoritmos de administración de las extensiones sean sencillos.

• Para asignar una extensión uniforme, el motor de base de datos busca un bit 1 en la página GAM y lo establece en 0.
• Para buscar una extensión mixta con páginas disponibles, el motor de base de datos busca un bit 1 en la página SGAM.
• Para asignar una extensión mixta, el motor de base de datos busca 1 bit en la página GAM, lo establece en 0 y, a continuación, establece también en 1 el bit correspondiente de la página SGAM.
• Para desasignar una extensión, el motor de base de datos se asegura de que el bit de la página GAM esté establecido en 1 y el bit de la página SGAM en 0.

Los algoritmos internos usados por el motor de base de datos son más complejos que los mencionados aquí, puesto que el motor de base de datos distribuye los datos en la base de datos de manera uniforme. Sin embargo, incluso los algoritmos reales quedan simplificados al no tener que administrar cadenas de información de asignación de extensiones.

Seguimiento del espacio libre

Las páginas Espacio disponible en páginas (PFS) registran el estado de asignación de cada página, si una página concreta está asignada y la cantidad de espacio libre en cada página. La PFS tiene 1 byte por página, que registra si la página está asignada y, en ese caso, si está vacía, llena entre el 1 y el 50 %, entre el 51 y el 80 %, entre el 81 y el 95 %, o bien entre el 96 y el 100 %.

Una vez que se ha asignado una extensión a un objeto, el motor de base de datos utiliza las páginas PFS para registrar las páginas de la extensión que están asignadas o libres. Esta información se utiliza cuando el motor de base de datos tiene que asignar una nueva página. La cantidad de espacio libre de una página solo se mantiene en páginas de texto e imagen y de montón. Se usa cuando el motor de base de datos tiene que buscar una página con espacio libre disponible para almacenar una fila recién insertada. Los índices no necesitan que se haga un seguimiento del espacio libre en páginas, porque el punto en el que se inserta una nueva fila se establece mediante los valores de clave del índice.

En el archivo de datos se agrega una nueva página PFS, GAM o SGAM para cada intervalo adicional del que se realiza el seguimiento. Por tanto, hay una nueva página PFS 8088 páginas después de la primera, y páginas PFS adicionales en intervalos posteriores de 8088 páginas. Como ejemplo, el id. de página 1 es una página PFS, el id. de página 8088 es una página PFS, el id. de página 16176 es una página PFS, etc.

Hay una nueva página GAM de 64 000 extensiones después de la primera página GAM, y realiza el seguimiento de las 64 000 extensiones que la siguen; la secuencia continúa en intervalos de 64 000 extensiones. Del mismo modo, hay una nueva página SGAM 64 000 extensiones después de la primera y páginas SGAM adicionales en intervalos de 64 000 extensiones posteriores.

En la siguiente ilustración se muestra la secuencia de páginas que usa el motor de base de datos para asignar y administrar extensiones.

Administración del espacio usado por los objetos

Una página del Mapa de asignación de índices (IAM) asigna las extensiones en una parte de 4 GB de un archivo de base de datos usado por una unidad de asignación. Existen tres tipos de unidades de asignación:

• IN_ROW_DATA

  Contiene una partición de un montón o un índice.

• LOB_DATA

  Contiene tipos de datos de objetos grandes (LOB), como xml, varbinary(max), y varchar(max).

• ROW_OVERFLOW_DATA

  Contiene datos de longitud variable almacenados en columnas varchar, nvarchar, varbinary o sql_variant que superan el límite de tamaño de fila de 8060 bytes.

Cada partición de un montón o un índice contiene al menos una unidad de asignación IN_ROW_DATA. También puede contener una unidad de asignación LOB_DATA o ROW_OVERFLOW_DATA en función del esquema del montón o el índice.

Una página IAM cubre un intervalo de 4 GB en un archivo, lo que equivale a la cobertura de una página GAM o SGAM. Si la unidad de asignación contiene extensiones de más de un archivo o más de un intervalo de 4 GB de un archivo, se vincularán varias páginas IAM en una cadena IAM. Por lo tanto, cada unidad de asignación tiene como mínimo una página IAM para cada archivo en el que tiene extensiones. También puede haber más de una página IAM en un archivo si el intervalo de las extensiones del archivo asignado a la unidad de asignación supera el intervalo que una sola página IAM puede registrar.

Las páginas IAM se asignan según se necesitan para cada unidad de asignación y se ubican en el archivo de forma aleatoria. La vista del sistema sys.system_internals_allocation_units apunta a la primera página IAM de una unidad de asignación. Todas las páginas IAM de esa unidad de asignación se vinculan en una cadena IAM.

Una página IAM tiene un encabezado que indica la extensión inicial del intervalo de extensiones asignado por la página IAM. La página IAM también tiene un mapa de bits grande en el que cada bit representa una extensión. El primer bit del mapa representa la primera extensión del intervalo, el segundo bit representa la segunda extensión, etc. Si un bit es 0, la extensión que representa no está asignada a la unidad de asignación propietaria de IAM. Si el bit es 1, la extensión que representa está asignada a la unidad de asignación propietaria de la página IAM.

Si el motor de base de datos necesita insertar una fila nueva y no hay espacio disponible en la página actual, utiliza las páginas IAM y PFS para buscar una página para la asignación o, en el caso de un montón o una página de texto o imagen, una página con espacio suficiente para contener la fila. El motor de base de datos utiliza las páginas IAM para buscar las extensiones asignadas a la unidad de asignación. Para cada extensión, el motor de base de datos busca las páginas PFS para ver si existe una que se pueda utilizar. Cada página IAM y PFS cubre muchas páginas de datos, por lo que en una base de datos hay pocas páginas IAM y PFS. Esto significa que las páginas IAM y PFS están generalmente en el grupo de búferes de SQL Server y se pueden buscar con rapidez. Para los índices, el punto de inserción de una fila nueva se establece mediante la clave de índice, pero cuando se necesita una página nueva, se produce el proceso descrito anteriormente.

El motor de base de datos solo asigna una nueva extensión a una unidad de asignación cuando no puede encontrar rápidamente una página en una extensión existente con espacio suficiente para almacenar la fila que se va a insertar.

Asignación de relleno proporcional

El motor de base de datos asigna las extensiones entre las que están disponibles en el grupo de archivos siguiendo un algoritmo de asignación de relleno proporcional. En el mismo grupo de archivos con dos archivos, si uno de ellos tiene el doble de espacio disponible que el otro, se asignarán dos páginas del archivo con más espacio disponible por cada página asignada del otro archivo. Esto significa que los archivos de un grupo de archivos tienen un porcentaje de espacio utilizado similar.

Seguimiento de las extensiones modificadas

SQL Server utiliza dos estructuras de datos internas para realizar un seguimiento de las extensiones modificadas mediante operaciones de copia masiva y de las extensiones modificadas desde la última copia de seguridad completa. Esas estructuras de datos aceleran en gran medida las copias de seguridad diferenciales. También aceleran el registro de las operaciones de copia masiva cuando una base de datos utiliza el modelo de recuperación optimizado para cargas masivas de registros. Al igual que las páginas GAM y SGAM, estas nuevas estructuras son mapas de bits en los que cada bit representa una única extensión.

• Mapa cambiado diferencial (DCM)

  Realiza un seguimiento de las extensiones que han cambiado desde la última instrucción BACKUP DATABASE. Si el bit de una extensión es 1, esta se ha modificado desde la última instrucción BACKUP DATABASE. Si el bit es 0, la extensión no se ha modificado.

  Las copias de seguridad diferenciales solo leen las páginas DCM para determinar las extensiones que se han modificado. Esto reduce enormemente el número de páginas que debe recorrer una copia de seguridad diferencial. El tiempo de ejecución de una copia de seguridad diferencial es proporcional al número de extensiones modificadas desde la última instrucción BACKUP DATABASE y no al tamaño global de la base de datos.

• Mapa cambiado masivamente (BCM)

  Realiza un seguimiento de las extensiones que se han modificado mediante operaciones de registro masivo desde la última instrucción BACKUP LOG. Si el bit de una extensión es 1, esta se ha modificado mediante una operación de registro masivo después de la última instrucción BACKUP LOG. Si el bit es 0, la extensión no se ha modificado mediante operaciones de registro masivo.

  Aunque las páginas BCM aparecen en todas las bases de datos, son relevantes únicamente cuando la base de datos está utilizando el modelo de recuperación optimizado para cargas masivas de registros. En este modelo de recuperación, cuando se ejecuta BACKUP LOG, el proceso de copia de seguridad recorre los BCM buscando extensiones que se hayan modificado. A continuación incluye dichas extensiones en la copia de seguridad del registro. Esto recupera operaciones de registro masivo si se restaura la base de datos a partir de una copia de seguridad y una secuencia de copias de seguridad de registro de transacciones. Las páginas BCM no son relevantes en una base de datos que está utilizando el modelo de recuperación simple, porque no se registran las operaciones de registro masivo. No son relevantes en una base de datos que utiliza el modelo de recuperación completa, porque este modelo trata las operaciones de registro masivo como operaciones de registro completo.

El intervalo entre las páginas DCM y BCM es el mismo que el intervalo entre las páginas GAM y SGAM, es decir, 64.000 extensiones. Las páginas DCM y BCM se colocan después de las páginas GAM y SGAM en un archivo físico de la manera siguiente:

Contenido relacionado

• Aspectos básicos de E/S de SQL Server
• sys.allocation_units (Transact-SQL)
• Montones (tablas sin índices clúster)
• sys.dm_db_page_info (Transact-SQL)
• Leer páginas
• Escribir páginas