Administración de la retención de datos históricos en las tablas temporales con versiones del sistema

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL Database Azure SQL Managed Instance

Con las tablas temporales con control de versiones del sistema, la tabla de historial puede aumentar el tamaño de la base de datos más que las tablas normales, especialmente en las siguientes condiciones:

• Retención de datos históricos durante un largo período
• Disponibilidad de una actualización o eliminación del modelo de modificación de gran cantidad de datos

Una tabla de historial de gran tamaño y creciente puede ser un problema debido a los costos de almacenamiento puro y a la imposición de un impuesto de rendimiento sobre las consultas temporales. El desarrollo de una directiva de retención de datos para administrar datos en la tabla de historial es un aspecto importante de la planeación y la administración del ciclo de vida de cada tabla temporal.

Administración de la retención de datos para la tabla de historial

La administración de la retención de datos de la tabla temporal empieza por determinar el período de retención requerido para cada tabla temporal. La directiva de retención, en la mayoría de los casos, debe ser parte de la lógica de negocios de la aplicación con tablas temporales. Por ejemplo, las aplicaciones de escenarios de auditoría de datos y recorridos en el tiempo tienen requisitos firmes en términos de cuánto tiempo deben estar disponibles los datos históricos para la consulta en línea.

Una vez que determine el periodo de retención de datos, debe desarrollar un plan para administrar los datos históricos. Decida cómo y dónde almacenar los datos históricos, y cómo eliminar los datos históricos que son más antiguos que los requisitos de retención. Los enfoques siguientes están disponibles para administrar los datos históricos en la tabla temporal de historial:

• Partición de tabla
• Script de limpieza personalizado
• Directiva de retención

Con cada uno de estos enfoques, la lógica para la migración o limpieza de datos del historial se basa en la columna que se corresponde con el final del período en la tabla actual. El final del valor del período para cada fila determina el momento en el que la versión de fila se cierra, es decir, cuando llega a la tabla de historial. Por ejemplo, la condición ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) especifica que esos datos históricos anteriores a un mes tienen quitarse o extraerse de la tabla de historial.

En los ejemplos de este artículo se usan los ejemplos creados en el artículo Creación de una tabla temporal con control de versiones del sistema.

Utilizar el enfoque de la partición de tabla

Las tablas con particiones y los índices pueden hacer que las tablas sean más escalables y fáciles de administrar. Con el enfoque de partición de tabla, puede implementar la limpieza de datos personalizada o el archivado sin conexión según una condición de tiempo. La creación de particiones de tabla también le proporciona ventajas de rendimiento cuando se realicen consultas de tablas temporales en un subconjunto de historial de datos mediante la eliminación de una partición.

Con la partición de tablas, puede implementar una ventana deslizante para extraer la parte más antigua de los datos históricos de la tabla de historial y mantener un tamaño constante de la parte conservada en términos de edad. Una ventana deslizante mantiene los datos de la tabla de historial igual al periodo de retención necesario. Se admite la operación de conmutación de datos fuera de la tabla de historial cuando SYSTEM_VERSIONING está ON, lo que significa que puedes limpiar una parte de los datos del historial sin introducir una ventana de mantenimiento o bloquear las cargas de trabajo normales.

Una ventana deslizante tiene dos conjuntos de tareas que tiene que realizar:

• Una tarea de configuración de partición
• Tareas periódicas de mantenimiento de partición

En la ilustración, supongamos que quieres mantener datos históricos durante seis meses y mantener todos los meses de datos en una partición independiente. Además, supongamos que has activado el control de versiones del sistema en septiembre de 2023.

Una tarea de configuración de particiones crea la configuración inicial de partición de la tabla de historial. En este ejemplo, se crean el mismo número de particiones que el tamaño de la ventana deslizante, en meses, más una partición vacía adicional preparada previamente (se explica más tarde en este artículo). Esta configuración garantiza que el sistema se puede almacenar correctamente los datos nuevos cuando inicies la tarea de mantenimiento periódico de la partición la primera vez y garantiza que nunca dividirás las particiones con datos para evitar movimientos de datos valiosos. Debes realizar esta tarea mediante Transact-SQL con el siguiente script de ejemplo.

En la siguiente imagen se muestra la configuración inicial de la creación de particiones para mantener 6 meses de datos.

