Dela via

Hantera kvarhållning av historiska data i systemversionsbaserade temporala tabeller

  • Artikel

gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

Med systemversionsbaserade temporala tabeller kan historiktabellen öka databasstorleken mer än vanliga tabeller, särskilt under följande förhållanden:

  • Du behåller historiska data under en längre tid
  • Du har ett mönster för omfattande dataändringar genom uppdateringar eller borttagningar

En stor och ständigt växande historiktabell kan bli ett problem både på grund av rena lagringskostnader och införandet av en prestandaskatt för temporala frågor. Att utveckla en datakvarhållningsprincip för att hantera data i historiktabellen är en viktig aspekt av planering och hantering av livscykeln för varje temporal tabell.

Datakvarhållningshantering för historiktabell

Hanteringen av datakvarhållning i tidstabellen börjar med att fastställa vilken kvarhållningsperiod som krävs för varje temporal tabell. Din kvarhållningsprincip bör i de flesta fall vara en del av programmets affärslogik med hjälp av tidstabellerna. Till exempel har program i scenarier för datagranskning och tidsresor fasta krav på hur länge historiska data måste vara tillgängliga för onlinefrågor.

När du har fastställt datakvarhållningsperioden bör du utveckla en plan för att hantera historiska data. Bestäm hur och var du lagrar dina historiska data och hur du tar bort historiska data som är äldre än dina kvarhållningskrav. Följande metoder för att hantera historiska data i tabellen över tidshistorik är tillgängliga:

Med var och en av dessa metoder baseras logiken för att migrera eller rensa historikdata på kolumnen som motsvarar slutet av perioden i den aktuella tabellen. Värdet för slutet av perioden för varje rad avgör det ögonblick då radversionen blir stängd, dvs när den hamnar i historiktabellen. Villkoret ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) anger till exempel att historiska data som är äldre än en månad måste tas bort eller flyttas ut från historiktabellen.

Exemplen i den här artikeln använder exemplen som skapats i artikeln Skapa en systemversionsbaserad temporal tabell.

Använda tabellpartitioneringsmetod

Partitionerade tabeller och index kan göra stora tabeller mer hanterbara och skalbara. Med metoden för tabellpartitionering kan du implementera anpassad datarensning eller offlinearkivering baserat på ett tidsvillkor. Tabellpartitionering ger dig också prestandafördelar när du kör frågor mot temporala tabeller i en delmängd av datahistoriken med hjälp av partitionseliminering.

Med tabellpartitionering kan du implementera ett skjutfönster för att flytta ut den äldsta delen av historiska data från historiktabellen och behålla storleken på den bevarade delen konstant när det gäller ålder. Ett skjutande fönster upprätthåller data i historiktabellen som är lika med den nödvändiga kvarhållningsperioden. Funktionen att flytta data från historiktabellen stöds när SYSTEM_VERSIONING är ON, vilket innebär att du kan rensa en del av historikdatan utan att behöva införa en underhållsperiod eller blockera dina vanliga arbetsbördor.

Notera

För att kunna utföra partitionsväxling måste det klustrade indexet i historiktabellen vara anpassat till partitioneringsschemat (det behöver innehålla ValidTo). Standardhistoriktabellen som skapas av systemet innehåller ett klustrat index som innehåller kolumnerna ValidTo och ValidFrom, vilket är optimalt för partitionering, infogning av nya historikdata och typisk tidsfråga. Mer information finns i temporala tabeller.

Ett skjutfönster har två uppsättningar uppgifter som du behöver utföra:

  • En konfigurationsuppgift för partitionering
  • Återkommande partitionsunderhållsuppgifter

För bilden antar vi att du vill behålla historiska data i sex månader och att du vill behålla data varje månad i en separat partition. Anta också att du aktiverade systemversionering i september 2023.

En konfigurationsuppgift för partitionering skapar den inledande partitioneringskonfigurationen för historiktabellen. I det här exemplet skapar du samma antal partitioner som storleken på skjutfönstret, i månader, plus en extra tom partition som är förberedd (förklaras senare i den här artikeln). Den här konfigurationen säkerställer att systemet kan lagra nya data korrekt när du startar den återkommande partitionsunderhållsuppgiften för första gången, och garanterar att du aldrig delar partitioner med data för att undvika dyra dataförflyttningar. Du bör utföra den här uppgiften med hjälp av Transact-SQL med hjälp av exempelskriptet senare i den här artikeln.

Följande bild visar den inledande partitioneringskonfigurationen för att behålla sex månaders data.

diagram som visar den inledande partitioneringskonfigurationen för att behålla sex månaders data.

