Verwalten der Aufbewahrung historischer Daten in systemversionierten temporalen Tabellen

Gilt für: SQL Server 2016 (13.x) und spätere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL database in Microsoft Fabric

Eine systemversionierte temporale Tabelle speichert jede vorherige Version jeder Zeile in ihrer Verlaufstabelle. Die Verlaufstabelle könnte Ihre Datenbankgröße unter den folgenden Bedingungen stärker erhöhen als reguläre Tabellen:

• Man speichert historische Daten über einen langen Zeitraum.
• Sie haben ein Datenänderungsmuster mit vielen Updates oder Löschungen.

Eine große, ständig wachsende Historikentabelle könnte zum Problem werden, sowohl wegen der Speicherkosten als auch wegen der Performance-Belastung, die sie auf zeitliche Abfragen auferlegt. Die Entwicklung einer Datenaufbewahrungsrichtlinie für die Verlaufstabelle ist ein wichtiger Teil der Planung und Verwaltung des Lebenszyklus jeder temporalen Tabelle.

Planen Sie eine Datenaufbewahrungspolitik

Um die Speicherung von zeitlichen Tabellendaten zu verwalten, bestimmen Sie zunächst die erforderliche Aufbewahrungszeit für jede Zeittabelle. Ihre Aufbewahrungspolitik sollte in den meisten Fällen Teil der Geschäftslogik der Anwendung sein, die die temporalen Tabellen verwendet. Beispielsweise haben Anwendungen im Bereich Datenaudit und Zeitreiseszenarien klare Vorgaben, wie lange historische Daten für Online-Abfragen verfügbar sein müssen.

Nachdem Sie Ihre Datenaufbewahrungszeit festgelegt haben, entwickeln Sie einen Plan zur Verwaltung historischer Daten. Entscheiden Sie, wie und wo Sie Ihre Verlaufsdaten speichern und wie Verlaufsdaten gelöscht werden, die älter als Ihre Aufbewahrungsanforderungen sind.

Jeder Ansatz in diesem Artikel wirkt auf die Spalte, die dem Ende des Zeitraums in der aktuellen Tabelle entspricht, nämlich die ValidTo Spalte in den folgenden Beispielen. Der Periodenendwert für jeder Zeile legt den Zeitpunkt fest, zu dem die Zeilenversion geschlossen wird, also dann, wenn sie in der History-Tabelle landet. Zum Beispiel stimmt die Erkrankung ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) mit historischen Daten überein, die älter als 30 Tage sind.

Wählen Sie eine der folgenden Ansätze, um auf diese Zeilen zu reagieren:

Die Beispiele für Partitionierung und benutzerdefinierte Bereinigung in diesem Artikel verwenden die Beispiele aus dem Artikel "Erstellen einer systemversionierten temporalen Tabelle ".

Verwenden Sie eine Richtlinie zur Aufbewahrung der temporalen Geschichte

Gilt für: SQL Server 2017 (14.x) und spätere Versionen, Azure SQL-Datenbank, Azure SQL Managed Instance und SQL Database in Microsoft Fabric.

Sie können die Aufbewahrung des temporalen Verlaufs auf Ebene einzelner Tabellen konfigurieren, wodurch Sie flexible Aufbewahrungsrichtlinien erstellen können. Um die zeitliche Aufbewahrung zu aktivieren, legen Sie HISTORY_RETENTION_PERIOD während der Tabellenerstellung oder bei einer Schemaänderung fest.

Nachdem Sie die Aufbewahrungsrichtlinie definiert haben, führt die Datenbank-Engine eine geplante Hintergrundaufgabe aus, die historische Zeilen findet und transparent entfernt, deren Endwert älter ist als die Aufbewahrungszeit.

So konfigurieren Sie die Aufbewahrungsrichtlinie

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

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

