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

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure 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 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. Par conséquent, 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 être considérée comme faisant 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 qui définissent 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, l’étape suivante consiste à développer un plan de gestion des données historiques. Vous devez décider 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.

Remarque

Les exemples de cet article utilisent la fonction Créer une table temporelle avec contrôle de version 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 cette méthode, vous pouvez utiliser des partitions de table d’historique pour implémenter un nettoyage de données personnalisé ou un archivage hors connexion basé sur 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é (en conservant dans la table d’historique les données correspondant à 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, il faudrait créer 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.

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

Remarque

Voir plus loin dans cet article les considérations de performance relatives au partitionnement des tables pour en savoir plus sur les conséquences d’une utilisation de RANGE LEFT plutôt que RANGE RIGHT sur les performances lors de la configuration du partitionnement.

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. C’est à ce moment-là que la tâche de maintenance périodique sera lancée pour la première fois (elle doit être planifiée pour s’exécuter périodiquement, 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).

Diagram showing the recurring partition maintenance tasks.

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 (Transact-SQL) 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 (Transact-SQL) 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 (Transact-SQL) 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), la fonction de partition est fractionnée par la création d’une nouvelle limite supérieure : SPLIT RANGE (N'2024-04-30T23:59:59.999' après le déplacement des données d’octobre.

Cependant, la solution idéale serait d’exécuter régulièrement un script Transact-SQL générique qui soit capable effectuer l’action appropriée chaque mois sans modification du script. Il est possible de généraliser le script précédent de sorte qu’il agisse en fonction des paramètres fournis (limite inférieure devant être fusionnée et nouvelle limite créée avec fractionnement de partition). Afin d'éviter la création d'une table de mise en lots chaque mois, vous pouvez en créer une à l'avance et la réutiliser en modifiant la contrainte CHECK pour qu'elle corresponde à la partition qui sera remplacée. Consultez les pages suivantes pour vous faire une idée de la manière dont la fenêtre glissante peut être entièrement automatisée à l'aide d'un script Transact-SQL.

Considérations relatives aux performances du partitionnement de table

Il est important d’effectuer les opérations MERGE et SPLIT RANGE pour éviter tout déplacement des données, qui peut entraîner une baisse significative des performances. 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.

Pour commencer, expliquons visuellement la signification des options RANGE LEFT et RANGE RIGHT :

Diagram showing the RANGE LEFT and RANGE RIGHT options.

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 : l’utilisation de RANGE LEFT dans une partition glissante facilite grandement la gestion des partitions et évite le déplacement des données. Cependant, définir les limites de partition avec RANGE RIGHT s’avère un peu plus simple, car vous n’êtes pas confronté aux problèmes d’heure et de date.

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 un impact important.

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’impact sur les charges de travail en cours d’exécution.

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

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 (examinez attentivement ce code et ajustez-le avant de l’appliquer dans votre environnement) :

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;

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. L’application de la rétention temporelle est simple : un seul paramètre doit être défini durant la création de la table ou le changement de 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 la 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 en fonction de la colonne représentant la fin de la période SYSTEM_TIME. Par exemple, si la période de rétention est définie à six mois, les lignes de table éligibles pour un nettoyage répondent à 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 d’abord 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 les utilisateurs peuvent 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 :

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 la requête suivante qui permet de joindre 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 SQL Database supprime les anciennes lignes

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+ (B+ tree) 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+ (B+ tree)) supprime les anciennes lignes par petits blocs (jusqu’à 10 Ko), 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+ (B+ tree) nécessaire, l’ordre des suppressions des lignes antérieures à la période de rétention ne peut pas être garanti. Vous ne devez donc pas accepter de dépendances relatives à l’ordre de nettoyage dans vos applications.

La tâche de nettoyage pour le cluster columnstore supprime l’ensemble des groupes de lignes en une fois (ceux-ci contiennent généralement 1 million de lignes chacun), ce qui est très efficace, en particulier lorsque les données d’historique sont générées à un rythme élevé.

Screenshot of clustered columnstore retention.

En raison de l’excellence de la compression des données et de l’efficacité du nettoyage de la rétention, l’index columnstore cluster est un choix idéal dans les scénarios où votre charge de travail génère rapidement une grande quantité de données d’historique. 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.