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

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure AzureSQL Managed InstanceSQL database in Microsoft Fabric

Una tabella temporale versionata al sistema conserva ogni versione precedente di ogni riga nella sua tabella storica. La tabella di cronologia potrebbe aumentare la dimensione del tuo database più rispetto alle tabelle normali nelle seguenti condizioni:

• Conservi i dati storici per un lungo periodo di tempo.
• Hai un pattern di modifica dei dati con prevalenza di aggiornamenti o eliminazioni.

Una grande tabella di cronologia in continua crescita potrebbe diventare un problema, sia per i costi di archiviazione sia per la tassa sulle prestazioni che impone alle query temporali. Sviluppare una politica di conservazione dei dati per la tabella storica è una parte importante della pianificazione e gestione del ciclo di vita di ogni tabella temporale.

Pianifica una politica di conservazione dei dati

Per gestire la conservazione dei dati delle tabelle temporali, si determina prima il periodo di conservazione richiesto per ciascuna tabella temporale. La tua politica di retention, nella maggior parte dei casi, dovrebbe far parte della logica di business dell'applicazione che utilizza le tabelle temporali. Ad esempio, le applicazioni in audit dei dati e scenari di viaggio nel tempo hanno requisiti fermi su quanto tempo i dati storici devono essere disponibili per le query online.

Dopo aver determinato il periodo di conservazione dei dati, sviluppa un piano per gestire i dati storici. Occorre decidere come e dove archiviare i dati cronologici e come eliminare i dati cronologici precedenti ai requisiti di conservazione.

Ogni approccio in questo articolo agisce sulla colonna che corrisponde alla fine del periodo nella tabella corrente, che è la ValidTo colonna negli esempi che seguono. 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 (DAY, -30, SYSUTCDATETIME()) corrisponde a dati storici che hanno più di 30 giorni fa.

Scegli uno dei seguenti approcci per agire su quelle righe:

Gli esempi di partizionamento e di pulizia personalizzata in questo articolo utilizzano gli esempi tratti dall'articolo Creare una tabella temporale con controllo delle versioni gestito dal sistema.

Usa una politica di conservazione della cronologia temporale

Si applica a: SQL Server 2017 (14.x) e versioni successive, database SQL di Azure, Istanza gestita di SQL di Azure e database SQL in Microsoft Fabric.

Puoi configurare la conservazione della cronologia temporale a livello di tavola individuale, il che ti permette di creare politiche di invecchiamento flessibili. Per abilitare la ritenzione temporale, imposta HISTORY_RETENTION_PERIOD durante la creazione della tabella o un cambiamento dello schema.

Dopo aver definito la policy di retention, il motore di database esegue un compito di background programmato che trova e rimuove trasparentmente le righe storiche il cui valore di fine periodo è più vecchio del periodo di conservazione.

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 del database è predefinito ON, ma puoi cambiarlo usando l'istruzione ALTER DATABASE . Il motore di database lo imposta automaticamente anche dopo un'operazione di ripristino temporizzato (PITR), come descritto in Considerazioni sul ripristino temporizzato. Per impostare la pulizia della cronologia temporale per il database eseguire la seguente istruzione. Sostituisci <myDB> con il database che vuoi modificare:

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Puoi configurare la politica di retention durante la creazione della tabella specificando un 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
    )
);

Con questa politica in vigore, le righe in dbo.WebsiteUserInfoHistory possono essere rimosse quando soddisfano la seguente condizione:

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

Puoi specificare il periodo di conservazione in DAYS, WEEKS, MONTHS, o YEARS. Se ometti HISTORY_RETENTION_PERIOD, la ritenzione predefinita è INFINITE. È anche possibile usare esplicitamente la parola chiave INFINITE.

In alcuni scenari, potresti voler configurare la ritenzione dopo la creazione della tabella o cambiare il valore precedentemente configurato. 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 dei criteri di conservazione, usare il seguente esempio. Questa interrogazione unisce il flag di abilitazione della conservazione temporale dei dati a livello di database con i periodi di conservazione delle 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 AS T1
    OUTER APPLY (
        SELECT is_temporal_history_retention_enabled
        FROM sys.databases
        WHERE name = DB_NAME()
) AS DB
    LEFT OUTER JOIN sys.tables AS T2
        ON T1.history_table_id = T2.object_id
