管理系统版本控制时态表中历史数据的保留期

项目
08/01/2024

适用于：SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库 Azure SQL 托管实例

对于系统版本控制时态表，历史记录表可能比常规表更容易增加数据库大小，尤其是在以下条件下：

你长期保留历史数据
你采用频繁更新或删除的数据修改模式

不断增长的大型历史记录表可能会成为一个问题，这不单单体现在存储成本的增加上，而且还会降低时态查询的性能。针对管理历史记录表中的数据制定一个数据保留策略，是规划和管理每个时态表的生命周期的一个重要方面。

历史记录表的数据保留管理

管理临时表数据的保留首先要确定每个临时表的所需保留期。在大多数情况下，应将保留策略作为使用时态表的应用程序业务逻辑的一部分。例如，数据审核和按时间顺序查看方案中的应用程序对历史数据必须可供在线查询的时间有严格要求。

确定数据保留期后，应制定管理历史数据的计划。必须确定存储历史数据的方式和位置，以及如何删除超过保留要求的历史数据。可通过以下方法管理时态历史记录表中的历史数据：

表分区
自定义清理脚本
保留策略

使用其中每种方法时，迁移或清理历史记录数据的逻辑将基于对应于当前表期末时间的列。每行的期末时间值确定行版本“结束”（即放入历史记录表）的时刻。例如，条件 ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) 指定超过一个月的历史数据需要删除并从历史记录表中移出。

本文中的示例使用创建系统版本控制时态表文章中创建的示例。

使用表分区方法

已分区表和索引可以使大型表更易于管理和扩展。借助表分区方法，可以根据时间条件实现自定义数据清理或脱机存档。通过使用分区消除，表分区还可以在查询数据历史记录子集上的时态表时带来性能优势。

借助表分区，可以使用滑动窗口将最早的历史数据部分移出历史记录表，并使保留部分的大小在期限上保持恒定。滑动窗口维护历史记录表中的数据，使其等于所需的保留期。当 SYSTEM_VERSIONING 为 ON 时，支持将数据换出历史记录表的操作，这意味着，你可以在不造成维护时段或阻碍常规工作负载的情况下清除一部分历史记录数据。

注意

若要执行分区切换，历史记录表中的聚集索引必须与分区架构相符（必须包含 ValidTo）。系统创建的默认历史记录表包含一个聚集索引，其中包括 ValidTo 和 ValidFrom 列，并且最适合于用于分区、插入新历史记录数据和典型的时态查询。有关详细信息，请参阅临时表。

使用滑动窗口时需要执行两组任务：

分区配置任务
重复性分区维护任务

为便于演示，假设你希望将历史数据保留六个月，并且希望在一个独立的分区中保留每个月的数据。此外，假设你在 2023 年 9 月激活了系统版本控制。

分区配置任务将为历史记录表创建初始分区配置。对于本示例，你将创建与滑动窗口（以月为单位）相符的分区数，加上一个预先准备的附加空分区（如本文后面部分所述）。此配置可确保首次启动重复性分区维护任务时系统能够正确存储新数据，同时保证永远不会拆分包含数据的分区，从而避免很高的数据移动开销。应参考本文后面部分中的示例脚本，使用 Transact-SQL 执行此任务。

下图显示了将数据保留六个月的初始分区配置。

注意

有关配置分区时使用 RANGE LEFT 与 RANGE RIGHT 对性能产生的影响，请参阅本文后面部分中的表分区的性能注意事项。

第一个和最后一个分区分别以下限和上限“打开”，以确保每个新行具有目标分区，而不管分区依据列中的值如何。随着时间的推移，历史记录表中的新行将移入更高的分区。当第六个分区被填满时，即表示达到了目标保留期。这是首次启动定期分区维护任务的时刻。需要安排定期运行此任务，在此示例中为一个月一次。

下图演示了重复性分区维护任务（参阅本部分稍后介绍的详细步骤）。

重复性分区维护任务的详细步骤如下：

SWITCH OUT：创建临时表，然后结合使用 SWITCH PARTITION 参数和 ALTER TABLE 语句，在历史记录表与时态表之间切换分区（参阅示例 C。在表之间切换分区）。
```
ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];
```
切换分区后，可以选择存档时态表中的数据，然后删除或截断临时表，以便下一次可以执行此定期分区维护任务。
MERGE RANGE：结合使用 1 和 ALTER PARTITION FUNCTION，将空分区 2 与分区 MERGE RANGE 合并（参阅示例 B）。通过使用此函数删除下限，可以有效地将空分区 1 与以前的分区 2 合并，从而构成新分区 1。其他分区也会有效地更改其序号。
SPLIT RANGE：结合使用 7 和 ALTER PARTITION FUNCTION，创建新的空分区 SPLIT RANGE（参阅示例 A）。通过使用此函数添加新上限，可以有效地为下一个月创建独立的分区。

