Gestire la conservazione dei dati cronologici nelle tabelle temporali con controllo delle versioni di sistema

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure Istanza gestita di SQL di Azure

Con le tabelle temporali con controllo delle versioni di sistema, la tabella di cronologia potrebbe aumentare le dimensioni del database più delle normali tabelle, in particolare nelle condizioni seguenti:

  • I dati cronologici vengono conservati per un lungo periodo di tempo
  • Si esegue un aggiornamento o un'eliminazione con un modello di modifica dati con impatto elevato

Una tabella di cronologia di grandi dimensioni e in continua crescita può diventare un problema a causa dei costi di archiviazione puri e l'imposizione di un'imposta sulle prestazioni per le query temporali. Di conseguenza, lo sviluppo di criteri di conservazione dei dati per la gestione dei dati nella tabella della cronologia è un aspetto importante della pianificazione e della gestione del ciclo di vita di ogni tabella temporale.

Gestione della conservazione dei dati per la tabella di cronologia

Per gestire la conservazione dei dati della tabella temporale, è prima di tutto necessario determinare il periodo di conservazione obbligatorio per ogni tabella temporale. Nella maggior parte dei casi, i criteri di conservazione devono essere considerati come parte della logica di business dell'applicazione che usa le tabelle temporali. Ad esempio le applicazioni negli scenari di controllo dei dati e di spostamento cronologico prevedono requisiti rigorosi a livello di durata della disponibilità dei dati cronologici per le query online.

Dopo aver determinato il periodo di conservazione dei dati, il passaggio successivo consiste nel sviluppare un piano per la gestione dei dati cronologici. È necessario decidere come e dove archiviare i dati cronologici e come eliminare i dati cronologici precedenti ai requisiti di conservazione. Sono disponibili gli approcci seguenti per la gestione dei dati cronologici nella tabella della cronologia temporale:

In ogni approccio la logica per la migrazione o la pulizia dei dati cronologici è basata sulla colonna che corrisponde alla fine del periodo nella tabella corrente. Il valore di fine periodo per ogni riga determina il momento in cui la versione della riga viene chiusa, ovvero quando viene inserita nella tabella di cronologia. Ad esempio, la condizione ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) specifica che i dati cronologici più vecchi di un mese devono essere rimossi o spostati dalla tabella di cronologia.

Nota

Gli esempi in questo articolo usano questa tabella temporale con controllo delle versioni di sistema.

Usare l'approccio di partizionamento delle tabelle

Le tabelle e gli indici partizionati possono rendere più gestibili e scalabili tabelle di grandi dimensioni. L'approccio con partizionamento delle tabelle consente di usare le partizioni delle tabelle di cronologia per implementare la pulizia dei dati personalizzata o l'archiviazione offline in base a una condizione temporale. Il partizionamento delle tabelle offre anche vantaggi in termini di prestazioni durante l'esecuzione di query su tabelle temporali in un subset di cronologia dei dati tramite l'eliminazione della partizione.

Con il partizionamento delle tabelle, è possibile implementare una finestra scorrevole per uscire dalla parte meno recente dei dati cronologici dalla tabella di cronologia e mantenere la dimensione della parte mantenuta costante in termini di età, mantenendo i dati nella tabella di cronologia uguale al periodo di conservazione richiesto. L'operazione di cambio dei dati dalla tabella di cronologia è supportata mentre SYSTEM_VERSIONING è ON, il che significa che è possibile pulire una parte dei dati di cronologia senza introdurre una finestra di manutenzione o bloccare i normali carichi di lavoro.

Nota

Per eseguire il cambio di partizione, l'indice cluster nella tabella di cronologia deve essere allineato allo schema di partizionamento (deve contenere ValidTo). La tabella di cronologia predefinita creata dal sistema contiene un indice cluster che include le colonne eValidFrom, che è ottimale per il ValidTo partizionamento, l'inserimento di nuovi dati di cronologia e la tipica query temporale. Per altre informazioni, vedere Tabelle temporali.

Una finestra scorrevole include due set di attività che è necessario eseguire:

  • Attività di configurazione del partizionamento
  • Attività di manutenzione ricorrenti della partizione

Per l'illustrazione, si supponga di voler mantenere i dati cronologici per sei mesi e che si desidera conservare ogni mese di dati in una partizione separata. Si supponga inoltre di aver attivato il controllo delle versioni del sistema nel mese di settembre 2023.

