Beheer het bewaren van historische gegevens in systeem-geversioneerde temporele tabellen.

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Een temporele tabel met door het systeem beheerde versies bewaart elke vorige versie van elke rij in de historietabel. De geschiedenistabel kan je databasegrootte meer vergroten dan gewone tabellen onder de volgende voorwaarden:

• Je bewaart historische gegevens voor een lange periode.
• Je hebt een patroon met veel gegevenswijzigingen door updates of verwijderingen.

Een grote, steeds groeiende geschiedenistabel kan een probleem worden, zowel vanwege opslagkosten als de prestatiebelasting die het oplegt op temporele zoekopdrachten. Het ontwikkelen van een gegevensbehoudsbeleid voor de geschiedenistabel is een belangrijk onderdeel van het plannen en beheren van de levenscyclus van elke temporele tabel.

Plan een beleid voor gegevensbewaring

Om het behoud van temporele tabel gegevens te beheren, bepaal eerst de vereiste retentieperiode voor elke temporele tabel. Je retentiebeleid zou in de meeste gevallen deel moeten uitmaken van de bedrijfslogica van de applicatie die de temporele tabellen gebruikt. Toepassingen in data-audit en tijdreisscenario's hebben bijvoorbeeld strikte eisen over hoe lang historische data beschikbaar moet zijn voor online query's.

Nadat je je gegevensbewaartijd hebt vastgesteld, ontwikkel je een plan voor het beheren van historische data. Bepaal hoe en waar u uw historische gegevens opslaat en hoe u historische gegevens verwijdert die ouder zijn dan uw bewaarvereisten.

Elke benadering in dit artikel werkt op de kolom die overeenkomt met het einde van de periode in de huidige tabel, namelijk de ValidTo kolom in de volgende voorbeelden. Het waarde aan het einde van de periode voor elke rij bepaalt het moment waarop de rijversie wordt gesloten, oftewel wanneer deze in de geschiedenistabel terechtkomt. Zo komt de aandoening ValidTo < DATEADD (DAY, -30, SYSUTCDATETIME()) overeen met historische gegevens die ouder zijn dan 30 dagen.

Kies een van de volgende manieren om iets met deze rijen te doen:

De voorbeelden van partitionering en aangepaste opschoning in dit artikel maken gebruik van de voorbeelden uit het artikel Een systeemtijdsversiebeheerde tabel maken.

Gebruik een beleid voor het bewaren van de tijdelijke geschiedenis

Van toepassing op: SQL Server 2017 (14.x) en latere versies, Azure SQL Database, Azure SQL Managed Instance en SQL database in Microsoft Fabric.

Je kunt temporele geschiedenisbehoud instellen op het niveau van de individuele tabel, waarmee je flexibele verouderingsbeleid kunt maken. Om temporele retentie mogelijk te maken, wordt ingesteld HISTORY_RETENTION_PERIOD tijdens het aanmaken van een tabel of een schemawijziging.

Nadat je het retentiebeleid hebt gedefinieerd, voert de Database Engine een geplande achtergrondtaak uit die historische rijen vindt en transparant verwijdert waarvan de eindwaarde ouder is dan de retentieperiode.

Bewaarbeleid configureren

Voordat u bewaarbeleid voor een tijdelijke tabel configureert, controleert u of tijdelijke historische retentie is ingeschakeld op databaseniveau:

SELECT is_temporal_history_retention_enabled,
       name
FROM sys.databases;

De databasevlag is_temporal_history_retention_enabled staat standaard op ON, maar je kunt dit aanpassen door de ALTER DATABASE instructie te gebruiken. De Database Engine stelt dit ook automatisch in op OFF na een point-in-time restore (PITR)-bewerking, zoals beschreven in Overwegingen bij point-in-time restore. Voer de volgende instructie uit om het opschonen van tijdelijke geschiedenis voor uw database in te schakelen. Vervang <myDB> door de database die je wilt wijzigen:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Je kunt het retentiebeleid configureren tijdens het aanmaken van de tabel door een waarde voor de HISTORY_RETENTION_PERIOD parameter op te geven:

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

Met dat beleid komen rijen in dbo.WebsiteUserInfoHistory aanmerking voor schoonmaak wanneer ze aan de volgende voorwaarde voldoen:

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

