Verwalten der Beibehaltung von Verlaufsdaten in temporalen Tabellen mit Systemversionsverwaltung

  • Artikel

Gilt für: SQL Server 2016 (13.x) und höher Azure SQL-DatenbankAzure SQL Managed Instance

Durch temporale Tabellen mit Systemversionsverwaltung kann die Verlaufstabelle die Datenbank stärker vergrößern als reguläre Tabellen, insbesondere in den folgenden Situationen:

  • Sie behalten Verlaufsdaten für eine langen Zeitraum bei.
  • Ihr Muster für Datenänderungen erfordert umfangreiche Aktualisierungen oder Löschungen.

Eine große und ständig wachsende Verlaufstabelle kann zu einem Problem werden, sowohl aufgrund der reinen Speicherkosten und durch Leistungsbeeinträchtigungen aufgrund von temporalen Abfragen. Daher ist die Entwicklung einer Aufbewahrungsrichtlinie für die Verwaltung von Daten in der Verlaufstabelle ein wichtiger Aspekt der Planung und Verwaltung des Lebenszyklus jeder temporalen Tabelle.

Verwaltung der Datenbeibehaltung für die Verlaufstabelle

Das Verwalten der Datenbeibehaltung für temporale Tabellen beginnt damit, die Beibehaltungsdauer für jede temporale Tabelle zu bestimmen. Ihrer Beibehaltungsrichtlinie sollte in den meisten Fällen als Teil der Geschäftslogik der Anwendung betrachtet werden, die die temporalen Tabellen verwendet. Für Anwendungen in Datenüberwachungs- und Zeitreiseszenarios gelten beispielsweise feste Anforderungen dafür, wie lange Verlaufsdaten für Onlineabfragen verfügbar sein müssen.

Nachdem Sie ihren Datenaufbewahrungszeitraum ermittelt haben, besteht der nächste Schritt darin, einen Plan für die Verwaltung von Verlaufsdaten zu entwickeln. Sie müssen entscheiden, wie und wo Sie Ihre Verlaufsdaten speichern und wie Verlaufsdaten gelöscht werden, die älter als Ihre Aufbewahrungsanforderungen sind. Die folgenden Ansätze für das Verwalten von Verlaufsdaten in der temporalen Verlaufstabelle stehen Ihnen zur Verfügung:

Bei jedem dieser Ansätze basiert die Logik für die Migration oder Bereinigung von Verlaufsdaten auf der Spalte, die dem Ende der Dauer in der aktuellen Tabelle entspricht. Der Wert für das Ende der Dauer für jede Zeile bestimmt den Moment, an dem die Zeilenversion geschlossen wird, an dem sie also in die Verlaufstabelle aufgenommen wird. Beispielsweise gibt die Bedingung ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) an, dass Verlaufsdaten, die älter als einen Monat sind, aus der Verlaufstabelle entfernt oder verschoben werden müssen.

Hinweis

Die Beispiele in diesem Artikel verwenden diese Erstellen Sie eine system-versionierte temporale Tabelle.

Verwenden des Tabellenpartitionierungsansatzes

Partitionen und Indizes können große Tabellen besser verwaltbar und skalierbar machen. Wenn Sie den Ansatz mit Tabellenpartitionierung verwenden, können Sie Verlaufstabellenpartitionen nutzen, um eine angepasste Datenbereinigung oder Offlinearchivierung basierend auf einer Zeitbedingung zu implementieren. Durch die Tabellenpartitionierung erhalten Sie auch Leistungsvorteile bei der Abfrage von temporalen Tabellen auf einer Teilmenge des Datenverlaufs mit Hilfe der Partitionsentfernung.

Mit der Tabellenpartitionierung können Sie ein gleitendes Fenster implementieren, um den ältesten Teil der Verlaufsdaten aus der Verlaufstabelle zu verschieben und die Größe des beibehaltenen Teils im Hinblick auf das Alter konstant zu halten. So verwalten Sie die Daten in der Verlaufstabelle entsprechend der erforderlichen Beibehaltungsdauer. Der Vorgang des Austauschens von Daten aus der Verlaufstabelle wird unterstützt, wenn SYSTEM_VERSIONING auf ON festgelegt ist. Dies bedeutet, dass ein Teil der Verlaufsdaten bereinigt werden kann, ohne ein Wartungsfenster einzurichten oder normale Arbeitsauslastungen zu blockieren.