Las primeras y las últimas particiones están abiertas en los límites inferior y superior respectivamente para asegurarse de que cada fila nueva tiene la partición de destino con independencia del valor de la columna de partición. A medida que pasa el tiempo, las nuevas filas de la tabla del historial se dirigen a particiones superiores. Cuando se llene la sexta partición, has alcanzado el período de retención de destino. Es el momento de iniciar la tarea de mantenimiento de particiones periódica por primera vez. Se debe programar para ejecutarse periódicamente, una vez al mes en este ejemplo.

La imagen siguiente muestra las tareas de mantenimiento periódico de la partición (consulta los pasos detallados máss tarde en esta sección).

Los pasos detallados para las tareas de mantenimiento periódico de la partición son:

1. SWITCH OUT: cree una tabla de almacenamiento provisional y, después, cambie una partición entre la tabla de historial y la tabla de almacenamiento provisional mediante la instrucción ALTER TABLE con el argumento SWITCH PARTITION (vea el ejemplo C. Cambio de particiones entre tablas).

   ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];
   

   Después del cambio de partición, puede archivar opcionalmente los datos de la tabla de almacenamiento provisional y, después, quitar o truncar la tabla de almacenamiento provisional para que estén listos para la próxima vez que necesite realizar esta tarea de mantenimiento periódico de la partición.

2. MERGE RANGE: combine la partición 1 vacía con la partición 2 mediante ALTER PARTITION FUNCTION con MERGE RANGE (vea el ejempl  B). Al quitar el límite inferior con esta función, se combina eficazmente la partición vacía 1 con la partición anterior 2 para formar una nueva partición 1. Las demás particiones también cambian de forma efectiva sus ordinales.

3. SPLIT RANGE: cree una partición vacía 7 mediante ALTER PARTITION FUNCTION con SPLIT RANGE (vea el ejemplo A). Al agregar un nuevo límite superior mediante esta función, crea eficazmente una partición independiente para el próximo mes.

Uso de Transact-SQL para crear particiones en la tabla de historial

Utiliza el siguiente script de Transact-SQL para crear la función de partición y el esquema de partición, y volver a crear el índice agrupado para que la partición se alinee con las particiones o el esquema de partición. En este ejemplo, crearás una ventana deslizante de seis meses con particiones mensuales a partir de septiembre de 2023.

BEGIN TRANSACTION

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo] (DATETIME2(7))
AS RANGE LEFT FOR VALUES (
    N'2023-09-30T23:59:59.999',
    N'2023-10-31T23:59:59.999',
    N'2023-11-30T23:59:59.999',
    N'2023-12-31T23:59:59.999',
    N'2024-01-31T23:59:59.999',
    N'2024-02-29T23:59:59.999'
);

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo] TO (
    [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY]
);

/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = PAGE
) ON [sch_Partition_DepartmentHistory_By_ValidTo](ValidTo);

COMMIT TRANSACTION;

Utilizar Transact-SQL para mantener particiones en el escenario de ventana deslizante

Utiliza el siguiente script de Transact-SQL para mantener las particiones en el escenario de ventana deslizante. En este ejemplo, conmutas la partición de septiembre de 2023 con la opción MERGE RANGE y, a continuación, agregas una nueva partición de marzo de 2024 con la opción SPLIT RANGE.

BEGIN TRANSACTION

/*(1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023] (
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2(7) NOT NULL,
    ValidTo DATETIME2(7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/*(2) Create index on the same filegroups as the partition that will be switched out*/
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];

/*(3) Create constraints matching the partition that will be switched out*/
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    WITH CHECK ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
    CHECK (ValidTo <= N'2023-09-30T23:59:59.999')

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]

/*(4) Switch partition to staging table*/
ALTER TABLE [dbo].[DepartmentHistory] SWITCH PARTITION 1
TO [dbo].[staging_DepartmentHistory_September_2023]
    WITH (WAIT_AT_LOW_PRIORITY(MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))

/*(5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/

/*(6) merge range to move lower boundary one month ahead*/
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE(N'2023-09-30T23:59:59.999');

