Gestire i dati cronologici nelle tabelle temporali con criteri di conservazioneManage historical data in Temporal Tables with retention policy

Le tabelle temporali possono aumentare le dimensioni del database più delle tabelle normali, in particolare se si conservano i dati cronologici per un periodo di tempo più lungo.Temporal Tables may increase database size more than regular tables, especially if you retain historical data for a longer period of time. Di conseguenza, i criteri di conservazione per i dati cronologici sono un aspetto importante della pianificazione e della gestione del ciclo di vita di ogni tabella temporale.Hence, retention policy for historical data is an important aspect of planning and managing the lifecycle of every temporal table. Le tabelle temporali nel database SQL Azure sono dotate di un meccanismo di conservazione di facile uso che aiuta a eseguire questa operazione.Temporal Tables in Azure SQL Database come with easy-to-use retention mechanism that helps you accomplish this task.

Il periodo di conservazione della cronologia temporale può essere configurato a livello di singola tabella, per consentire agli utenti di creare criteri di giacenza flessibili.Temporal history retention can be configured at the individual table level, which allows users to create flexible aging polices. L'applicazione della conservazione temporale è semplice: è necessario configurare un solo parametro durante la creazione della tabella o la modifica dello schema.Applying temporal retention is simple: it requires only one parameter to be set during table creation or schema change.

Dopo aver definito i criteri di conservazione, il database SQL di Azure avvia una verifica periodica per controllare se sono presenti righe di cronologia idonee alla pulizia automatica dei dati.After you define retention policy, Azure SQL Database starts checking regularly if there are historical rows that are eligible for automatic data cleanup. L'identificazione delle righe corrispondenti e la loro rimozione della tabella di cronologia si verificano in modo trasparente, nell'attività in background pianificata ed eseguita dal sistema.Identification of matching rows and their removal from the history table occur transparently, in the background task that is scheduled and run by the system. Le condizioni di età per le righe della tabella della cronologia vengono controllate in base alla colonna che rappresenta la fine del periodo SYSTEM_TIME.Age condition for the history table rows is checked based on the column representing end of SYSTEM_TIME period. Se, ad esempio, il periodo di conservazione definito è di sei mesi, le righe della tabella idonee per la pulizia soddisfano la condizione seguente:If retention period, for example, is set to six months, table rows eligible for cleanup satisfy the following condition:

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

L'esempio precedente presuppone che la colonna ValidTo corrisponda alla fine del periodo SYSTEM_TIME.In the preceding example, we assumed that ValidTo column corresponds to the end of SYSTEM_TIME period.

Come si configurano i criteri di conservazione?How to configure retention policy?

Prima di configurare criteri di conservazione per una tabella temporale, innanzitutto è necessario controllare se la conservazione della cronologia temporale è abilitata a livello di database.Before you configure retention policy for a temporal table, check first whether temporal historical retention is enabled at the database level.

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases

Il flag del database is_temporal_history_retention_enabled è impostato su ON per impostazione predefinita, tuttavia gli utenti possono sostituirlo con l'istruzione ALTER DATABASE.Database flag is_temporal_history_retention_enabled is set to ON by default, but users can change it with ALTER DATABASE statement. Inoltre, viene impostato automaticamente su OFF dopo l'operazione di ripristino temporizzato.It is also automatically set to OFF after point in time restore operation. Per abilitare la pulizia della conservazione della cronologia temporale per il database, eseguire l'istruzione seguente:To enable temporal history retention cleanup for your database, execute the following statement:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

Importante

È possibile configurare la conservazione per le tabelle temporali anche se is_temporal_history_retention_enabled è impostato su OFF. Tuttavia in questo caso la pulizia automatica delle righe obsolete non verrà attivata.You can configure retention for temporal tables even if is_temporal_history_retention_enabled is OFF, but automatic cleanup for aged rows is not triggered in that case.

I criteri di conservazione vengono configurati durante la creazione di una tabella specificando il valore per il parametro HISTORY_RETENTION_PERIOD:Retention policy is configured during table creation by specifying value for the HISTORY_RETENTION_PERIOD parameter:

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

Il database SQL di Azure consente di specificare il periodo di conservazione tramite unità di tempo diverse: DAYS, WEEKS, MONTHS e YEARS.Azure SQL Database allows you to specify retention period by using different time units: DAYS, WEEKS, MONTHS, and YEARS. Se HISTORY_RETENTION_PERIOD viene omesso, viene usata la conservazione INFINITE.If HISTORY_RETENTION_PERIOD is omitted, INFINITE retention is assumed. È inoltre possibile usare esplicitamente la parola chiave INFINITE.You can also use INFINITE keyword explicitly.