Hinweis

Für einen Partitionswechsel muss der gruppierte Index für die Verlaufstabelle am Partitionierungsschema ausgerichtet werden (ValidTo muss enthalten sein). Die vom System erstellte Standardverlaufstabelle enthält einen gruppierten Index, der die Spalten ValidTo und ValidFrom aufweist. Dies ist optimal für die Partitionierung, das Einfügen neuer Daten und normale temporale Abfragen. Weitere Informationen finden Sie unter Temporale Tabellen.

Für e in gleitendes Fenster müssen Sie zwei Sätze von Aufgaben ausführen:

  • Eine Partitionierungskonfigurationsaufgabe
  • Aufgaben für die wiederholte Partitionswartung

Zur Veranschaulichung gehen wir davon aus, dass Sie Verlaufsdaten für sechs Monate beibehalten möchten und dass die Daten der einzelnen Monate jeweils in einer separaten Partition gespeichert werden sollen. Außerdem nehmen wir an, dass Sie im September 2023 die Systemversionsverwaltung aktiviert haben.

Mit einer Partitionierungskonfigurationsaufgabe wird die erste Partitionierungskonfiguration für die Verlaufstabelle erstellt. In diesem Beispiel würden Sie eine Anzahl von Partitionen erstellen, die der Größe des gleitenden Fensters entspricht (in Monaten). Zusätzlich wird eine leere Partition vorbereitet (wird später in diesem Artikel erklärt). Mit dieser Konfiguration wird sichergestellt, dass das System neue Daten ordnungsgemäß speichern kann, wenn die Aufgabe für die wiederholte Partitionswartung das erste Mal ausgeführt wird. Zudem wird gewährleistet, dass Sie Partitionen mit Daten nicht unterteilen, um teure Datenverschiebungen zu vermeiden. Sie sollten diese Aufgabe mit Transact-SQL durchführen, indem Sie das Beispielskript weiter unten in diesem Artikel verwenden.

Die folgende Abbildung zeigt die erste Partitionierungskonfiguration, mit der Daten von sechs Monaten beibehalten werden.

Diagram showing initial partitioning configuration to keep six months of data.

Hinweis

Unter „Überlegungen zur Leistung bei der Tabellenpartitionierung“ weiter unten in diesem Artikel finden Sie Informationen zu Leistungseinbußen bei der Verwendung von RANGE LEFT oder RANGE RIGHT beim Konfigurieren der Partitionierung.

Beachten Sie, dass die untere bzw. die obere Grenze bei der ersten bzw. der letzten Partition offen ist, um sicherzustellen, dass für jede neue Zeile eine Zielpartition vorhanden ist, unabhängig vom Wert in der Partitionierungsspalte. Im Laufe der Zeit werden neue Zeilen in der Verlaufstabelle in höhere Partitionen aufgenommen. Wenn die 6. Partition gefüllt wird, haben Sie die vorgesehene Beibehaltungsdauer erreicht. Dies ist der Zeitpunkt, an dem die Aufgabe für die wiederholte Partitionswartung zum ersten Mal gestartet wird (sie muss in regelmäßigen Abständen ausgeführt werden, in diesem Beispiel einmal pro Monat).

Die folgende Abbildung veranschaulicht die Aufgabe für die wiederholte Partitionswartung (siehe detaillierte Schritte weiter unten in diesem Abschnitt).

Diagram showing the recurring partition maintenance tasks.