Je kunt de retentieperiode specificeren in DAYS, WEEKS, , MONTHSof YEARS. Als je HISTORY_RETENTION_PERIOD weglaat, wordt de retentieperiode standaard ingesteld op INFINITE. U kunt ook het trefwoord INFINITE expliciet gebruiken.

In sommige scenario's wil je retentie configureren na het aanmaken van de tabel of de eerder geconfigureerde waarde wijzigen. Gebruik in dat geval de ALTER TABLE-opdracht:

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

Als u de huidige status van het bewaarbeleid wilt bekijken, gebruikt u het volgende voorbeeld. Met deze query worden de tijdelijke bewaarcapaciteit vlag op het databaseniveau gekoppeld aan de bewaarperioden voor afzonderlijke 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;

Hoe de database-engine verouderde rijen verwijdert

Het opschonen is afhankelijk van de indexindeling van de geschiedenistabel. Je kunt een eindig retentiebeleid alleen instellen op geschiedenistabellen met een geclusterde rowstore (B-boom) of geclusterde columnstore-index. Een achtergrondtaak voert verouderde data-opschoning uit voor alle temporele tabellen met een eindige retentieperiode.

B-tree rowstore-index

De geclusterde index van de rowstore moet beginnen met de kolom die overeenkomt met het einde van de SYSTEM_TIME periode. Als zo'n index niet bestaat, kun je geen eindige retentieperiode configureren:

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.

De standaardgeschiedenistabel heeft al een compliant geclusterde index. Als je probeert die index te plaatsen in een geschiedenistabel met een eindige retentieperiode, mislukt de bewerking met de volgende fout:

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.

Opruimlogica voor de geclusterde index van de rowstore verwijdert verouderde rijen in kleinere stukken (tot 10.000), waardoor de druk op het databaselog en het I/O-subsysteem wordt geminimaliseerd. Hoewel opruimlogica de vereiste B-boomindex gebruikt, kan het de verwijderingsvolgorde niet garanderen voor rijen die ouder zijn dan de retentieperiode. Vertrouw niet op de opschoonvolgorde in uw toepassingen.

Geclusterde columnstore-index

De opruimtaak voor de geclusterde columnstore verwijdert hele rijgroepen tegelijk. Elke rijgroep bevat doorgaans één miljoen rijen. Deze methode is efficiënter, vooral wanneer je werklast historische data in hoog tempo genereert.

Datacompressie en retentie-opschoning maken de geclusterde columnstore-index een goede keuze voor scenario's waarin je werklast snel een grote hoeveelheid historische data genereert. Dat patroon is typisch voor intensieve transactionele verwerkingsworkloads die temporele tabellen gebruiken voor wijzigingstracking en audit, trendanalyse of het opnemen van Internet of Things (IoT)-data.

Het opschonen van de geclusterde columnstore-index werkt optimaal wanneer historische rijen in oplopende volgorde binnenkomen (gesorteerd op de kolom voor het einde van de periode). Deze voorwaarde geldt altijd wanneer alleen het mechanisme SYSTEM_VERSIONING de geschiedenistabel vult. Als de rijen in de geschiedenistabel niet zijn gesorteerd op de kolom voor het einde van de periode (wat kan gebeuren bij een migratie van bestaande historische gegevens), maak de geclusterde columnstore-index dan opnieuw op basis van een correct gesorteerde B-tree-rowstore-index voor optimale prestaties.

Vermijd het herbouwen van de geclusterde kolomopslagindex op een geschiedenistabel met een eindige retentieperiode, omdat herbouwen de volgorde van rijgroepen die de systeemversiebewerking van nature oplegt, kan veranderen. Als je de geclusterde kolomopslagindex op de geschiedenistabel opnieuw moet opbouwen, maak deze dan opnieuw aan bovenop een compliant B-boomindex om de rijgroepvolgorde te behouden die nodig is voor regelmatige gegevensopschoning. Gebruik dezelfde aanpak als je een temporele tabel maakt met een bestaande geschiedenistabel die een geclusterde columnstore-index heeft zonder gegarandeerde datavolgorde:

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

Wanneer je een eindige retentieperiode configureert voor een geschiedenistabel met een geclusterde columnstore-index, kun je geen extra niet-geclusterde B-boomindexen op die tabel maken:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

De vorige uitspraak faalt met de volgende fout:

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.

