Delen via

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

  • Artikel

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

Met tijdelijke tabellen met systeemversies kan de geschiedenistabel de database groter maken dan gewone tabellen, met name onder de volgende omstandigheden:

  • U behoudt historische gegevens gedurende een lange periode
  • U hebt een patroon voor het bijwerken of verwijderen van zware gegevenswijziging

Een grote en steeds groeiende geschiedenistabel kan een probleem worden vanwege pure opslagkosten en het opleggen van een prestatiebelasting voor tijdelijke query's. Het ontwikkelen van een beleid voor gegevensretentie voor het beheren van gegevens in de geschiedenistabel is een belangrijk aspect van het plannen en beheren van de levenscyclus van elke tijdelijke tabel.

Beheer van gegevensretentie voor geschiedenistabel

Het beheren van het bewaren van tijdelijke tabelgegevens begint met het bepalen van de vereiste bewaarperiode voor elke tijdelijke tabel. Uw bewaarbeleid moet in de meeste gevallen deel uitmaken van de bedrijfslogica van de toepassing met behulp van de tijdelijke tabellen. Toepassingen in scenario's voor gegevenscontrole en tijdreizen hebben bijvoorbeeld vaste vereisten met betrekking tot hoelang historische gegevens beschikbaar moeten zijn voor onlinequery's.

Zodra u de bewaarperiode voor gegevens hebt vastgesteld, moet u een plan ontwikkelen voor het beheren van historische gegevens. Bepaal hoe en waar u uw historische gegevens opslaat en hoe u historische gegevens verwijdert die ouder zijn dan uw bewaarvereisten. De volgende benaderingen voor het beheren van historische gegevens in de tijdelijke geschiedenistabel zijn beschikbaar:

Bij elk van deze benaderingen is de logica voor het migreren of opschonen van geschiedenisgegevens gebaseerd op de kolom die overeenkomt met het einde van de periode in de huidige tabel. 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. De voorwaarde ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) geeft bijvoorbeeld aan dat historische gegevens die ouder zijn dan één maand moeten worden verwijderd of uit de geschiedenistabel moeten worden verplaatst.

In de voorbeelden in dit artikel worden de voorbeeldgegevens gebruikt die zijn gemaakt in het Een tijdelijke tabel met systeemversies maken artikel.

Gebruik de benadering voor tabelpartitionering

Gepartitioneerde tabellen en indexen grote tabellen beter beheersbaar en schaalbaar kunnen maken. Met de partitioneringsbenadering voor tabellen kunt u aangepaste gegevensopruiming of offlinearchivering implementeren op basis van een tijdsvoorwaarde. Tabelpartitionering biedt ook prestatievoordelen bij het uitvoeren van query's op tijdelijke tabellen in een subset van de gegevensgeschiedenis door partitieverwijdering te gebruiken.

Met tabelpartitionering kunt u een schuifvenster implementeren om het oudste gedeelte van de historische gegevens uit de geschiedenistabel te verplaatsen en de grootte van het bewaarde deel constant te houden in termen van leeftijd. In een schuifvenster blijven gegevens in de geschiedenistabel gelijk aan de vereiste bewaarperiode. De bewerking om gegevens uit de geschiedenistabel te verwijderen wordt ondersteund terwijl SYSTEM_VERSIONING is ON, wat betekent dat u een deel van de geschiedenisgegevens kunt opschonen zonder dat er een onderhoudsvenster nodig is of uw reguliere werklast te blokkeren.

Notitie

Als u partitiewisselingen wilt uitvoeren, moet de geclusterde index in de geschiedenistabel worden uitgelijnd met het partitioneringsschema (deze moet ValidTobevatten). De standaardgeschiedenistabel die door het systeem is gemaakt, bevat een geclusterde index met de kolommen ValidTo en ValidFrom, wat optimaal is voor partitionering, het invoegen van nieuwe geschiedenisgegevens en typische tijdelijke query's. Zie Tijdelijke tabellenvoor meer informatie.

Een schuifvenster heeft twee sets taken die u moet uitvoeren:

  • Een partitioneringsconfiguratietaak
  • Terugkerende onderhoudstaken voor partities

Voor de afbeelding gaan we ervan uit dat u historische gegevens zes maanden wilt bewaren en dat u elke maand aan gegevens in een afzonderlijke partitie wilt bewaren. Laten we er ook van uitgaan dat u in september 2023 systeemversiebeheer hebt geactiveerd.

Een partitioneringsconfiguratietaak maakt de eerste partitioneringsconfiguratie voor de geschiedenistabel. In dit voorbeeld maakt u dezelfde nummerpartities als de grootte in maanden van het schuifvenster, plus een extra lege partitie die vooraf is voorbereid (zie verderop in dit artikel). Deze configuratie zorgt ervoor dat het systeem nieuwe gegevens correct kan opslaan wanneer u de terugkerende onderhoudstaak voor partities voor de eerste keer start en garandeert dat u nooit partities splitst met gegevens om dure gegevensverplaatsingen te voorkomen. U moet deze taak uitvoeren met behulp van Transact-SQL met behulp van het voorbeeldscript verderop in dit artikel.

