Gérer la conservation des données historiques dans les tables temporelles à versionnement géré par le système

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Une table temporelle version système conserve toutes les versions précédentes de chaque ligne dans sa table historique. La table historique peut augmenter la taille de votre base de données plus que les tables classiques dans les conditions suivantes :

• Vous conservez les données historiques pendant une longue période.
• Vous avez un modèle de modification de données intensif en mises à jour ou en suppressions.

Une grande table d’historique en constante expansion pourrait poser problème, à la fois à cause des coûts de stockage et de la taxe de performance qu’elle impose aux requêtes temporelles. Élaborer une politique de conservation des données pour la table d’historique est une partie importante de la planification et de la gestion du cycle de vie de chaque table temporelle.

Planifiez une politique de conservation des données

Pour gérer la conservation des données des tables temporelles, il faut d’abord déterminer la période de conservation requise pour chaque table temporelle. Votre politique de rétention, dans la plupart des cas, devrait faire partie de la logique métier de l’application qui utilise les tables temporelles. Par exemple, les applications dans les scénarios d’audit de données et de voyage dans le temps ont des exigences strictes concernant la durée des données historiques nécessaires pour la requête en ligne.

Après avoir déterminé votre période de conservation des données, élaborez un plan pour gérer les 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.

Chaque approche dans cet article agit sur la colonne correspondant à la fin de période dans le tableau actuel, qui est la ValidTo colonne dans les exemples suivants. 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 (DAY, -30, SYSUTCDATETIME()) correspond à des données historiques datant de plus de 30 jours.

Choisissez l’une des approches suivantes pour effectuer une action sur ces lignes :

Approche	Fonctionnement	Quand l′utiliser ?
Politique de conservation de l’historique temporel	Vous définissez une période de rétention pour chaque table, et une tâche en arrière-plan supprime automatiquement les lignes anciennes.	L’option la plus simple, c’est quand on peut supprimer l’historique ancien complètement.
Partitionnement de tables	Une fenêtre coulissante sort la partition la plus ancienne de la table d’historique, donc vous pouvez l’archiver ou la jeter.	Quand vous souhaitez archiver des données historiques avant de les retirer, ou lorsque vous souhaitez éliminer les partitions pour les requêtes temporelles.
Script de nettoyage personnalisé	Un script planifié désactive la gestion de versions système, supprime les lignes anciennes par petits morceaux, puis réactive la gestion de versions système.	Quand une politique de rétention n’est pas disponible pour votre table et que le partitionnement n’est pas viable.

Les exemples de partitionnement et de nettoyage personnalisé de cet article s’appuient sur les exemples fournis dans l’article Créer une table temporelle versionnée par le système.

Utilisez une politique de conservation de l’historique temporel

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Database, Azure SQL Managed Instance, et SQL database dans Microsoft Fabric.

Vous pouvez configurer la rétention de l’historique temporel au niveau de tables individuelles, ce qui vous permet de créer des politiques de vieillissement flexibles. Pour permettre la rétention temporelle, définissez HISTORY_RETENTION_PERIOD lors de la création de la table ou d’un changement de schéma.

Après avoir défini la politique de rétention, le Moteur de base de données exécute une tâche planifiée en arrière-plan qui trouve et supprime de manière transparente les lignes historiques dont la valeur de fin de période est antérieure à la période de rétention.

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 par défaut sur ON, mais vous pouvez le modifier à l’aide de l’instruction ALTER DATABASE. Le Moteur de base de données le règle également automatiquement OFF après une opération de restauration à un moment donné (PITR), comme décrit dans Considérations de restauration à un moment donné. Si vous souhaitez activer le nettoyage de la rétention d’historique temporelle pour votre base de données, exécutez l’instruction suivante. Remplacez <myDB> par la base de données que vous souhaitez modifier :

ALTER DATABASE [<myDB>]
    SET TEMPORAL_HISTORY_RETENTION ON;

Important

Vous pouvez configurer la rétention des tables temporelles même si is_temporal_history_retention_enabled est OFF, mais le Moteur de base de données ne déclenche pas le nettoyage automatique des lignes anciennes dans ce cas.

