Hantera historiska data i temporala tabeller med kvarhållningsprincip

gäller för:Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Fabric

Tidstabeller kan öka databasstorleken mer än vanliga tabeller, särskilt om du behåller historiska data under en längre tidsperiod. Därför är kvarhållningsprincipen för historiska data en viktig aspekt av planering och hantering av livscykeln för varje temporal tabell. Temporala tabeller i Azure SQL Database och Azure SQL Managed Instance levereras med en lättanvänd kvarhållningsmekanism som hjälper dig att utföra den här uppgiften.

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. Det är enkelt att tillämpa tidsmässig kvarhållning: det krävs bara en parameter som anges när tabellen skapas eller schemaändringen.

När du har definierat kvarhållningsprincipen börjar Azure SQL Database och Azure SQL Managed Instance kontrollera regelbundet om det finns historiska rader som är berättigade till automatisk datarensning. Identifiering av matchande rader och borttagningen från historiktabellen sker transparent i bakgrundsaktiviteten som schemaläggs och körs av systemet. Åldersvillkoret för raderna i historiktabellen kontrolleras baserat på kolumnen som representerar slutet av SYSTEM_TIME perioden. Om till exempel kvarhållningsperioden är inställd på sex månader uppfyller tabellrader som är berättigade till rensning följande villkor:

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

I föregående exempel antog vi att ValidTo kolumnen motsvarar slutet av SYSTEM_TIME perioden.

Så här konfigurerar du kvarhållningsprincip

Innan du konfigurerar kvarhållningsprincipen för en temporal tabell kontrollerar du först 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 ON på som standard, men användarna kan ändra den med ALTER DATABASE -instruktionen. Den ställs också automatiskt in på AV efter återställningsoperation vid en specifik tidpunkt. Om du vill aktivera kvarhållningsrensning för tidshistorik för databasen kör du följande instruktion:

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

Med Azure SQL Database och Azure SQL Managed Instance kan du ange kvarhållningsperiod med hjälp av olika tidsenheter: DAYS, WEEKS, MONTHSoch YEARS. Om HISTORY_RETENTION_PERIOD utelämnas, förutsätts oändlig kvarhållning. Du kan också använda INFINITE nyckelord explicit.

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

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 fråga som ansluter 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;

Hur föråldrade rader tas bort

Rensningsprocessen beror på indexlayouten för historiktabellen. Det är viktigt att observera att 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 begränsad kvarhållningsperiod. Rensningslogiken 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 obligatoriskt B-trädindex, kan ordningen på borttagningar för rader som är äldre än kvarhållningsperioden inte garanteras. Därför bör du inte vara beroende av rensningsordningen i dina program.

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

Utmärkt datakomprimering och effektiv kvarhållningsrensning gör grupperade kolumnlagringsindex till ett perfekt val för scenarier när din arbetsbelastning snabbt genererar stora mängder historiska data. Det mönstret är typiskt för intensiva transaktionsbearbetningsarbetsbelastningar som använder temporala tabeller för ändringsspårning och granskning, trendanalys eller IoT-datainmatning.

Index considerations

Rensningsaktiviteten för tabeller med ett radlagerklusterindex kräver att indexet börjar med kolumnen som motsvarar slutet av SYSTEM_TIME perioden. Om det inte finns något sådant index kan du inte konfigurera en begränsad kvarhållningsperiod:

Msg 13765, nivå 16, delstat 1

Det gick inte att ange en begränsad kvarhållningsperiod i den systemversionsbaserade temporaltabellen "temporalstagetestdb.dbo.WebsiteUserInfo" eftersom historiktabellen "temporalstagetestdb.dbo.WebsiteUserInfoHistory" inte innehåller det obligatoriska klustrade indexet. Överväg att skapa ett grupperat kolumnarkiv eller B-trädindex som börjar med kolumnen som matchar slutet av SYSTEM_TIME period i historiktabellen.

Det är viktigt att observera att standardhistoriktabellen som skapats av Azure SQL Database och Azure SQL Managed Instance redan har klustrade index, vilket är kompatibelt med kvarhållningsprincipen. Om du försöker ta bort indexet i en tabell med begränsad kvarhållningsperiod misslyckas åtgärden med följande fel:

Msg 13766, nivå 16, status 1

