Condividi tramite

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

  • Articolo

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

Le tabelle temporali con controllo delle versioni di sistema consentono alla tabella di cronologia di aumentare le dimensioni del database in modo superiore rispetto alle tabelle normali, in particolare se si verificano le 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ò costituire un problema, a causa dei semplici costi di archiviazione e dell'impatto sulle prestazioni delle 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 nello 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. Per la gestione dei dati cronologici nella tabella di cronologia temporale sono disponibili gli approcci seguenti:

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 relativo alla fine del periodo per ogni riga determina il momento in cui la versione della riga diventa 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 con partizionamento delle tabelle

Le tabelle e gli indici partizionati possono rendere più gestibili e scalabili le 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 a livello di prestazioni in caso di query su tabelle temporali relative a un subset di cronologia dei dati tramite l'eliminazione delle partizioni.

Il partizionamento delle tabelle consente di implementare una finestra temporale scorrevole per spostare le parti più vecchie dalla tabella di cronologia e mantenere costanti le dimensioni della parte conservata in termini di età, mantenendo i dati della tabella di cronologia uguali al periodo di conservazione necessario. L'operazione di disattivazione dei dati dalla tabella di cronologia è supportata se SYSTEM_VERSIONING è ON, ovvero è possibile pulire una parte dei dati di cronologia senza introdurre una finestra di manutenzione o bloccare i carichi di lavoro normali.

Nota

Per eseguire la disattivazione delle partizioni, è necessario che l'indice cluster sulla tabella di cronologia sia allineato allo schema di partizionamento, ovvero che contenga ValidTo. La tabella di cronologia predefinita creata dal sistema contiene un indice cluster che include le colonne ValidTo e ValidFrom, ottimali per il partizionamento, l'inserimento di nuovi dati di cronologia e le query temporali tipiche. Per altre informazioni, vedere Tabelle temporali.

In una finestra temporale scorrevole è necessario eseguire due set di attività:

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

Si supponga, ad esempio, che si vogliano conservare i dati cronologici per 6 mesi e inserire i dati di ogni mese in una partizione separata. Si presupponga anche di avere attivato il controllo delle versioni di 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 viene creato un numero di partizioni corrispondente alle dimensioni della finestra temporale scorrevole, in mesi, oltre a una partizione aggiuntiva vuota già pre-preparata, come illustrato di seguito in questo articolo. Questa configurazione assicura che il sistema archivia correttamente nuovi dati quando viene avviata per la prima volta l'attività ricorrente di manutenzione della partizione e assicura che le partizioni non verranno mai suddivise con i dati, per evitare spostamenti costosi dei dati. È consigliabile eseguire questa attività usando Transact-SQL con lo script di esempio che segue in questo articolo.

La figura seguente illustra la configurazione iniziale del partizionamento per la conservazione di 6 mesi di dati.

Diagramma che mostra la configurazione iniziale del partizionamento per conservare sei mesi di dati.

Nota

Vedere Considerazioni sulle prestazioni con il partizionamento delle tabelle più avanti in questo articolo per informazioni sulle conseguenze dell'uso di RANGE LEFT invece di RANGE RIGHT sulle prestazioni durante la configurazione del partizionamento.

La prima e l'ultima partizione sono aperte rispettivamente sul limite inferiore e superiore, per garantire che ogni nuova riga abbia una partizione di destinazione, indipendentemente dal valore della colonna di partizionamento. Con il passare del tempo, le nuove righe della tabella di cronologia vengono inserite in partizioni superiori. Quando la sesta partizione viene riempita, si raggiunge il limite del periodo di conservazione specificato. 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.

La figura seguente illustra le attività ricorrenti di manutenzione della partizione. Vedere la procedura dettagliata più avanti in questa sezione.

Diagramma che mostra le attività ricorrenti di manutenzione della partizione.

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 l'esempio C relativo al cambio di partizioni tra tabelle.

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

    Dopo il cambio di partizione, è possibile archiviare facoltativamente i dati dalla tabella di staging e quindi eliminare o troncare la tabella di staging in modo da essere pronti per quando sarà necessario eseguire di nuovo questa attività ricorrente di manutenzione della partizione.

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

  3. SPLIT RANGE: Creare una nuova partizione 7 vuota usando l'istruzione 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 di Transact-SQL seguente per creare la funzione di partizione e ricreare l'indice cluster in modo che sia allineato a livello di partizioni con lo schema delle partizioni e le partizioni. Per questo esempio viene creato una finestra temporale scorrevole di sei mesi con partizioni mensili a partire dal mese di 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 la manutenzione di partizioni in uno scenario con finestra temporale scorrevole

Usare lo script di Transact-SQL seguente per la manutenzione delle partizioni nello scenario con finestra temporale scorrevole. Per questo esempio viene disattivata la partizione per il mese di settembre 2023 mediante MERGE RANGE e quindi verrà aggiunta una nuova partizione per il mese di marzo 2024 mediante 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 processo di manutenzione mensile regolare:

  1. Nel passaggio (1) creare una nuova tabella di staging per il mese da rimuovere. Il mese successivo in questo esempio è ottobre.
  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) eseguire l'istruzione SWITCH per la partizione 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 la rimozione 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 la rimozione 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 in modo che reagisca ai parametri specificati, ovvero un limite inferiore da unire e un nuovo limite che verrà creato con la suddivisione della 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 temporale scorrevole può essere completamente automatizzata usando uno script Transact-SQL.