Vous pouvez configurer la politique de rétention lors de la création de la table en spécifiant une valeur pour le HISTORY_RETENTION_PERIOD paramètre :

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

Une fois cette politique en place, les lignes dans dbo.WebsiteUserInfoHistory peuvent être nettoyées lorsqu’elles satisfont à la condition suivante :

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

Vous pouvez spécifier la période de conservation dans DAYS, WEEKS, MONTHS, ou YEARS. Si vous excluez HISTORY_RETENTION_PERIOD, la rétention par défaut est INFINITE. Vous pouvez également utiliser explicitement le mot clé INFINITE.

Dans certains cas, vous pouvez vouloir configurer la rétention après la création de la table ou modifier la valeur précédemment configurée. Dans ce cas, utilisez l’instruction ALTER TABLE :

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

Important

Mettre SYSTEM_VERSIONING sur OFF ne conserve pas la valeur de la période de rétention. Définir SYSTEM_VERSIONING sur ON sans HISTORY_RETENTION_PERIOD explicite entraîne la rétention de INFINITE.

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

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. Vous pouvez configurer une politique de rétention finie uniquement sur des tables d’historique avec un index de rowstore clusteré (B-tree) ou un index de columnstore clusteré. Une tâche en arrière-plan effectue un nettoyage de données anciennes pour toutes les tables temporelles avec une période de conservation finie.

Note

De manière générale, la documentation SQL Server utilise le terme B-tree en référence aux index. Dans les index rowstore, le moteur de base de données implémente une structure B+. Cela ne s’applique pas aux index columnstore ou aux index sur les tables à mémoire optimisée. Pour plus d’informations, consultez le Guide de conception et d’architecture d’index SQL Server et Azure SQL.

Index de rowstore de l’arbre B

L’index regroupé du rowstore doit commencer par la colonne correspondant à la fin de la SYSTEM_TIME période. Si un tel indice n’existe pas, vous ne pouvez pas configurer une période de conservation finie :

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 table d’historique par défaut possède déjà un index cluster conforme. Si vous essayez de supprimer cet index sur une table d’historique avec une période de conservation finie, l’opération échoue avec l’erreur suivante :

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 logique de nettoyage de l’index rowstore clusterisé supprime les lignes anciennes par lots plus petits (jusqu’à 10 000), afin de réduire la pression 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 requis de l’arbre B, elle ne peut pas garantir l’ordre de suppression des lignes antérieures à la période de rétention. Ne dépendez pas de l'ordre de nettoyage dans vos applications.

Index en cluster columnstore

La tâche de nettoyage du magasin de colonnes en cluster supprime des groupes de lignes entiers en même temps. Chaque groupe de lignes contient généralement un million de lignes. Cette méthode est plus efficace, surtout lorsque votre charge de travail génère des données historiques à un rythme soutenu.

La compression des données et le nettoyage par rétention font de l’index de colonne en cluster un bon choix pour les situations où votre charge de travail génère rapidement une grande quantité de données historiques. Ce schéma est typique des charges de travail transactionnelles intensives qui utilisent des tables temporelles pour le suivi et l’audit des changements, l’analyse des tendances ou l’ingestion de données de l’Internet des objets (IoT).

Le nettoyage de l’index columnstore clusterisé fonctionne de façon optimale lorsque les lignes historiques arrivent par ordre croissant (triées selon la colonne de fin de période). Cette condition est toujours vraie lorsque seul le SYSTEM_VERSIONING mécanisme remplit la table d’historique. Si les lignes de la table d’historique ne sont pas ordonnées selon la colonne de fin de période (ce qui peut se produire lors de la migration de données historiques existantes), recréez l’index columnstore clusterisé sur un index rowstore en arborescence B correctement ordonné afin d’obtenir des performances optimales.