Die genauen Schritte für die Aufgabe für die wiederholte Partitionswartung:

  1. SWITCH OUT: Erstellen Sie eine Stagingtabelle, und wechseln Sie dann eine Partition zwischen der Verlaufstabelle und der Stagingtabelle. Verwenden Sie dazu die Anweisung ALTER TABLE (Transact-SQL) mit dem Argument SWITCH PARTITION (siehe Beispiel „C. Wechseln von Partitionen zwischen Tabellen“).

    ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];

    Nach dem Partitionswechsel können Sie optional die Daten aus der Stagingtabelle archivieren und dann die Stagingtabelle löschen oder kürzen, damit Sie vorbereitet sind, wenn Sie diese Aufgabe für die wiederholte Partitionswartung das nächste Mal ausführen müssen.

  2. MERGE RANGE: Führen Sie die leere Partition 1 mit der Partition 2 zusammen. Verwenden Sie dazu ALTER PARTITION FUNCTION (Transact-SQL) mit MERGE RANGE (siehe Beispiel B). Durch Entfernen der untersten Grenze mit dieser Funktion führen Sie effektiv die leere Partition 1 mit der ehemaligen Partition 2 zusammen, sodass eine neue Partition 1 entsteht. Bei den anderen Partitionen werden ebenfalls die Ordinalzahlen geändert.

  3. SPLIT RANGE: Erstellen Sie eine neue leere Partition 7. Verwenden Sie dazu ALTER PARTITION FUNCTION (Transact-SQL) mit SPLIT RANGE (siehe Beispiel A). Durch das Hinzufügen einer neuen oberen Grenze mit dieser Funktion erstellen Sie effektiv eine separate Partition für den kommenden Monat.

Verwenden von Transact-SQL zum Erstellen von Partitionen in der Verlaufstabelle

Verwenden Sie das folgende Transact-SQL-Skript, um die Partitionsfunktion und das Partitionsschema zu erstellen, und erstellen Sie den gruppierten Indexes so neu, dass die Partitionierung am Partitionsschema und den Partitionen ausgerichtet ist. In diesem Beispiel erstellen Sie ein sechsmonatiges gleitendes Fenster mit monatlichen Unterteilungen, beginnend im September 2023.

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;

Verwenden von Transact-SQL zum Verwalten von Partitionen im Szenario mit gleitendem Fenster

Verwenden Sie das folgende Transact-SQL-Skript, um Partitionen im Szenario mit gleitendem Fenster zu verwalten. In diesem Beispiel lagern Sie die Partition für September 2023 mit MERGE RANGE aus und fügen dann eine neue Partition für März 2024 mit SPLIT RANGE hinzu.

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