Tabellen opvragen met retentiebeleid

Alle queries in de temporele tabel filteren automatisch historische rijen die overeenkomen met het eindige retentiebeleid, om onvoorspelbare en inconsistente resultaten te voorkomen. De opruimtaak verwijdert verouderde rijen op elk moment en in willekeurige volgorde.

De volgende screenshot toont het queryplan voor een basisquery. Dit voorbeeld gaat uit van een retentieperiode van één inMONTH de WebsiteUserInfo tabel:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Het queryplan bevat een extra filter in de einde-van-periode-kolom (ValidTo) in de Clustered Index Scan-operator (gemarkeerd in de volgende afbeelding) in de geschiedenistabel.

Als je direct de geschiedenistabel raadpleegt, kun je rijen zien die ouder zijn dan de opgegeven retentieperiode, maar zonder garantie op herhaalbare zoekresultaten. De volgende schermafbeelding toont het queryplan voor een query op de historietabel zonder extra filters:

Vertrouw niet op bedrijfslogica die de geschiedenistabel voorbij de retentieperiode leest, want je kunt inconsistente of onverwachte resultaten krijgen. Gebruik temporele queries met de FOR SYSTEM_TIME clausule om data in temporele tabellen te analyseren.

Overwegingen voor herstel op een specifiek tijdstip

Wanneer je een database herstelt naar een specifiek tijdstip, wordt de nieuwe database op databaseniveau temporetentie uitgeschakeld (is_temporal_history_retention_enabled ingesteld op OFF). Dit gedrag stelt je in staat historische rijen te inspecteren die ouder zijn dan de retentieperiode voordat de opruimtaak ze verwijdert. Om de automatische opruiming van de herstelde database te hervatten, zet TEMPORAL_HISTORY_RETENTION terug naar ON.

Gebruik tabelpartitionering

Gepartitioneerde tabellen en indexen grote tabellen beter beheersbaar en schaalbaar kunnen maken. Door gebruik te maken van de tabelpartitioneringsmethode kun je aangepaste data-opschoning of offline archivering implementeren op basis van een tijdsconditie. Tabelpartitionering biedt ook prestatievoordelen bij het uitvoeren van query's op tijdelijke tabellen in een subset van de gegevensgeschiedenis door partitieverwijdering te gebruiken.

Gebruik tabelpartitionering om een schuifvenster te implementeren waarmee het oudste deel van de historische data uit de geschiedenistabel wordt gehaald, en houd de grootte van het behouden deel constant naar leeftijd. Een schuifvenster bewaart gegevens in de geschiedenistabel gelijk aan de vereiste bewaarperiode. De geschiedenistabel ondersteunt het uitwisselen van data terwijl SYSTEM_VERSIONING is ON, wat betekent dat je een deel van de geschiedenisgegevens kunt opschonen zonder een onderhoudsvenster te introduceren of je reguliere workloads te blokkeren.

Een schuifvenster vereist twee sets taken:

• Een partitioneringsconfiguratietaak
• Terugkerende onderhoudstaken voor partities

Voor deze illustratie kun je aannemen dat je historische gegevens zes maanden wilt bewaren en dat je elke maand data in een aparte partitie wilt bewaren. Ga er ook van uit dat je in september 2023 systeemversie-indeling hebt geactiveerd.

Een partitioneringsconfiguratietaak maakt de eerste partitioneringsconfiguratie voor de geschiedenistabel. Voor dit voorbeeld maak je hetzelfde aantal partities als de grootte van het schuifvenster, in maanden, plus één extra lege partitie. Deze configuratie zorgt ervoor dat het systeem nieuwe gegevens correct kan opslaan wanneer je de terugkerende partitieonderhoudstaak start. Het garandeert ook dat je nooit partities splitst die data bevatten, wat dure dataverplaatsingen voorkomt. Definieer de partitiefunctie met RANGE LEFT in plaats van RANGE RIGHT. Voor meer informatie, zie Prestatie-overwegingen met tabelpartitionering later in dit artikel.

De volgende afbeelding toont de initiële partitioneringsconfiguratie om zes maanden aan data te bewaren.

