Hantera historiska data i temporala tabeller med kvarhållningsprincip

Gäller för:Azure SQL DatabaseAzure SQL Managed Instance

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 period. 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 kolumnen ValidTo motsvarar slutet av SYSTEM_TIME period.

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 på PÅ som standard, men användarna kan ändra den med ALTER DATABASE-instruktionen. Den ställs också automatiskt in på AV efter återställning vid 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

Viktigt!

Du kan konfigurera kvarhållning för temporala tabeller även om is_temporal_history_retention_enabled är AV, men automatisk rensning för föråldrade rader utlöses inte i så fall.

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: DAGAR, VECKOR, MÅNADER och ÅR. Om HISTORY_RETENTION_PERIOD utelämnas antas OÄNDLIG kvarhållning. Du kan också uttryckligen använda INFINITE-nyckelord.

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-instruktionen:

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

Viktigt!

Om du anger SYSTEM_VERSIONING till OFF bevaras inte kvarhållningsperiodvärdet. Om du anger SYSTEM_VERSIONING till PÅ utan att HISTORY_RETENTION_PERIOD anges, resulterar det uttryckligen i INFINITE-kvarhållningsperioden.

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överväganden

Rensningsaktiviteten för tabeller med ett radlagerklusterindex kräver att indexet börjar med kolumnen som motsvarar slutet av SYSTEM_TIME period. 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, delstat 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.

Rensning på 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 naturligt åläggs 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.

Köra frågor mot tabeller 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 i slutet av perioden (ValidTo) i operatorn Klustrad indexgenomsökning i historiktabellen (markerad). Det här exemplet förutsätter att kvarhållningsperioden MONTH 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 frågekörningsplanen för frågan i historiktabellen utan ytterligare filter:

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.

Överväganden för återställning till tidpunkt

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 funktionen kan du undersöka alla historiska rader vid återställning, utan att oroa dig för att föråldrade rader tas bort innan du får fråga dem. Du kan använda den för att granska historiska data utöver den konfigurerade kvarhållningsperioden.

Anta att en tidstabell har en ANGIVEN MONTH-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 temporär kvarhållningsrensning kör du följande Transact-SQL-instruktion efter återställning till tidpunkt:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Nästa steg

Mer information om hur du använder temporala tabeller i dina program finns i Komma igång med temporala tabeller.

Detaljerad information om temporala tabeller finns i Temporala tabeller.