システム バージョン管理されたテンポラル テーブルの履歴データの保有期間管理
適用対象: 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 データの取り込みにテンポラル テーブルを使用する大量のトランザクション処理ワークロードでよく見られます。
詳細については、「保持ポリシーを使用したテンポラル テーブルでの履歴データの管理」を参照してください。