使用 Transact-SQL 在历史记录表中创建分区

使用以下 Transact-SQL 脚本来创建分区函数和分区架构，然后重新创建要与分区架构进行分区对齐的聚集索引以及分区。对于本示例，将使用从 2023 年 9 月开始的每月分区创建六个月的滑动窗口。

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;

使用 Transact-SQL 来维护滑动窗口方案中的分区

使用以下 Transact-SQL 脚本来维护滑动窗口方案中的分区。对于本示例，将使用 MERGE RANGE 切出 2023 年 9 月的分区，然后使用 SPLIT RANGE 添加 2024 年 3 月的新分区。

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

你可以稍微修改上述脚本，并将其用于每月的例行维护过程：

在步骤 (1) 中，为想要删除的月份创建新的临时表（在本示例中，下一个月是 10 月）。
在步骤 (3) 中，创建并检查与想要删除数据的月份匹配的约束：对于 10 月分区，约束为 ValidTo <= N'2023-10-31T23:59:59.999'。
在步骤 (4) 中，SWITCH 分区 1 到新创建的临时表。
在步骤 (6) 中，通过合并下限来更改分区函数：移出 10 月份的数据后为 MERGE RANGE(N'2023-10-31T23:59:59.999'。
在步骤 (7) 中，通过创建新上限来拆分分区函数：移出 10 月份的数据后为 SPLIT RANGE (N'2024-04-30T23:59:59.999' 。

但是，最佳解决方案是定期运行一个通用 Transact-SQL 脚本，该脚本无需修改即可每月执行相应的操作。可以通用化上述脚本，以便能够使用提供的参数（需要合并的下限，以及要使用分区拆分创建的新边界）运行。为了避免每月创建临时表，可以预先创建一个临时表并重复使用，方法是更改 CHECK 约束来匹配切换出的分区。有关详细信息，请参阅如何完全自动化滑动窗口。

表分区的性能注意事项

必须执行 MERGE 和 SPLIT RANGE 操作来避免任何数据移动，因为数据移动可能会产生极高的性能开销。有关详细信息，请参阅修改分区函数。为此，在创建分区函数时使用 RANGE LEFT 而不是 RANGE RIGHT。

下图描述了 RANGE LEFT 和 RANGE RIGHT 选项：

将某个分区函数定义为 RANGE LEFT 时，指定的值为分区的上限。使用 RANGE RIGHT 时，指定的值为分区的下限。使用 MERGE RANGE 操作从分区函数定义中删除边界时，底层实现也会删除包含边界的分区。如果该分区不为空，则会因 MERGE RANGE 操作而将数据移到分区。

在滑动窗口方案中，始终要删除最低的分区边界。

RANGE LEFT 用例：最低分区边界属于分区 1，而该分区是空的（在换出分区后），因此执行 MERGE RANGE 不会发生任何数据移动。
RANGE RIGHT 用例：最低分区边界属于分区 2，而该分区不是空的，因为分区 1 在换出后是空的。在这种情况下，MERGE RANGE 会发生数据移动（将数据从分区 2 移动到分区 1）。为了避免此问题，滑动窗口方案中的 RANGE RIGHT 需要一个始终为空的分区 1。这意味着，如果使用 RANGE RIGHT，则应创建和维护一个额外的分区（与 RANGE LEFT 用例进行比较）。

结论：在滑动分区中使用 RANGE LEFT 时，进行分区管理要方便得多，还能避免数据移动。但是，使用 RANGE RIGHT 定义分区要稍微简单一些，因为无需处理日期和时间检查问题。

使用自定义清理脚本方法

如果表分区方法不可行，则另一种方法是使用自定义清理脚本从历史记录表中删除数据。仅当 SYSTEM_VERSIONING = OFF 时，才可以从历史记录表中删除数据。为了避免数据不一致，请在维护时段（修改数据的工作负载处于非活动状态）或在事务中（有效阻止其他工作负载）执行清理。此操作需要对当前和历史记录表拥有 CONTROL 权限。

为了将对常规应用程序和用户查询的阻碍减到最小，请在事务中执行清理脚本时，以一定的延迟删除较小区块中的数据。尽管在所有情况下要删除的每个数据区块的最佳大小并没有定论，但在单个事务中删除超过 10,000 行可能会造成严重损失。

清理逻辑对于每个时态表是相同的，因此，可以通过你计划的、要针对你要限制其数据历史记录的每个时态表定期运行的泛型存储过程，来自动化该逻辑。

下图说明了应该如何针对单个表组织清理逻辑，以便降低对运行中工作负载的影响。

下面是有关实施该过程的概要指导。将清理逻辑计划为每天运行，并循环访问所有需要清理数据的临时表。使用 SQL Server 代理或其他工具来计划此过程：

通过多次迭代删除较小区块中每个时态表的历史数据（从最早的行开始，到最新的行为止），并避免在单个事务中删除所有行，如上图所示。
将每个迭代实现为泛型存储过程的调用，以便从历史记录表中删除一部分数据（参阅以下代码示例来了解此过程）。
计算每次调用该过程时，需要在单个临时表中删除的行数。根据该结果以及想要使用的迭代次数，确定每个过程调用的动态拆分点。
计划针对单个表的迭代之间的延迟时段，以降低对访问时态表的应用程序的影响。

删除单个时态表的数据的存储过程可能类似于以下代码片段。请仔细查看此代码，并在应用于环境之前对其进行调整。

此脚本生成在事务中运行的三个语句：

SET SYSTEM_VERSIONING = OFF
DELETE FROM <history_table>
SET SYSTEM_VERSIONING = ON

在 SQL Server 2016 (13.x) 中，前两个步骤必须在单独的 EXEC 语句中运行，否则 SQL Server 将生成类似于以下示例的错误：

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;

使用时态历史记录保留策略方法

适用于：SQL Server 2017 (14.x) 及更高版本和 Azure SQL 数据库。

可以在单个表的级别配置临时历史记录保留期，以便用户创建灵活的老化策略。时态保留要求在创建表或更改架构期间设置一个参数即可。

定义保留策略后，数据库引擎将开始定期检查是否有符合数据自动清理条件的历史数据行。匹配行的识别以及从历史记录表中删除这些行的过程以透明方式在系统计划和运行的后台任务中发生。历史记录表行的期限条件根据表示 SYSTEM_TIME 期限结束时间的列进行检查（在这些示例中，为 ValidTo 列）。例如，如果保留期设置为六个月，则可以清理符合以下条件的表行：

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

在前面的示例中，ValidTo 列对应于句点的 SYSTEM_TIME 期限结束时间。

如何配置保留策略

配置时态表的保留策略之前，请检查是否已在数据库级别启用时态历史记录保留：

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

数据库标志 is_temporal_history_retention_enabled 默认设置为 ON，但可以使用 ALTER DATABASE 语句对其进行更改。在执行时间点还原 (PITR) 操作后，该值会自动设置为 OFF。要为数据库启用时态历史记录保留策略清理，请运行以下语句。必须将 <myDB> 替换为要更改的数据库：

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

在创建表的过程中，可以通过指定 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
    )
);