Évitez de reconstruire l’index columnstore clusterisé sur une table d’historique avec une période de rétention limitée, car sa reconstruction pourrait modifier l’ordre des groupes de lignes que l’opération de versionnement géré par le système impose naturellement. Si vous devez reconstruire l’index columnstore clusterisé sur la table d’historique, recréez-le sur la base d’un index B-tree conforme afin de préserver l’ordre des groupes de lignes nécessaire au nettoyage régulier des données. Adoptez la même approche si vous créez une table temporelle avec une table d’historique existante qui possède un index de colonne regroupé sans ordre garanti des données :

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

Lorsque vous configurez une période de conservation finie pour une table d’historique avec un index de colonne groupé, vous ne pouvez pas créer d’index B non regroupés supplémentaires sur cette table :

CREATE NONCLUSTERED INDEX IX_WebHistNCI
    ON WebsiteUserInfoHistory(UserName);

L’instruction précédente échoue avec l’erreur suivante :

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.

Requête de tables avec stratégie de conservation

Toutes les requêtes sur la table temporelle filtrent automatiquement les lignes historiques correspondant à la politique de rétention finie, afin d’éviter des résultats imprévisibles et incohérents. La tâche de nettoyage supprime les lignes anciennes à tout moment et dans un ordre arbitraire.

La capture d’écran suivante montre le plan de requête pour une requête basique. Cet exemple suppose une période de rétention de MONTH sur la table WebsiteUserInfo :

SELECT *
FROM dbo.WebsiteUserInfo FOR SYSTEM_TIME ALL;

Le plan de requête inclut un filtre supplémentaire sur la colonne de fin de période (ValidTo) dans l’opérateur Clustered Index Scan (mis en évidence dans l’image suivante) dans la table historique.

Si vous interrogez directement la table d’historique, vous pouvez voir des lignes plus anciennes que la période de conservation spécifiée, mais sans garantie de résultats de requête répétables. La capture d’écran suivante montre le plan de requête pour une requête dans la table d’historique sans filtres supplémentaires :

Ne vous fiez pas à la logique métier qui lit le tableau historique au-delà de la période de rétention, car vous pourriez obtenir des résultats incohérents ou inattendus. Utilisez des requêtes temporelles avec la FOR SYSTEM_TIME clause pour analyser les données dans des tables temporelles.

Considérations relatives à la restauration à un point dans le temps

Lorsque vous restaurez une base de données à un moment précis, la rétention temporelle de la nouvelle base de données est désactivée au niveau de la base de données (is_temporal_history_retention_enabled défini à OFF). Ce comportement vous permet d’inspecter les rangées historiques plus anciennes que la période de conservation avant que la tâche de nettoyage ne les supprime. Pour reprendre le nettoyage automatique de la base de données restaurée, remettez TEMPORAL_HISTORY_RETENTION à ON.

Note

Une base de données créée dans le niveau Premium sur Azure SQL Database conserve les sauvegardes jusqu’à 35 jours, donc vous pouvez la restaurer à un moment donné n’importe où dans cette fenêtre. Pour une table temporelle avec une période de conservation d’un mois, cela vous permet d’inspecter des lignes historiques jusqu’à 65 jours en interrogeant la table d’historique directement sur la base de données restaurée.

Utiliser le partitionnement de tables

Les tables et index partitionnés peuvent rendre les grandes tables plus faciles à gérer et plus évolutives. En utilisant l’approche de partitionnement de tables, vous pouvez mettre en place un nettoyage personnalisé des données ou un archivage hors ligne 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.

Utilisez le partitionnement de table pour implémenter une fenêtre glissante afin de déplacer la partie la plus ancienne des données historiques de la table d’historique, et gardez la taille de la partie conservée constante selon l’âge. Une fenêtre coulissante conserve les données dans la table d’historique équivalentes à la période de conservation requise. La table d’historique permet de retirer des données alors que SYSTEM_VERSIONING est ON, ce qui signifie que vous pouvez purger une partie des données d’historique sans introduire de fenêtre de maintenance ni bloquer vos charges de travail habituelles.

Note

Pour effectuer le changement de partition, votre index clusterisé sur la table d’historique doit être aligné avec le schéma de partitionnement (il doit contenir ValidTo). La table d’historique par défaut contient un index regroupé qui inclut les ValidTo colonnes et ValidFrom , ce qui est optimal pour le partitionnement, l’insertion de nouvelles données d’historique et les requêtes temporelles typiques. Pour plus d’informations, voir Tables temporelles.

