Tables temporelles à versionnement géré par le système avec tables optimisées en mémoire

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

Les tables temporelles versionnées système pour les tables optimisées en mémoire offrent une solution rentable pour les scénarios où un audit de données et une analyse ponctuelle sont nécessaires en plus des données collectées avec des charges de travail OLTP en mémoire.

Note

Les tables temporelles optimisées en mémoire sont disponibles uniquement dans SQL Server et Azure SQL Managed Instance. Les tables optimisées en mémoire et les tables temporelles sont disponibles indépendamment dans Azure SQL Database.

Overview

Les tables temporelles à versionnage système conservent automatiquement un historique complet des modifications apportées aux données et offrent des extensions Transact-SQL pratiques pour l’analyse à un instant donné. Dans un scénario typique, l’historique des données est conservé longtemps (plusieurs mois, voire des années), même s’il n’est pas régulièrement interrogé.

L’audit des données et l’analyse basée sur le temps peuvent être exigés dans différents environnements, notamment dans les systèmes OLTP qui traitent un très grand nombre de requêtes et où la technologie OLTP en mémoire est utilisée. Toutefois, l’utilisation de tables optimisées en mémoire dans des scénarios temporels est complexe, car une grande quantité de données d’historique générées excède généralement la limite RAM disponible. Par ailleurs, l'utilisation de la RAM pour stocker des données historiques en lecture seule, auxquelles on accède moins souvent à mesure qu'elles vieillissent, n'est pas une solution optimale.

Les tables temporelles à versionnement géré par le système pour les tables optimisées en mémoire offrent un débit transactionnel élevé et une concurrence sans verrouillage. Vous pouvez stocker une grande quantité de données historiques en utilisant des tables en mémoire pour stocker les données actuelles (la table temporelle), et des tables sur disque pour les données historiques. L’impact sur les opération DML est réduit au minimum grâce à l’utilisation d’un table de mise en lots interne optimisée en mémoire et générée automatiquement qui stocke l’historique récent et permet aux DML de s’exécuter à partir du code compilé en mode natif.

Le diagramme suivant illustre cette architecture.

Informations d’implémentation

Lorsque vous créez une table à mémoire optimisée et versionnée par le système, tenez compte des points suivants. Pour obtenir des options de syntaxe et pour obtenir un exemple, consultez CREATE TABLE.

Seules les tables durables optimisées en mémoire peuvent être versionnées par le système (DURABILITY = SCHEMA_AND_DATA).
La table d’historique d’une table optimisée en mémoire pour version système doit être basée sur disque, que ce soit vous la créiez ou que le système la crée.
Vous pouvez utiliser des requêtes qui n’affectent que la table en mémoire actuelle dans des modules T-SQL compilés nativement. Les modules compilés nativement ne supportent pas la FOR SYSTEM TIME clause, mais les requêtes ad hoc et les modules non natifs peuvent utiliser la clause contre des tables optimisées en mémoire.
Avec SYSTEM_VERSIONING = ON, le système crée automatiquement une table intermédiaire interne optimisée en mémoire pour recevoir les modifications les plus récentes liées au versionnage système, résultant d’opérations de mise à jour et de suppression sur une table active optimisée en mémoire.
Une tâche de vidage asynchrone des données déplace régulièrement les données de la table de staging interne optimisée pour la mémoire vers la table d’historique basée sur disque. Ce mécanisme de vidage des données maintient les mémoires tampons internes à moins de 10 pourcent de la consommation de mémoire de leurs objets parents. Vous pouvez suivre la consommation totale de mémoire d’une table temporelle à versionnement système optimisée en mémoire en interrogeant sys.dm_db_xtp_memory_consumers et en synthétisant les données de la table intermédiaire interne optimisée en mémoire et de la table temporelle active.
Pour effectuer manuellement un vidage des données, exécutez sp_xtp_flush_temporal_history.
Avec SYSTEM_VERSIONING = OFF, ou lorsque vous modifiez le schéma d’une table version système en ajoutant, supprimant ou modifiant des colonnes, l’intégralité du contenu du tampon de staging interne est déplacée dans la table d’historique basée sur disque.
L’interrogation des données historiques s’effectue effectivement avec le niveau d’isolation Snapshot et renvoie toujours l’union du tampon de préparation en mémoire et de la table stockée sur disque, sans doublons.
ALTER TABLE Les opérations qui modifient le schéma de la table en interne doivent effectuer un nettoyage des données, ce qui pourrait prolonger l’opération.

La table de mise en lots interne optimisée en mémoire

Le système crée une table de mise en lots interne à mémoire optimisée pour optimiser les opérations DML.

Le nom de la table utilise le format suivant : Memory_Optimized_History_Table_<object_id> où <object_id> est l’identifiant de la table temporelle actuelle.
La table reproduit le schéma de la table temporelle courante plus une colonne bigint . Cette colonne supplémentaire garantit l’unicité des lignes déplacées vers le tampon d’historique interne.
La colonne supplémentaire a le format de nom suivant : Change_ID[<suffix>], où <suffix> est optionnellement ajouté dans le cas où le tableau possède déjà une colonne Change_ID.
La taille de ligne maximale d’une table optimisée en mémoire avec versionnage géré par le système est réduite de 8 octets en raison de la colonne bigint supplémentaire dans la table intermédiaire. Le maximum est désormais de 8 052 octets.
La table de staging interne optimisée pour la mémoire n'apparaît pas dans Explorateur d'objets de SQL Server Management Studio.
Vous pouvez trouver des métadonnées sur cette table, ainsi que sa connexion avec la table temporelle actuelle, dans sys.internal_tables.

Tâche de vidage de données

La tâche de vidange des données s’exécute régulièrement, et cela vérifie si une table optimisée en mémoire remplit une condition basée sur la taille de la mémoire pour le déplacement des données. Le déplacement des données commence lorsque la consommation mémoire de la table de staging interne atteint huit pour cent de la consommation mémoire de la table temporelle actuelle.

La tâche de vidage de données est activée régulièrement selon une planification qui varie en fonction de la charge de travail existante. Avec une charge de travail importante, la tâche s’exécute aussi fréquemment que toutes les 5 secondes. Avec une charge de travail légère, la fréquence augmente toutes les minutes. Un thread est généré pour chaque table de mise en lots interne optimisée en mémoire qui doit être nettoyée.

L'effacement des données supprime tous les enregistrements de la mémoire tampon interne qui sont plus anciens que la transaction la plus ancienne en cours d'exécution, afin de les transférer dans la table d'historique sur disque.

Vous pouvez exécuter un vidage des données en exécutant sp_xtp_flush_temporal_history et en spécifiant le schéma et le nom de la table :

EXEC sys.sp_xtp_flush_temporal_history <schema_name>, <object_name>;

Le même processus de déplacement des données est invoqué que lorsque le système exécute la tâche de vidange des données sur son calendrier interne.

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2026-08-18