Das Datenbank-Flag is_temporal_history_retention_enabled steht standardmäßig auf ON, aber du kannst es mit der ALTER DATABASE Anweisung ändern. Das Datenbankmodul setzt es außerdem nach dem Vorgang OFF einer Point-in-Time-Wiederherstellung (PITR) automatisch fest, wie in Überlegungen zur Point-in-Time-Wiederherstellung beschrieben. Um die Bereinigung der Beibehaltung temporaler Verlaufsdaten in Ihrer Datenbank zu aktivieren, führen Sie folgende Anweisung aus. Ersetzen Sie <myDB> durch die Datenbank, die Sie ändern möchten:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Sie können die Aufbewahrungsrichtlinie während der Tabellenerstellung konfigurieren, indem Sie einen Wert für den Parameter HISTORY_RETENTION_PERIOD angeben:

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

Mit dieser Richtlinie werden Zeilen in dbo.WebsiteUserInfoHistory für die Bereinigung infrage kommen, wenn sie die folgende Bedingung erfüllen:

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

Sie können die Aufbewahrungsdauer in DAYS, WEEKS, , MONTHSoder YEARSangeben. Wenn du HISTORY_RETENTION_PERIOD weglässt, wird die Aufbewahrungsdauer standardmäßig auf INFINITE festgelegt. Sie können das Schlüsselwort INFINITE auch explizit verwenden.

In manchen Szenarien möchtest du die Aufbewahrung 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));

Verwenden Sie das folgende Beispiel, um den aktuellen Status der Aufbewahrungsrichtlinie zu überprüfen. Diese Abfrage verknüpft das Aktivierungsflag für die temporale Aufbewahrung auf Datenbankebene mit den Aufbewahrungszeiträumen einzelner Tabellen:

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 AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

Wie die Datenbank-Engine veraltete Zeilen löscht

Der Bereinigungsprozess hängt vom Indexlayout der Verlaufstabelle ab. Man kann eine endliche Aufbewahrungsrichtlinie nur auf Historientabellen mit einem Clustered Rowstore (B-Baum) oder einem clusterierten Columnstore-Index konfigurieren. Eine Hintergrundaufgabe führt die Bereinigung alter Daten für alle zeitlichen Tabellen mit begrenzter Aufbewahrungszeit durch.

B-Baum-Index für zeilenorientierte Speicherung

Der Rowstore-Cluster-Index muss mit der Spalte beginnen, die dem Ende der Periode SYSTEM_TIME entspricht. Wenn ein solcher Index nicht existiert, können Sie keine endliche Aufbewahrungszeit konfigurieren:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table 'dbo.WebsiteUserInfoHistory'
does not contain required clustered index. Consider creating a clustered
columnstore or B-tree index starting with the column that matches end of
SYSTEM_TIME period, on the history table.

Die Standard-Historiktabelle hat bereits einen konformen Cluster-Index. Wenn Sie versuchen, diesen Index in einer Historik-Tabelle mit endlicher Aufbewahrungszeit zu platzieren, schlägt die Operation mit folgendem Fehler fehl:

Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory'
because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned
temporal table if you need to drop this index.

Die Reinigungslogik für den Rowstore-Cluster-Index löscht gealterte Zeilen in kleineren Abschnitten (bis zu 10.000), wodurch der Druck auf das Datenbankprotokoll und das I/O-Subsystem minimiert wird. Obwohl die Reinigungslogik den erforderlichen B-Baum-Index verwendet, kann sie die Löschreihenfolge für Zeilen, die älter als die Aufbewahrungszeit sind, nicht garantieren. Verlassen Sie sich in Ihren Anwendungen nicht auf die Reihenfolge der Bereinigung.

Geclusterter Columnstore-Index

Die Aufräumaufgabe für den clustered columnstore entfernt ganze Zeilengruppen auf einmal. Jede Zeilengruppe enthält typischerweise eine Million Zeilen. Diese Methode ist effizienter, besonders wenn Ihre Arbeitslast historische Daten in hohem Tempo generiert.

Datenkompression und Aufbewahrungsbereinigung machen den Clustered Columnstore-Index zu einer guten Wahl für Szenarien, in denen Ihr Workload schnell große Mengen historischer Daten erzeugt. Dieses Muster ist typisch für intensive transaktionale Verarbeitungs-Workloads, die temporale Tabellen für Änderungsverfolgung und Audit, Trendanalyse oder Internet-of-Things-(IoT)-Datenerfassung verwenden.