De eerste en laatste partities zijn open aan respectievelijk de onder- en bovengrens, om ervoor te zorgen dat elke nieuwe rij een bestemmingspartitie heeft, ongeacht de waarde in de partitiekolom. In de loop van de tijd landen nieuwe rijen in de geschiedenistabel in hogere partities. Wanneer de zesde partitie vol is, bereik je de beoogde retentieperiode. Begin op dit moment voor het eerst met de terugkerende partitieonderhoudstaak. Plan het in om periodiek te draaien, in dit voorbeeld één keer per maand.

De volgende afbeelding illustreert de terugkerende taken van partitieonderhoud.

Elke uitvoering van de terugkerende onderhoudstaak voert de volgende stappen uit:

1. SWITCH OUT: Maak een stagingtabel aan en wissel vervolgens een partitie tussen de geschiedenistabel en de stagingtabel door de ALTER TABLE instructie met het SWITCH PARTITION argument te gebruiken.

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

   Na de partitiewisseling kun je optioneel de data uit de stagingtabel archiveren en vervolgens de stagingtabel laten vallen of afkorten om je voor te bereiden op de volgende onderhoudscyclus.

2. MERGE RANGE: Voeg de lege partitie 1 samen met de partitie 2 door de ALTER PARTITION FUNCTION instructie met MERGE RANGEte gebruiken. Wanneer je deze functie gebruikt om de laagste grens te verwijderen, voeg je effectief de lege partitie 1 samen met de vorige partitie 2 om een nieuwe partitie 1te vormen. De andere partities veranderen ook daadwerkelijk hun rangnummers.

3. SPLIT RANGE: Maak een nieuwe lege partitie 7 aan door de instructie SPLIT RANGE met ALTER PARTITION FUNCTION te gebruiken. Wanneer je deze functie gebruikt om een nieuwe bovengrens toe te voegen, maak je effectief een aparte partitie voor de komende maand.

Gebruik Transact-SQL om partities te maken in de geschiedenistabel

Gebruik het volgende Transact-SQL script om de partitiefunctie te maken, het partitieschema, en maak de geclusterde index opnieuw zodat deze partitie-uitgelijnd is met het schema. In dit voorbeeld maakt u een schuifvenster van zes maanden met maandelijkse partities vanaf 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;

Gebruik Transact-SQL om partities te onderhouden in het scenario met schuifvensters

Gebruik het volgende Transact-SQL script om partities te onderhouden in het scenario met schuifvensters. Voor dit voorbeeld wissel je de partitie voor september 2023 door te gebruiken MERGE RANGE, en voeg je vervolgens een nieuwe partitie toe voor maart 2024 met .SPLIT RANGE

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;

De optimale oplossing is echter om elke maand een generiek Transact-SQL script uit te voeren zonder aanpassingen. Je kunt het vorige script generaliseren om te handelen op basis van je gegeven parameters (de onderste grens die moet samensmelten, en de nieuwe grens die wordt gecreëerd door de partitiesplitsing). Om te voorkomen dat je elke maand een stagingtabel maakt, maak je er van tevoren een aan en hergebruik je deze door de checkbeperking aan te passen bij de partitie die je vervangt. Voor meer informatie, zie hoe je het sliding window-scenario volledig kunt automatiseren.

Prestatieoverwegingen met tabelpartitionering

Voer de MERGE RANGE en-operaties SPLIT RANGE uit op een manier die databeweging vermijdt, omdat databeweging aanzienlijke prestatie-overhead kan veroorzaken. Zie Een partitiefunctie wijzigenvoor meer informatie.

Wanneer je de partitiefunctieRANGE LEFTals aanmaakt, zijn de gespecificeerde waarden de bovengrenzen van de partities. Wanneer u RANGE RIGHTgebruikt, zijn de opgegeven waarden de lagere grenzen van de partities. Wanneer u de MERGE RANGE-bewerking gebruikt om een grens te verwijderen uit de definitie van de partitiefunctie, verwijdert de onderliggende implementatie ook de partitie die de grens bevat. Als die partitie niet leeg is, MERGE RANGE verplaatst de data naar de resulterende partitie.

In het volgende diagram worden de opties RANGE LEFT en RANGE RIGHT beschreven:

In een schuifvensterscenario verwijdert u altijd de laagste partitiegrens.

• RANGE LEFT Geval: De laagste partitiegrens behoort tot partitie 1, die leeg is (na partitiewisseling), waardoor MERGE RANGE er geen databeweging ontstaat.

