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 par le système pour les tables optimisées en mémoire offrent une solution économique pour les scénarios où l’audit des données et l’analyse à un instant précis sont requis en complément de données collectées à l’aide de charges de travail OLTP In-Memory.

Vue d’ensemble

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 classique, l'historique des données est conservé pendant une longue période (plusieurs mois, voire plusieurs années), même s'il n'est pas interrogé régulièrement.

L’audit de données et l’analyse à un moment donné peuvent être exigés dans différents environnements, notamment dans les systèmes OLTP qui traitent un grand nombre de requêtes et où la technologie OLTP en mémoire est mise en œuvre. 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 versionnées par le système pour les tables optimisées en mémoire offrent un débit transactionnel élevé et des accès concurrentiels sans verrouillage. Ils vous permettent de stocker une grande quantité de données d’historique à l’aide de tables en mémoire pour stocker les données actuelles (la table temporelle) et les 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 à versionnement géré par le système et optimisée en mémoire doit être stockée sur disque, qu’elle ait été créée par l’utilisateur final ou par le système.

• Les requêtes qui affectent uniquement la table active en mémoire peuvent être utilisées dans des modules T-SQL compilés en mode natif. Les requêtes temporelles utilisant la clause FOR SYSTEM TIME ne sont pas prises en charge dans les modules compilés en mode natif. La clause FOR SYSTEM TIME est prise en charge avec les tables optimisées en mémoire dans les requêtes ad hoc et les modules non natifs.

• Quand SYSTEM_VERSIONING = ON, une table intermédiaire interne optimisée en mémoire est automatiquement créée pour accepter les modifications les plus récentes avec gestion de versions par le système, résultant d’opérations de mise à jour et de suppression sur la table active optimisée en mémoire.

• Les données de la table intermédiaire interne optimisée en mémoire sont régulièrement déplacées vers la table d’historique basée sur disque par une tâche asynchrone de vidage des données. 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 de mémoire totale de la table temporelle à système par version optimisée en mémoire en interrogeant sys.dm_db_xtp_memory_consumers et en résumant les données de la table de mise en lots interne optimisée en mémoire et de la table temporelle actuelle.

• Vous pouvez exécuter manuellement un vidage des données en exécutant sp_xtp_flush_temporal_history.

• Lorsque SYSTEM_VERSIONING = OFF ou lorsque le schéma d’une table versionnée par le système est modifié par ajout, suppression ou modification de colonnes, l’intégralité du contenu du tampon intermédiaire interne est déplacée vers 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.

• Les opérations ALTER TABLE qui modifient le schéma de la table en interne doivent effectuer un vidage de données, ce qui peut allonger la durée de 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 est généré au format suivant : Memory_Optimized_History_Table_<object_id> où <object_id> est l’identificateur de la table temporelle actuelle.

• La table réplique le schéma de la table temporelle actuelle et 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. La nouvelle valeur maximale est désormais 8 052 octets.

• La table de mise en lots interne optimisée en mémoire n’est pas représentée dans l'Explorateur d’objets de SQL Server Management Studio.

• Les métadonnées relatives à cette table, ainsi que sa connexion à la table temporelle actuelle, sont disponibles dans sys.internal_tables.

Tâche de vidage de données

Le vidage des données est une tâche régulièrement activée qui vérifie si une table optimisée en mémoire remplit une condition liée à la taille de la mémoire autorisant le déplacement des données. Le déplacement des données commence lorsque la consommation de mémoire de la table de mise en lots interne atteint huit pourcent de la consommation de 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.

Contenu connexe

• Tables temporelles
• Bien démarrer avec les tables temporelles avec versions gérées par le système
• Scénarios d’utilisation des tables temporelles
• 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
• Gérer la rétention des données d'historique dans les tables temporelles avec contrôle de version par le système
• Vues et fonctions des métadonnées des tables temporelles