Det går inte att släppa det klustrade indexet "WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory" eftersom det används för automatisk rensning av föråldrade data. Överväg att ange HISTORY_RETENTION_PERIOD till INFINITE i motsvarande systemversionsbaserade temporaltabell om du behöver ta bort det här indexet.

Rensningen av det klustrade kolumnlagringsindexet fungerar optimalt om historiska rader infogas i stigande ordning (ordnade efter slutet av periodkolumnen), vilket alltid är fallet när historiktabellen endast fylls i av mekanismen SYSTEM_VERSIONIOING . Om rader i historiktabellen inte sorteras efter periodkolumnens slut (vilket kan vara fallet om du migrerade befintliga historiska data) bör du återskapa klustrat kolumnlagringsindex ovanpå B-trädradlagringsindexet som är korrekt ordnat för att uppnå optimala prestanda.

Undvik att återskapa klustrade columnstore-index i historiktabellen med den begränsade kvarhållningsperioden, eftersom det kan ändra ordningen i radgrupperna som påförs naturligt av systemversionsåtgärden. Om du behöver återskapa klustrade kolumnlagringsindex i historiktabellen gör du det genom att återskapa det ovanpå kompatibelt B-trädindex, vilket bevarar ordningen i de radgrupper som krävs för regelbunden datarensning. Samma metod bör användas om du skapar en temporal tabell med en befintlig historiktabell som har grupperat kolumnindex utan garanterad dataordning:

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

När den begränsade kvarhållningsperioden har konfigurerats för historiktabellen med det klustrade kolumnlagringsindexet kan du inte skapa ytterligare B-trädindex som inte är grupperade i tabellen:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Ett försök att köra ovanstående instruktion misslyckas med följande fel:

Msg 13772, nivå 16, delstat 1

Det går inte att skapa icke-klustrade index i en tabell med tidshistorik "WebsiteUserInfoHistory" eftersom den har definierat begränsad kvarhållningsperiod och klustrat kolumnlagringsindex.

Frågetabeller med kvarhållningsprincip

Alla frågor i den tidsmässiga tabellen filtrerar automatiskt bort historiska rader som matchar ändlig kvarhållningsprincip för att undvika oförutsägbara och inkonsekventa resultat, eftersom föråldrade rader kan tas bort av rensningsaktiviteten, när som helst och i godtycklig ordning.

Följande bild visar frågeplanen för en enkel fråga:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Frågeplanen innehåller ytterligare filter som tillämpas på kolumnen för slutet av perioden (ValidTo) i operatorn Klustrad indexgenomsökning i historiktabellen (markerad). Det här exemplet förutsätter att en månads kvarhållningsperiod har angetts i tabellen WebsiteUserInfo.

Men om du frågar historiktabellen direkt kan du se rader som är äldre än den angivna kvarhållningsperioden, men utan någon garanti för repeterbara frågeresultat. Följande bild visar körplanen för sökfrågan i historiktabellen utan att några ytterligare filter har tillämpats.

Förlita dig inte på din affärslogik på att läsa historiktabeller bortom kvarhållningsperioden eftersom du kan få inkonsekventa eller oväntade resultat. Vi rekommenderar att du använder temporala frågor med FOR SYSTEM_TIME -sats för att analysera data i temporala tabeller.

Ögonblicksåterställning överväganden

När du skapar en ny databas genom att återställa en befintlig databas till en viss tidpunkt har den tidsmässig kvarhållning inaktiverad på databasnivå. (is_temporal_history_retention_enabled flagga inställd på OFF). Med den här funktionaliteten kan du vid återställning undersöka alla historiska rader utan att oroa dig för att äldre rader tas bort före du får möjlighet att fråga dem. Du kan använda den för att granska historiska data utöver den konfigurerade kvarhållningsperioden.

Anta att en temporal tabell har en MONTH angiven kvarhållningsperiod. Om databasen skapades på Premium Service-nivån skulle du kunna skapa databaskopiering med databastillståndet upp till 35 dagar tillbaka i tiden. På så sätt kan du analysera historiska rader som är upp till 65 dagar gamla genom att fråga historiktabellen direkt.

Om du vill aktivera tidsbaserad kvarhållningsrensning kör du följande Transact-SQL-kommando efter återställning vid tidpunkt.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Related content

• Komma igång med temporala tabeller
• Temporal tables