In de volgende afbeelding ziet u de eerste partitioneringsconfiguratie om zes maanden aan gegevens te bewaren.

diagram met de eerste partitioneringsconfiguratie om zes maanden aan gegevens te bewaren.

Notitie

Zie Prestatieoverwegingen voor tabelpartitionering verderop in dit artikel voor prestatieoverwegingen voor het gebruik van RANGE LEFT versus RANGE RIGHT bij het configureren van partitionering.

De eerste en laatste partities zijn open aan respectievelijk de onder- en bovengrenzen om ervoor te zorgen dat elke nieuwe rij een doelpartitie heeft, ongeacht de waarde van de partitiekolom. Naarmate de tijd verstrijkt, komen nieuwe rijen in de geschiedenistabel terecht in hogere partities. Wanneer de zesde partitie wordt ingevuld, bereikt u de beoogde bewaarperiode. Dit is het moment om de terugkerende onderhoudstaak voor partities voor de eerste keer te starten. Het moet worden gepland om periodiek uit te voeren, eenmaal per maand in dit voorbeeld.

In de volgende afbeelding ziet u de terugkerende onderhoudstaken voor partities (zie gedetailleerde stappen verderop in deze sectie).

diagram met de terugkerende onderhoudstaken voor partities.

De gedetailleerde stappen voor de terugkerende onderhoudstaken voor partities zijn:

  1. SWITCH OUT: Maak een faseringstabel en schakel vervolgens een partitie tussen de geschiedenistabel en de faseringstabel met behulp van de instructie ALTER TABLE met het argument SWITCH PARTITION (zie voorbeeld C. Partities schakelen tussen tabellen).

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

    Na de partitieswitch kunt u de gegevens desgewenst archiveren uit de faseringstabel en vervolgens de faseringstabel verwijderen of afkappen om klaar te zijn voor de volgende keer dat u deze terugkerende onderhoudstaak voor partities moet uitvoeren.

  2. MERGE RANGE: voeg de lege partitie 1 samen met partitie 2 met behulp van de ALTER PARTITION FUNCTION met MERGE RANGE (zie voorbeeld B). Door de laagste grens met deze functie te verwijderen, voegt u de lege partitie 1 effectief samen met de voormalige partitie 2 om een nieuwe partitie te vormen 1. De andere partities veranderen ook daadwerkelijk hun rangnummers.

  3. SPLIT RANGE: Maak een nieuwe lege partitie 7 met behulp van de ALTER PARTITION FUNCTION met SPLIT RANGE (zie voorbeeld A). Door een nieuwe bovengrens toe te voegen met deze functie, maakt u effectief een afzonderlijke partitie voor de komende maand.

Gebruik Transact-SQL om partities te maken in de geschiedenistabel

Gebruik het volgende Transact-SQL script om de partitiefunctie en het partitieschema te maken en om de geclusterde index opnieuw te maken zodat deze is uitgelijnd met het partitieschema, partities. 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. In dit voorbeeld schakelt u de partitie voor september 2023 uit met behulp van MERGE RANGEen voegt u vervolgens een nieuwe partitie toe voor maart 2024 met behulp van 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 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