Die Aufräumung im Clustered Columnstore-Index funktioniert optimal, wenn historische Zeilen in aufsteigender Reihenfolge (geordnet nach der Spalte zum Ende des Zeitraums) eintreffen. Diese Bedingung tritt immer auf, wenn nur der Mechanismus SYSTEM_VERSIONING die Verlaufstabelle befüllt. Wenn die Zeilen in der Verlaufstabelle nicht nach der Spalte für das Periodenende sortiert sind (was bei der Migration vorhandener historischer Daten vorkommen kann), erstellen Sie den geclusterten Columnstore-Index auf Grundlage eines korrekt sortierten B-Tree-Rowstore-Index neu, um eine optimale Leistung zu erzielen.

Vermeiden Sie es, den geclusterten Spaltenspeicherindex in einer History-Tabelle mit endlicher Aufbewahrungszeit neu aufzubauen, da ein Wiederaufbau die Reihenfolge der Zeilengruppen verändern könnte, die die Systemversionierungsoperation natürlich auferlegt. Wenn Sie den geclusterten Spaltenspeicherindex in der History-Tabelle neu erstellen müssen, erstellen Sie ihn auf einem konformen B-Baum-Index, um die für die regelmäßige Datenbereinigung notwendige Reihenreihenfolge der Zeilengruppen zu erhalten. Verwenden Sie denselben Ansatz, wenn Sie eine zeitliche Tabelle mit einer vorhandenen Verlaufstabelle erstellen, die einen Clustered Columnstore-Index ohne garantierte Reihenfolge der Daten enthält:

