システムバージョン管理されたテンポラルテーブルの履歴データの保有期間管理

[アーティクル]
07/29/2024

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL Database Azure SQL Managed Instance

テンポラルテーブルがシステムバージョン管理されている場合、履歴テーブルによりデータベースのサイズが通常のテーブルよりも増えることがあります。これは特に、次の条件下で当てはまります。

長期間にわたり履歴データを保持する。
大量のデータを定期的に更新または削除する。

大量の履歴テーブルが増加を続けると、ストレージ費用と一時的なクエリ実行による負荷の両方に起因した問題が引き起こされる可能性があります。データの保持ポリシーを作成して履歴テーブルのデータを管理することが、あらゆるテンポラルテーブルのライフサイクルの計画と管理において重要な要素となります。

履歴テーブルのデータ保有期間管理

テンポラルテーブルのデータ保有期間の管理は、テンポラルテーブルごとに必要な保有期間を決定することから始まります。ほとんどの場合、保持ポリシーは、テンポラルテーブルを利用する用途のビジネスロジックの一部であると見なすべきです。たとえば、データ監査時のアプリケーションや時間移動のシナリオでは、オンラインクエリ実行のために履歴データを利用できる期間について、要件が確定されます。

データ保持期間を決定したら、履歴データを管理するための計画を作成する必要があります。履歴データを格納する方法と場所、および保持要件より古い履歴データを削除する方法を決めるます。次の手法で、テンポラル履歴テーブルの履歴データを管理できます。

テーブルパーティション
カスタムクリーンアップスクリプト
保持ポリシー

いずれの手法でも、履歴データを移行またはクリーンアップするためのロジックは、現在のテーブルの期間終了に相当する列に基づきます。各行の期間終了値により、その行のバージョンが 閉じられる、つまり、履歴テーブルに入るタイミングが決定されます。たとえば、条件 ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) では、1 か月以上経過した履歴データは履歴テーブルから削除されます。

この記事の例では、こちらの「システムバージョン管理されたテンポラルテーブルを作成する」の記事で作成されたサンプルを使用します。

テーブルパーティション分割手法の利用

パーティション分割とインデックスにより、大規模なテーブルがより管理しやすくなり、拡張性が向上します。テーブルのパーティション分割手法では、時間条件を基準にカスタムデータクリーンアップやオフラインアーカイブを実装できます。テーブルパーティション分割でも、パーティションの削除を利用してデータ履歴のサブセットのテンポラルテーブルにクエリを実行するとき、パフォーマンス上の利点が得られます。

テーブルパーティション分割では、スライディングウィンドウを実行し、経過時間に基づき、履歴テーブルから最も古い履歴データを除外し、保持部分のサイズを一定に維持できます。スライディングウィンドウでは、履歴テーブル内のデータが必要な保持期間と等しく保持されます。履歴テーブルからデータを切り替える操作は、SYSTEM_VERSIONING が ON になっている間サポートされます。つまり、メンテナンスウィンドウを導入したり、通常の作業負荷をブロックしたりしなくても履歴データの一部をクリーンアップできます。

Note

パーティションを切り替えるには、履歴テーブルのクラスター化インデックスがパーティション分割スキーマと連携している必要があります (これには ValidTo が含まれている必要があります)。システムにより作成される既定の履歴テーブルには、ValidTo と ValidFrom 列を含むクラスター化インデックスが含まれており、パーティション分割、新しい履歴データの挿入、標準的で一時的なクエリ実行に最適です。詳細については、「テンポラルテーブル」を参照してください。

スライディングウィンドウは 2 セットのタスクからなり、ユーザーがそれを実行する必要があります:

パーティション分割構成タスク
定期的パーティション保守管理タスク

説明のために、履歴データを 6 か月保持するし、月ごとのデータを個別のパーティションに保管するものとします。また、2023 年 9 月にシステムのバージョン管理をアクティブにしたとします。

パーティション分割構成タスクでは、履歴テーブルの初回パーティション分割構成を作成します。この例では、数ヶ月間のスライディングウィンドウのサイズと同じ数のパーティションを作成し、さらに空の追加パーティションを事前に用意しておきます (下記で説明します)。この構成により、初めて定期的パーティション保守タスクを始めたとき、システムが新しいデータを適切に格納できるようになり、データ移動が高額になるのを回避するためにパーティションをデータで分割しないことが保証されます。このタスクは Transact-SQL を利用し、本記事で後述するサンプルスクリプトを用いて実行します。

次は図は、6 か月分のデータを維持する初回パーティション分割構成です。

Note

パーティション分割を構成する際に RANGE LEFT 対 RANGE RIGHT を利用するときのパフォーマンスの影響については、本記事で後出の「テーブルパーティション分割におけるパフォーマンス上の考慮事項」を参照してください。