WHERE T1.temporal_type = 2;

In che modo vengono eliminate le righe obsolete dal motore del database

Il processo di pulizia dipende dal layout dell'indice della tabella di cronologia. Puoi configurare un criterio di conservazione finito solo nelle tabelle della cronologia con un indice rowstore clusterizzato (B-tree) o un indice columnstore clusterizzato. Un compito in background esegue la pulizia dei dati datati per tutte le tabelle temporali con un periodo di conservazione finito.

Indice di rowstore B-tree

L'indice raggruppato del rowstore deve iniziare con la colonna corrispondente alla fine del SYSTEM_TIME periodo. Se tale indice non esiste, non puoi configurare un periodo di conservazione finito:

Msg 13765, Level 16, State 1
Setting finite retention period failed on system-versioned temporal table
'dbo.WebsiteUserInfo' because the history table '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.

La tabella della cronologia predefinita ha già un indice clusterizzato conforme. Se provi a inserire quell'indice su una tabella di cronologia con un periodo di ritenzione finito, l'operazione fallisce con il seguente errore:

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 logica di pulizia per l'indice clusterizzato rowstore elimina le righe vecchie in blocchi più piccoli (fino a 10.000), riducendo la pressione sul log del database e sul sottosistema I/O. Sebbene la logica di pulizia utilizzi l'indice richiesto dell'albero B, non può garantire l'ordine di cancellazione per le righe più vecchie del periodo di conservazione. Evitare qualsiasi dipendenza dall'ordine di pulizia nelle applicazioni.

Indice columnstore clusterizzato

Il compito di pulizia per il deposito a colonne clusterizzato rimuove interi gruppi di righe contemporaneamente. Ogni gruppo di righe contiene tipicamente un milione di righe. Questo metodo è più efficiente, soprattutto quando il carico di lavoro genera dati storici a un ritmo elevato.

La compressione dei dati e la pulizia dei dati in base ai criteri di conservazione rendono l'indice columnstore clusterizzato una buona scelta per gli scenari in cui il carico di lavoro genera rapidamente una notevole quantità di dati storici. Questo schema è tipico per carichi di lavoro di elaborazione transazionale intensivi che utilizzano tabelle temporali per il tracciamento delle modifiche e l'auditing, l'analisi delle tendenze o l'ingestione di dati tramite Internet delle Cose (IoT).

La pulizia dell’indice columnstore clusterizzato funziona in modo ottimale quando le righe storiche arrivano in ordine crescente (ordinate in base alla colonna di fine periodo). Questa condizione è sempre vera quando solo il SYSTEM_VERSIONING meccanismo popola la tabella storica. Se le righe nella tabella di cronologia non sono ordinate per colonna di fine periodo (cosa che potrebbe accadere migrando dati storici esistenti), ricrea l'indice clusterizzato columnstore sopra un indice di rowstore B-tree ordinato correttamente per ottenere prestazioni ottimali.

Evitare di ricostruire l'indice clusterizzato columnstore su una tabella storica con un periodo di conservazione finito, perché ricostruire potrebbe modificare l'ordine dei gruppi righe che l'operazione di versionamento del sistema impone naturalmente. Se devi ricostruire l'indice columnstore clusterizzato nella tabella di cronologia, ricrealo a partire da un indice B-tree conforme per preservare l'ordinamento dei gruppi di righe necessario per la regolare pulizia dei dati. Adotta lo stesso approccio se crei una tabella temporale con una tabella di cronologia esistente che ha un indice clusterizzato a colonne senza ordine garantito dei dati:

/* 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 the clustered columnstore index */
CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
    ON WebsiteUserInfoHistory WITH (DROP_EXISTING = ON);

Quando si configura un periodo di conservazione finito per una tabella di cronologia con un indice di columnstore clusterizzato, non si possono creare indici B-tree non clusterizzati su quella tabella:

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