In alcuni scenari, è possibile configurare la conservazione dopo la creazione della tabella o per modificare un valore configurato in precedenza.In some scenarios, you may want to configure retention after table creation, or to change previously configured value. In questo caso usare l'istruzione ALTER TABLE:In that case use ALTER TABLE statement:

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

Importante

Con l'impostazione di SYSTEM_VERSIONING su OFF il valore relativo al periodo di conservazione non viene mantenuto.Setting SYSTEM_VERSIONING to OFF does not preserve retention period value. Con l'impostazione di SYSTEM_VERSIONING su ON senza specificare esplicitamente HISTORY_RETENTION_PERIOD si ottiene come risultato un periodo di conservazione INFINITE.Setting SYSTEM_VERSIONING to ON without HISTORY_RETENTION_PERIOD specified explicitly results in the INFINITE retention period.

Per esaminare lo stato corrente del criterio di conservazione, usare la seguente query che unisce il flag di abilitazione della conservazione temporale a livello di database con i periodi di conservazione per le singole tabelle:To review current state of the retention policy, use the following query that joins temporal retention enablement flag at the database level with retention periods for individual tables:

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

In che modo vengono eliminate le righe obsolete dal database SQL?How SQL Database deletes aged rows?

Il processo di pulizia dipende dal layout indice della tabella di cronologia.The cleanup process depends on the index layout of the history table. È importante notare che solo nelle tabelle di cronologia con un indice cluster (struttura B-tree o columnstore) è possibile avere la configurazione dei criteri di conservazione definiti.It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured. Viene creata un'attività in background per eseguire la pulizia dei dati obsoleti per tutte le tabelle temporali con periodo di conservazione definito.A background task is created to perform aged data cleanup for all temporal tables with finite retention period. La logica di pulizia per l'indice in cluster rowstore (B-tree) elimina la riga obsoleta in blocchi più piccoli (fino a 10.000) riducendo al minimo pressione sul log del database e sul sottosistema I/O.Cleanup logic for the rowstore (B-tree) clustered index deletes aged row in smaller chunks (up to 10K) minimizing pressure on database log and I/O subsystem. Anche se la logica di pulizia usa un indice B-tree obbligatorio, l'ordine di eliminazione per le righe antecedenti il periodo di conservazione non può essere garantito saldamente.Although cleanup logic utilizes required B-tree index, order of deletions for the rows older than retention period cannot be firmly guaranteed. Di conseguenza, non è consentito accettare le dipendenze nell'ordine di pulizia nelle applicazioni.Hence, do not take any dependency on the cleanup order in your applications.

L'attività di pulizia per columnstore in cluster rimuove interi gruppi di righe in una sola volta (in genere ogni gruppo contiene un milione di righe); questa procedura è molto efficace, soprattutto quando vengono generati dati cronologici a ritmo elevato.The cleanup task for the clustered columnstore removes entire row groups at once (typically contain 1 million of rows each), which is very efficient, especially when historical data is generated at a high pace.

Conservazione di columnstore in cluster

Un'ottima compressione dei dati e un'efficace pulizia per la conservazione fanno dell'indice columnstore in cluster la scelta ideale per gli scenari in cui il carico di lavoro genera rapidamente elevate quantità di dati cronologici.Excellent data compression and efficient retention cleanup makes clustered columnstore index a perfect choice for scenarios when your workload rapidly generates high amount of historical data. Tale modello è tipico per un l'elaborazione transazionale intensiva dei carichi di lavoro che usano tabelle temporali per il rilevamento delle modifiche e il controllo, l'analisi delle tendenze o l'inserimento dei dati IoT.That pattern is typical for intensive transactional processing workloads that use temporal tables for change tracking and auditing, trend analysis, or IoT data ingestion.

Considerazioni sull'indiceIndex considerations

L'attività di pulizia per le tabelle con indice rowstore in cluster richiede che l'indice inizi con la colonna che corrisponde alla fine del periodo SYSTEM_TIME.The cleanup task for tables with rowstore clustered index requires index to start with the column corresponding the end of SYSTEM_TIME period. Se questa colonna non esiste, non è possibile configurare un periodo di conservazione finito:If such index doesn't exist, you cannot configure a finite retention period:

Msg 13765, Level 16, State 1
L'impostazione del periodo di conservazione definito ha avuto esito negativo nella tabella temporale con controllo delle versioni del sistema 'temporalstagetestdb.dbo.WebsiteUserInfo' perché la tabella di cronologia 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' non contiene l'indice in cluster richiesto. È consigliabile creare un columnstore cluster o indice B-tree a partire dalla colonna che corrisponde alla fine del periodo SYSTEM_TIME nella tabella di cronologia.
Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table 'temporalstagetestdb.dbo.WebsiteUserInfo' because the history table 'temporalstagetestdb.dbo.WebsiteUserInfoHistory' does not contain required clustered index. Consider creating a clustered columnstore or B-tree index starting with the column that matches end of SYSTEM_TIME period, on the history table.