/*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo] NEXT USED [PRIMARY]
    ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE(N'2024-03-31T23:59:59.999');
COMMIT TRANSACTION

Puedes modificar ligeramente el script anterior y usarlo en el proceso normal de mantenimiento mensual:

1. En el paso (1), cree una tabla de almacenamiento provisional para el mes que quiera quitar (octubre sería el siguiente en este ejemplo).
2. En el paso (3), cree la restricción y compruebe que coincide con el mes de datos que quiere quitar: ValidTo <= N'2023-10-31T23:59:59.999' para la partición de octubre.
3. En el paso (4), SWITCH la partición 1 a la tabla de almacenamiento provisional recién creada.
4. En el paso (6), modifique la función de partición mediante la combinación del límite inferior: MERGE RANGE(N'2023-10-31T23:59:59.999' después de extraer los datos de octubre.
5. En el paso (7), divida la función de partición mediante la creación del límite superior: SPLIT RANGE (N'2024-04-30T23:59:59.999' después de extraer los datos de octubre.

Pero la mejor solución sería ejecutar regularmente un script de Transact-SQL genérico que ejecute la acción apropiada cada mes sin modificación. Puede generalizar el script anterior para que actúe sobre los parámetros proporcionados (el límite inferior que se debe combinar y el límite nuevo que se crea con la división de particiones). Para evitar crear una tabla de almacenamiento provisional cada mes, puede crear una de antemano y reutilizarla y cambiar la restricción de comprobación para que coincida con la partición que se intercambia. Para más información, vea Procedimiento para automatizar completamente la ventana deslizante.

Consideraciones de rendimiento con las particiones de tabla

Debe realizar las operaciones MERGE y SPLIT RANGE para evitar el movimiento de datos, ya que este puede provocar una sobrecarga considerable del rendimiento. Para obtener más información, consulta Modificación de una función de partición. Para ello, usa RANGE LEFT en lugar de RANGE RIGHT al crear la función de partición.

En el diagrama siguiente se describen las opciones RANGE LEFT y RANGE RIGHT:

Si defines una función de partición como RANGE LEFT, los valores especificados son los límites superiores de las particiones. Cuando utilices la opción RANGE RIGHT, los valores especificados son los límites inferiores de las particiones. Cuando utilices la operación MERGE RANGE para quitar un límite de la definición de la función de partición, la implementación subyacente también quita la partición que contiene el límite. Si esa partición no está vacía, los datos se moverán a la partición que resulta de la operación MERGE RANGE.

En un escenario de ventana deslizante, quite siempre el límite inferior de la partición.

• En el caso RANGE LEFT, el límite inferior de la partición pertenece a la partición 1, que está vacía (después de conmutar la partición), por lo que MERGE RANGE no causará ningún movimiento de datos.

• En el caso RANGE RIGHT, el límite de partición más bajo pertenece a la partición 2, que no está vacía, ya que la partición 1 se ha vaciado cambiándola. En este caso, MERGE RANGE causa un movimiento de datos (los datos de la partición 2 se mueven a la partición 1). Para evitar esto, la opción RANGE RIGHT del escenario de ventana deslizante debe tener la partición 1, que siempre está vacía. Esto significa que si usas RANGE RIGHT, debes crear y mantener una partición adicional en comparación con el caso de RANGE LEFT.

Conclusión: la administración de particiones es más sencilla cuando se usa RANGE LEFT en una ventana deslizante y se evita el movimiento de datos. Pero la definición de los límites de partición con RANGE RIGHT es ligeramente más sencilla, ya que no tiene que tratar con problemas de comprobación de fecha y hora.

Utilizar el enfoque de script de limpieza personalizado

En los casos en los que los enfoques de particiones de tabla no sean opciones viables, otro enfoque consiste en eliminar los datos de la tabla de historial con un script de limpieza personalizado. La eliminación de los datos de la tabla de historial es posible solo cuando se aplica SYSTEM_VERSIONING = OFF. Para evitar la incoherencia de datos, realice la limpieza durante la ventana de mantenimiento (cuando las cargas de trabajo que modifican datos no están activas) o dentro de una transacción (para bloquear de forma efectiva otras cargas de trabajo). Esta operación requiere el permiso de CONTROL sobre tablas de historial y actuales.

Para bloquear mínimamente las aplicaciones normales y las consultas de usuario, elimine los datos en fragmentos más pequeños con un retraso, al ejecutar el script de limpieza dentro de una transacción. Aunque no hay ningún tamaño óptimo para la eliminación de cada fragmento de datos en todos los escenarios, la eliminación de más de 10 000 filas en una sola transacción puede suponer un impacto significativo.

La lógica de limpieza es la misma para todas las tablas temporales, por lo que se puede automatizar a través de un procedimiento almacenado genérico que puedes programar para que se ejecute periódicamente para cada tabla temporal para la que desees limitar el historial de datos.

En el siguiente diagrama se muestra cómo se debe organizar la lógica de limpieza para una tabla única a fin de reducir el impacto en las cargas de trabajo en ejecución.

Estas son algunas directrices de alto nivel para implementar el proceso. Programe la lógica de limpieza para que se ejecute todos los días y realice la iteración sobre todas las tablas temporales que necesitan la limpieza de datos. Use el Agente SQL Server u otra herramienta para programar este proceso:

• Elimina los datos históricos en cada tabla temporal empezando por las filas más antiguas hasta las más recientes en varias iteraciones en pequeños fragmentos y evita la eliminación de todas las filas en una sola transacción tal como se muestra en el diagrama anterior.

• Implemente cada iteración como una invocación del procedimiento almacenado genérico que quita una parte de los datos de la tabla de historial (vea a el siguiente ejemplo de código para obtener procedimiento).

• Calcule el número de filas que debe eliminar para una tabla temporal individual cada vez que se invoca el proceso. Según el resultado y el número de iteraciones que quiera tener, determine los puntos de división dinámicos para cada invocación del procedimiento.

• Planifica un período de retraso entre las iteraciones para una tabla única para reducir el impacto en las aplicaciones que dispongan de acceso a la tabla temporal.

Un procedimiento almacenado que elimine los datos de una tabla temporal única podría ser similar al fragmento de código siguiente. Revise detenidamente este código y ajústelo antes de aplicarlo en el entorno.

Este script genera tres instrucciones que se ejecutan dentro de una transacción:

1. SET SYSTEM_VERSIONING = OFF
2. DELETE FROM <history_table>
3. SET SYSTEM_VERSIONING = ON

En SQL Server 2016 (13.x), los dos primeros pasos se deben ejecutar en instrucciones EXEC independientes o SQL Server genera un error similar al del ejemplo siguiente:

Msg 13560, Level 16, State 1, Line XXX
Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO
CREATE PROCEDURE usp_CleanupHistoryData @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
AS
DECLARE @disableVersioningScript NVARCHAR(MAX) = '';
DECLARE @deleteHistoryDataScript NVARCHAR(MAX) = '';
DECLARE @enableVersioningScript NVARCHAR(MAX) = '';
DECLARE @historyTableName SYSNAME
DECLARE @historyTableSchema SYSNAME
DECLARE @periodColumnName SYSNAME

/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE sp_executesql
N'SELECT @hst_tbl_nm = t2.name,
      @hst_sch_nm = s2.name,
      @period_col_nm = c.name
  FROM sys.tables t1
  INNER JOIN sys.tables t2 ON t1.history_table_id = t2.object_id
  INNER JOIN sys.schemas s1 ON t1.schema_id = s1.schema_id
  INNER JOIN sys.schemas s2 ON t2.schema_id = s2.schema_id
  INNER JOIN sys.periods p ON p.object_id = t1.object_id
  INNER JOIN sys.columns c ON p.end_column_id = c.column_id AND c.object_id = t1.object_id
  WHERE t1.name = @tblName AND s1.name = @schName',
N'@tblName sysname,
    @schName sysname,
    @hst_tbl_nm sysname OUTPUT,
    @hst_sch_nm sysname OUTPUT,
    @period_col_nm sysname OUTPUT',
@tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;

SET @disableVersioningScript = @disableVersioningScript
    + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName
    + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript = @deleteHistoryDataScript + ' DELETE FROM ['
    + @historyTableSchema + '].[' + @historyTableName + '] WHERE ['
    + @periodColumnName + '] < ' + '''' + CONVERT(VARCHAR(128), @cleanupOlderThanDate, 126) + ''''
SET @enableVersioningScript = @enableVersioningScript + ' ALTER TABLE ['
    + @temporalTableSchema + '].[' + @temporalTableName
    + '] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema
    + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '

BEGIN TRANSACTION
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Utilizar el enfoque de la directiva de retención de historial temporal

Se aplica a: SQL Server 2017 (14.x) y versiones posteriores, y Azure SQL Database.

La retención del historial temporal se puede configurar a nivel de tabla individual, lo que permite a los usuarios crear directivas de antigüedad flexibles. Para la retención temporal es necesario establecer un solo parámetro al cambiar el esquema o al crear la tabla.

Después de definir la directiva de retención, el motor de base de datos comienza a comprobar regularmente si hay filas históricas que sean aptas para la limpieza de datos automática. La identificación de las filas coincidentes y su eliminación de la tabla de historial se producen de forma transparente, en la tarea en segundo plano que programa y ejecuta el sistema. Se comprueba la condición de vencimiento para las filas de la tabla de historial en función del periodo SYSTEM_TIME (en estos ejemplos, la columna ValidTo). Si el periodo de retención se establece, por ejemplo, en seis meses, las filas aptas para la limpieza de la tabla cumplen la condición siguiente:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

En el ejemplo anterior, la columna ValidTo corresponde al final del período SYSTEM_TIME.

Configuración de la directiva de retención

Antes de configurar la directiva de retención para una tabla temporal, compruebe si la retención de historial temporal está habilitada en el nivel de base de datos:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

La marca de base de datos is_temporal_history_retention_enabled se establece en ON de manera predeterminada, pero los usuarios pueden cambiarla con la instrucción ALTER DATABASE. Este valor se establece automáticamente en OFF después de la operación de restauración a un momento dado (PITR). Para habilitar la limpieza de la retención de historial temporal de la base de datos, ejecute la instrucción siguiente. Debe reemplazar <myDB> por la base de datos que quiera modificar:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

La directiva de retención se configura al crear la tabla especificando el valor del parámetro HISTORY_RETENTION_PERIOD:

CREATE TABLE dbo.WebsiteUserInfo
(
    UserID INT NOT NULL PRIMARY KEY CLUSTERED,
    UserName NVARCHAR(100) NOT NULL,
    PagesVisited int NOT NULL,
    ValidFrom DATETIME2(0) GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2(0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.WebsiteUserInfoHistory,
        HISTORY_RETENTION_PERIOD = 6 MONTHS
    )
);

Puede especificar el periodo de retención mediante distintas unidades de tiempo: DAYS, WEEKS, MONTHS y YEARS. Si se omite HISTORY_RETENTION_PERIOD, se presupone la retención INFINITE. También puede usar explícitamente la palabra clave INFINITE.

En algunos escenarios, es posible que quiera configurar la retención tras crear la tabla, o bien cambiar a un valor previamente configurado. En ese caso, usa la instrucción ALTER TABLE:

ALTER TABLE dbo.WebsiteUserInfo
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 9 MONTHS));

Para revisar el estado actual de la directiva de retención, use el ejemplo siguiente. Esta consulta combina la marca de habilitación de retención temporal en el nivel de base de datos con periodos de retención para tablas individuales:

SELECT DB.is_temporal_history_retention_enabled,
    SCHEMA_NAME(T1.schema_id) AS TemporalTableSchema,
    T1.name AS TemporalTableName,
    SCHEMA_NAME(T2.schema_id) AS HistoryTableSchema,
    T2.name AS HistoryTableName,
    T1.history_retention_period,
    T1.history_retention_period_unit_desc
FROM sys.tables T1
OUTER APPLY (
    SELECT is_temporal_history_retention_enabled
    FROM sys.databases
    WHERE name = DB_NAME()
    ) AS DB
LEFT JOIN sys.tables T2
    ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Cómo elimina el motor de base de datos las filas antiguas

El proceso de limpieza depende del diseño del índice de la tabla de historial. Solo las tablas de historial con un índice agrupado (árbol B+ o almacén de columnas) pueden tener una directiva de retención finita configurada. Se crea una tarea en segundo plano para realizar la limpieza de datos antiguos de todas las tablas temporales con un periodo de retención finito. La lógica de limpieza del índice agrupado de almacén de filas (árbol B+) elimina las filas antiguas en fragmentos más pequeños (hasta 10 000), lo que minimiza la presión en el registro de base de datos y el subsistema de E/S. Aunque la lógica de limpieza usa el índice de árbol B+ necesario, no se puede garantizar el orden de las eliminaciones de las filas más antiguas en relación con el periodo de retención. No hay ninguna dependencia en el orden de limpieza en las aplicaciones.

La tarea de limpieza del almacén de columnas agrupado quita grupos de filas enteros a la vez (normalmente contiene un millón de filas cada uno), lo que es más eficaz, especialmente cuando los datos históricos se generan a un ritmo elevado.

La compresión de datos y la limpieza de la retención hacen que el índice de almacén de columnas agrupadas sea una elección perfecta en escenarios en los que la carga de trabajo genera rápidamente una gran cantidad de datos de historial. Este patrón es típico de cargas de trabajo intensivas de procesamiento de transacciones que usan tablas temporales para el seguimiento de cambios y la auditoría, el análisis de tendencias o la ingesta de datos de IoT.

Para más información, consulta Administración de datos históricos en tablas temporales con directivas de retención.

Contenido relacionado

• Tablas temporales
• Introducción a las tablas temporales con control de versiones del sistema
• Comprobaciones de coherencia del sistema de la tabla temporal
• Creación de particiones con tablas temporales
• Consideraciones y limitaciones de las tablas temporales
• Seguridad de la tabla temporal
• Tablas temporales con control de versiones del sistema con tablas optimizadas para memoria
• Funciones y vistas de metadatos de la tabla temporal