使用保留原則管理時態表中的歷程記錄資料

發行項
03/20/2024

時態表可能會比一般資料表增加資料庫大小，特別是當您保留較長時間的歷程記錄資料時。因此，開發資料保留原則來管理歷程記錄資料是規劃及管理每個時態表生命週期的重要環節。 Azure SQL 資料庫和 Azure SQL 受控執行個體中的時態表隨附易於使用的保留機制，可協助您完成這項工作。

時態歷程記錄保留可在個別的資料表層級上設定，讓使用者建立彈性的過時原則。套用暫時保留很簡單：只需要在資料表建立或結構描述變更期間設定一個參數。

定義保留原則之後，Azure SQL 資料庫和 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 陳述式來變更它。它也會在時間點還原作業之後自動設為 OFF。若要為您的資料庫啟用時態歷程記錄保留清除功能，請執行下列陳述式：

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

重要

即使is_temporal_history_retention_enabled 為 OFF，您也可以設定時態表的保留期，但在此情況下不會觸發過時資料列的自動清理。

藉由指定 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 資料庫和 Azure SQL 受控執行個體可讓您使用不同的時間單位來指定保留期間：DAYS、WEEKS、MONTHS 和 YEARS。如果省略了 HISTORY_RETENTION_PERIOD，則會假設 INFINITE 保留。您也可以明確地使用 INFINITE 關鍵字。

在某些狀況中，您可能想要在資料表建立後設定保留，或變更先前設定的值。在此情況下，請使用 ALTER TABLE 陳述式：

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

重要

將 SYSTEM_VERSIONING 設定為 OFF 不會保留保留期間值。將 SYSTEM_VERSIONING 設定為 ON，而不明確指定 HISTORY_RETENTION_PERIOD 會導致 INFINITE 保留期間。

若要檢閱目前的保留原則狀態，請使用下列查詢，以聯結資料庫層級的暫時保留啟用旗標與個別資料表的保留期間：

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 百萬個資料列)，這是非常有效率的方法，特別是在高速產生歷程記錄資料時。

叢集資料行存放區保留

卓越的資料壓縮和有效率的保留清除，可讓叢集資料行存放區索引成為您的工作負載快速產生大量歷程記錄資料時的完美選擇。此模式一般適用於使用時態表進行變更追蹤和稽核、趨勢分析或 IoT 資料擷取的大量交易處理工作負載。

索引考量

具有資料列存放區叢集索引之資料表的清理工作需要索引，以從對應 SYSTEM_TIME 期間結束的資料行開始。如果這類索引不存在，您無法設定有限的保留期間：

訊息 13765，層級 16，狀態 1

在系統版本設定時態表 'temporalstagetestdb.dbo.WebsiteUserInfo' 上設定有限保留期間失敗，因為歷程記錄資料表 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' 不包含必要的叢集索引。考慮在歷程記錄資料表上建立叢集資料行存放區或 B 型樹狀結構索引，從符合 SYSTEM_TIME 期間結束的資料行開始。

請務必注意，Azure SQL 資料庫和 Azure SQL 受控執行個體所建立的預設歷程記錄資料表已經有叢集索引，這符合保留原則規範。如果您嘗試移除具有有限保留期間之資料表上的該索引，作業會失敗，並出現下列錯誤：

訊息 13766，層級 16，狀態 1

無法卸除叢集索引 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'，因為它正用於自動清理過時資料。如果您需要卸除此索引，請考慮在對應的系統版本設定時態表上將 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])

嘗試執行上述陳述式會失敗，並出現下列錯誤：

訊息 13772，層級 16，狀態 1

無法在時態性記錄資料表 'WebsiteUserInfoHistory' 上建立非叢集索引，因為它已定義有限的保留期間和叢集資料行存放區索引。

使用保留原則查詢資料表

時態表上的所有查詢都會自動篩選出符合有限保留原則的歷程記錄資料列，以避免無法預期且不一致的結果，因為清理工作可以在任何時間點按任意順序刪除過時的資料列。

下圖顯示簡單查詢的查詢計劃：

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

查詢計劃包含在歷程記錄資料表上的叢集索引掃描運算子中套用至期間結束資料行 (ValidTo) 的其他篩選 (已醒目提示)。此範例假設 WebsiteUserInfo 資料表上已設定一個 MONTH 的保留期限。

保留查詢篩選條件

不過，如果您直接查詢歷程記錄資料表，您可能會看到早於指定保留期間的資料列，但沒有可重複查詢結果的任何保證。下圖顯示歷程記錄資料表上查詢的查詢執行計畫，但未套用其他篩選：

沒有保留篩選條件的查詢歷史記錄

請勿依賴商務邏輯來讀取超過保留期間的歷程記錄資料表，因為您可能會得到不一致或非預期的結果。建議您搭配 FOR SYSTEM_TIME 子句使用時態查詢，以分析時態表中的資料。

還原時間點考量

當您藉由將現有的資料庫還原至特定時間點來建立新的資料庫時，它會在資料庫層級停用時態性保留。 (is_temporal_history_retention_enabled 旗標設定為 OFF)。這項功能可讓您在還原時檢查所有歷程記錄資料列，而不必擔心在查詢過時的資料列之前會將其移除。您可以使用它來檢查超過設定保留期間的歷程記錄資料。

假設時態表已指定一個 MONTH 的保留期間。如果您的資料庫是在進階服務層級中建立，您就能夠建立資料庫複本，且資料庫狀態最多回溯到過去 35 天。這實際上可讓您藉由直接查詢歷程記錄資料表，分析最多 65 天的歷程記錄資料列。

如果您想要啟用時態性保留清理，請在時間點還原之後執行下列 Transact-SQL 陳述式：

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

下一步

若要了解如何在應用程式中使用時態表，請參閱開始使用時態表。

如需時態表的詳細資訊，請檢閱時態表。

共用方式為