/* 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 the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

Wenn Sie eine endliche Aufbewahrungszeit für eine History-Tabelle mit einem clusterierten Columnstore-Index konfigurieren, können Sie in dieser Tabelle keine zusätzlichen nicht-clusterten B-Baum-Indizes erstellen:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

Die vorherige Aussage scheitert mit folgendem Fehler:

Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Abfragen von Tabellen mit Aufbewahrungsrichtlinie

Alle Abfragen in der temporalen Tabelle filtern automatisch historische Zeilen heraus, die mit der Finite-Retention Policy übereinstimmen, um unvorhersehbare und inkonsistente Ergebnisse zu vermeiden. Die Aufräum-Aufgabe löscht veraltete Zeilen jederzeit und in beliebiger Reihenfolge.

Der folgende Screenshot zeigt den Abfrageplan für eine einfache Abfrage. Dieses Beispiel setzt auf der WebsiteUserInfo Tabelle eine Aufbewahrungsdauer vonMONTH nur einer an:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Der Abfrageplan enthält in der Verlaufstabelle einen zusätzlichen Filter für die Spalte für das Ende des Zeitraums (ValidTo) im Operator „Clustered Index Scan“ (in der folgenden Abbildung hervorgehoben).

Wenn Sie die Versionstabelle direkt abfragen, könnten Sie Zeilen sehen, die älter sind als die angegebene Aufbewahrungsfrist, aber ohne Garantie wiederholbarer Abfrageergebnisse. Der folgende Screenshot zeigt den Abfrageplan für eine Abfrage der Verlaufstabelle ohne zusätzliche Filter:

Verlassen Sie sich nicht auf Geschäftslogik, die die Versionstabelle über die Aufbewahrungszeit hinaus liest, denn Sie könnten inkonsistente oder unerwartete Ergebnisse erhalten. Verwenden Sie temporale Abfragen mit der FOR SYSTEM_TIME Klausel, um Daten in temporalen Tabellen zu analysieren.

Überlegungen zur Point-in-Time-Wiederherstellung

Wenn Sie eine Datenbank auf einen bestimmten Zeitpunkt wiederherstellen, wird die temporale Aufbewahrung der neuen Datenbank auf Datenbankebene deaktiviert (is_temporal_history_retention_enabled auf OFF). Dieses Verhalten erlaubt es dir, historische Zeilen zu inspizieren, die älter als die Aufbewahrungszeit sind, bevor die Aufräumaufgabe sie entfernt. Um die automatische Bereinigung auf der wiederhergestellten Datenbank fortzusetzen, setzen Sie TEMPORAL_HISTORY_RETENTION zurück auf ON.

Verwenden Sie Tabellenpartitionierung

Partitionen und Indizes können große Tabellen besser verwaltbar und skalierbar machen. Durch die Verwendung des Tabellenpartitionierungsansatzes können Sie eine individuelle Datenbereinigung oder eine Offline-Archivierung basierend auf einer Zeitbedingung implementieren. Durch die Tabellenpartitionierung erhalten Sie auch Leistungsvorteile bei der Abfrage temporaler Tabellen für eine Teilmenge des Datenverlaufs, und zwar durch Partitionseliminierung.

Verwenden Sie die Tabellenpartitionierung, um ein gleitendes Fenster zu implementieren, mit dem der älteste Teil der historischen Daten aus der Verlaufstabelle entfernt wird und die Größe des beibehaltenen Teils nach Alter konstant gehalten wird. Ein gleitendes Fenster speichert Daten in der Verlaufstabelle, die der erforderlichen Aufbewahrungszeit entsprechen. Die Verlaufstabelle unterstützt das Auslagern von Daten, während SYSTEM_VERSIONINGON ist. Das bedeutet, dass Sie einen Teil der Verlaufsdaten bereinigen können, ohne ein Wartungsfenster erforderlich zu machen oder Ihre regulären Workloads zu blockieren.

Ein Schiebefenster erfordert zwei Aufgabensätze:

• Eine Partitionierungskonfigurationsaufgabe
• Wiederkehrende Aufgaben für die Partitionswartung

Für dieses Beispiel nehmen wir an, dass Sie historische Daten für sechs Monate speichern möchten und jeden Monat in einer separaten Partition speichern möchten. Nehmen wir außerdem an, dass Sie die Systemversionierung im September 2023 aktiviert haben.

Eine Partitionierungskonfigurationsaufgabe erstellt die anfängliche Partitionierungskonfiguration für die Verlaufstabelle. Für dieses Beispiel erstellst du die gleiche Anzahl an Partitionen wie die Größe des gleitenden Fensters, in Monaten, plus eine zusätzliche leere Partition. Diese Konfiguration stellt sicher, dass das System neue Daten korrekt speichern kann, wenn Sie die wiederkehrende Partitionspflege zum ersten Mal starten. Außerdem wird sichergestellt, dass man Partitionen, die Daten enthalten, niemals aufteilt, was teure Datenbewegungen vermeidet. Definiere die Partitionsfunktion mit RANGE LEFT anstelle von RANGE RIGHT. Weitere Informationen finden Sie später in diesem Artikel unter Leistungsüberlegungen mit Tabellenaufteilung .

Das folgende Bild zeigt die anfängliche Partitionierungskonfiguration, um sechs Monate Daten zu speichern.

Die erste und letzte Partition sind jeweils an der unteren und oberen Grenze geöffnet , um sicherzustellen, dass jede neue Zeile unabhängig vom Wert in der Spaltungsspalte eine Zielpartition hat. Mit der Zeit landen neue Zeilen in der Geschichtstabelle in höheren Partitionen. Wenn die sechste Partition gefüllt ist, ist die gewünschte Aufbewahrungsfrist erreicht. An diesem Punkt starten Sie zum ersten Mal die wiederkehrende Partitionspflegeaufgabe. Plane es so ein, dass es in diesem Beispiel regelmäßig läuft, einmal pro Monat.

Das folgende Bild zeigt die wiederkehrenden Aufgaben der Partitionwartung.

Jeder Durchlauf der wiederkehrenden Wartungsaufgabe führt folgende Schritte durch:

1. SWITCH OUT: Erstellen Sie eine Staging-Tabelle und wechseln Sie dann eine Partition zwischen der History-Tabelle und der Staging Table, indem Sie die ALTER TABLE Anweisung mit dem SWITCH PARTITION Argument verwenden.

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

   Nach dem Partitionwechsel können Sie die Daten aus der Staging-Tabelle optional archivieren und die Staging-Tabelle anschließend entweder löschen oder leeren, um sie für den nächsten Wartungszyklus vorzubereiten.

2. MERGE RANGE: Füge die leere Partition 1 mit der Partition 2 zusammen, indem du die ALTER PARTITION FUNCTION Anweisung mit MERGE RANGEverwendest. Wenn man diese Funktion verwendet, um die unterste Grenze zu entfernen, verschmilzt man effektiv die leere Partition 1 mit der vorherigen Partition 2 , um eine neue Partition 1zu bilden. Auch bei den anderen Partitionen ändern sich dadurch faktisch die Ordnungsnummern.

3. SPLIT RANGE: Erstelle eine neue leere Partition7, indem du die Anweisung mit SPLIT RANGEverwendestALTER PARTITION FUNCTION. Wenn du diese Funktion verwendest, um eine neue obere Grenze hinzuzufügen, erstellst du effektiv eine separate Partition für den kommenden Monat.

Verwenden Sie Transact-SQL, um Partitionen für die Verlaufstabelle zu erstellen

Verwenden Sie das folgende Transact-SQL Skript, um die Partitionsfunktion, das Partitionsschema zu erstellen und den geclusterten Index so neu zu erstellen, dass er partitionsausgerichtet mit dem Schema 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 tauschen Sie die Partition für September 2023 mithilfe von MERGE RANGE aus und fügen dann mithilfe von SPLIT RANGE eine neue Partition für März 2024 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 to switch 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 to switch 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;

Die optimale Lösung ist jedoch, regelmäßig jeden Monat ein generisches Transact-SQL-Skript ohne Änderungen auszuführen. Du kannst das vorherige Skript verallgemeinern, um auf deine bereitgestellten Parameter zu reagieren (die untere Grenze, die zusammengeführt werden muss, und die neue Grenze, die durch die Partitionsaufteilung entsteht). Um zu vermeiden, dass jeden Monat eine Stagingtabelle erstellt werden muss, erstellen Sie im Voraus eine Tabelle und verwenden Sie sie wieder, indem Sie die CHECK-Einschränkung so ändern, dass sie der Partition entspricht, die Sie ausschalten. Weitere Informationen finden Sie unter So automatisieren Sie das Sliding-Window-Szenario vollständig.

Überlegungen zur Leistung bei der Tabellenpartitionierung

Führen Sie die Operationen MERGE RANGE und SPLIT RANGE so aus, dass Datenbewegungen vermieden werden, da Datenbewegungen erhebliche Leistungseinbußen verursachen können. Weitere Informationen finden Sie unter Ändern einer Partitionsfunktion.

Wenn Sie die Partitionsfunktion alsRANGE LEFTerstellen, 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, MERGE RANGE werden die Daten auf die resultierende Partition verschoben.

Im folgenden Diagramm werden die Optionen RANGE LEFT und RANGE RIGHT beschrieben:

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

• RANGE LEFT Fall: Die niedrigste Partitionsgrenze gehört zur Partition 1, die leer ist (nach dem Partitionswechsel) und MERGE RANGE daher keine Datenbewegung verursacht.

• RANGE RIGHT Fall: Die niedrigste Partitionsgrenze gehört zur Partition 2, die nicht leer ist, weil das Umschalten nur die Partition 1leert. In diesem Fall verursacht MERGE RANGE eine Datenverschiebung, indem Daten von Partition 2 zu Partition 1 verschoben werden. Um diese Datenbewegung zu vermeiden, muss im Schiebefenster-Szenario eine Partition 1vorhanden sein, RANGE RIGHT die immer leer ist. Diese Anforderung bedeutet, dass Sie, wenn Sie RANGE RIGHTverwenden, eine zusätzliche Partition im Vergleich zum Gehäuse RANGE LEFT erstellen und pflegen sollten.

Fazit: Partitionsverwaltung ist einfacher, wenn man sie in einer Sliding-Partition nutzt RANGE LEFT , und sie vermeidet Datenbewegungen. Das Definieren der Partitionsgrenzen mit RANGE RIGHT ist jedoch etwas einfacher, da Sie sich nicht um Probleme mit der Prüfung von Datum und Uhrzeit kümmern müssen.

Verwenden Sie ein benutzerdefiniertes Reinigungsskript

Wenn für Ihre Tabelle keine Aufbewahrungsrichtlinie verfügbar ist und die Tabellenpartitionierung nicht möglich ist, können Sie die Daten aus der History-Tabelle löschen, indem Sie ein benutzerdefiniertes Reinigungsskript verwenden. Dieser Prozess ist nur möglich, wenn SYSTEM_VERSIONING = OFF. Um Dateninkonsistenz zu vermeiden, führen Sie die Bereinigung entweder während eines Wartungsfensters durch (wenn Workloads, die Daten verändern, nicht aktiv sind) oder innerhalb einer Transaktion (wodurch andere Workloads effektiv blockiert werden). Dieser Vorgang erfordert die CONTROL-Berechtigung für aktuelle Tabellen und Verlaufstabellen.

Die Aufräum-Logik ist für jede temporale Tabelle gleich, sodass man sie durch ein generisches gespeichertes Verfahren automatisieren kann. Verwenden Sie den SQL Server-Agent oder ein anderes Tool, um diese Prozedur täglich zu planen und über jede temporale Tabelle zu iterieren, für die Sie den Datenverlauf begrenzen möchten.

Das folgende Diagramm zeigt, wie Sie Ihre Reinigungs-Logik für eine einzelne Tabelle organisieren können, um die Auswirkungen auf die laufenden Arbeitslasten zu verringern.

Hier sind einige übergeordnete Richtlinien zur Umsetzung des Prozesses:

• Lösche historische Daten in jeder zeitlichen Tabelle in mehreren Iterationen kleiner Abschnitte. Beginnen Sie mit den ältesten Reihen und gehen Sie zu den jüngsten. Vermeiden Sie es, alle Zeilen in einer einzigen Transaktion zu löschen, wie das vorherige Diagramm zeigt. Obwohl keine einzelne Chunk-Größe für alle Szenarien funktioniert, kann das Löschen von mehr als 10.000 Zeilen in einer einzigen Transaktion eine erhebliche Strafe verursachen.

• Implementiere jede Iteration als Aufruf einer generischen gespeicherten Prozedur, die einen Teil der Daten aus der Versionstabelle entfernt.

• Berechnen Sie jedes Mal, wenn Sie den Prozess aufrufen, wie viele Zeilen Sie für eine einzelne temporale Tabelle löschen müssen. Legen Sie basierend auf dem Ergebnis und der Anzahl der gewünschten Iterationen dynamische Split-Punkte für jeden Prozeduraufruf fest.

• Plane eine Verzögerung zwischen den Iterationen für eine einzelne Tabelle, um die Auswirkungen auf Anwendungen zu verringern, die auf die temporale Tabelle zugreifen.

Das folgende gespeicherte Verfahren löscht die Daten für eine einzelne temporale Tabelle. Es entdeckt die Historiktabelle und die Spalte zum Periodenende aus den Katalogansichten und führt dann drei Anweisungen innerhalb einer Transaktion aus: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, und SET SYSTEM_VERSIONING = ON. Überprüfen Sie diesen Code sorgfältig und passen Sie ihn an, bevor Sie ihn in Ihrer Umgebung anwenden.

In SQL Server 2016 (13.x) müssen die ersten beiden Schritte in separaten EXECUTE-Anweisungen ausgeführt werden, sonst generiert SQL Server einen Fehler in der Art des folgenden Beispiels:

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 AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS 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 AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS 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;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Verwandte Inhalte

• Temporale Tabellen
• Einführung in systemversionierte temporale Tabellen
• Systemkonsistenzprüfungen von temporalen Tabellen
• Partitionierung mit temporalen Tabellen
• Überlegungen und Einschränkungen zu temporalen Tabellen
• Sicherheit bei temporalen Tabellen
• Systemversionsverwaltete temporale Tabellen mit speicheroptimierten Tabellen
• Metadatenansichten und Funktionen für temporale Tabellen