È importante notare che la tabella di cronologia predefinito già creata dal database SQL di Azure dispone già dell'indice in cluster, che è conforme a criteri di conservazione.It is important to notice that the default history table created by Azure SQL Database already has clustered index, which is compliant for retention policy. Se si tenta di rimuovere l'indice in una tabella con periodo di conservazione definito, l'operazione ha esito negativo con l'errore seguente:If you try to remove that index on a table with finite retention period, operation fails with the following error:

Msg 13766, Level 16, State 1
Impossibile eliminare l'indice cluster 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' perché è usato per la pulizia automatica dei dati obsoleti. Si consiglia di impostare HISTORY_RETENTION_PERIOD su INFINITE nella corrispondente tabella temporale con controllo delle versioni del sistema se è necessario eliminare l'indice.
Msg 13766, Level 16, State 1
Cannot drop the clustered index 'WebsiteUserInfoHistory.IX_WebsiteUserInfoHistory' because it is being used for automatic cleanup of aged data. Consider setting HISTORY_RETENTION_PERIOD to INFINITE on the corresponding system-versioned temporal table if you need to drop this index.

La pulizia dell'indice columnstore in cluster funziona in modo ottimale se vengono inserite righe cronologiche in ordine crescente (ordinate in base alla fine della colonna del periodo); questo viene sempre applicato quando la tabella di cronologia viene popolata esclusivamente dal meccanismo SYSTEM_VERSIONIOING.Cleanup on the clustered columnstore index works optimally if historical rows are inserted in the ascending order (ordered by the end of period column), which is always the case when the history table is populated exclusively by the SYSTEM_VERSIONIOING mechanism. Se le righe della tabella di cronologia non sono ordinate in base alla fine della colonna del periodo (ad esempio in caso di migrazione dei dati cronologici esistenti), è necessario ricreare un indice columnstore in cluster sull'indice rowstore B-tree ordinato in modo corretto, per ottenere prestazioni ottimali.If rows in the history table are not ordered by end of period column (which may be the case if you migrated existing historical data), you should re-create clustered columnstore index on top of B-tree rowstore index that is properly ordered, to achieve optimal performance.

Evitare la ricompilazione dell'indice columnstore in cluster nella tabella di cronologia con il periodo di conservazione definito, perché può cambiare l'ordine nei gruppi di righe imposti naturalmente dall'operazione di controllo delle versioni di sistema.Avoid rebuilding clustered columnstore index on the history table with the finite retention period, because it may change ordering in the row groups naturally imposed by the system-versioning operation. Se è necessario ricompilare l'indice columnstore in cluster della tabella di cronologia, crearne uno nuovo al posto dell'indice B-tree conforme, mantenendo l'ordinamento in gruppi di righe necessario per la pulizia dei dati regolare.If you need to rebuild clustered columnstore index on the history table, do that by re-creating it on top of compliant B-tree index, preserving ordering in the rowgroups necessary for regular data cleanup. Lo stesso approccio deve essere adottato se si crea una tabella temporale con la tabella di cronologia esistente che dispone di un indice di colonna in cluster senza ordine dei dati garantito:The same approach should be taken if you create temporal table with existing history table that has clustered column index without guaranteed data order:

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

Quando viene configurato il periodo di conservazione definito per la tabella di cronologia con l'indice columnstore in cluster, non è possibile creare indici B-tree non in cluster aggiuntivi nella tabella:When finite retention period is configured for the history table with the clustered columnstore index, you cannot create additional non-clustered B-tree indexes on that table:

CREATE NONCLUSTERED INDEX IX_WebHistNCI ON WebsiteUserInfoHistory ([UserName])

Un tentativo di eseguire l'istruzione sopra indicata avrà esito negativo con l'errore seguente:An attempt to execute above statement fails with the following error:

Msg 13772, Level 16, State 1
Impossibile creare un indice non in cluster in una tabella di cronologia temporale 'WebsiteUserInfoHistory' perché ha un periodo di conservazione definito e l'indice columnstore in cluster è definito.
Msg 13772, Level 16, State 1
Cannot create non-clustered index on a temporal history table 'WebsiteUserInfoHistory' since it has finite retention period and clustered columnstore index defined.

Esecuzione di query su tabelle con criteri di conservazioneQuerying tables with retention policy