可使用不同的时间单位指定保留期：DAYS、WEEKS、MONTHS 和 YEARS。如果省略 HISTORY_RETENTION_PERIOD，则假定 INFINITE 保留。也可以显式使用 INFINITE 关键字。

在某些情况下，你可能想要在创建表后配置保留策略或更改以前配置的值。在这种情况下，请使用 ALTER TABLE 语句：

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

要查看保留策略的当前状态，请使用以下示例。该查询将数据库级别的时态保留启用标志与单个表的保留期相联接：

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;

数据库引擎如何删除陈旧行

清理过程具体取决于历史记录表的索引布局。只有具有聚集索引（B+ 树或列存储）的历史记录表可配置有限保留策略。对于具有有限保留期的所有时态表，系统会创建一个后台任务来执行陈旧数据清理。行存储（B+ 树）聚集索引的清理逻辑会删除较小区块（最多 10,000）的陈旧行，以便减轻数据库日志和 I/O 子系统的压力。虽然清理逻辑使用要求的 B+ 树索引，但无法保证删除超过保留期的行的顺序。因此，请不要对应用程序中的清理顺序有任何依赖。

针对聚集列存储的清理任务会一次性删除整个行组（每个行组通常包含 1 百万行），这种方式非常高效，尤其是在高速生成历史数据时。

工作负载快速生成大量的历史数据时，数据压缩和保留数据清理使得聚集列存储索引成为完美的选择。使用临时表进行更改跟踪和审核、趋势分析或 IoT 数据引入的密集型事务处理工作负荷往往使用该模式。

有关详细信息，请参阅使用保留策略管理时态表中的历史数据。

通过

管理系统版本控制时态表中历史数据的保留期

历史记录表的数据保留管理

使用表分区方法

使用 Transact-SQL 在历史记录表中创建分区

使用 Transact-SQL 来维护滑动窗口方案中的分区

表分区的性能注意事项

使用自定义清理脚本方法

使用时态历史记录保留策略方法

如何配置保留策略

数据库引擎如何删除陈旧行

反馈

其他资源

通过

管理系统版本控制时态表中历史数据的保留期

历史记录表的数据保留管理

使用表分区方法

使用 Transact-SQL 在历史记录表中创建分区

使用 Transact-SQL 来维护滑动窗口方案中的分区

表分区的性能注意事项

使用自定义清理脚本方法

使用时态历史记录保留策略方法

如何配置保留策略

数据库引擎如何删除陈旧行

相关内容

反馈

其他资源