Partage via

Gérer la rétention des données d'historique dans les tables temporelles avec contrôle de version par le système

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Base de données Azure SQL Azure SQL Managed Instance

En ce qui concerne les tables temporelles avec contrôle de version par le système, la table d’historique peut augmenter la taille de votre la base de données davantage que les tables ordinaires, en particulier dans les conditions suivantes :

  • conservation des données d’historique sur une longue période ;
  • existence d’un modèle de mise à jour ou de suppression des modifications des données lourd.

Une table d’historique volumineuse et sans cesse croissante peut devenir un problème en raison des coûts de stockage purs et de l’imposition d’une taxe de performance sur l’interrogation temporelle. Le développement d’une stratégie de rétention des données pour gérer les données dans la table d’historique est un aspect important de la planification et de la gestion du cycle de vie de chaque table temporelle.

Gestion de la rétention de données pour la table d’historique

La gestion de la rétention de données pour les tables temporelles consiste en premier lieu à déterminer la période de rétention nécessaire pour chaque table temporelle. Dans la plupart des cas, votre stratégie de rétention doit faire partie de la logique métier de l’application utilisant les tables temporelles. Par exemple, les applications s’inscrivant dans des scénarios d’audit de données et de voyage dans le temps ont des exigences strictes concernant la durée pendant laquelle les données d’historique doivent être disponibles pour l’interrogation en ligne.

Une fois que vous avez déterminé votre période de rétention des données, vous devez développer un plan de gestion des données historiques. Décidez comment et où stocker vos données historiques et comment supprimer des données historiques antérieures à vos exigences de rétention. Les approches suivantes permettent de gérer les données d’historique dans la table d’historique temporelle :

Pour chacune de ces méthodes, la logique de migration ou de nettoyage des données d’historique est basée sur la colonne qui correspond à la fin de période dans la table active. La valeur de fin de période de chaque ligne détermine le moment où la version de la ligne devient fermée, c’est-à-dire où elle arrive dans la table d’historique. Par exemple, la condition ValidTo < DATEADD (DAYS, -30, SYSUTCDATETIME ()) spécifie que les données d’historique de plus d’un mois doivent être supprimées ou déplacées de la table d’historique.

Les exemples de cet article utilisent la fonction Créer une table temporelle versionnée par le système.

Utilisation de la méthode de partitionnement de table

Les tables et index partitionnés peuvent rendre les grandes tables plus faciles à gérer et plus évolutives. Avec l'approche du partitionnement des tables, vous pouvez mettre en œuvre un nettoyage personnalisé des données, ou un archivage hors ligne, sur la base d'une condition temporelle. Le partitionnement de table s’avère aussi bénéfique en termes de performances quand il s’agit d’interroger les tables temporelles d’un sous-ensemble d’historique de données, grâce à l’élimination de partition.

Avec le partitionnement de table, vous pouvez implémenter une approche de fenêtre glissante pour déplacer une partie plus ancienne des données d’historique de la table d’historique et ainsi stabiliser la taille de la partie conservée en termes d’ancienneté. Une fenêtre glissante conserve les données de la table d’historique égale à la période de rétention requise. L’opération d’extraction des données de la table d’historique est prise en charge quand SYSTEM_VERSIONING est ON, ce qui signifie que vous pouvez nettoyer une partie des données d’historique sans introduire de fenêtres de maintenance ni bloquer vos charges de travail normales.

Remarque

Pour procéder au basculement de partition, votre index cluster sur la table d’historique doit être aligné sur le schéma de partitionnement (il doit contenir ValidTo). La table d’historique par défaut créée par le système contient un index cluster qui inclut les colonnes ValidTo et ValidFrom, ce qui est optimal pour le partitionnement, l’insertion de nouvelles données d’historique et l’interrogation temporelle classique. Pour plus d’informations, voir Tables temporelles.

Dans une approche de fenêtre glissante, il y a deux ensembles de tâches à effectuer :

  • une tâche de configuration du partitionnement ;
  • des tâches de maintenance de partition périodiques.

