Administración de datos históricos en tablas temporales con directivas de retención

Se aplica a:Azure SQL DatabaseAzure SQL Managed Instance

Las tablas temporales pueden aumentar el tamaño de la base de datos más que las tablas normales, especialmente si conserva los datos históricos durante un período de tiempo más largo. De ahí que las directivas de retención de datos históricos sean un aspecto importante del planeamiento y la administración del ciclo de vida de cada tabla temporal. Las tablas temporales de Azure SQL Database e Instancia administrada de Azure SQL incluyen un mecanismo de retención fácil de usar que ayuda a realizar esta tarea.

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. Aplicar la retención temporal es muy sencillo: solo requiere establecer un parámetro al cambiar el esquema o al crear la tabla.

Después de definir la directiva de retención, Azure SQL Database e Instancia administrada de Azure SQL comprueban de forma periódica si hay filas de historial 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. La condición de antigüedad de las filas de la tabla de historial se comprueba en función de la columna que representa el final del SYSTEM_TIME período. Si el período 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, se supone que la ValidTo columna corresponde al final del SYSTEM_TIME período.

Configuración de la directiva de retención

Antes de configurar la directiva de retención para una tabla temporal, primero compruebe si está habilitada la retención temporal de datos históricos en el nivel de base de datos.

SELECT is_temporal_history_retention_enabled, [name]
FROM sys.databases;

La marca is_temporal_history_retention_enabled de base de datos está establecida en ON de forma predeterminada, pero los usuarios pueden cambiarla con la instrucción ALTER DATABASE. También se establece automáticamente en OFF después de la operación de restauración a un momento dado. Para habilitar la limpieza de la retención de historial temporal de la base de datos, ejecute la instrucción siguiente:

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
     )
 );

Azure SQL Database e Instancia administrada de Azure SQL permiten especificar el período de retención mediante unidades de tiempo diferentes: DAYS, WEEKS, MONTHSy YEARS. Si HISTORY_RETENTION_PERIOD se omite, se supone una retención infinita. También puede usar explícitamente la INFINITE palabra clave .

En algunos escenarios, es posible que desee configurar la retención después de la creación de la tabla o cambiar el valor configurado anteriormente. En ese caso, use 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 la siguiente consulta, que combina la marca de habilitación de retención temporal en el nivel de base de datos con períodos 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;

Procedimiento para eliminar las filas antiguas

El proceso de limpieza depende del diseño del índice de la tabla de historial. Es importante tener en cuenta que 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 el período de retención finito. La lógica de limpieza del índice agrupado de almacén de filas (árbol B) elimina la fila antigua en fragmentos más pequeños (hasta 10 K), lo que reduce la presión en el registro de base de datos y el subsistema de E/S. A pesar de que 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 período de retención. Por tanto, no hay ninguna dependencia en el orden de limpieza en sus aplicaciones.

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

La excelente compresión de datos y la limpieza eficaz 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.

Consideraciones sobre el índice

La tarea de limpieza de tablas con índice agrupado de almacén de filas requiere que el índice se inicie con la columna correspondiente al final del período de SYSTEM_TIME. Si este tipo de índice no existe, no se puede configurar un periodo de retención finito:

Mensaje 13765, nivel 16, estado 1

Error al establecer un período de retención finito en la tabla temporal con versiones del sistema "temporalstagetestdb.dbo.WebsiteUserInfo" porque la tabla Historial "temporalstagetestdb.dbo.WebsiteUserInfoHistory" no contiene el índice agrupado requerido. Puede crear un índice agrupado de almacén de columnas o de árbol en B que empiece por la columna que coincide con el final del período SYSTEM_TIME en la tabla Historial.

Es importante tener en cuenta que la tabla de historial predeterminada creada mediante Azure SQL Database e Instancia administrada de Azure SQL ya tiene el índice agrupado, lo que es compatible con la directiva de retención. Si intenta quitar ese índice en una tabla con el período de retención finito, se produce un error en la operación:

Mensaje 13766, nivel 16, estado 1

No se puede anular el índice agrupado 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' porque se está usando para la limpieza automática de datos antiguos. Si necesita anular este índice, considere establecer HISTORY_RETENTION_PERIOD en INFINITE en la correspondiente tabla temporal con versiones del sistema.