Tutte le query sulla tabella temporale consentono di escludere automaticamente tramite filtro le righe cronolgiche definite corrispondenti, per evitare risultati imprevisti e incoerenti, poiché è possibile eliminare righe obsolete tramite l'attività di pulizia, in qualsiasi momento e in ordine arbitrario.All queries on the temporal table automatically filter out historical rows matching finite retention policy, to avoid unpredictable and inconsistent results, since aged rows can be deleted by the cleanup task, at any point in time and in arbitrary order.

Nell'immagine seguente viene illustrato il piano di query per una query semplice:The following picture shows the query plan for a simple query:

SELECT * FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Il piano di query include un altro filtro applicato alla fine della colonna del periodo (ValidTo) nell'operatore Clustered Index Scan nella tabella di cronologia (evidenziata).The query plan includes additional filter applied to end of period column (ValidTo) in the Clustered Index Scan operator on the history table (highlighted). Questo esempio presuppone che il periodo di conservazione di un MONTH sia stato impostato nella tabella WebsiteUserInfo.This example assumes that one MONTH retention period was set on WebsiteUserInfo table.

Filtro di query di conservazione

Tuttavia, se si esegue una query direttamente in una tabella di cronologia, è possibile vedere le righe precedenti al periodo di conservazione specificato, ma senza alcuna garanzia relativa ai risultati ripetibili.However, if you query history table directly, you may see rows that are older than specified retention period, but without any guarantee for repeatable query results. Nell'immagine seguente è mostrato il piano di esecuzione di query per la query nella tabella di cronologia senza altri filtri applicati:The following picture shows query execution plan for the query on the history table without additional filters applied:

Esecuzione di query senza filtro di conservazione

Non affidare la logica di business alla lettura della tabella di cronologia oltre il periodo di conservazione, perché si potrebbero ottenere risultati incoerenti o imprevisti.Do not rely your business logic on reading history table beyond retention period as you may get inconsistent or unexpected results. È consigliabile usare query temporali con la clausola FOR SYSTEM_TIME per l'analisi dei dati nelle tabelle temporali.We recommend that you use temporal queries with FOR SYSTEM_TIME clause for analyzing data in temporal tables.

Considerazioni sul ripristino temporizzatoPoint in time restore considerations

Quando si crea un nuovo database attraverso il ripristino di un database esistente in un punto specifico nel tempo, la conservazione temporale è disabilitata a livello di database.When you create new database by restoring existing database to a specific point in time, it has temporal retention disabled at the database level. (Il flag is_temporal_history_retention_enabled è impostato su OFF).(is_temporal_history_retention_enabled flag set to OFF). Questa funzionalità consente di esaminare tutte le righe di cronologia al momento del ripristino, senza doversi preoccupare che le righe obsolete vengano rimosse prima di procedere con l'esecuzione di query.This functionality allows you to examine all historical rows upon restore, without worrying that aged rows are removed before you get to query them. È possibile usarla per esaminare i dati cronologici oltre il periodo di conservazione configurato.You can use it to inspect historical data beyond configured retention period.

Si supponga che per una tabella temporale sia specificato il periodo di conservazione MONTH.Say that a temporal table has one MONTH retention period specified. Se il database è stato creato al livello di servizio Premium, è possibile creare una copia del database con lo stato del database risalendo fino a 35 giorni prima.If your database was created in Premium Service tier, you would be able to create database copy with the database state up to 35 days back in the past. Una tale efficacia consentirebbe di analizzare le righe cronologiche risalenti anche a 65 giorni prima eseguendo una query direttamente nella tabella di cronologia.That effectively would allow you to analyze historical rows that are up to 65 days old by querying the history table directly.

Se si desidera attivare la pulizia della conservazione temporale, eseguire l'istruzione Transact-SQL seguente dopo il ripristino temporizzato:If you want to activate temporal retention cleanup, run the following Transact-SQL statement after point in time restore:

ALTER DATABASE <myDB>
SET TEMPORAL_HISTORY_RETENTION  ON

Passaggi successiviNext steps

Per informazioni su come usare le tabelle temporali nelle applicazioni, consultare Introduzione alle tabelle temporali nel database SQL di Azure.To learn how to use Temporal Tables in your applications, check out Getting Started with Temporal Tables in Azure SQL Database.

Andare su Channel 9 per ascoltare una storia di successo reale relativa all'implementazione temporale di un cliente e guardare una dimostrazione temporale in tempo reale.Visit Channel 9 to hear a real customer temporal implementation success story and watch a live temporal demonstration.

Per informazioni dettagliate sulle tabelle temporali, esaminare la documentazione su MSDN.For detailed information about Temporal Tables, review MSDN documentation.