Pour illustrer cela, supposons que vous voulez conserver les données d’historique pendant six mois et que vous voulez conserver les données de chaque mois dans une partition distincte. Par ailleurs, supposons que vous avez activé le contrôle de version par le système en septembre 2023.

Une tâche de configuration du partitionnement permet de créer la configuration initiale du partitionnement de la table d’historique. Pour cet exemple, vous créez le même nombre de partitions que la taille de fenêtre glissante, exprimée en mois, plus une partition vide préalablement préparée (explications plus loin dans cet article). Cette configuration est l’assurance que le système pourra stocker correctement les nouvelles données quand la tâche de maintenance périodique sera lancée pour la première fois. De même, elle garantit que les partitions ne seront jamais fractionnées avec des données pour éviter des déplacements des données coûteux. Vous devez effectuer cette tâche à l’aide de Transact-SQL en utilisant le script d’exemple présenté plus loin dans cet article.

Le schéma suivant illustre la configuration initiale du partitionnement visant à conserver six mois de données.

Diagramme montrant la configuration initiale du partitionnement visant à conserver six mois de données.

Remarque

Pour connaître les implications en termes de performances de l'utilisation de RANGE LEFT par rapport à RANGE RIGHT lors de la configuration du partitionnement, reportez-vous à la section Considérations sur les performances du partitionnement des tables, plus loin dans cet article.

La première et la dernière partition sont toutes deux ouvertes au niveau des limites inférieure et supérieure, respectivement. Chaque nouvelle ligne est donc assurée de trouver une partition de destination, quelle que soit la valeur de la colonne de partitionnement. Au fil du temps, les nouvelles lignes de la table d’historique atterriront dans les partitions supérieures. Quand la sixième partition est remplie, cela signifie que la période de rétention visée est atteinte. Il s’agit du moment où démarrer la tâche de maintenance de partition périodique pour la première fois. Il doit être planifié pour s’exécuter régulièrement, une fois par mois dans cet exemple.

Le schéma suivant illustre les tâches de maintenance périodique de partition (voir la procédure détaillée plus loin dans cette section).

Diagramme montrant les tâches de maintenance de partition périodiques.

Voici la procédure à suivre pour effectuer les tâches de maintenance périodique de partition :

  1. SWITCH OUT : créez une table de mise en lots et faites un échange de partition entre la table d’historique et la table de mise en lots en utilisant l’instruction ALTER TABLE avec l’argument SWITCH PARTITION (voir l’exemple C. Échange de partitions entre des tables).

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

    À l’issue de l’échange de partition, vous pouvez archiver les données de la table de mise en lots, puis supprimer ou tronquer cette même table pour la préparer à la prochaine tâche de maintenance périodique de partition.

  2. MERGE RANGE : fusionnez la partition vide 1 avec la partition 2 en utilisant ALTER PARTITION FUNCTION avec MERGE RANGE (consultez l’exemple B). En supprimant la limite inférieure avec cette fonction, vous fusionnez en réalité la partition vide 1 avec l’ancienne partition 2 pour former une nouvelle partition 1. Les ordinaux des autres partitions changent également.

  3. SPLIT RANGE : créez une partition vide 7 à l’aide de ALTER PARTITION FUNCTION avec SPLIT RANGE (consultez l’exemple A). En ajoutant une nouvelle limite supérieure avec cette fonction, vous créez en réalité une partition distincte pour le mois à venir.

Créer des partitions dans la table d’historique avec Transact-SQL

Utilisez le script Transact-SQL suivant pour créer la fonction de partition, le schéma de partition et recréer l’index cluster pour qu’il soit aligné sur le schéma de partition, les partitions. Pour cet exemple, vous allez créer une fenêtre glissante de six mois avec des partitions mensuelles, à partir de septembre 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;

Utilisation de Transact-SQL pour gérer les partitions dans un scénario de fenêtre glissante

Utilisez le script Transact-SQL suivant pour gérer les partitions dans le scénario de fenêtre glissante. Dans cet exemple, vous supprimez la partition de septembre 2023 en utilisant MERGE RANGE, puis vous ajoutez une nouvelle partition pour mars 2024 en utilisant 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