L'affermazione precedente fallisce con il seguente errore:

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.

Eseguire query sulle tabelle con criteri di conservazione

Tutte le query sulla tabella temporale filtrano automaticamente le righe storiche che corrispondono alla politica di retention finita, per evitare risultati imprevedibili e incoerenti. Il compito di pulizia elimina righe vecchie in qualsiasi momento e in ordine arbitrario.

Lo screenshot seguente mostra il piano di query per una query di base. Questo esempio presuppone un periodo di conservazione di unMONTH per la tabella WebsiteUserInfo:

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Il piano di query include un filtro aggiuntivo sulla colonna di fine periodo (ValidTo) nell'operatore Clustered Index Scan (evidenziato nell'immagine seguente) nella tabella storica.

Se interroghi direttamente la tabella storica, potresti vedere righe più vecchie del periodo di conservazione specificato, ma senza alcuna garanzia di risultati ripetibili della ricerca. Lo screenshot seguente mostra il piano di query per una query nella tabella di cronologia senza filtri aggiuntivi:

Non affidarti alla logica aziendale che legge la tabella storica oltre il periodo di conservazione, perché potresti ottenere risultati incoerenti o inaspettati. Usa query temporali con la FOR SYSTEM_TIME clausola per analizzare i dati nelle tabelle temporali.

Considerazioni sul ripristino in un momento specifico

Quando ripristini un database a un determinato momento temporale, il nuovo database ha la ritenzione temporale disabilitata a livello di database (is_temporal_history_retention_enabled impostata su OFF). Questo comportamento ti permette di ispezionare le righe storiche più vecchie del periodo di conservazione prima che il compito di pulizia le rimuova. Per riprendere la pulizia automatica del database ripristinato, imposta TEMPORAL_HISTORY_RETENTION indietro su ON.

Uso della partizionazione delle tabelle

Le tabelle e gli indici partizionati possono rendere più gestibili e scalabili le tabelle di grandi dimensioni. Utilizzando l'approccio di partizionamento delle tabelle, puoi implementare una pulizia personalizzata dei dati o un archivio 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.

Usa la partizionazione delle tabelle per implementare una finestra scorrevole che sposti la parte più antica dei dati storici dalla tabella storica e mantiene costante la dimensione della parte conservata per età. Una finestra scorrevole mantiene i dati nella tabella storica pari al periodo di conservazione richiesto. La tabella della cronologia supporta la sostituzione dei dati mentre SYSTEM_VERSIONING è ON, il che significa che puoi pulire una parte dei dati della cronologia senza introdurre una finestra di manutenzione o bloccare i tuoi carichi di lavoro abituali.

Una finestra scorrevole richiede due serie di compiti:

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

Per questo esempio, supponiamo di voler conservare i dati storici per sei mesi e di tenere ogni mese di dati in una partizione separata. Inoltre, supponiamo che tu abbia attivato il controllo delle versioni di sistema nel settembre 2023.

Un'attività di configurazione del partizionamento crea la configurazione iniziale del partizionamento per la tabella di cronologia. Per questo esempio, crei lo stesso numero di partizioni della dimensione della finestra scorrevole, in mesi, più una partizione vuota in più. Questa configurazione garantisce che il sistema possa memorizzare correttamente i nuovi dati quando inizi il compito ricorrente di manutenzione delle partizioni. Garantisce inoltre di non dividere mai partizioni che contengono dati, evitando così costosi movimenti di dati. Definisci la funzione di partizione con RANGE LEFT invece di RANGE RIGHT. Per ulteriori informazioni, vedi Considerazioni sulle prestazioni con partizionamento delle tabelle più avanti in questo articolo.

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

La prima e l'ultima partizione sono aperte rispettivamente ai confini inferiore e superiore, per garantire che ogni nuova riga abbia una partizione di destinazione indipendentemente dal valore nella colonna di partizionamento. Col tempo, nuove righe nella tabella della storia finiscono in partizioni superiori. Quando la sesta partizione si riempie, si raggiunge il periodo di conservazione previsto. A questo punto, avvia per la prima volta il compito ricorrente di manutenzione della partizione. Programmalo per farlo funzionare periodicamente, una volta al mese in questo esempio.