Notera

Mer information om prestandaeffekter av att använda RANGE LEFT jämfört med RANGE RIGHT vid konfiguration av partitionering finns i Prestandaöverväganden med tabellpartitionering senare i den här artikeln.

De första och sista partitionerna är öppna på nedre respektive övre gränser för att säkerställa att varje ny rad har målpartition oavsett värdet i partitioneringskolumnen. Med tiden hamnar nya rader i historiktabellen i högre partitioner. När den sjätte partitionen fylls i når du den avsedda kvarhållningsperioden. Det här är dags att starta den återkommande partitionsunderhållsaktiviteten för första gången. Den måste schemaläggas att köras regelbundet, en gång per månad i det här exemplet.

Följande bild illustrerar de återkommande underhållsaktiviteterna för partitioner (se detaljerade steg senare i det här avsnittet).

diagram som visar återkommande underhållsaktiviteter för partitioner.

De detaljerade stegen för återkommande partitionsunderhållsuppgifter är:

  1. SWITCH OUT: Skapa en mellanlagringstabell och växla sedan en partition mellan historiktabellen och mellanlagringstabellen med hjälp av instruktionen ALTER TABLE med argumentet SWITCH PARTITION (se exempel C. Växla partitioner mellan tabeller).

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

    Efter partitionsväxlingen kan du eventuellt arkivera data från mellanlagringstabellen och sedan antingen släppa eller trunkera mellanlagringstabellen för att vara redo för nästa gång du behöver utföra den här återkommande partitionsunderhållsuppgiften.

  2. MERGE RANGE: Sammanfoga den tomma partitionen 1 med partitionen 2 med hjälp av funktionen ALTER PARTITION med MERGE RANGE (se exempel B). Genom att ta bort den lägsta gränsen med den här funktionen sammanfogar du effektivt den tomma partitionen 1 med den tidigare partitionen 2 för att skapa en ny partition 1. De andra partitionerna ändrar också effektivt sina ordningstal.

  3. SPLIT RANGE: Skapa en ny tom partition 7 med hjälp av funktionen ALTER PARTITION med SPLIT RANGE (se exempel A). Genom att lägga till en ny övre gräns med den här funktionen skapar du effektivt en separat partition för den kommande månaden.

Använd Transact-SQL för att skapa partitioner i historiktabellen

Använd följande Transact-SQL-skript för att skapa partitionsfunktionen, partitionsschemat och återskapa det klustrade indexet så att det är justerat med partitionsschemat och partitionerna. I det här exemplet skapar du ett skjutfönster på sex månader med månatliga partitioner från och med 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;

Använd Transact-SQL för att underhålla partitioner i scenariot med skjutfönster

Använd följande Transact-SQL skript för att underhålla partitioner i scenariot med skjutfönstret. I det här exemplet växlar du ut partitionen för september 2023 med hjälp av MERGE RANGEoch lägger sedan till en ny partition för mars 2024 med hjälp av 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