Vous pouvez rectifier légèrement le script précédent et l’utiliser dans le processus normal de maintenance mensuelle :

  1. À l’étape (1), il convient de créer une nouvelle table de mise en lots pour le mois à supprimer (octobre serait le prochain mois dans notre exemple).
  2. À l’étape (3), une contrainte de validation correspondant au mois de données à supprimer est créée : ValidTo <= N'2023-10-31T23:59:59.999' pour la partition d’octobre.
  3. À l’étape (4), SWITCH partitionne 1 dans la table de mise en lots créée.
  4. À l’étape (6), la fonction de partition est modifiée par la fusion de la limite inférieure : MERGE RANGE(N'2023-10-31T23:59:59.999' après le déplacement des données d’octobre.
  5. À l'étape (7), divisez la fonction de partition, en créant une nouvelle limite supérieure : SPLIT RANGE (N'2024-04-30T23:59:59.999' après avoir retiré les données pour le mois d'octobre.

Toutefois, la solution optimale consisterait à exécuter régulièrement un script Transact-SQL générique qui exécuterait l'action appropriée chaque mois sans modification. Vous pouvez généraliser le script précédent pour qu'il agisse en fonction des paramètres que vous avez fournis (la frontière inférieure qui doit être fusionnée et la nouvelle frontière qui est créée avec la division de la partition). Pour éviter de créer une table de mise en lots chaque mois, vous pouvez en créer une au préalable et la réutiliser, en modifiant la contrainte de vérification pour qu’elle corresponde à la partition que vous basculez. Pour plus d’informations, consultez comment la fenêtre glissante peut être entièrement automatisée.

Considérations relatives aux performances du partitionnement de table

Vous devez effectuer les opérations MERGE et SPLIT RANGE afin d'éviter le déplacement des données, car ceux-ci peuvent entraîner des surcoûts de performance importants. Pour plus d’informations, consultez Modifier une fonction de partition. Pour ce faire, utilisez RANGE LEFT plutôt que RANGE RIGHT lorsque vous créez la fonction de partition.

Le diagramme suivant décrit les options RANGE LEFT et RANGE RIGHT :

Diagramme montrant les options RANGE LEFT et RANGE RIGHT.

Quand vous définissez une fonction de partition en tant que RANGE LEFT, les valeurs spécifiées correspondent aux limites supérieures des partitions. Quand vous utilisez RANGE RIGHT, les valeurs spécifiées correspondent aux limites inférieures des partitions. Quand vous utilisez l’opération MERGE RANGE pour supprimer une limite de la définition de la fonction de partition, l’implémentation sous-jacente supprime aussi la partition qui contient la limite. Si cette partition n’est pas vide, les données sont déplacées dans la partition qui résulte de l’opération MERGE RANGE.

Dans un scénario de fenêtre glissante, la limite de partition inférieure est toujours supprimée.

  • Cas de RANGE LEFT : la limite de partition inférieure appartient à la partition 1, qui est vide (après l’extraction de partition). Autrement dit, MERGE RANGE ne subit aucun déplacement des données.

  • Cas RANGE RIGHT : la limite de partition la plus basse appartient à la partition 2, qui n’est pas vide, car la partition 1 a été vidée en basculant. Dans ce cas, MERGE RANGE entraîne le déplacement des données (les données de la partition 2 sont déplacées vers la partition 1). Pour éviter cela, RANGE RIGHT se trouvant dans le scénario de fenêtre glissante doit avoir la partition 1, qui est toujours vide. Cela signifie que si vous utilisez RANGE RIGHT, vous devez créer et gérer une partition supplémentaire par rapport au cas RANGE LEFT.

Conclusion : la gestion des partitions est plus facile lorsque vous utilisez RANGE LEFT dans une partition coulissante, ce qui évite le déplacement des données. Cependant, il est légèrement plus facile de définir les limites d'une partition avec RANGE RIGHT, car vous n'avez pas à gérer les problèmes de vérification de la date et de l'heure.

Utilisation de la méthode de script de nettoyage personnalisé

Dans les cas où le partitionnement de table n’est pas viable, une autre approche consiste à supprimer les données de la table d’historique à l’aide d’un script de nettoyage personnalisé. La suppression des données de la table d’historique n’est possible que lorsque SYSTEM_VERSIONING = OFF. Pour éviter une incohérence de données, procédez à un nettoyage pendant la fenêtre de maintenance (quand les charges de travail qui modifient les données ne sont pas actives), ou lors d’une transaction (les autres charges de travail sont alors bloquées). Cette opération nécessite une autorisation CONTROL sur les tables actives et d’historique.

Pour éviter de trop bloquer les applications usuelles et les requêtes utilisateur, supprimez les données en blocs plus petits, en prévoyant un laps de temps pendant l’exécution du script de nettoyage dans une transaction. Même s’il n’y a pas de taille optimale pour chaque bloc de données à supprimer pour tous les scénarios, le fait de supprimer plus de 10 000 lignes dans une même transaction peut avoir une pénalité importante.

La logique de nettoyage étant identique pour toutes les tables temporelles, il est donc possible de l’automatiser via une procédure stockée générique dont vous planifiez l’exécution périodique pour chaque table temporelle dont vous voulez limiter l’historique des données.

Le diagramme suivant illustre la façon dont votre logique de nettoyage doit être organisée pour une table unique afin de réduire l’effet sur les charges de travail en cours d’exécution.

Diagramme montrant la façon dont votre logique de nettoyage doit être organisée pour une table unique afin de réduire l’effet sur les charges de travail en cours d’exécution.

Voici quelques indications générales pour implémenter le processus. Planifiez une exécution quotidienne de la logique de nettoyage, ainsi que son itération sur toutes les tables temporelles nécessitant un nettoyage de données. À l’aide de SQL Server Agent ou d’un autre outil, planifiez ce processus :

  • Supprimez les données d’historique dans chaque table temporelle en partant des lignes les plus anciennes aux plus récentes en plusieurs itérations et sur de petits blocs, tout en évitant de supprimer toutes les lignes d’une même transaction, comme indiqué dans le diagramme précédent.

  • Implémentez chaque itération comme une invocation de procédure stockée générique, qui supprime une partie des données de la table d’historique (voir l’exemple de code ci-dessous pour cette procédure).

  • Calculez le nombre de lignes que vous devez supprimer pour une table temporelle unique chaque fois que vous appelez le processus. Sur la base de ce nombre et du nombre d’itérations souhaité, déterminez les points de fractionnement dynamique pour chaque invocation de procédure.

  • Prévoyez un délai entre les itérations pour une seule table, afin de réduire l’effet sur les applications qui accèdent à la table temporelle.

Une procédure stockée qui supprime les données pour une table temporelle unique peut rappeler l’extrait de code suivant. Passez en revue attentivement ce code et ajustez-le avant de l’appliquer dans votre environnement.

Ce script génère trois instructions qui s’exécutent à l’intérieur d’une transaction :

  1. SET SYSTEM_VERSIONING = OFF
  2. DELETE FROM <history_table>
  3. SET SYSTEM_VERSIONING = ON

Dans SQL Server 2016 (13.x), les deux premières étapes doivent s’exécuter dans des instructions distinctes EXEC, ou SQL Server génère une erreur similaire à l’exemple suivant :

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

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;

Utilisation de l’approche de la stratégie de rétention d’historique temporelle

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, et Azure SQL Database.

La conservation de l’historique temporel peut être configurée au niveau de la table, ce qui permet aux utilisateurs de créer des stratégies de vieillissement flexibles. La conservation temporelle exige que vous ne définissiez qu'un seul paramètre lors de la création d'une table ou de la modification d'un schéma.

Une fois que vous avez défini la stratégie de rétention, le moteur de base de données commence par vérifier régulièrement s’il existe des lignes d’historique éligibles pour le nettoyage automatique des données. L’identification des lignes correspondantes et leur suppression de la table d’historique se produisent de façon transparente, dans une tâche en arrière-plan planifiée et exécutée par le système. La condition d'âge des lignes de la table d'historique est vérifiée sur la base de la colonne représentant la fin de la période SYSTEM_TIME (dans ces exemples, la colonne ValidTo). Si la période de rétention est définie à six mois, les lignes de table éligibles pour un nettoyage répondent par exemple, à la condition suivante :

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

Dans l’exemple précédent, la colonne ValidTo correspond à la fin de la période SYSTEM_TIME.

Configuration d’une stratégie de rétention

Avant de configurer la stratégie de rétention d’une table temporelle, vérifiez si la rétention d’historique temporelle est activée au niveau de la base de données :

SELECT is_temporal_history_retention_enabled, name
FROM sys.databases;

L’indicateur de base de données is_temporal_history_retention_enabled est défini sur ON par défaut, mais vous pouvez le modifier avec l’instruction ALTER DATABASE. Il est automatiquement défini sur OFF après une opération de limite de restauration dans le temps (PITR). Si vous souhaitez activer le nettoyage de la rétention d’historique temporelle pour votre base de données, exécutez l’instruction suivante. Vous devez remplacer <myDB> par la base de données que vous souhaitez modifier :

ALTER DATABASE [<myDB>]
SET TEMPORAL_HISTORY_RETENTION ON;

La stratégie de rétention est configurée durant la création de la table quand vous spécifiez la valeur du paramètre 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
    )
);

