Historische gegevens in tijdelijke tabellen beheren met bewaarbeleid

Van toepassing op: Azure SQL DatabaseAzure SQL Managed Instance

Tijdelijke tabellen kunnen de database groter maken dan gewone tabellen, met name als u historische gegevens langere tijd bewaart. Daarom is bewaarbeleid voor historische gegevens een belangrijk aspect van het plannen en beheren van de levenscyclus van elke tijdelijke tabel. Tijdelijke tabellen in Azure SQL Database en Azure SQL Managed Instance worden geleverd met een gebruiksvriendelijk bewaarmechanisme waarmee u deze taak kunt uitvoeren.

Tijdelijke bewaarperiode voor geschiedenis kan worden geconfigureerd op het niveau van de afzonderlijke tabel, waardoor gebruikers flexibel verouderingsbeleid kunnen maken. Het toepassen van tijdelijke retentie is eenvoudig: er moet slechts één parameter worden ingesteld tijdens het maken van een tabel of schemawijziging.

Nadat u bewaarbeleid hebt gedefinieerd, controleren Azure SQL Database en Azure SQL Managed Instance regelmatig 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 de achtergrondtaak die is gepland en uitgevoerd door het systeem. De leeftijdsvoorwaarde voor de rijen in de geschiedenistabel wordt gecontroleerd op basis van de kolom die het einde van SYSTEM_TIME periode aangeeft. Als de bewaarperiode bijvoorbeeld is ingesteld op zes maanden, komen tabelrijen in aanmerking voor opschoning aan de volgende voorwaarde:

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

In het voorgaande voorbeeld hebben we ervan uitgegaan dat de kolom ValidTo overeenkomt met het einde van SYSTEM_TIME periode.

Bewaarbeleid configureren

Voordat u bewaarbeleid voor een tijdelijke tabel configureert, moet u eerst controleren of tijdelijke historische retentie is ingeschakeld op databaseniveau.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Databasevlag is_temporal_history_retention_enabled is standaard ingesteld op AAN, maar gebruikers kunnen deze wijzigen met de instructie ALTER DATABASE. Het wordt ook automatisch ingesteld op UIT na herstelbewerking naar een bepaald tijdstip. Voer de volgende instructie uit om het opschonen van tijdelijke geschiedenis voor uw database in te schakelen:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Belangrijk

U kunt retentie voor tijdelijke tabellen configureren, zelfs als is_temporal_history_retention_enabled uit is, maar automatisch opschonen voor verouderde rijen wordt in dat geval niet geactiveerd.

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

Met Azure SQL Database en Azure SQL Managed Instance kunt u een bewaarperiode opgeven met behulp van verschillende tijdseenheden: DAGEN, WEKEN, MAANDEN en JAREN. Als HISTORY_RETENTION_PERIOD wordt weggelaten, wordt er uitgegaan van ONEINDIGe retentie. U kunt ook het trefwoord INFINITE expliciet gebruiken.

In sommige scenario's kunt u retentie configureren na het maken van de tabel of de eerder geconfigureerde waarde wijzigen. In dat geval gebruikt u de instructie ALTER TABLE:

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

Belangrijk

Als u SYSTEM_VERSIONING instelt op UIT , blijft de waarde van de bewaarperiode niet behouden . Als u SYSTEM_VERSIONING instelt op AAN zonder HISTORY_RETENTION_PERIOD opgegeven, resulteert dit expliciet in de ONEINDIGE bewaarperiode.

Als u de huidige status van het bewaarbeleid wilt controleren, gebruikt u de volgende query waarmee tijdelijke activeringsvlag voor retentie op databaseniveau wordt samengevoegd met 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 verouderde rijen worden verwijderd

Het opschonen is afhankelijk van de indexindeling van de geschiedenistabel. Het is belangrijk om te zien dat alleen geschiedenistabellen met een geclusterde index (B-tree of columnstore) eindig bewaarbeleid kunnen hebben geconfigureerd. Er wordt een achtergrondtaak gemaakt voor het opschonen van verouderde gegevens voor alle tijdelijke tabellen met een eindige bewaarperiode. Met opschoningslogica voor de geclusterde index van rowstore (B-tree) worden verouderde rijen in kleinere segmenten (maximaal 10K) verwijderd, waardoor de druk op het databaselogboek en het IO-subsysteem wordt geminimaliseerd. Hoewel opschoningslogica gebruikmaakt van de vereiste B-structuurindex, kan de volgorde van verwijderingen voor de rijen ouder dan de bewaarperiode niet stevig worden gegarandeerd. Neem daarom geen afhankelijkheid van de opschoonvolgorde in uw toepassingen.

Met de opschoningstaak voor het gegroepeerde columnstore worden hele rijgroepen tegelijk verwijderd (meestal 1 miljoen rijen per rij), wat zeer efficiënt is, met name wanneer historische gegevens in een hoog tempo worden gegenereerd.

Uitstekende gegevenscompressie en efficiënt opschonen van retentie maakt geclusterde columnstore-index een perfecte keuze voor scenario's wanneer uw workload snel een grote hoeveelheid 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.

Overwegingen voor indexen

Voor de opschoningstaak voor tabellen met een geclusterde rijopslagindex moet de index beginnen met de kolom die overeenkomt met het einde van SYSTEM_TIME periode. Als deze index niet bestaat, kunt u een eindige bewaarperiode niet configureren:

Msg 13765, Niveau 16, Staat 1

Het instellen van een eindige bewaarperiode is mislukt op de tijdelijke tabel 'temporalstagetestdb.dbo.WebsiteUserInfo' omdat de geschiedenistabel 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' geen vereiste geclusterde index bevat. Overweeg om een geclusterde columnstore of B-tree-index te maken die begint met de kolom die overeenkomt met het einde van SYSTEM_TIME periode, in de geschiedenistabel.

