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 DatabaseAzure SQL Managed Instance
Con las tablas temporales con 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. Por lo tanto, al desarrollar 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 considerarse parte de la lógica de negocios de la aplicación mediante las tablas temporales. Por ejemplo, las aplicaciones de datos de auditoría y escenarios de viaje 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 determines el período de retención de datos, el siguiente paso es desarrollar un plan para administrar los datos históricos. Debes decidir 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:
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.
Nota:
Los ejemplos de este artículo usan Crear tabla temporal con 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 usar particiones de tabla de historial para implementar la limpieza de datos personalizada o el archivado sin conexión según una condición de tiempo. La partición de tabla también te 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 tabla, puede implementar una ventana deslizante para extraer la parte más antigua de los datos históricos de la tabla de historial y mantener el tamaño de la parte retenida constante en términos de edad: manteniendo los datos en la tabla de historial igual que en el período de retención requerido. 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.
Nota:
Para realizar la conmutación de particiones, el índice agrupado en la tabla de historial se debe alinear con el esquema de creación de particiones (debe contener ValidTo). La tabla de historial predeterminada creada por el sistema contiene un índice agrupado que incluye las columnas ValidTo y ValidFrom, que es óptimo para la creación de particiones, la inserción de nuevos datos de historial y las consultas temporales típicas. Para más información, consulte Tablas temporales.
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, crearías las mismas particiones de número 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.
Nota:
Consulta las consideraciones de rendimiento con las particiones de tabla siguientes para las implicaciones de rendimiento de uso de la opción RANGE LEFT frente a la opción RANGE RIGHT al configurar la creación de particiones.
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. Este es el momento en el que se debe iniciar la tarea de mantenimiento periódico de la partición por primera vez (debe programarse 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:
SWITCH OUT: permite crear una tabla de almacenamiento provisional y, después, cambiar una partición entre la tabla de historial y la tabla de almacenamiento provisional mediante la instrucción ALTER TABLE (Transact-SQL) con el argumento
SWITCH PARTITION(consulta 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, puedes archivar opcionalmente los datos de la tabla de almacenamiento provisional y, a continuación, quitar o truncar la tabla de almacenamiento provisional para que estén listos para la próxima vez que necesites realizar esta tarea de mantenimiento periódico de la partición.
MERGE RANGE: combinar la partición1vacía con la partición2mediante ALTER PARTITION FUNCTION (Transact-SQL); con la opciónMERGE RANGE(consulta el ejemplo B). Al quitar el límite inferior con esta función, se combina eficazmente la partición vacía1con la partición anterior2para formar una nueva partición1. Las demás particiones también cambian de forma efectiva sus ordinales.SPLIT RANGE: crear una partición 7 vacía mediante ALTER PARTITION FUNCTION (Transact-SQL) con la opciónSPLIT RANGE(consulta 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:
- En el paso (1), crea una tabla de almacenamiento provisional para el mes que quieras quitar (octubre sería el siguiente en este ejemplo).
- 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. - En el paso (4),
SWITCHla partición1a la tabla de almacenamiento provisional recién creada. - En el paso (6), modifica 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. - En el paso (7), divide 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.
Sin embargo, la mejor solución sería ejecutar regularmente un script de Transact-SQL genérico que fuese capaz de llevar a cabo la acción apropiada cada mes sin modificar el script. Es posible generalizar el script anterior para que actúe sobre los parámetros proporcionados (límite inferior que debe combinarse y límite nuevo que se creará con la división de particiones). Para evitar la creación de tablas de almacenamiento provisional cada mes, puede crear una de antemano y reutilizarla cambiando la restricción CHECK para que coincida con la partición que se va a desactivar. Consulte las páginas siguientes para obtener ideas sobre cómo se puede automatizar completamente la ventana deslizante mediante un script transact-SQL.
Consideraciones de rendimiento con las particiones de tabla
Es importante realizar las operaciones MERGE y SPLIT RANGE para evitar cualquier 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.
Primero se va a explicar visualmente el significado de 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, siempre quitas 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ón1, que está vacía (después de conmutar la partición), por lo queMERGE RANGEno causará ningún movimiento de datos. - En el caso
RANGE RIGHT, el límite de partición más bajo pertenece a la partición2, que no está vacía, ya que la partición1se ha vaciado cambiándola. En este caso,MERGE RANGEcausa un movimiento de datos (los datos de la partición2se mueven a la partición1). Para evitar esto, la opciónRANGE RIGHTdel escenario de ventana deslizante debe tener la partición1, que siempre está vacía. Esto significa que si usasRANGE RIGHT, debes crear y mantener una partición adicional en comparación con el caso deRANGE LEFT.
Conclusión: utilizar la opción RANGE LEFT en la partición deslizante es mucho más simple para la administración de la partición y evita el movimiento de datos. Sin embargo, la definición de los límites de partición con la opción RANGE RIGHT es un poco más simple, ya que no tiene que tratar con problemas de marca de tiempo 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, realiza 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 (bloqueando 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 realizar 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.
El siguiente diagrama muestra cómo debe organizarse la lógica de limpieza para una tabla única para 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.
- Implementa cada iteración como una invocación del procedimiento almacenado genérico que quita una parte de datos de la tabla de historial (consulta el siguiente ejemplo de código siguiente para este procedimiento).
- Calcule el número de filas que debe eliminar para una tabla temporal individual cada vez que se invoca el proceso. Según esto y el número de iteraciones que quieras tener, determina 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 permita eliminar los datos de una tabla temporal única podría ser similar al fragmento de código siguiente (revise este código con cuidado y ajústelo antes de aplicarlo a su entorno):
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;
/*Generate 3 statements that run inside a transaction:
(1) SET SYSTEM_VERSIONING = OFF,
(2) DELETE FROM history_table,
(3) SET SYSTEM_VERSIONING = ON
On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server generates the following error:
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>'.
*/
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. 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, 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 de la columna que representa el final del período SYSTEM_TIME. 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, 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 is_temporal_history_retention_enabled de base de datos se establece en ON de forma 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, ejecuta 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
)
);
Puedes especificar el período 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 puedes usar explícitamente la palabra clave INFINITE.
En algunos escenarios, es posible que quieras configurar la retención tras crear la tabla o 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 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;
Eliminación de las filas antiguas en SQL Database
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 el período 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 cual minimiza 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.
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 versión 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 con optimización para memoria
- Funciones y vistas de metadatos de la tabla temporal
Comentarios
Próximamente: A lo largo de 2024 iremos eliminando gradualmente GitHub Issues como mecanismo de comentarios sobre el contenido y lo sustituiremos por un nuevo sistema de comentarios. Para más información, vea: https://aka.ms/ContentUserFeedback.
Enviar y ver comentarios de