La limpieza en el índice de almacén de columnas agrupado funciona de manera óptima si las filas históricas se insertan en orden ascendente (ordenadas por la columna de fin de período), que suele ser el caso cuando la tabla de historial se rellena exclusivamente mediante el mecanismo SYSTEM_VERSIONIOING. Si las filas de la tabla de historial no se ordenan al final de la columna de período (lo que podría ser el caso si ha migrado los datos históricos existentes), debe volver a crear un índice de almacén de columnas agrupado encima del índice de almacén de filas de árbol B que se ordena correctamente, para lograr un rendimiento óptimo.

Evite volver a generar el índice de almacén de columnas agrupado en la tabla de historial con el período de retención finito, ya que puede cambiar el orden de los grupos de filas impuesto naturalmente por la operación de control de versiones. Si necesita volver a generar el índice de almacén de columnas agrupado en la tabla de historial, hágalo creándolo de nuevo encima del índice de árbol B compatible, así se conserva el orden en los grupos de filas necesarios para la limpieza normal de datos. El mismo enfoque se debe adoptar si crea una tabla temporal con una tabla de historial existente que tiene un índice de columnas agrupado sin orden de datos garantizado:

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

Cuando la tabla de historial con el índice de almacén de columnas agrupado tiene configurado el período de retención finito, no puede crear índices de árbol B no agrupados adicionales en esa tabla:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Se intenta ejecutar la instrucción anterior se producirá el siguiente error:

Mensaje 13772, nivel 16, estado 1

No se puede crear un índice no agrupado en la tabla de historial temporal "WebsiteUserInfoHistory" porque tiene definido un período de retención finito y un índice agrupado de almacén de columnas.

Consulta de tablas con directiva de retención

Todas las consultas en la tabla temporal filtran automáticamente las filas históricas que coinciden con la directiva de retención finita, a fin de evitar resultados imprevisibles e incoherentes, ya que las filas antiguas se pueden eliminar mediante la tarea de limpieza, en cualquier momento en el tiempo y en orden aleatorio.

La siguiente imagen muestra el plan de consulta para una consulta simple:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

El plan de consulta incluye un filtro adicional aplicado al final de la columna de período (ValidTo) en el operador Clustered Index Scan en la tabla de historial (resaltado). En este ejemplo se supone que se ha establecido el período de retención de 1 MES en la tabla WebsiteUserInfo.

Sin embargo, si consulta directamente la tabla del historial, es posible que vea filas anteriores al período de retención especificado, pero sin ninguna garantía de resultados de consulta repetibles. La siguiente imagen muestra el plan de ejecución de consultas de la consulta en la tabla de historial sin aplicar filtros adicionales:

No base su lógica de negocio en la lectura de la tabla de historial más allá del período de retención, ya que podría obtener resultados incoherentes o inesperados. Se recomienda usar consultas temporales con FOR SYSTEM_TIME cláusula para analizar datos en tablas temporales.

Consideraciones sobre la restauración a un momento dado

Cuando crea una nueva base de datos mediante la restauración de una base de datos existente a un momento específico en el tiempo, tiene una retención temporal deshabilitada en el nivel de base de datos. (Indicador is_temporal_history_retention_enabled establecido en OFF). Esta funcionalidad permite examinar todas las filas históricas tras la restauración, sin preocuparse de que las filas antiguas se quiten antes de conseguir consultarlas. Puede usarla para inspeccionar los datos históricos más allá del período de retención configurado.

Supongamos que una tabla temporal tiene MONTH un período de retención especificado. Si la base de datos se creó en el nivel de servicio Premium, podrá crear una copia de ella con el estado de hasta 35 días hacia atrás en el pasado. Eso permitiría analizar de forma eficaz las filas históricas que tienen hasta 65 días de antigüedad consultando la tabla de historial directamente.

Si desea activar la limpieza de retención temporal, ejecute la siguiente instrucción de Transact-SQL después de la restauración a un momento dado:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Contenido relacionado

• Introducción a las tablas temporales
• Tablas temporales