最初と最後のパーティションがそれぞれ、下と上の境界で オープン になっており、パーティション分割列の値に関係なく、すべての新しい行に対象パーティションがあることを確実にします。時間の経過と共に、履歴テーブルの新しい行が上位のパーティションに入ります。 6 番目のパーティションがいっぱいになると、目標とした保持期間に到達したことになります。これは、定期的なパーティションメンテナンスタスクを初めて開始する時です。この例では、1 か月に 1 回、定期的に実行するようにスケジュールする必要があります。

次の図は、定期パーティション保守タスクの例です (本記事の後出に詳しい手順があります)。

定期パーティション保守管理タスクの詳しい手順:

SWITCH OUT: ステージングテーブルを作成した後、ALTER TABLE ステートメントと SWITCH PARTITION 引数を使って、履歴テーブルとステージングテーブルの間でパーティションを切り替えます (「例 C. テーブル間でパーティションを切り替える」を参照)。
```
ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];
```
パーティション切り替え後、任意でデータをステージングテーブルからアーカイブし、それから、この定期パーティション保守タスクを次に実行するときのために、ステージングテーブルを削除するか、切り詰めることができます。
MERGE RANGE: ALTER PARTITION FUNCTION と MERGE RANGE を利用し、空のパーティション 1 とパーティション 2 を統合します (例 B を参照)。この関数で一番下の境界を削除することで、空のパーティション 1 と前のパーティション 2 を統合し、新しいパーティション 1 を効果的に作成します。結果として、他のパーティションの序数も変更されます。
SPLIT RANGE: ALTER PARTITION FUNCTION と 7 を利用し、新しい空のパーティション SPLIT RANGE を作成します (例 A を参照)。この関数で上位の境界を新しく追加することで、翌月のために別個のパーティションを効果的に作成します。

Transact-SQL を利用し、履歴テーブルでパーティションを作成する

次の Transact-SQL スクリプトを利用し、パーティション関数とパーティションスキーマを作成します。また、クラスター化インデックスを再作成し、パーティションをパーティションスキーマに合わせて調整します。この例では、月単位のパーティションで 2023 年 9 月に開始する 6 か月のスライディングウィンドウを作成します。

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) では、パーティション 1 を新しく作成したステージングテーブルに SWITCH します。
手順 (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 RIGHT ではなく、RANGE LEFT を使用します。

次の図は、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 ケースと比較して追加のパーティションを 1 つ作成して保守する必要があることを意味します。

結論: スライディングパーティションに RANGE LEFT を利用することはパーティション管理としてはより簡単であり、データ移動を回避します。 RANGE RIGHT でパーティション境界を定義する場合、日時のチェック問題を扱う必要がないため、少し単になります。

カスタムクリーンアップスクリプト法の利用

テーブルパーティション分割が実行できない場合、別の手法では、カスタムクリーンアップスクリプトを利用して履歴テーブルからデータを削除することになります。履歴テーブルからデータを削除することは、SYSTEM_VERSIONING = OFF のときにのみ可能です。データの不整合を回避するために、保守ウィンドウ内 (データを変更するワークロードがアクティブではないとき) か、トランザクション (他のワークロードが効果的にブロックされる) 内でクリーンアップを実行します。この操作には、現行テーブルと履歴テーブルの CONTROL 権限が必要になります。

通常のアプリケーションとユーザークエリを最小限ブロックするには、トランザクション内でクリーンアップスクリプトを実行するとき、遅延ありで、データの小規模なまとまりを削除します。削除されるデータチャンクごとのサイズについては、あらゆるシナリオに最適なサイズというものはありませんが、1 回のトランザクションで 10,000 行以上を削除すると、大幅な負担が発生する可能性があります。

クリーンアップロジックは、すべてのテンポラルテーブルで同じです。そのため、一般的なストアドプロシージャで自動化し、データ履歴を制限するテンポラルテーブルごとに、定期的に実行できます。

次の図は、実行中のワークロードに対する影響を抑えるように、1 つのテーブルのクリーンアップロジックを調整する方法を示しています。

プロセスの実装については、上位のガイドラインがあります。クリーンアップロジックを毎日実行するようにスケジュールし、データクリーンアップが必要なすべてのテンポラルテーブルに繰り返して起用します。 SQL Server エージェントまたは別のツールを利用し、このプロセスをスケジュールします。

最も古い行から最新の行まで、すべてのテンポラルテーブルの履歴データを削除します。上の図のように、数回繰り返して小さいチャンクで実行し、1 回のトランザクションで全行を削除することは避けます。
履歴テーブルから一部のデータを削除する汎用ストアドプロシージャを呼び出すことですべての繰り返しを実行します (この手順については、次のコード例を参照してください)。
プロセスを呼び出すたびに、個々のテンポラルテーブルに対して、削除する行を計算します。結果と必要な反復の数に基づき、手順の呼び出しごとに動的な分割点を決定します。
1 つテーブルに対する反復の間に一定の遅延を与え、テンポラルテーブルにアクセスするアプリケーションに与える影響を抑えます。

1 つのテンポラルテーブルのデータを削除するストアドプロシージャは次のコードスニペットのようになります。このコードを慎重に確認し、環境に適用する前に調整します。

このスクリプトは、トランザクション内で実行される 3 つのステートメントを生成します。

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

SQL Server 2016 (13.x) では、最初の 2 つの手順を個別の 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 Database

テンポラル履歴保有期間は個々のテーブルレベルで構成でき、ユーザーは柔軟なエージングポリシーを作成できます。テンポラル保有期間に必要なのは、テーブル作成時またはスキーマ変更時にパラメーターを 1 つ設定することだけです。

アイテム保持ポリシーを定義した後、データベースエンジンは自動データクリーンアップの対象となる履歴行があるかどうかの定期的な確認を開始します。一致する行の識別と履歴テーブルからの削除は、システムによってスケジュール設定されて実行されるバックグラウンドタスクにおいて透過的に行われます。履歴テーブルの行の経過時間の条件は、SYSTEM_TIME 期間の終了を表す列 (これらの例では、ValidTo 列) に基づいてチェックされます。たとえば、保持期間が 6 か月に設定されている場合、クリーンアップの対象となるテーブルの行は次の条件を満たします。

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 ステートメントを使用して変更できます。この値は、ポイントインタイムリストア操作後は、自動的に 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+ ツリー) クラスター化インデックスのクリーンアップロジックは、データベースログと I/O サブシステムへの負荷を最小限に抑えるため、小さいチャンク (最大 10,000) で期限切れの行を削除します。クリーンアップロジックは必要な B+ ツリーインデックスを利用しますが、保持期間より古い行の削除の順序は保証できません。アプリケーションではクリーンアップ順序に依存しないでください。