Du kan ändra föregående skript något och använda det i regelbunden månatlig underhållsprocess:

  1. I steg (1) skapar du en ny mellanlagringstabell för den månad som du vill ta bort (oktober blir nästa i det här exemplet).
  2. I steg (3) skapar och kontrollerar du den begränsning som matchar den datamånad som du vill ta bort: ValidTo <= N'2023-10-31T23:59:59.999' för en oktoberpartition.
  3. I steg (4), partitionera SWITCH1 till den nyligen skapade mellanlagringstabellen.
  4. I steg (6) ändrar du partitionsfunktionen genom att slå samman den nedre gränsen: MERGE RANGE(N'2023-10-31T23:59:59.999' när du har flyttat ut data för oktober.
  5. I steg (7) delar du partitionsfunktionen och skapar en ny övre gräns: SPLIT RANGE (N'2024-04-30T23:59:59.999' när du har flyttat ut data för oktober.

Den optimala lösningen är dock att regelbundet köra ett allmänt Transact-SQL skript som kör lämplig åtgärd varje månad utan ändringar. Du kan generalisera föregående skript så att det fungerar enligt dina angivna parametrar (den nedre gränsen som måste sammanfogas och den nya gränsen som skapas med partitionsdelningen). Om du vill undvika att skapa en mellanlagringstabell varje månad kan du skapa en i förväg och återanvända den genom att ändra kontrollbegränsningen så att den matchar den partition som du växlar ut. Mer information finns i hur skjutfönster kan automatiseras helt.

Prestandaöverväganden med tabellpartitionering

Du måste utföra MERGE- och SPLIT RANGE åtgärder för att undvika dataflytt, eftersom dataflytt kan medföra betydande prestandakostnader. Mer information finns i Ändra en partitionsfunktion. Du gör det med hjälp av RANGE LEFT i stället för RANGE RIGHT när du skapa partitionsfunktionen.

I följande diagram beskrivs alternativen RANGE LEFT och RANGE RIGHT:

diagram som visar alternativen INTERVALL VÄNSTER och INTERVALL HÖGER.

När du definierar en partitionsfunktion som RANGE LEFTär de angivna värdena de övre gränserna för partitionerna. När du använder RANGE RIGHTär de angivna värdena de nedre gränserna för partitionerna. När du använder åtgärden MERGE RANGE för att ta bort en gräns från partitionsfunktionsdefinitionen tar den underliggande implementeringen även bort den partition som innehåller gränsen. Om partitionen inte är tom flyttas data till den partition som är resultatet av MERGE RANGE åtgärden.

I ett scenario med skjutfönster tar du alltid bort den lägsta partitionsgränsen.

  • RANGE LEFT fall: Den lägsta partitionsgränsen tillhör partitionen 1, som är tom (efter att partitionen har växlats ut), så MERGE RANGE inte medför någon dataflytt.

  • RANGE RIGHT fall: Den lägsta partitionsgränsen tillhör partitionen 2, som inte är tom, eftersom partitionen 1 tömdes genom att växla ut. I det här fallet medför MERGE RANGE dataförflyttning (data från partition 2 flyttas till partition 1). För att undvika detta måste RANGE RIGHT i scenariot med skjutfönstret ha partitionen 1, som alltid är tom. Det innebär att om du använder RANGE RIGHTbör du skapa och underhålla en extra partition jämfört med RANGE LEFT fall.

Slutsats: Partitionshantering är enklare när du använder RANGE LEFT i en glidande partition och undviker dataförflyttning. Det är dock lite enklare att definiera partitionsgränser med RANGE RIGHT eftersom du inte behöver hantera problem med datum- och tidskontroll.

Använd anpassad reningsskriptsansats

Om tabellpartitionering inte är genomförbart är en annan metod att ta bort data från historiktabellen med hjälp av ett anpassat rensningsskript. Det går bara att ta bort data från historiktabellen när SYSTEM_VERSIONING = OFF. För att undvika datainkonsekvens utför du rensning antingen under ett underhållsfönster (när arbetsbelastningar som ändrar data inte är aktiva) eller inom en transaktion (vilket effektivt blockerar andra arbetsbelastningar). Den här åtgärden kräver CONTROL behörighet för aktuella tabeller och historiktabeller.

För att minimalt blockera normala program och användarförfrågningar, ta bort data i mindre delar med avsiktlig fördröjning när du kör rensningsskriptet i en transaktion. Det finns ingen optimal storlek för varje datasegment som ska tas bort för alla scenarier, men om du tar bort mer än 10 000 rader i en enda transaktion kan det medföra en betydande straffavgift.

Rensningslogik är densamma för varje temporal tabell, så den kan automatiseras via en allmän lagrad procedur som du schemalägger för att köras regelbundet, för varje temporal tabell som du vill begränsa datahistoriken för.

Följande diagram visar hur din rensningslogik ska ordnas för en enskild tabell för att minska effekten på de pågående arbetsbelastningarna.

diagram som visar hur din rensningslogik ska organiseras för en enskild tabell för att minska effekten på pågående arbetsbelastningar.

Här följer några riktlinjer på hög nivå för att implementera processen. Schemalägg rensningslogik som ska köras varje dag och iterera över alla temporala tabeller som behöver datarensning. Använd SQL Server-agenten eller ett annat verktyg för att schemalägga den här processen:

  • Ta bort historiska data i varje temporal tabell, med början från de äldsta till de senaste raderna i flera iterationer i små segment, och undvik att ta bort alla rader i en enda transaktion, som du ser i föregående diagram.

  • Implementera varje iteration som ett anrop av en allmän lagrad procedur, vilket tar bort en del av data från historiktabellen (se följande kodexempel för den här proceduren).

  • Beräkna hur många rader du behöver ta bort för en enskild temporal tabell varje gång du anropar processen. Baserat på resultatet och antalet iterationer som du vill ha avgör du dynamiska delningspunkter för varje proceduranrop.

  • Planera att ha en fördröjningsperiod mellan iterationer för en enskild tabell för att minska effekten på program som har åtkomst till den tidsmässiga tabellen.

En lagrad procedur som tar bort data för en enda temporal tabell kan se ut som följande kodfragment. Granska den här koden noggrant och justera den innan den tillämpas i din miljö.

Det här skriptet genererar tre instruktioner som körs i en transaktion:

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

I SQL Server 2016 (13.x) måste de två första stegen köras i separata EXEC-instruktioner, eller så genererar SQL Server ett fel som liknar följande exempel:

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;

Använd en policy för kvarhållning av tidsmässig historik

gäller för: SQL Server 2017 (14.x) och senare versioner och Azure SQL Database.

Kvarhållning av tidshistorik kan konfigureras på enskild tabellnivå, vilket gör det möjligt för användare att skapa flexibla principer för åldrande. Tidsmässig kvarhållning kräver att du endast anger en parameter när tabellen skapas eller schemaändringen.

När du har definierat kvarhållningsprincipen börjar databasmotorn kontrollera regelbundet om det finns historiska rader som är berättigade till automatisk rensning av data. Identifiering av matchande rader och borttagningen från historiktabellen sker transparent i en bakgrundsaktivitet som schemaläggs och körs av systemet. Åldersvillkoret för historiktabellrader kontrolleras baserat på kolumnen som representerar slutet av den SYSTEM_TIME perioden (i dessa exempel ValidTo kolumnen). Om kvarhållningsperioden är inställd på sex månader, till exempel, uppfyller tabellrader som är berättigade till rensning följande villkor:

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

I föregående exempel motsvarar kolumnen ValidTo slutet av SYSTEM_TIME period.

Så här konfigurerar du kvarhållningsprincip

Innan du konfigurerar kvarhållningsprincipen för en temporal tabell kontrollerar du om tidsmässig historisk kvarhållning är aktiverad på databasnivå:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Databasflaggan is_temporal_history_retention_enabled är inställd på ON som standard, men du kan ändra den med ALTER DATABASE-instruktionen. Det här värdet anges automatiskt till OFF efter en punkt-i-tid-återställning (PITR). Om du vill aktivera rensning av temporal historik för databasen kör du följande kommando. Du måste ersätta <myDB> med databasen som du vill ändra:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

Kvarhållningsprincipen konfigureras när tabellen skapas genom att ange värdet för parametern 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
    )
);