Une fenêtre coulissante nécessite deux ensembles de tâches :

• une tâche de configuration du partitionnement ;
• Tâches récurrentes de maintenance des partitions

Pour cette illustration, supposons que vous souhaitiez conserver les données historiques pendant six mois et que vous souhaitiez garder chaque mois de données dans une partition séparée. Supposons aussi que vous ayez activé le système de versionnement 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 la fenêtre coulissante, en mois, plus une partition vide supplémentaire. Cette configuration garantit que le système peut stocker correctement les nouvelles données dès le début de la tâche récurrente de maintenance de partition. Cela garantit également que vous ne séparez jamais les partitions contenant des données, ce qui évite des mouvements de données coûteux. Définissons la fonction de partition avec RANGE LEFT plutôt que RANGE RIGHT. Pour plus d’informations, voir Considérations de performance avec partitionnement de tables plus loin dans cet article.

L’image suivante montre la configuration initiale de partitionnement pour conserver six mois de données.

Les première et dernière partitions sont ouvertes respectivement sur les limites inférieure et supérieure, afin de garantir que chaque nouvelle ligne possède une partition de destination, quelle que soit la valeur dans la colonne de partition. Avec le temps, de nouvelles lignes dans la table d’histoire se retrouvent dans des partitions supérieures. Lorsque la sixième partition se remplit, vous atteignez la période de conservation ciblée. À ce stade, lance la tâche récurrente de maintenance de partition pour la première fois. Programmez-le pour qu’il s’exécute périodiquement, une fois par mois dans cet exemple.

L’image suivante illustre les tâches récurrentes de maintenance des partitions.

Chaque exécution de la tâche de maintenance récurrente exécute les étapes suivantes :

1. SWITCH OUT: Créer une table de staging puis basculer une partition entre la table d’historique et la table staging en utilisant l’instruction ALTER TABLE avec l’argument SWITCH PARTITION .

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

   Après le changement de partition, vous pouvez éventuellement archiver les données de la table de staging, puis soit supprimer soit tronquer la table staging pour préparer le prochain cycle de maintenance.

2. MERGE RANGE: Fusionner la partition 1 vide avec 2 partition en utilisant l’instruction ALTER PARTITION FUNCTION avec MERGE RANGE. Lorsque vous utilisez cette fonction pour supprimer la limite la plus basse, vous fusionnez effectivement la partition 1 vide avec l’ancienne partition 2 pour former une nouvelle partition 1. Les autres partitions modifient également de fait leur numéro d’ordre.

3. SPLIT RANGE: Créer une nouvelle partition 7 vide en utilisant l’instruction ALTER PARTITION FUNCTION avec SPLIT RANGE. Lorsque vous utilisez cette fonction pour ajouter une nouvelle limite supérieure, vous créez effectivement une partition séparée pour le mois suivant.

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 regroupé pour qu’il soit aligné sur la partition avec le schéma. 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. Pour cet exemple, vous échangez la partition pour septembre 2023 en utilisant MERGE RANGE, puis 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 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;

Cependant, la solution optimale est d’exécuter régulièrement un script Transact-SQL générique chaque mois sans modification. Vous pouvez généraliser le script précédent pour agir selon vos paramètres fournis (la limite inférieure qui doit fusionner, et la nouvelle frontière créée par la séparation de la partition). Pour éviter de créer une table intermédiaire chaque mois, créez-en une à l’avance et réutilisez-la en modifiant la contrainte CHECK afin qu’elle corresponde à la partition que vous retirez. Pour plus d’informations, consultez comment automatiser entièrement le scénario de fenêtre glissante.

Considérations relatives aux performances du partitionnement de table

Effectuez les opérations MERGE RANGE et SPLIT RANGE de manière à éviter le déplacement des données, car celui-ci peut entraîner une dégradation significative des performances. Pour plus d’informations, consultez Modifier une fonction de partition.