Un'attività di configurazione del partizionamento crea la configurazione iniziale del partizionamento per la tabella di cronologia. Per questo esempio, si creerebbero le stesse partizioni numeriche delle dimensioni della finestra temporale scorrevole, in mesi, più una partizione vuota aggiuntiva prepreparata (illustrata più avanti in questo articolo). Questa configurazione garantisce che il sistema sia in grado di archiviare correttamente i nuovi dati quando si avvia l'attività di manutenzione della partizione ricorrente per la prima volta e garantisce che le partizioni non vengano mai suddivise con i dati per evitare spostamenti di dati costosi. È consigliabile eseguire questa attività usando Transact-SQL usando lo script di esempio più avanti in questo articolo.

L'immagine seguente mostra la configurazione iniziale del partizionamento per mantenere sei mesi di dati.

Diagram showing initial partitioning configuration to keep six months of data.

Nota

Vedere considerazioni sulle prestazioni con il partizionamento delle tabelle più avanti in questo articolo per le implicazioni sulle prestazioni dell'uso RANGE LEFT rispetto RANGE RIGHT a quando si configura il partizionamento.

Le prime e le ultime partizioni sono aperte rispettivamente sui limiti inferiori e superiori, per assicurarsi che ogni nuova riga abbia una partizione di destinazione indipendentemente dal valore nella colonna di partizionamento. Man mano che il tempo passa, le nuove righe nella tabella di cronologia vengono inserite in partizioni più elevate. Quando la sesta partizione viene riempita, si raggiunge il periodo di conservazione di destinazione. Questo è il momento in cui avviare per la prima volta l'attività ricorrente di manutenzione della partizione. Questa attività deve essere pianificata per l'esecuzione periodica, una volta al mese in questo esempio.

L'immagine seguente illustra le attività ricorrenti di manutenzione della partizione (vedere i passaggi dettagliati più avanti in questa sezione).

Diagram showing the recurring partition maintenance tasks.

Ecco la procedura dettagliata per le attività ricorrenti di manutenzione della partizione:

  1. SWITCH OUT: creare una tabella di staging e quindi cambiare una partizione tra la tabella di cronologia e la tabella di staging usando l'istruzione ALTER TABLE (Transact-SQL) con l'argomento SWITCH PARTITION (vedere Esempio C. Passaggio di partizioni tra tabelle).

    ALTER TABLE [<history table>] SWITCH PARTITION 1 TO [<staging table>];
    

    Dopo l'opzione di partizione, è possibile archiviare facoltativamente i dati dalla tabella di staging e quindi eliminare o troncare la tabella di staging per essere pronti per la successiva volta che è necessario eseguire questa attività ricorrente di manutenzione della partizione.

  2. MERGE RANGE: unire la partizione 1 vuota con la partizione 2 usando ALTER PARTITION FUNCTION (Transact-SQL) con MERGE RANGE (vedere l'esempio B). Rimuovendo il limite più basso usando questa funzione, si unisce effettivamente la partizione vuota con la partizione 1 precedente per formare una nuova partizione 21. Vengono modificati anche i numeri ordinali relativi alle altre partizioni.

  3. SPLIT RANGE: creare una nuova partizione vuota 7 usando ALTER PARTITION FUNCTION (Transact-SQL) con SPLIT RANGE (vedere l'esempio A). Aggiungendo un nuovo limite superiore mediante questa funzione, si crea effettivamente una partizione separata per il mese successivo.

Usare Transact-SQL per creare partizioni nella tabella di cronologia

Usare lo script Transact-SQL seguente per creare la funzione di partizione, lo schema di partizione e ricreare l'indice cluster per essere allineato alla partizione con lo schema di partizione, le partizioni. Per questo esempio, si crea una finestra scorrevole di sei mesi con partizioni mensili, a partire da settembre 2023.

BEGIN TRANSACTION

/*Create partition function*/
CREATE PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo] (DATETIME2(7))
AS RANGE LEFT FOR VALUES (
    N'2023-09-30T23:59:59.999',
    N'2023-10-31T23:59:59.999',
    N'2023-11-30T23:59:59.999',
    N'2023-12-31T23:59:59.999',
    N'2024-01-31T23:59:59.999',
    N'2024-02-29T23:59:59.999'
);

/*Create partition scheme*/
CREATE PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo]
AS PARTITION [fn_Partition_DepartmentHistory_By_ValidTo] TO (
    [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY],
    [PRIMARY], [PRIMARY], [PRIMARY]
);