Du kan ange kvarhållningsperioden med hjälp av olika tidsenheter: DAYS, WEEKS, MONTHSoch YEARS. Om HISTORY_RETENTION_PERIOD utelämnas antas kvarhållning av INFINITE. Du kan också använda nyckelordet INFINITE explicit.

I vissa scenarier kanske du vill konfigurera kvarhållning efter att tabellen har skapats eller ändra det tidigare konfigurerade värdet. I så fall använder du satsen ALTER TABLE:

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

Om du vill granska det aktuella tillståndet för kvarhållningsprincipen använder du följande exempel. Den här frågan kopplar flaggan för temporär kvarhållningsaktivering på databasnivå med kvarhållningsperioder för enskilda tabeller:

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;

Så här tar databasmotorn bort föråldrade rader

Rensningsprocessen beror på indexlayouten för historiktabellen. Endast historiktabeller med ett grupperat index (B+-träd eller kolumnarkiv) kan ha en begränsad kvarhållningsprincip konfigurerad. En bakgrundsaktivitet skapas för att utföra rensning av föråldrade data för alla temporala tabeller med en begränsad kvarhållningsperiod. Rensningslogik för det grupperade indexet i radarkivet (B+ träd) tar bort föråldrade rader i mindre segment (upp till 10 000), vilket minimerar trycket på databasloggen och I/O-undersystemet. Även om rensningslogik använder det obligatoriska B+-trädindexet kan ordningen på borttagningar för rader som är äldre än kvarhållningsperioden inte garanteras. Var inte beroende av rensningsordningen i dina program.

Rensningsaktiviteten för det klustrade kolumnarkivet tar bort hela radgrupper samtidigt (vanligtvis innehåller 1 miljon rader vardera), vilket är mer effektivt, särskilt när historiska data genereras i hög takt.

Skärmbild av klustrad kolumnlagring.

Rensning och komprimering av data gör ett klusterbaserat kolumnlagringsindex till ett perfekt val för scenarier där arbetsbelastningen snabbt genererar en stor mängd historikdata. Det mönstret är typiskt för intensiva transaktionsbearbetningsarbetsbelastningar som använder temporala tabeller för ändringsspårning och granskning, trendanalys eller IoT-datainmatning.

Mer information finns i Hantera historiska data i temporala tabeller med kvarhållningsprincip.

Relaterat innehåll