• RANGE RIGHT geval: De laagste partitiegrens behoort tot partitie 2, die niet leeg is omdat door het uitschakelen alleen partitie 1 leeg raakt. In dit geval veroorzaakt MERGE RANGE gegevensverplaatsing, waarbij data van partitie 1 naar partitie 2 wordt verplaatst. Om deze gegevensbeweging te vermijden, moet in het sliding window-scenario partitie 1zijn, RANGE RIGHT die altijd leeg is. Deze eis betekent dat als je gebruikt RANGE RIGHT, je één extra partitie moet aanmaken en onderhouden in vergelijking met de RANGE LEFT case.

Conclusie: partitiebeheer is eenvoudiger als je het in een schuifpartitie gebruikt RANGE LEFT , en het voorkomt dataverplaatsing. Het definiëren van partitiegrenzen met RANGE RIGHT is echter iets eenvoudiger, omdat u niet te maken hebt met problemen met datum- en tijdcontrole.

Gebruik een aangepast cleanup-script

Wanneer er geen retentiebeleid beschikbaar is voor je tabel en tabelpartitionering niet haalbaar is, kun je de data uit de geschiedenistabel verwijderen met een aangepast cleanup script. Dit proces is alleen mogelijk wanneer SYSTEM_VERSIONING = OFF. Om data-inconsistentie te voorkomen, voer je opruiming uit tijdens een onderhoudsvenster (wanneer workloads die data wijzigen niet actief zijn), of binnen een transactie (waardoor andere workloads effectief worden geblokkeerd). Voor deze bewerking is CONTROL machtiging vereist voor huidige en geschiedenistabellen.

De opruimlogica is hetzelfde voor elke temporele tabel, dus je kunt het automatiseren via een generieke opgeslagen procedure. Gebruik de SQL Server Agent of een andere tool om die procedure elke dag te plannen, waarbij je elke temporele tabel herhaalt waarvoor je de gegevensgeschiedenis wilt beperken.

Het volgende diagram illustreert hoe je je opruimlogica voor één enkele tabel kunt organiseren om het effect op de lopende workloads te verminderen.

Hier zijn enkele richtlijnen op hoog niveau voor het implementeren van het proces:

• Verwijder historische gegevens in elke temporele tabel in meerdere iteraties van kleine stukken. Begin bij de oudste rijen en ga naar de meest recente. Vermijd het verwijderen van alle rijen in één enkele transactie, zoals het vorige diagram laat zien. Hoewel geen enkele chunkgrootte voor alle scenario's werkt, kan het verwijderen van meer dan 10.000 rijen in één transactie een aanzienlijke straf opleveren.

• Implementeer elke iteratie als een aanroep van een generieke opgeslagen procedure, die een deel van de data uit de geschiedenistabel verwijdert.

• Bereken het aantal rijen dat u voor een afzonderlijke tijdelijke tabel moet verwijderen telkens wanneer u het proces aanroept. Op basis van het resultaat en het aantal gewenste iteraties bepaal je dynamische splitpunten voor elke procedure-aanroep.

• Plan een vertraging tussen iteraties voor één enkele tabel om het effect op applicaties die toegang krijgen tot de temporele tabel te verminderen.

De volgende opgeslagen procedure verwijdert de gegevens voor een enkele temporele tabel. Het ontdekt de geschiedenistabel en de kolom eindeperiode vanuit de catalogusweergaven, en voert vervolgens drie statements uit binnen een transactie: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, en SET SYSTEM_VERSIONING = ON. Bekijk deze code zorgvuldig en pas hem aan voordat je hem toepast in je omgeving.

In SQL Server 2016 (13.x) moeten de eerste twee stappen worden uitgevoerd in afzonderlijke EXECUTE-instructies, of SQL Server genereert een fout die vergelijkbaar is met het volgende voorbeeld:

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;

Verwante onderwerpen

• Tijdelijke tabellen
• Begin met systeem-geversioneerde temporele tabellen
• consistentiecontroles van het tijdelijke tabelsysteem
• partitioneren met tijdelijke tabellen
• Tijdelijke tabeloverwegingen en -beperkingen
• tijdelijke tabelbeveiliging
• Systeem-geversioneerde temporele tabellen met geheugen-geoptimaliseerde tabellen
• weergaven en functies van metagegevens van tijdelijke tabellen