Considerazioni sulle prestazioni con il partizionamento delle tabelle

È importante eseguire le operazioni MERGE e SPLIT RANGE per evitare qualsiasi spostamento di dati, perché lo spostamento di dati può provocare un overhead 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.

Di seguito una spiegazione visiva del significato delle opzioni RANGE LEFT e RANGE RIGHT:

Diagramma che mostra le opzioni RANGE LEFT e RANGE RIGHT.

Quando si definisce una funzione di partizione come RANGE LEFT, i valori specificati corrispondono ai 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 che è il risultato dell'operazione MERGE RANGE.

In uno scenario con finestra temporale scorrevole, si rimuove sempre il limite inferiore della partizione.

  • Caso RANGE LEFT: il limite inferiore della partizione appartiene alla partizione 1, che è vuota, dopo il cambio di partizioni, pertanto MERGE RANGE non provocherà alcuno spostamento di dati.
  • Caso RANGE RIGHT: 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 2 vengono spostati nella partizione 1). Per evitare questo problema, è necessario che RANGE RIGHT nello scenario con finestra temporale scorrevole abbia la partizione 1, che è sempre vuota. Ciò significa che se si usa RANGE RIGHT, è necessario creare e gestire una partizione aggiuntiva rispetto al caso RANGE LEFT.

Conclusione: l'uso di RANGE LEFT nella partizione con finestra temporale scorrevole è molto più semplice per la gestione delle partizioni e consente di evitare lo spostamento dei dati. La definizione dei limiti delle partizioni con RANGE RIGHT risulta tuttavia leggermente più semplice, perché non è necessario gestire i problemi di controllo data/ora.

Usare l'approccio con script di pulizia personalizzato

Nei casi in cui il partizionamento delle tabelle non è un’opzione valida, un altro approccio consiste nell'eliminare i dati dalla tabella di cronologia usando uno script di pulizia personalizzato. L'eliminazione dei dati dalla tabella di cronologia è possibile solo se SYSTEM_VERSIONING = OFF. Per evitare l'incoerenza dei dati, eseguire una pulizia durante la finestra di manutenzione, quando i carichi di lavoro che modificano i dati non sono attivi, oppure entro una transazione, bloccando effettivamente altri carichi di lavoro. Questa operazione richiede l'autorizzazione CONTROL sulla tabella corrente e sulla tabella 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 avere un effetto significativo.

La logica di pulizia è uguale per ogni tabella temporale, quindi è possibile automatizzare la pulizia tramite una stored procedure generica pianificata per l'esecuzione periodica per ogni tabella temporale di 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.

Diagramma che mostra come organizzare la logica di pulizia per una singola tabella, in modo da ridurre l'impatto sui carichi di lavoro in esecuzione.

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 dalle righe più vecchie fino alle righe più recenti in diverse iterazioni in piccoli blocchi ed evitare di eliminare tutte le righe in una singola transazione, come illustrato nel diagramma precedente.
  • Implementare ogni iterazione come una chiamata della stored procedure generica che rimuove una parte di dati dalla tabella di cronologia. Per informazioni su questa procedura, vedere l'esempio di codice seguente.
  • Calcolare il numero di righe da eliminare per una singola tabella temporale ogni volta che si chiama il processo. In base a tale valore e al numero di iterazioni desiderato, determinare i punti di suddivisione dinamici per ogni chiamata di procedura.
  • Pianificare un periodo di ritardo tra le iterazioni per una singola tabella, in modo da 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 sp_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 con criteri di conservazione della cronologia temporale

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

La conservazione della cronologia temporale può essere configurata a livello di singola tabella. Ciò consente agli utenti di creare criteri di aging 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 che sono stati definiti i criteri di conservazione, il motore di database inizia a verificare periodicamente la presenza di righe di cronologia con i requisiti 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 cronologica delle righe della tabella viene verificata in base alla colonna che rappresenta la fine del periodo SYSTEM_TIME. 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 colonna ValidTo corrisponde alla fine del periodo SYSTEM_TIME.

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 di database is_temporal_history_retention_enabled è impostato su ON per impostazione predefinita, ma gli utenti possono modificarlo con l'istruzione ALTER DATABASE. Questo valore viene impostato automaticamente su OFF dopo l'operazione di ripristino temporizzato. Per impostare la pulizia 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 un valore per il parametro 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
    )
);

È possibile specificare il periodo di conservazione con unità di tempo diverse: DAYS, WEEKS, MONTHS, e YEARS. Se HISTORY_RETENTION_PERIOD è omesso, si applicherà il periodo di conservazione INFINITE. È anche possibile usare esplicitamente la parola chiave INFINITE.

In alcuni scenari risulta utile configurare la conservazione dopo la creazione della tabella o per modificare un 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 nelle tabelle di cronologia con un indice cluster (albero B+ o columnstore) è possibile configurare criteri di conservazione finiti. 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 di eliminazione delle righe con durata superiore al periodo di conservazione non può essere garantito con certezza. Di conseguenza evitare qualsiasi dipendenza 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 della conservazione columnstore cluster.

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 criteri di conservazion.

Contenuto correlato