L'immagine seguente illustra i compiti ricorrenti di manutenzione delle partizioni.

Ogni esecuzione del compito di manutenzione ricorrente esegue i seguenti passaggi:

1. SWITCH OUT: Crea una tabella di staging e poi scambia una partizione tra la tabella storica e la tabella di staging usando l'affermazione ALTER TABLE con l'argomento SWITCH PARTITION .

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

   Dopo il cambio di partizione, puoi opzionalmente archiviare i dati dalla tabella di staging e poi eliminare o troncare la tabella di staging per prepararti al prossimo ciclo di manutenzione.

2. MERGE RANGE: Unisci la partizione 1 vuota con la partizione 2 usando l'istruzione ALTER PARTITION FUNCTION con MERGE RANGE. Quando usi questa funzione per rimuovere il confine più basso, di fatto unisci la partizione 1 vuota con quella precedente 2 per formare una nuova partizione 1. Anche le altre partizioni cambiano di fatto il loro ordine.

3. SPLIT RANGE: Creare una nuova partizione 7 vuota usando l'istruzione ALTER PARTITION FUNCTION con SPLIT RANGE. Quando usi questa funzione per aggiungere un nuovo limite superiore, crei di fatto una partizione separata per il mese successivo.

Usare Transact-SQL per creare partizioni nella tabella di cronologia

Usa il seguente script Transact-SQL per creare la funzione di partizione, lo schema di partizione e ricreare l'indice clusterizzato per essere allineato alla partizione con lo schema. 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, si sostituisce la partizione di settembre 2023 usando MERGE RANGE, e poi aggiungi 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 to switch 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 to switch 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;

Tuttavia, la soluzione ottimale è eseguire regolarmente uno script Transact-SQL generico ogni mese senza modifiche. Puoi generalizzare lo script precedente per agire sui parametri forniti (il confine inferiore che deve essere unito e il nuovo confine creato dalla divisione della partizione). Per evitare di creare una tabella di staging ogni mese, creala in anticipo e riutilizzala modificando il vincolo di controllo per farlo corrispondere alla partizione che si sostituisce. Per maggiori informazioni, vedi come automatizzare completamente lo scenario della finestra scorrevole.

Considerazioni sulle prestazioni con il partizionamento delle tabelle

Esegui le operazioni MERGE RANGE e SPLIT RANGE in modo da evitare lo spostamento dei dati, perché lo spostamento dei dati può causare un notevole overhead in termini di prestazioni. Per altre informazioni, vedere Modificare una funzione di partizione.

Quando crei la 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 quella partizione non è vuota, MERGE RANGE sposta i dati nella partizione risultante.

L'elenco seguente descrive le opzioni RANGE LEFT e RANGE RIGHT:

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

• RANGE LEFT caso: Il confine più basso della partizione appartiene alla partizione 1, che è vuota (dopo lo scambio di partizione), quindi MERGE RANGE non causa alcun movimento di dati.

• RANGE RIGHT caso: Il limite inferiore della partizione appartiene alla partizione 2, che non è vuota perché la sostituzione svuota solo la partizione 1. In questo caso, MERGE RANGE causa il movimento dei dati, spostando i dati da una partizione 2 all'altra 1. Per evitare questo movimento dei dati, RANGE RIGHT nello scenario della finestra scorrevole è necessario avere una partizione 1, che è sempre vuota. Questo requisito significa che se usi RANGE RIGHT, dovresti creare e mantenere una partizione extra rispetto al RANGE LEFT caso.

Conclusione: La gestione delle partizioni è più semplice quando si usa RANGE LEFT in una partizione scorrevole ed evita il movimento 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 e ora.

Usa uno script di pulizia personalizzato