Sie können das vorherige Skript etwas ändern und im regelmäßigen monatlichen Wartungsprozess verwenden:

  1. Erstellen Sie in Schritt (1) eine neue Stagingtabelle für den Monat, den Sie entfernen möchten (Oktober wäre der nächste in diesem Beispiel).
  2. Erstellen Sie in Schritt (3) eine CHECK-Einschränkung, die dem Monat entspricht, dessen Daten Sie entfernen möchten: ValidTo <= N'2023-10-31T23:59:59.999' für die Oktober-Partition.
  3. Ändern Sie in Schritt (4) SWITCH Partition 1 in die neu erstellte Stagingtabelle.
  4. Ändern Sie in Schritt (6) die Partitionsfunktion durch Zusammenführen der unteren Grenze: MERGE RANGE(N'2023-10-31T23:59:59.999' nach dem Auslagern der Daten für Oktober.
  5. Teilen Sie in Schritt (7) die Partitionsfunktion durch Erstellen der neuen oberen Grenze: SPLIT RANGE (N'2024-04-30T23:59:59.999' nach dem Auslagern der Daten für Oktober.

Die optimale Lösung wäre jedoch, regelmäßig ein generisches Transact-SQL-Skript auszuführen, das die entsprechende Aktion monatlich ohne Skriptänderungen durchführen kann. Es ist möglich, das vorherige Skript zu verallgemeinern, um die bereitgestellten Parameter zu bearbeiten (die untere Grenze, die zusammengeführt werden muss, und die neue Grenze, die durch Teilen der Partition erstellt wird). Um zu vermeiden, dass jeden Monat Stagingtabellen erstellt werden, können Sie eine Stagingtabelle im Voraus erstellen und wiederverwenden, indem Sie die CHECK-Einschränkung entsprechend der Partition ändern, die ausgelagert wird. Auf den folgenden Seiten finden Sie Ideen für die vollständige Automatisierung eines gleitenden Fensters mithilfe eines Transact-SQL-Skripts.

Überlegungen zur Leistung bei der Tabellenpartitionierung

Es ist äußerst wichtig, MERGE und SPLIT RANGE-Vorgänge durchzuführen, um das Verschieben von Daten zu vermeiden, da dies einen erheblichen Verarbeitungsaufwand verursachen kann. Weitere Informationen finden Sie unter Ändern einer Partitionsfunktion. Dies geschieht, indem Sie bei der Erstellung der PartitionsfunktionRANGE LEFT statt RANGE RIGHT verwenden.

Lassen Sie uns zuerst visuell die Bedeutung der Optionen RANGE LEFT und RANGE RIGHT erläutern:

Diagram showing the RANGE LEFT and RANGE RIGHT options.

Beim Definieren einer Partitionsfunktion als RANGE LEFT sind die angegebenen Werte die oberen Grenzen der Partitionen. Wenn Sie RANGE RIGHT verwenden, sind die angegebenen Werte die unteren Grenzen der Partitionen. Wenn Sie den MERGE RANGE-Vorgang verwenden, um eine Grenze aus der Definition der Partitionsfunktion zu entfernen, entfernt die zugrunde liegende Implementierung auch die Partition, die die Grenze enthält. Wenn diese Partition nicht leer ist, werden Daten in die Partition verschoben, die das Ergebnis des MERGE RANGE-Vorgangs ist.

Im Szenario mit einem gleitendem Fenster entfernen Sie immer die unterste Partitionsgrenze.

  • RANGE LEFT-Fall: Die unterste Partitionsgrenze gehört zu Partition 1, die leer ist (nach dem Auslagern der Partition), sodass MERGE RANGE keine Datenverschiebungen verursacht.
  • RANGE RIGHT-Fall: Die niedrigste Partitionsgrenze gehört zur Partition 2, die nicht leer ist, da die Partition 1 durch das Switching geleert wurde. In diesem Fall kommt es bei MERGE RANGE zu einer Datenbewegung (Daten aus der Partition 2 werden in die Partition 1 verschoben). Um dies zu vermeiden, muss RANGE RIGHT im Szenario mit gleitendem Fenster eine Partition 1 aufweisen, die immer leer ist. Das bedeutet, dass Sie bei der Verwendung von RANGE RIGHT eine zusätzliche Partition im Vergleich zum Fall RANGE LEFT erstellen und pflegen sollten.

Fazit: Die Verwendung von RANGE LEFT in einer gleitenden Partition ist für die Partitionsverwaltung einfacher und vermeidet Datenverschiebungen. Das Definieren der Partitionsgrenzen mit RANGE RIGHT ist jedoch etwas einfacher, da Sie sich nicht um Probleme mit dem datetime-Zeittakt kümmern müssen.

Verwenden des Ansatzes mit einem benutzerdefiniertem Bereinigungsskript

In Fällen, in denen eine Tabellenpartitionierung nicht praktikabel ist, besteht ein anderer Ansatz darin, die Daten aus der Verlaufstabelle mithilfe eines benutzerdefinierten Bereinigungsskripts zu löschen. Das Löschen von Daten aus einer Verlaufstabelle ist nur möglich, wenn SYSTEM_VERSIONING = OFF gilt. Um Dateninkonsistenz zu vermeiden, führen Sie die Bereinigung während des Wartungsfensters (wenn Arbeitsauslastungen, bei denen Daten geändert werden, nicht aktiv sind) oder innerhalb einer Transaktion (sodass andere Arbeitsauslastungen blockiert sind) durch. Dieser Vorgang erfordert die CONTROL-Berechtigung für aktuelle Tabellen und Verlaufstabellen.

Um reguläre Anwendungen und Benutzerabfragen in möglichst geringem Umfang zu blockieren, löschen Sie Daten in kleineren Blöcken mit einer Verzögerung, wenn Sie das Bereinigungsskript innerhalb einer Transaktion ausführen. Es gibt zwar keine optimale Größe für jeden zu löschenden Datenblock für alle Szenarios, aber das Löschen von mehr als 10.000 Zeilen in einer einzigen Transaktion kann erhebliche Auswirkungen haben.

Die Bereinigungslogik ist für jede temporale Tabelle identisch, sodass eine Automatisierung über eine generische gespeicherte Prozedur möglich ist. Sie können die regelmäßige Ausführung dieser gespeicherten Prozedur für jede Tabelle planen, für die Sie den Datenverlauf einschränken möchten.

Das folgende Diagramm veranschaulicht, wie Ihre Bereinigungslogik für eine einzelne Tabelle strukturiert sein sollte, um die Auswirkungen auf die aktiven Arbeitsauslastungen zu verringern.

Diagram showing how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

Im Folgenden finden Sie einige allgemeine Richtlinien für die Implementierung des Prozesses. Planen Sie eine tägliche Ausführung der Bereinigungslogik, und wenden Sie sie auf alle temporalen Tabellen an, für die eine Datenbereinigung erforderlich ist. Verwenden Sie den SQL Server-Agent oder ein anderes Tool, um diesen Prozess zu planen:

  • Löschen Sie Verlaufsdaten in allen temporalen Tabellen. Beginnen Sie dabei mit den ältesten Zeilen, und arbeiten Sie sich in mehrere Iterationen in kleinen Blöcken zu den letzten Zeilen vor. Vermeiden Sie wie im vorherigen Diagramm veranschaulicht das Löschen aller Zeilen in einer einzigen Transaktion.
  • Implementieren Sie jede Iteration als Aufruf der generischen gespeicherten Prozedur, die einen Teil der Daten aus der Verlaufstabelle entfernt (siehe das folgende Codebeispiel für dieses Verfahren).
  • Berechnen Sie jedes Mal, wenn Sie den Prozess aufrufen, wie viele Zeilen Sie für eine einzelne temporale Tabelle löschen müssen. Basierend darauf und auf der Anzahl der Iterationen müssen Sie dynamische Teilungspunkte für jeden Prozeduraufruf bestimmen.
  • Planen Sie eine Verzögerung zwischen Iterationen für eine einzelne Tabelle ein, um die Auswirkungen auf die Anwendungen zu reduzieren, die auf die temporale Tabelle zugreifen.

Eine gespeicherte Prozedur, die die Daten für eine einzelne temporale Tabelle löscht, kann wie der folgende Codeausschnitt aussehen (prüfen Sie diesen Code sorgfältig, und passen Sie ihn an, bevor Sie ihn in Ihrer Umgebung anwenden):

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;

Verwenden eines Ansatzes für die Richtlinie zur Beibehaltung temporaler Verlaufsdaten

Gilt für: SQL Server 2017 (14.x) und höhere Versionen sowie Azure SQL-Datenbank.

Die Beibehaltung temporaler Verlaufsdaten kann auf den einzelnen Tabellenebenen konfiguriert werden, sodass Benutzer flexible Ablaufrichtlinien erstellen können. Das Anwenden der temporalen Beibehaltung ist einfach: Sie erfordert nur einen Parameter, der bei der Tabellenerstellung oder einer Schemaänderung festgelegt werden muss.

Nachdem Sie Aufbewahrungsrichtlinie definiert haben, überprüft die Datenbank-Engine in regelmäßigen Abständen, ob Zeilen mit Verlaufsdaten existieren, die für die automatische Datenbereinigung infrage kommen. Die Ermittlung übereinstimmender Zeilen und ihre Entfernung aus der Verlaufstabelle erfolgen transparent mithilfe eines vom System geplanten und ausgeführten Hintergrundtasks. Die Ablaufbedingungen für die Zeilen der Verlaufstabelle werden basierend auf der Spalte überprüft, die das Ende des SYSTEM_TIME-Zeitraums repräsentiert. Wenn die Beibehaltungsdauer beispielsweise auf sechs Monate festgelegt ist, erfüllen die für die Bereinigung infrage kommenden Zeilen folgende Bedingung:

ValidTo < DATEADD (MONTH, -6, SYSUTCDATETIME())

Im vorangegangenen Beispiel entspricht die Spalte ValidTo dem Ende der Periode SYSTEM_TIME.

Konfigurieren der Aufbewahrungsrichtlinie

Bevor Sie die Aufbewahrungsrichtlinie für eine temporale Tabelle konfigurieren, überprüfen Sie zunächst, ob die Beibehaltung temporaler Verlaufsdaten auf Datenbankebene aktiviert ist:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Das Datenbankflag is_temporal_history_retention_enabled ist standardmäßig auf ON gesetzt, kann aber mit der Anweisung ALTER DATABASE geändert werden. Dieser Wert wird nach einer Point-in-Time-Wiederherstellung (PITR)-Operation automatisch auf OFF gesetzt. Um die Bereinigung der Beibehaltung temporaler Verlaufsdaten in Ihrer Datenbank zu aktivieren, führen Sie folgende Anweisung aus:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

Die Beibehaltungsrichtlinien werden während der Tabellenerstellung konfiguriert, indem ein Wert für den Parameter HISTORY_RETENTION_PERIOD angegeben wird:

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

Sie können den Beibehaltungszeitraum mithilfe verschiedener Zeiteinheiten angeben: DAYS, WEEKS, MONTHS und YEARS. Wenn HISTORY_RETENTION_PERIOD weggelassen wird, wird die Beibehaltung von INFINITE angenommen. Sie können das Schlüsselwort INFINITE auch explizit verwenden.

In manchen Szenarios sollten Sie die Beibehaltung erst nach der Tabellenerstellung konfigurieren oder den zuvor konfigurierten Wert ändern. Verwenden Sie in diesem Fall die ALTER TABLE-Anweisung:

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

Zum Prüfen des aktuellen Status der Beibehaltungsrichtlinie verwenden Sie die folgende Abfrage, die das Flag für die Aktivierung der temporalen Beibehaltung auf Datenbankebene mit den Beibehaltungszeiträumen für die einzelnen Tabellen verknüpft:

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;

Wie löscht SQL-Datenbank veraltete Zeilen?

Der Bereinigungsprozess hängt vom Indexlayout der Verlaufstabelle ab. Nur für Verlaufstabellen mit einem gruppierten Index (B+-Struktur oder Columnstore)) kann eine endliche Aufbewahrungsrichtlinie konfiguriert werden. Es wird ein Hintergrundtask erstellt, um die Bereinigung veralteter Daten für alle temporalen Tabellen mit begrenztem Beibehaltungszeitraum auszuführen. Die Bereinigungslogik für den gruppierten Index für Rowstore (B+-Struktur) löscht die veralteten Zeilen in kleineren Blöcken (max. 10K), was die Belastung des Datenbankprotokolls und des E/A-Subsystems minimiert. Die Bereinigungslogik verwendet zwar den benötigten B+-Strukturindex, jedoch kann die Löschreihenfolge der Zeilen, die den Beibehaltungszeitraum übersteigen, nicht sicher garantiert werden. Verwenden Sie in Ihren Anwendungen daher keine Abhängigkeit von der Bereinigungsreihenfolge.

Der Bereinigungstask für den gruppierten Columnstore entfernt ganze Zeilengruppen gleichzeitig (die üblicherweise jeweils 1 Mio. Zeilen enthalten). Dies ist sehr effizient, vor allem dann, wenn mit einer hohen Geschwindigkeit Verlaufsdaten generiert werden.

Screenshot of clustered columnstore retention.

Die hervorragende Datenkompression und die effiziente Beibehaltungsbereinigung machen den gruppierten Columnstore-Index zur perfekten Lösung für Szenarios, bei denen Ihre Workload in kürzester Zeit eine große Menge an Verlaufsdaten generiert. Dieses Muster ist typisch für intensive Transaktionsverarbeitungs-Workloads, die temporale Tabellen verwenden, um die Änderungsnachverfolgung und -überwachung, Trendanalysen oder die Erfassung von IoT-Daten durchzuführen.

Weitere Informationen finden Sie unter Verwalten von Verlaufsdaten in temporalen Tabellen mit einer Aufbewahrungsrichtlinie.

Zugehöriger Inhalt