クラスター化された列ストアのクリーンアップタスクでは行グループ(通常は、1 グループに 100 万行が含まれます)全体が一度に削除されるため、特に、履歴データが頻繁に生成されるような場合により効果的です。

データ圧縮と保持期間のクリーンアップにより、クラスター化列ストアインデックスはワークロードが急速に大量の履歴データを生成するシナリオに最適な選択肢になります。このようなパターンは、変更の追跡や監査、傾向分析、IoT データの取り込みにテンポラルテーブルを使用する大量のトランザクション処理ワークロードでよく見られます。

詳細については、「保持ポリシーを使用したテンポラルテーブルでの履歴データの管理」を参照してください。

次の方法で共有

システムバージョン管理されたテンポラルテーブルの履歴データの保有期間管理

履歴テーブルのデータ保有期間管理

テーブルパーティション分割手法の利用

Transact-SQL を利用し、履歴テーブルでパーティションを作成する

Transact-SQL を利用し、スライディングウィンドウシナリオのパーティションを維持します

テーブルパーティション分割に関するパフォーマンス上の考慮事項

カスタムクリーンアップスクリプト法の利用

テンポラル履歴保持期間ポリシー法の利用

リテンションポリシーの構成方法

データベースエンジンによって期限切れの行が削除されるしくみ

フィードバック

その他のリソース

次の方法で共有

システム バージョン管理されたテンポラル テーブルの履歴データの保有期間管理

履歴テーブルのデータ保有期間管理

テーブル パーティション分割手法の利用

Transact-SQL を利用し、履歴テーブルでパーティションを作成する

Transact-SQL を利用し、スライディング ウィンドウ シナリオのパーティションを維持します

テーブル パーティション分割に関するパフォーマンス上の考慮事項

カスタム クリーンアップ スクリプト法の利用

テンポラル履歴保持期間ポリシー法の利用

リテンション ポリシーの構成方法

データベース エンジンによって期限切れの行が削除されるしくみ

関連するコンテンツ

フィードバック

その他のリソース

システムバージョン管理されたテンポラルテーブルの履歴データの保有期間管理

テーブルパーティション分割手法の利用

Transact-SQL を利用し、スライディングウィンドウシナリオのパーティションを維持します

テーブルパーティション分割に関するパフォーマンス上の考慮事項

カスタムクリーンアップスクリプト法の利用

リテンションポリシーの構成方法

データベースエンジンによって期限切れの行が削除されるしくみ