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

项目
03/14/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 (Transact-SQL) 语句在历史记录表与临时表之间切换分区（参阅示例 C：在表之间切换分区）。
```
ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];
```
切换分区后，可以选择存档临时表中的数据，然后删除或截断临时表，以便下一次可以执行此重复性分区维护任务。
MERGE RANGE：结合 1 使用 ALTER PARTITION FUNCTION (Transact-SQL) 将空分区 2 与分区 MERGE RANGE 合并（参阅示例 B）。通过使用此函数删除下限，可以有效地将空分区 1 与以前的分区 2 合并，以构成新分区 1。其他分区也会有效地更改其序号。
SPLIT RANGE：结合 SPLIT RANGE 使用 ALTER PARTITION FUNCTION (Transact-SQL) 创建新的空分区 7（参阅示例 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 约束来重复使用，从而匹配将要切换的分区。请参阅以下页面，了解如何使用 Transact-SQL 脚本实现滑动窗口的完全自动化。

表分区的性能注意事项

必须执行 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 代理或其他工具来计划此过程：

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

以下代码段中显示了一个用于删除单个临时表的数据的存储过程（请仔细研究此代码，并在将它应用到环境之前进行调整）：

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;

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

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

可以在单个表的级别配置临时历史记录保留期，以便用户创建灵活的老化策略。应用临时保留的过程十分简单：只需在创建表或更改架构期间设置一个参数即可。

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

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。若要为数据库启用时态历史记录保留策略清理，请执行以下语句：

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;

SQL 数据库如何删除陈旧行

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

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

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

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

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

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

使用表分区方法

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

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

表分区的性能注意事项

使用自定义清理脚本方法

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

如何配置保留策略

SQL 数据库如何删除陈旧行

反馈

反馈

其他资源

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

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

使用表分区方法

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

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

表分区的性能注意事项

使用自定义清理脚本方法

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

如何配置保留策略

SQL 数据库如何删除陈旧行

相关内容

反馈

反馈

其他资源