Lorsque vous créez la fonction de partition comme RANGE LEFT, les valeurs spécifiées sont les 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, MERGE RANGE il déplace les données vers la partition résultante.

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

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

• RANGE LEFT cas : La limite la plus basse de partition appartient à la partition 1, qui est vide (après le changement de partition), donc MERGE RANGE ne provoque aucun mouvement de données.

• RANGE RIGHT cas : La limite inférieure de la partition appartient à la partition 2, qui n’est pas vide, car le retrait ne vide que la partition 1. Dans ce cas, MERGE RANGE provoque un déplacement de données, déplaçant les données d’une partition 2 à 1l’autre. Pour éviter ce déplacement de données, RANGE RIGHT dans le scénario de la fenêtre glissante, il faut avoir une partition 1, qui est toujours vide. Cette exigence signifie que si vous utilisez RANGE RIGHT, vous devez créer et maintenir une partition de plus par rapport au cas de RANGE LEFT.

Conclusion : La gestion des partitions est plus facile lorsque vous l’utilisez RANGE LEFT dans une partition glissante, et elle évite les mouvements de 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.

Utilisez un script de nettoyage personnalisé

Lorsqu’une politique de rétention n’est pas disponible pour votre table, et que le partitionnement de table n’est pas viable, vous pouvez supprimer les données de la table historique en utilisant un script de nettoyage personnalisé. Ce processus n’est possible que lorsque SYSTEM_VERSIONING = OFF. Pour éviter l’incohérence des données, effectuez le nettoyage soit pendant une fenêtre de maintenance (lorsque les charges de travail modifiant les données ne sont pas actives), soit au sein d’une transaction (bloquant effectivement d’autres charges de travail). Cette opération nécessite une autorisation CONTROL sur les tables actives et d’historique.

La logique de nettoyage est la même pour chaque table temporelle, donc vous pouvez l’automatiser via une procédure stockée générique. Utilisez SQL Server Agent ou un autre outil pour programmer cette procédure à l’exécution quotidienne, en itérant sur chaque table temporelle pour laquelle vous souhaitez limiter l’historique des données.

Le schéma suivant illustre comment organiser votre logique de nettoyage pour une seule table afin de réduire l’effet sur les charges de travail en cours.

Voici quelques lignes directrices générales pour la mise en œuvre du processus :

• Supprimez les données historiques de chaque table temporelle en plusieurs itérations de petits blocs. Commencez par les rangées les plus anciennes puis passez à la plus récente. Évitez de supprimer toutes les lignes d’une seule transaction, comme le montre le diagramme précédent. Bien qu’aucune taille de bloc ne fonctionne pour tous les scénarios, supprimer plus de 10 000 lignes dans une seule transaction pourrait entraîner une pénalité importante.

• Implémentez chaque itération comme une invocation d’une procédure stockée générique, qui supprime une partie des données de la table d’historique.

• Calculez le nombre de lignes que vous devez supprimer pour une table temporelle unique chaque fois que vous appelez le processus. En fonction du résultat et du nombre d’itérations souhaitées, déterminez les points de séparation dynamiques 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.

La procédure stockée suivante supprime les données pour une seule table temporelle. Il découvre la table historique et la colonne de fin de période à partir des vues catalogue, puis exécute trois instructions à l’intérieur d’une transaction : SET SYSTEM_VERSIONING = OFF, DELETE FROM <history_table>, et SET SYSTEM_VERSIONING = ON. Examinez attentivement ce code et ajustez-le avant de l’appliquer dans votre environnement.

Dans SQL Server 2016 (13.x), les deux premières étapes doivent s’exécuter dans des instructions distinctes EXECUTE, 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 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;

Contenu connexe

• Tables temporelles
• Bien démarrer avec les tables temporelles avec versions gérées par le système
• Vérifications de cohérence système des tables temporelles
• Partition avec des tables temporelles
• Considérations et limitations liées aux tables temporelles
• Sécurité des tables temporelles
• Tables temporelles à versionnement géré par le système avec tables optimisées en mémoire
• Vues et fonctions des métadonnées des tables temporelles