Quando una politica di retention non è disponibile per la tua tabella e la partizionazione delle tabelle non è praticabile, puoi eliminare i dati dalla tabella di cronologia usando uno script di pulizia personalizzato. Questo processo è possibile solo quando SYSTEM_VERSIONING = OFF. Per evitare incoerenza nei dati, esegui la pulizia durante una finestra di manutenzione (quando i carichi di lavoro che modificano i dati non sono attivi), oppure all'interno di una transazione (bloccando di fatto altri carichi di lavoro). Questa operazione richiede l'autorizzazione CONTROL sulla tabella corrente e sulla tabella di cronologia.

La logica di pulizia è la stessa per ogni tabella temporale, quindi puoi automatizzarla tramite una procedura memorizzata generica. Usa SQL Server Agent o uno strumento diverso per programmare la procedura in esecuzione quotidiana, iterando su ogni tabella temporale per cui vuoi limitare la cronologia dei dati.

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

Ecco alcune linee guida di alto livello per implementare il processo:

• Elimina i dati storici in ogni tabella temporale in diverse iterazioni di piccoli blocchi. Inizia dalle righe più vecchie e passa a quella più recente. Evita di eliminare tutte le righe in una singola transazione, come mostra il diagramma precedente. Anche se nessuna singola dimensione di blocco funziona per tutti gli scenari, eliminare più di 10.000 righe in una singola transazione potrebbe comportare una penalità significativa.

• Implementa ogni iterazione come invocazione di una procedura memorizzata generica, che rimuove una porzione di dati dalla tabella storica.

• Calcolare il numero di righe da eliminare per una singola tabella temporale ogni volta che si chiama il processo. In base al risultato e al numero di iterazioni desiderate, determina i punti di divisione dinamici per ogni invocazione della procedura.

• Pianifica un ritardo tra le iterazioni per una singola tabella, per ridurre l'effetto sulle applicazioni che accedono alla tabella temporale.

La procedura memorizzata successiva elimina i dati per una singola tabella temporale. Scopre la tabella della storia e la colonna di fine periodo dalle viste del catalogo, e poi esegue tre istruzioni all'interno di una transazione: SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, e SET SYSTEM_VERSIONING = ON. Rivedi attentamente questo codice e correggilo prima di applicarlo nel tuo ambiente.

In SQL Server 2016 (13.x), i primi due passaggi devono essere eseguiti in istruzioni EXECUTE separate oppure SQL Server genera un errore simile all'esempio seguente:

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>'.

DROP PROCEDURE IF EXISTS usp_CleanupHistoryData;
GO

CREATE PROCEDURE usp_CleanupHistoryData (
    @temporalTableSchema SYSNAME,
    @temporalTableName SYSNAME,
    @cleanupOlderThanDate DATETIME2
)
AS
DECLARE @disableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @deleteHistoryDataScript AS NVARCHAR (MAX) = '';
DECLARE @enableVersioningScript AS NVARCHAR (MAX) = '';
DECLARE @historyTableName AS SYSNAME;
DECLARE @historyTableSchema AS SYSNAME;
DECLARE @periodColumnName AS 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 AS t1
         INNER JOIN sys.tables AS t2
             ON t1.history_table_id = t2.object_id
         INNER JOIN sys.schemas AS s1
             ON t1.schema_id = s1.schema_id
         INNER JOIN sys.schemas AS s2
             ON t2.schema_id = s2.schema_id
         INNER JOIN sys.periods AS p
             ON p.object_id = t1.object_id
         INNER JOIN sys.columns AS 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;

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;
    EXECUTE (@disableVersioningScript);
    EXECUTE (@deleteHistoryDataScript);
    EXECUTE (@enableVersioningScript);
COMMIT TRANSACTION;

Contenuti correlati

• Tabelle temporali
• Inizia a utilizzare le tabelle temporali versionate dal sistema
• Verifiche di coerenza del sistema delle tabelle temporali
• Partizioni con tabelle temporali
• Considerazioni e limitazioni delle tabelle temporali
• Sicurezza di una tabella temporale
• Tabelle temporali versionate a livello di sistema con tabelle ottimizzate per la memoria
• Funzioni e viste per i metadati delle tabelle temporali