アイテム保持ポリシーを使用してテンポラル テーブルで履歴データを管理する

適用対象:Azure SQL データベースAzure SQL Managed Instance

テンポラル テーブルは、特に履歴データを長期間保持する場合に、通常のテーブルよりもデータベース サイズが大きくなる可能性があります。 したがって、履歴データのリテンション ポリシーは、あらゆるテンポラル テーブルのライフサイクルの計画と管理において重要な要素となります。 Azure SQL Database と Azure SQL Managed Instance のテンポラル テーブルには、こういったタスクの実行に役立つ、使いやすい保持メカニズムが備わっています。

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

保有期間ポリシーを定義した後、Azure SQL Database と Azure SQL Managed Instance は自動データ クリーンアップの対象になる履歴行があるかどうかの定期的な確認を開始します。 一致する行の識別と履歴テーブルからの削除は、システムによってスケジュール設定されて実行されるバックグラウンド タスクにおいて透過的に行われます。 履歴テーブルの行の年齢条件は、 SYSTEM_TIME 期間の終わりを表す列に基づいてチェックされます。 たとえば、保有期間が 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 ステートメントを使用して変更できます。 また、ポイントインタイム リストア 操作後は、自動的にオフに設定されます。 データベースのテンポラル履歴保有期間のクリーンアップを有効にするには、次のステートメントを実行します。

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
     )
 );

Azure SQL Database と Azure SQL Managed Instance では、 DAYS、 WEEKS、 MONTHS、 YEARSなど、さまざまな期間単位を使用して保有期間を指定できます。 HISTORY_RETENTION_PERIODを省略すると、無限保持が想定されます。 キーワード 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 ツリー) のクラスター化されたインデックスのクリーンアップ ロジックでは、よりサイズの小さなまとまり (最大 10K) で期限切れの行が削除され、データベース ログや IO サブシステムへの負荷が軽減されます。 クリーンアップ ロジックは必要な B ツリー インデックスを利用しますが、保有期間より古い行の削除の順序は確実には保証できません。 そのため、"アプリケーションではクリーンアップ順序に依存しないでください"。

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

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

インデックスに関する考慮事項

行ストア クラスター化インデックスを持つテーブルのクリーンアップ タスクでは、インデックスが、 SYSTEM_TIME 期間の終わりに対応する列で始まる必要があります。 このようなインデックスが存在しない場合は、有限のリテンション期間を構成できません。

Msg 13765, レベル 16, 状態 1

Setting finite retention period failed on system-versioned temporal table 'temporalstagetestdb.dbo.WebsiteUserInfo' because the history table 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' does not contain required clustered index. (履歴テーブル 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' に必要なクラスター化インデックスがないため、システム バージョンのテンポラル テーブル 'temporalstagetestdb.dbo.WebsiteUserInfo' で有限のリテンション期間の設定が失敗しました) Consider creating a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table. (SYSTEM_TIME 期間終了に一致する列から始まるクラスター化列ストアまたは B ツリーを履歴テーブルに作成することを検討してください)

Azure SQL Database と Azure SQL Managed Instance で作成した既定の履歴テーブルには、既に、リテンション ポリシーに準拠しているクラスター化インデックスがあります。 有限のリテンション期間を持つテーブルのインデックスを削除しようとすると、次のエラーが表示されて操作が失敗します。

Msg 13766, レベル 16, 状態 1

Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' because it is being used for automatic cleanup of aged data. (期限切れのデータの自動クリーンアップに使用されているため、クラスター化インデックス 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' を削除できません) Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to drop this index. (このインデックスの削除が必要な場合は、対応するシステム バージョンのテンポラル テーブルで HISTORY_RETENTION_PERIOD を INFINITE に設定することを検討してください)