/*Re-create index to be partition-aligned with the partitioning schema*/
CREATE CLUSTERED INDEX [ix_DepartmentHistory] ON [dbo].[DepartmentHistory] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    DATA_COMPRESSION = PAGE
) ON [sch_Partition_DepartmentHistory_By_ValidTo](ValidTo);

COMMIT TRANSACTION;

Usare Transact-SQL per gestire le partizioni in uno scenario di finestra scorrevole

Usare lo script Transact-SQL seguente per gestire le partizioni nello scenario della finestra scorrevole. Per questo esempio, si disattiva la partizione per settembre 2023 usando MERGE RANGEe quindi si aggiunge una nuova partizione per marzo 2024 usando SPLIT RANGE.

BEGIN TRANSACTION

/*(1) Create staging table */
CREATE TABLE [dbo].[staging_DepartmentHistory_September_2023] (
    DeptID INT NOT NULL,
    DeptName VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2(7) NOT NULL,
    ValidTo DATETIME2(7) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

/*(2) Create index on the same filegroups as the partition that will be switched out*/
CREATE CLUSTERED INDEX [ix_staging_DepartmentHistory_September_2023]
ON [dbo].[staging_DepartmentHistory_September_2023] (
    ValidTo ASC,
    ValidFrom ASC
)
WITH (
    PAD_INDEX = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY];

/*(3) Create constraints matching the partition that will be switched out*/
ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    WITH CHECK ADD CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]
    CHECK (ValidTo <= N'2023-09-30T23:59:59.999')

ALTER TABLE [dbo].[staging_DepartmentHistory_September_2023]
    CHECK CONSTRAINT [chk_staging_DepartmentHistory_September_2023_partition_1]

/*(4) Switch partition to staging table*/
ALTER TABLE [dbo].[DepartmentHistory] SWITCH PARTITION 1
TO [dbo].[staging_DepartmentHistory_September_2023]
    WITH (WAIT_AT_LOW_PRIORITY(MAX_DURATION = 0 MINUTES, ABORT_AFTER_WAIT = NONE))

/*(5) [Commented out] Optionally archive the data and drop staging table
      INSERT INTO [ArchiveDB].[dbo].[DepartmentHistory]
      SELECT * FROM [dbo].[staging_DepartmentHistory_September_2023];
      DROP TABLE [dbo].[staging_DepartmentHIstory_September_2023];
*/
/*(6) merge range to move lower boundary one month ahead*/
ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    MERGE RANGE(N'2023-09-30T23:59:59.999');
/*(7) Create new empty partition for "April and after" by creating new boundary point and specifying NEXT USED file group*/
ALTER PARTITION SCHEME [sch_Partition_DepartmentHistory_By_ValidTo] NEXT USED [PRIMARY]
    ALTER PARTITION FUNCTION [fn_Partition_DepartmentHistory_By_ValidTo]()
    SPLIT RANGE(N'2024-03-31T23:59:59.999');
COMMIT TRANSACTION