Vous pouvez spécifier la période de rétention à l’aide de différentes unités de temps : DAYS, WEEKS, MONTHS et YEARS. Si vous omettez HISTORY_RETENTION_PERIOD, la rétention INFINITE est appliquée. Vous pouvez également utiliser explicitement le mot clé INFINITE.

Dans certains scénarios, vous pouvez configurer la rétention après la création de la table, ou pour changer une valeur préalablement configurée. Dans ce cas, utilisez l’instruction ALTER TABLE :

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

Pour passer en revue l’état actuel de la stratégie de rétention, utilisez l’exemple suivant. Cette requête joint l’indicateur d’activation de rétention temporelle au niveau de la base de données aux périodes de rétention de tables individuelles :

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;

Comment le moteur de base de données supprime les lignes vieillies

Le processus de nettoyage dépend de la disposition de l’index de la table d’historique. Seules les tables d’historique avec un index cluster (arbre B+ ou columnstore) peuvent avoir une stratégie de rétention limitée et configurée. Une tâche en arrière-plan est créée afin d’effectuer le nettoyage des anciennes données pour toutes les tables temporelles dont la période de rétention est limitée. La logique de nettoyage de l’index cluster rowstore (arbre B+) supprime les anciennes lignes par petits blocs (jusqu’à 10 000), ce qui réduit la pression exercée sur le journal de la base de données et le sous-système d’E/S. Bien que la logique de nettoyage utilise l’index d’arbre B+ nécessaire, l’ordre des suppressions des lignes antérieures à la période de rétention ne peut pas être garanti. Ne dépendez pas de l'ordre de nettoyage dans vos applications.

La tâche de nettoyage de l'entrepôt de données columnstore supprime en une seule fois des groupes de lignes entiers (contenant généralement 1 million de lignes chacun), ce qui est plus efficace, en particulier lorsque les données historiques sont générées à un rythme élevé.

Capture d’écran de la rétention columnstore en cluster.

La compression des données et le nettoyage de la rétention font de l'index columnstore clustérisé un choix parfait pour les scénarios dans lesquels votre charge de travail génère rapidement une grande quantité de données historiques. Ce modèle est généralement utilisé pour les charges de travail de traitement transactionnel intensives qui utilisent des tables temporelles à des fins de suivi des modifications et l’audit, d’analyse de tendances ou d’ingestion des données IoT.

Pour plus d’informations, consultez Gérer les données d’historique dans les tables temporelles avec la stratégie de rétention.

Contenu connexe