クラスター化列ストア インデックスのクリーンアップは、履歴行が昇順 (期間の終わり列で並べ替えられた順序) で挿入される場合に最適に機能します。これは、履歴テーブルが SYSTEM_VERSIONIOING メカニズムによって排他的に設定される場合に常に当てられます。 履歴テーブルの行が期間の終わり列で並べ替えられない場合 (既存の履歴データを移行した場合に当てはまる可能性があります)、最適なパフォーマンスを実現するには、適切に並べ替えられた B ツリー行ストア インデックスの上にクラスター化列ストア インデックスを再作成する必要があります。

システムのバージョン管理操作によって自然に行グループの順序が変更される可能性があるため、履歴テーブルのクラスター化列ストア インデックスは有限の保持期間で再構築しないでください。 履歴テーブルのクラスター化列ストア インデックスの再構築が必要な場合は、準拠している B ツリー インデックスの最上部に再作成してください。そうすることで、通常のデータ クリーンアップに必要な行グループの順番が保持されます。 データの順番を気にせずに、クラスター化列インデックスを持つ既存の履歴テーブルを持つテンポラル テーブルを作成する場合も、同じ方法で実行します。

/*Create B-tree ordered by the end of period column*/
CREATE CLUSTERED INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory (ValidTo)
WITH (DROP_EXISTING = ON);
GO
/*Re-create clustered columnstore index*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory ON WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

クラスター化列ストア インデックスを持つ履歴テーブルに有限のリテンション期間を構成するときは、そのテーブルにクラスター化されていない B ツリーを追加で作成することはできません。

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

上記のステートメントを実行すると、次のエラーが表示されて失敗します。

Msg 13772, レベル 16, 状態 1

Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined. (有限のリテンション期間とクラスター化列ストア インデックスが定義されているため、テンポラル履歴テーブル 'WebsiteUserInfoHistory' ではクラスター化されていないインデックスを作成できません)

保持ポリシーが適用されたテーブルをクエリする

クリーンアップ タスクでは任意の時間かつ任意の順序で期限切れの行を削除できるため、予期しない、一貫性のない結果を避けるために、テンポラル テーブルのクエリではすべて、有限のリテンション ポリシーに一致する履歴列が除外されます。

次の図は、シンプルなクエリのクエリ プランを示しています。

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

クエリ プランには、履歴テーブルのクラスター化インデックス スキャン演算子 (強調表示) の期間の終わり列 (ValidTo) に適用される追加のフィルターが含まれています。 この例では、WebsiteUserInfo テーブルに 1 か月のリテンション期間が設定されていると想定しています。

ただし、履歴テーブルに直接クエリを実行すると、指定した保持期間より古い行が表示されることがありますが、クエリの結果が繰り返し可能になる保証はありません。 次の図は、追加のフィルターを適用しない、履歴テーブルのクエリのクエリ実行プランを示します。

保持期間を超えて履歴テーブルを読み取るためにビジネス ロジックに依存しないでください。一貫性のない結果や予期しない結果になる可能性があるためです。 テンポラル テーブルのデータを分析するために、 FOR SYSTEM_TIME 句でテンポラル クエリを使用することをお勧めします。

ポイントインタイム リストアの考慮事項

既存のデータベースを特定の時点に復元して新しいデータベースを作成するときは、データベース レベルのテンポラル リテンション期間が無効になります (is_temporal_history_retention_enabled フラグは OFF に設定されます)。 この機能では、復元時にすべての履歴列を確認できるため、クエリを実行する前に期限切れの行が削除されることはありません。 この機能を使用すると、構成された保有期間を超える履歴データを検査できます。

テンポラル テーブルに 1 つの MONTH 保有期間が指定されている場合とします。 Premium サービス階層でデータベースが作成されている場合、過去 35 日間までの状態のデータベースのコピーを作成できます。 履歴テーブルにクエリを直接実行することにより、実質的に、最大 65 日間の履歴行を分析できます。

テンポラル リテンション期間のクリーンアップを有効にする場合は、ポイントインタイム リストアの後に次の Transact-SQL ステートメントを実行します。

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

関連コンテンツ

• テンポラル テーブルの開始
• テンポラル テーブル