È possibile modificare leggermente lo script precedente e usarlo nel normale processo di manutenzione mensile:

  1. Nel passaggio (1) creare una nuova tabella di staging per il mese che si vuole rimuovere (ottobre sarà la prossima in questo esempio).
  2. Nel passaggio (3) creare e controllare il vincolo corrispondente al mese di dati da rimuovere: ValidTo <= N'2023-10-31T23:59:59.999' per la partizione di ottobre.
  3. Nel passaggio (4) SWITCH partizionare 1 nella tabella di staging appena creata.
  4. Nel passaggio (6) modificare la funzione di partizione unendo il limite inferiore: MERGE RANGE(N'2023-10-31T23:59:59.999' dopo lo spostamento dei dati per ottobre.
  5. Nel passaggio (7) suddividere la funzione di partizione creando un nuovo limite superiore: SPLIT RANGE (N'2024-04-30T23:59:59.999' dopo lo spostamento dei dati per ottobre.

La soluzione ottimale consiste tuttavia nell'eseguire uno script di Transact-SQL generico, in grado di eseguire l'azione appropriata ogni mese, senza modifiche allo script. È possibile generalizzare lo script precedente per agire sui parametri forniti (limite inferiore che deve essere unito e nuovo limite che verrà creato con suddivisione partizione). Per evitare la creazione di tabelle di staging ogni mese, è possibile crearne uno in anticipo e riutilizzare modificando il vincolo CHECK in modo che corrisponda alla partizione che verrà disattivata. Esaminare le pagine seguenti per ottenere idee su come la finestra scorrevole può essere completamente automatizzata usando uno script Transact-SQL.

Considerazioni sulle prestazioni con il partizionamento delle tabelle

È importante eseguire le MERGE operazioni e SPLIT RANGE per evitare lo spostamento dei dati perché lo spostamento dei dati può comportare un sovraccarico significativo delle prestazioni. Per altre informazioni, vedere Modificare una funzione di partizione. A tale scopo, usare RANGE LEFT anziché RANGE RIGHT quando si crea la funzione di partizione.

Prima di tutto viene illustrato visivamente il RANGE LEFT significato delle opzioni e RANGE RIGHT :

Diagram showing the RANGE LEFT and RANGE RIGHT options.

Quando si definisce una funzione di partizione come RANGE LEFT, i valori specificati sono i limiti superiori delle partizioni. Quando si usa RANGE RIGHT, i valori specificati sono i limiti inferiori delle partizioni. Quando si usa l'operazione MERGE RANGE per rimuovere un limite dalla definizione della funzione di partizione, l'implementazione sottostante rimuove anche la partizione che contiene il limite. Se tale partizione non è vuota, i dati vengono spostati nella partizione risultante dall'operazione MERGE RANGE .

In uno scenario di finestra scorrevole si rimuove sempre il limite di partizione più basso.

  • RANGE LEFT case: il limite di partizione più basso appartiene alla partizione , che è vuota (dopo il cambio di partizione 1), quindi MERGE RANGE non comporta alcun spostamento dei dati.
  • RANGE RIGHT case: il limite di partizione più basso appartiene alla partizione 2, che non è vuota, perché la partizione 1 è stata svuotata disattivando. In questo caso, MERGE RANGE comporta lo spostamento dei dati (i dati della partizione vengono spostati nella partizione 21). Per evitare questo problema, RANGE RIGHT nello scenario della finestra temporale scorrevole deve avere una partizione 1, che è sempre vuota. Ciò significa che se si usa RANGE RIGHT, è necessario creare e gestire una partizione aggiuntiva rispetto al RANGE LEFT caso.

Conclusione: l'uso RANGE LEFT della partizione scorrevole è più semplice per la gestione delle partizioni ed evita lo spostamento dei dati. Tuttavia, la definizione dei limiti di partizione con RANGE RIGHT è leggermente più semplice perché non è necessario gestire i problemi di controllo dell'ora di data e ora.

Usare l'approccio personalizzato per lo script di pulizia

Nei casi in cui il partizionamento delle tabelle non è fattibile, un altro approccio consiste nell'eliminare i dati dalla tabella di cronologia usando uno script di pulizia personalizzato. L'eliminazione di dati dalla tabella di cronologia è possibile solo quando SYSTEM_VERSIONING = OFF. Per evitare l'incoerenza dei dati, eseguire la pulizia durante la finestra di manutenzione (quando i carichi di lavoro che modificano i dati non sono attivi) o all'interno di una transazione (bloccando efficacemente altri carichi di lavoro). Questa operazione richiede CONTROL l'autorizzazione per le tabelle correnti e di cronologia.

Per ridurre al minimo il blocco delle applicazioni normali e delle query utente, eliminare i dati in blocchi ridotti, con un ritardo durante l'esecuzione dello script di pulizia all'interno di una transazione. Anche se non esistono dimensioni ottimali per ogni blocco di dati da eliminare per tutti gli scenari, l'eliminazione di più di 10.000 righe in una singola transazione potrebbe comportare un effetto significativo.

La logica di pulizia è la stessa per ogni tabella temporale, quindi può essere automatizzata tramite una stored procedure generica pianificata per l'esecuzione periodica, per ogni tabella temporale per cui si vuole limitare la cronologia dei dati.

Il diagramma seguente illustra come organizzare la logica di pulizia per una singola tabella, in modo da ridurre l'impatto sui carichi di lavoro in esecuzione.

Diagram showing how your cleanup logic should be organized for a single table to reduce impact on the running workloads.

Ecco alcune indicazioni generali per l'implementazione del processo. Pianificare la logica di pulizia in modo che venga eseguita ogni giorno ed eseguire l'iterazione su tutte le tabelle temporali che necessitano della pulizia dei dati. Usare SQL Server Agent o uno strumento diverso per pianificare questo processo:

  • Eliminare i dati cronologici in ogni tabella temporale, a partire dal meno recente alle righe più recenti in diverse iterazioni in blocchi di piccole dimensioni ed evitare di eliminare tutte le righe in una singola transazione, come illustrato nel diagramma precedente.
  • Implementare ogni iterazione come chiamata di stored procedure generica che rimuove una parte di dati dalla tabella di cronologia (vedere l'esempio di codice seguente per questa procedura).
  • Calcolare il numero di righe da eliminare per una singola tabella temporale ogni volta che si chiama il processo. In base a questo e al numero di iterazioni che si desidera avere, determinare i punti di divisione dinamici per ogni chiamata di routine.
  • Pianificare un periodo di ritardo tra iterazioni per una singola tabella, per ridurre l'effetto sulle applicazioni che accedono alla tabella temporale.

Una stored procedure che elimina i dati per una singola tabella temporale potrebbe avere un aspetto analogo a quello del frammento di codice seguente. Verificare attentamente il codice e modificarlo prima di applicarlo all'ambiente specifico:

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO
CREATE PROCEDURE usp_CleanupHistoryData @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
AS
DECLARE @disableVersioningScript NVARCHAR(MAX) = '';
DECLARE @deleteHistoryDataScript NVARCHAR(MAX) = '';
DECLARE @enableVersioningScript NVARCHAR(MAX) = '';
DECLARE @historyTableName SYSNAME
DECLARE @historyTableSchema SYSNAME
DECLARE @periodColumnName SYSNAME

/*Generate script to discover history table name and end of period column for given temporal table name*/
EXECUTE usp_executesql
N'SELECT @hst_tbl_nm = t2.name,
      @hst_sch_nm = s2.name,
      @period_col_nm = c.name
  FROM sys.tables t1
  INNER JOIN sys.tables t2 ON t1.history_table_id = t2.object_id
  INNER JOIN sys.schemas s1 ON t1.schema_id = s1.schema_id
  INNER JOIN sys.schemas s2 ON t2.schema_id = s2.schema_id
  INNER JOIN sys.periods p ON p.object_id = t1.object_id
  INNER JOIN sys.columns c ON p.end_column_id = c.column_id AND c.object_id = t1.object_id
  WHERE t1.name = @tblName AND s1.name = @schName',
N'@tblName sysname,
    @schName sysname,
    @hst_tbl_nm sysname OUTPUT,
    @hst_sch_nm sysname OUTPUT,
    @period_col_nm sysname OUTPUT' @tblName = @temporalTableName,
@schName = @temporalTableSchema,
@hst_tbl_nm = @historyTableName OUTPUT,
@hst_sch_nm = @historyTableSchema OUTPUT,
@period_col_nm = @periodColumnName OUTPUT

IF @historyTableName IS NULL OR @historyTableSchema IS NULL OR @periodColumnName IS NULL
    THROW 50010, 'History table cannot be found. Either specified table is not system-versioned temporal or you have provided incorrect argument values.', 1;

/*Generate 3 statements that run inside a transaction:
  (1) SET SYSTEM_VERSIONING = OFF,
  (2) DELETE FROM history_table,
  (3) SET SYSTEM_VERSIONING = ON
  On SQL Server 2016, it is critical that (1) and (2) run in separate EXEC statements, or SQL Server generates the following error:
  Msg 13560, Level 16, State 1, Line XXX
  Cannot delete rows from a temporal history table '<database_name>.<history_table_schema_name>.<history_table_name>'.
*/

SET @disableVersioningScript = @disableVersioningScript
    + 'ALTER TABLE [' + @temporalTableSchema + '].[' + @temporalTableName
    + '] SET (SYSTEM_VERSIONING = OFF)'
SET @deleteHistoryDataScript = @deleteHistoryDataScript + ' DELETE FROM ['
    + @historyTableSchema + '].[' + @historyTableName + '] WHERE ['
    + @periodColumnName + '] < ' + '''' + CONVERT(VARCHAR(128), @cleanupOlderThanDate, 126) + ''''
SET @enableVersioningScript = @enableVersioningScript + ' ALTER TABLE ['
    + @temporalTableSchema + '].[' + @temporalTableName
    + '] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema
    + '].[' + @historyTableName + '], DATA_CONSISTENCY_CHECK = OFF )); '

BEGIN TRANSACTION
    EXEC (@disableVersioningScript);
    EXEC (@deleteHistoryDataScript);
    EXEC (@enableVersioningScript);
COMMIT;

Usare l'approccio ai criteri di conservazione della cronologia temporale

Si applica a: SQL Server 2017 (14.x) e versioni successive e database SQL di Azure.

La conservazione della cronologia temporale può essere configurata a livello di singola tabella, che consente agli utenti di creare criteri di invecchiamento flessibili. L'applicazione della conservazione della cronologia temporale è semplice e richiede l'impostazione di un solo parametro durante la creazione della tabella o la modifica dello schema.

Dopo aver definito i criteri di conservazione, il motore di database inizia a controllare regolarmente se sono presenti righe cronologiche idonee per la pulizia automatica dei dati. L'identificazione di righe corrispondenti e la loro rimozione dalla tabella di cronologia si verificano in modo trasparente nell'attività in background pianificata ed eseguita dal sistema. La condizione di validità per le righe della tabella di cronologia viene controllata in base alla colonna che rappresenta la fine del SYSTEM_TIME periodo. Se ad esempio il periodo di conservazione impostato è pari a sei mesi, le righe di tabella idonee per la rimozione soddisfano la condizione seguente:

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

Nell'esempio precedente la ValidTo colonna corrisponde alla fine del SYSTEM_TIME periodo.

Come si configurano i criteri di conservazione?

Prima di configurare i criteri di conservazione per una tabella temporale, verificare se la conservazione cronologica temporale è abilitata a livello di database:

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

Il flag is_temporal_history_retention_enabled di database è impostato su ON per impostazione predefinita, ma gli utenti possono modificarlo con l'istruzione ALTER DATABASE . Questo valore viene impostato automaticamente su dopo un'operazione di ripristino temporizzato.This value is automatically set to OFF after a point-in-time restore (PITR). Per abilitare la pulizia della conservazione della cronologia temporale per il database, eseguire l'istruzione seguente:

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

I criteri di conservazione vengono configurati durante la creazione della tabella specificando il valore per il HISTORY_RETENTION_PERIOD parametro :

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

È possibile specificare il periodo di conservazione usando unità di tempo diverse: DAYS, WEEKS, MONTHSe YEARS. Se HISTORY_RETENTION_PERIOD viene omesso, INFINITE si presuppone la conservazione. È anche possibile usare INFINITE la parola chiave in modo esplicito.

In alcuni scenari, potrebbe essere necessario configurare la conservazione dopo la creazione della tabella o modificare il valore configurato in precedenza. In tal caso, usare l'istruzione ALTER TABLE :

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

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:

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?

Il processo di pulizia dipende dal layout dell'indice della tabella di cronologia. Solo le tabelle di cronologia con un indice cluster (albero B+ o columnstore) possono avere criteri di conservazione limitati configurati. Viene creata un'attività in background per eseguire la pulizia dei dati obsoleti per tutte le tabelle temporali con periodo di conservazione finito. La logica di pulizia per l'indice rowstore cluster (albero B+) elimina le righe obsolete in gruppi più piccoli (fino a 10.000 unità), riducendo il carico di lavoro del log di database e del sottosistema I/O. Anche se la logica di pulizia usa l'indice albero B+ richiesto, l'ordine delle eliminazioni per le righe precedenti al periodo di conservazione non può essere garantito con fermezza. Di conseguenza, non dipendere dall'ordine di pulizia nelle applicazioni.

L'attività di pulizia per il columnstore cluster rimuove interi gruppi (ognuno in genere costituito da un milione di righe) in una sola operazione. Questo approccio è molto efficiente, soprattutto quando vengono generati dati cronologici a ritmi elevati.

Screenshot of clustered columnstore retention.

Un'ottima compressione dei dati e una pulizia efficiente dei dati conservati fanno dell'indice columnstore cluster la soluzione ottimale per gli scenari in cui il carico di lavoro genera rapidamente volumi elevati di dati cronologici. Questo scenario è tipico di carichi di lavoro di elaborazione transazioni intensiva, che usano le tabelle temporali per il rilevamento e il controllo delle modifiche, l'analisi dei trend o l'inserimento di dati IoT.

Per altre informazioni, vedere Gestire i dati cronologici nelle tabelle temporali con i criteri di conservazione.