Het is belangrijk te weten dat de standaardgeschiedenistabel die is gemaakt door Azure SQL Database en Azure SQL Managed Instance al een geclusterde index heeft, die compatibel is voor bewaarbeleid. Als u die index voor een tabel met een eindige bewaarperiode probeert te verwijderen, mislukt de bewerking met de volgende fout:

Msg 13766, Niveau 16, Staat 1

Kan de geclusterde index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' niet verwijderen omdat deze wordt gebruikt voor het automatisch opschonen van verouderde gegevens. U kunt HISTORY_RETENTION_PERIOD instellen op INFINITE in de bijbehorende tijdelijke tabel met systeemversies als u deze index wilt verwijderen.

Opschonen van de geclusterde columnstore-index werkt optimaal als historische rijen worden ingevoegd in oplopende volgorde (gerangschikt aan het einde van de kolom van de periode), wat altijd het geval is wanneer de geschiedenistabel exclusief wordt gevuld door het SYSTEM_VERSIONIOING mechanisme. Als rijen in de geschiedenistabel niet zijn geordend aan het einde van de puntkolom (wat het geval kan zijn als u bestaande historische gegevens hebt gemigreerd), moet u een geclusterde columnstore-index maken boven op de B-tree rowstore-index die correct is gerangschikt, om optimale prestaties te bereiken.

Vermijd het herbouwen van een geclusterde columnstore-index in de geschiedenistabel met de eindige bewaarperiode, omdat de volgorde in de rijgroepen kan worden gewijzigd die natuurlijk wordt opgelegd door de systeemversiebeheerbewerking. Als u de geclusterde columnstore-index opnieuw moet opbouwen in de geschiedenistabel, doet u dat door deze opnieuw te maken boven op de compatibele B-tree-index, waarbij de volgorde behouden blijft in de rijengroepen die nodig zijn voor het regelmatig opschonen van gegevens. Dezelfde benadering moet worden gebruikt als u een tijdelijke tabel maakt met een bestaande geschiedenistabel met een geclusterde kolomindex zonder gegarandeerde gegevensvolgorde:

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

Wanneer de eindige bewaarperiode is geconfigureerd voor de geschiedenistabel met de geclusterde columnstore-index, kunt u geen aanvullende niet-geclusterde B-tree-indexen voor die tabel maken:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Een poging om de bovenstaande instructie uit te voeren mislukt met de volgende fout:

Msg 13772, Niveau 16, Staat 1

Kan geen niet-geclusterde index maken in een tijdelijke geschiedenistabel 'WebsiteUserInfoHistory' omdat er een eindige bewaarperiode en een geclusterde columnstore-index is gedefinieerd.

Query's uitvoeren op tabellen met bewaarbeleid

Alle query's in de tijdelijke tabel filteren automatisch historische rijen die overeenkomen met eindig bewaarbeleid, om onvoorspelbare en inconsistente resultaten te voorkomen, omdat verouderde rijen kunnen worden verwijderd door de opschoontaak, op elk moment en in willekeurige volgorde.

In de volgende afbeelding ziet u het queryplan voor een eenvoudige query:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Het queryplan bevat extra filters die zijn toegepast op de kolom Einde van de periode (ValidTo) in de operator Geclusterde indexscan in de geschiedenistabel (gemarkeerd). In dit voorbeeld wordt ervan uitgegaan dat een retentieperiode van één MAAND is ingesteld in de tabel WebsiteUserInfo.

Als u de querygeschiedenistabel echter rechtstreeks uitvoert, ziet u mogelijk rijen die ouder zijn dan de opgegeven bewaarperiode, maar zonder enige garantie voor herhaalbare queryresultaten. In de volgende afbeelding ziet u het queryuitvoeringsplan voor de query in de geschiedenistabel zonder dat er extra filters zijn toegepast:

Vertrouw niet op uw bedrijfslogica voor het lezen van de geschiedenistabel na de bewaarperiode, omdat u mogelijk inconsistente of onverwachte resultaten krijgt. U wordt aangeraden tijdelijke query's te gebruiken met de component FOR SYSTEM_TIME voor het analyseren van gegevens in tijdelijke tabellen.

Overwegingen voor herstel naar een bepaald tijdstip

Wanneer u een nieuwe database maakt door de bestaande database te herstellen naar een bepaald tijdstip, is tijdelijke retentie uitgeschakeld op databaseniveau. (is_temporal_history_retention_enabled vlag is ingesteld op UIT). Met deze functionaliteit kunt u alle historische rijen na herstel onderzoeken, zonder dat u zich zorgen hoeft te maken dat verouderde rijen worden verwijderd voordat u query's op deze rijen uitvoert. U kunt deze gebruiken om historische gegevens te controleren na de geconfigureerde bewaarperiode.

Stel dat voor een tijdelijke tabel één retentieperiode van één MAAND is opgegeven. Als uw database is gemaakt in de Premium-servicelaag, kunt u een databasekopie maken met de databasestatus tot 35 dagen terug in het verleden. Op die manier kunt u historische rijen analyseren die maximaal 65 dagen oud zijn door rechtstreeks een query uit te voeren op de geschiedenistabel.

Als u tijdelijke opschoning van retentie wilt activeren, voert u de volgende Transact-SQL-instructie uit na herstel naar een bepaald tijdstip:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Volgende stappen

Zie Aan de slag met tijdelijke tabellen voor meer informatie over het gebruik van tijdelijke tabellen in uw toepassingen.

Raadpleeg tijdelijke tabellen voor gedetailleerde informatie over tijdelijke tabellen.