Historische gegevens in tijdelijke tabellen beheren met bewaarbeleid

van toepassing op:Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Fabric

Tijdelijke tabellen kunnen de database groter maken dan gewone tabellen, met name als u historische gegevens gedurende een langere periode 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. 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 ValidTo kolom overeenkomt met het einde van SYSTEM_TIME de 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 ON , maar gebruikers kunnen deze wijzigen met een ALTER DATABASE instructie. 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

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: DAYS, WEEKS, MONTHSen YEARS. Wanneer HISTORY_RETENTION_PERIOD wordt weggelaten, wordt aangenomen dat er sprake is van oneindige retentie. U kunt ook expliciet trefwoorden gebruiken INFINITE .

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

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

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. Reken daarom niet op de volgorde van opschonen in uw applicaties.

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.

Index considerations

Voor de opschoningstaak voor tabellen met een geclusterde rijopslagindex moet de index beginnen met de kolom die overeenkomt met het einde van SYSTEM_TIME de 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.

Het opschonen van de geclusterde columnstore-index werkt optimaal als historische rijen worden ingevoegd in oplopende volgorde (gerangschikt aan het einde van de periodekolom), wat altijd het geval is wanneer de geschiedenistabel uitsluitend door het SYSTEM_VERSIONIOING mechanisme wordt gevuld. Als rijen in de geschiedenistabel niet geordend zijn op de eind van de periodekolom (wat het geval kan zijn als u bestaande historische gegevens hebt gemigreerd), moet u een geclusterde kolomopslagindex bovenop de B-tree rowstore-index maken die correct geordend is, om de beste prestaties te bereiken.

Vermijd het herbouwen van een geclusterde columnstore-index in de geschiedenistabel met een beperkte bewaarperiode, omdat de volgorde in de rijgroepen kan worden gewijzigd die door systeemversiebeheer natuurlijk wordt opgelegd. 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.

Tabellen opvragen met retentiebeleid

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:

Het opvragen van geschiedenis zonder een retentiefilter.

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 met component FOR SYSTEM_TIME te gebruiken 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 ingesteld op OFF). 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 een tijdelijke tabel één MONTH bewaarperiode heeft 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 retentie opschoning wilt activeren, voert u de volgende Transact-SQL verklaring uit na herstel op een bepaald tijdstip.

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION  ON

Related content

• Aan de slag met tijdelijke tabellen
• Temporal tables