U kunt het vorige script enigszins wijzigen en dit gebruiken in het normale maandelijkse onderhoudsproces:

  1. Maak in stap (1) een nieuwe faseringstabel voor de maand die u wilt verwijderen (in dit voorbeeld ziet u de volgende oktobertabel).
  2. In stap (3) maakt en controleert u de beperking die overeenkomt met de maand met gegevens die u wilt verwijderen: ValidTo <= N'2023-10-31T23:59:59.999' voor een partitie oktober.
  3. In stap (4) partitioneer SWITCH1 in de nieuw aangemaakte stagingtabel.
  4. Wijzig in stap (6) de partitiefunctie door de ondergrens samen te voegen: MERGE RANGE(N'2023-10-31T23:59:59.999' nadat u gegevens voor oktober hebt verplaatst.
  5. In stap (7) splitst u de partitiefunctie en maakt u een nieuwe bovengrens: SPLIT RANGE (N'2024-04-30T23:59:59.999' nadat u gegevens voor oktober hebt verplaatst.

De optimale oplossing is echter om regelmatig een algemeen Transact-SQL script uit te voeren dat elke maand de juiste actie uitvoert zonder dat dit hoeft te worden gewijzigd. U kunt het vorige script generaliseren om te reageren op de opgegeven parameters (de ondergrens die moet worden samengevoegd en de nieuwe grens die wordt gemaakt met de partitiesplitsing). Om te voorkomen dat u elke maand een faseringstabel maakt, kunt u er vooraf een maken en opnieuw gebruiken door de controlebeperking te wijzigen zodat deze overeenkomt met de partitie die u uitschakelt. Zie hoe schuifvenster volledig geautomatiseerd kan wordenvoor meer informatie.

Prestatieoverwegingen met tabelpartitionering

U moet de MERGE- en SPLIT RANGE-bewerkingen uitvoeren om gegevensverplaatsing te voorkomen, omdat gegevensverplaatsing aanzienlijke overhead voor de prestaties kan hebben. Zie Een partitiefunctie wijzigenvoor meer informatie. U doet dit door RANGE LEFT te gebruiken in plaats van RANGE RIGHT wanneer u de partitiefunctie maakt.

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

diagram met de opties RANGE LEFT en RANGE RIGHT.

Wanneer u een partitiefunctie definieert als RANGE LEFT, zijn de opgegeven 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 deze partitie niet leeg is, worden gegevens verplaatst naar de partitie die het gevolg is van MERGE RANGE bewerking.

In een schuifvensterscenario verwijdert u altijd de laagste partitiegrens.

  • RANGE LEFT geval: de laagste partitiegrens behoort tot partitie 1, die leeg is (na het overschakelen van partitie), zodat MERGE RANGE geen gegevensverplaatsing ondervindt.

  • RANGE RIGHT geval: de laagste partitiegrens behoort tot partitie 2, wat niet leeg is, omdat partitie 1 is geleegd door uit te schakelen. In dit geval worden MERGE RANGE gegevensverplaatsing (gegevens van partitie 2 verplaatst naar partitie 1). Om dit te voorkomen, moet RANGE RIGHT in het schuifvensterscenario partitie-1hebben, wat altijd leeg is. Dit betekent dat als u RANGE RIGHTgebruikt, u één extra partitie moet maken en onderhouden in vergelijking met RANGE LEFT geval.

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

Aangepaste benadering voor opschoningsscripts gebruiken

In gevallen waarin tabelpartitionering niet haalbaar is, is een andere methode om de gegevens uit de geschiedenistabel te verwijderen met behulp van een aangepast opschoonscript. Het verwijderen van gegevens uit de geschiedenistabel is alleen mogelijk wanneer SYSTEM_VERSIONING = OFF. Als u inconsistentie van gegevens wilt voorkomen, moet u het opschonen uitvoeren tijdens een onderhoudsvenster (wanneer workloads die gegevens wijzigen niet actief zijn) of binnen een transactie (waardoor andere werkbelastingen effectief worden geblokkeerd). Voor deze bewerking is CONTROL machtiging vereist voor huidige en geschiedenistabellen.

Als u normale toepassingen en gebruikersquery's minimaal wilt blokkeren, verwijdert u gegevens in kleinere segmenten met een vertraging bij het uitvoeren van het opschoningsscript binnen een transactie. Hoewel er geen optimale grootte is voor elk gegevenssegment dat voor alle scenario's moet worden verwijderd, kan het verwijderen van meer dan 10.000 rijen in één transactie een aanzienlijke boete opleggen.

De opschoonlogica is hetzelfde voor elke tijdelijke tabel, zodat deze kan worden geautomatiseerd via een algemene opgeslagen procedure die u plant om periodiek uit te voeren, voor elke tijdelijke tabel waarvoor u de gegevensgeschiedenis wilt beperken.

In het volgende diagram ziet u op welke manier u uw opschoonlogica voor een enkele tabel moet organiseren om het effect op de actieve workloads te verminderen.

diagram waarin wordt weergegeven hoe uw opschoonlogica moet worden ingedeeld voor één tabel om het effect op de actieve workloads te verminderen.

Hier volgen enkele algemene richtlijnen voor het implementeren van het proces. Plan opschoningslogica die elke dag moet worden uitgevoerd en doorloop alle tijdtabellen waarvoor gegevens moeten worden opgeschoond. Gebruik de SQL Server Agent of een ander hulpprogramma om dit proces te plannen:

  • Verwijder historische gegevens in elke tijdelijke tabel, beginnend van de oudste tot de meest recente rijen in verschillende iteraties in kleine segmenten en vermijd het verwijderen van alle rijen in één transactie, zoals wordt weergegeven in het vorige diagram.

  • Implementeer elke iteratie als aanroep van een algemene opgeslagen procedure, waarmee een deel van de gegevens uit de geschiedenistabel wordt verwijderd (zie het volgende codevoorbeeld voor deze procedure).

  • 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 iteraties dat u wilt hebben, bepaalt u dynamische splitsingspunten voor elke procedure-aanroep.

  • Plan om een vertraging tussen iteraties voor één tabel te hebben om het effect op toepassingen die toegang hebben tot de tijdelijke tabel te verminderen.

Een opgeslagen procedure waarmee de gegevens voor één tijdelijke tabel worden verwijderd, kan eruitzien als het volgende codefragment. Bekijk deze code zorgvuldig en pas deze aan voordat u deze in uw omgeving toepast.

Met dit script worden drie instructies gegenereerd die worden uitgevoerd binnen een transactie:

  1. SET SYSTEM_VERSIONING = OFF
  2. DELETE FROM <history_table>
  3. SET SYSTEM_VERSIONING = ON

In SQL Server 2016 (13.x) moeten de eerste twee stappen worden uitgevoerd in afzonderlijke EXEC-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 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;

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;

Tijdelijke bewaarbeleidsbenadering voor geschiedenis gebruiken

van toepassing op: SQL Server 2017 (14.x) en latere versies en Azure SQL Database.

Tijdelijke bewaarperiode voor geschiedenis kan worden geconfigureerd op het niveau van de afzonderlijke tabel, waardoor gebruikers flexibel verouderingsbeleid kunnen maken. Tijdelijke retentie vereist dat u slechts één parameter instelt tijdens het maken van een tabel of schemawijziging.

Nadat u het bewaarbeleid hebt gedefinieerd, wordt er regelmatig gecontroleerd of er historische rijen zijn die in aanmerking komen voor het automatisch opschonen van gegevens. Identificatie van overeenkomende rijen en het verwijderen van de geschiedenistabel vindt transparant plaats in een achtergrondtaak die door het systeem wordt gepland en uitgevoerd. De leeftijdsvoorwaarde voor rijen in de geschiedenistabel wordt gecontroleerd op basis van de kolom die het einde van de SYSTEM_TIME periode aangeeft (in deze voorbeelden de kolom ValidTo). Als de bewaarperiode is ingesteld op zes maanden, voldoen bijvoorbeeld tabelrijen die in aanmerking komen voor opschoning aan de volgende voorwaarde:

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

In het vorige voorbeeld komt de kolom ValidTo overeen met het einde van de SYSTEM_TIME periode.

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 is standaard ingesteld op ON, maar u kunt deze wijzigen met de instructie ALTER DATABASE. Deze waarde wordt na een herstelproces naar een bepaald tijdstip (PITR) automatisch ingesteld op OFF. Voer de volgende instructie uit om het opschonen van tijdelijke geschiedenis voor uw database in te schakelen. U moet <myDB> vervangen door de database die u wilt wijzigen:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

Bewaarbeleid wordt geconfigureerd tijdens het maken van een tabel door waarde op te geven voor de parameter 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
    )
);

U kunt de bewaarperiode opgeven met verschillende tijdseenheden: DAYS, WEEKS, MONTHSen YEARS. Als HISTORY_RETENTION_PERIOD wordt weggelaten, wordt uitgegaan van het behoud van INFINITE. U kunt ook het trefwoord INFINITE expliciet gebruiken.

In sommige scenario's wilt u retentie configureren na het maken 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 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;

Hoe de database-engine verouderde rijen verwijdert

Het opschonen is afhankelijk van de indexindeling van de geschiedenistabel. Alleen geschiedenistabellen met een geclusterde index (B+-structuur of columnstore) kunnen een eindig bewaarbeleid hebben geconfigureerd. Er wordt een achtergrondtaak gemaakt om verouderde gegevens op te ruimen voor alle tijdelijke tabellen met een eindige bewaarperiode. De opschoningslogica voor de rowstore (B+ tree) gegroepeerde index verwijdert verouderde rijen in kleinere segmenten (maximaal 10.000), waardoor de druk op het databaselogboek en het I/O-subsysteem wordt verminderd. Hoewel opschoningslogica gebruikmaakt van de vereiste B+-structuurindex, kan de volgorde van verwijderingen voor de rijen ouder dan de bewaarperiode niet worden gegarandeerd. Vertrouw niet op de opschoonvolgorde in uw toepassingen.

Bij de opschoningstaak voor de geclusterde columnstore worden complete rijgroepen in één keer verwijderd (meestal met elk 1 miljoen rijen), wat efficiënter is, vooral wanneer historische gegevens op hoge snelheid worden gegenereerd.

Schermopname van geclusterde columnstoreretentie.

Gegevenscompressie en retentieopschoning maakt de geclusterde columnstore-index een perfecte keuze voor scenario's waarin uw workload snel veel historische gegevens genereert. Dit patroon is typisch voor intensieve transactionele verwerkingsworkloads die gebruikmaken van tijdelijke tabellen voor het bijhouden en controleren van wijzigingen, trendanalyse of IoT-gegevensopname.

Zie Historische gegevens beheren in tijdelijke tabellen met bewaarbeleidvoor meer informatie.

Verwante inhoud