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

  • Article

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

Les tables temporelles à système par version pour les tables optimisées en mémoire sont conçues pour fournir une solution rentable dans des scénarios où l’audit de données et l’analyse à un moment donné sont nécessaires sur des données collectées à l’aide de charges de travail OLTP en mémoire. Elles offrent un débit transactionnel élevé et une simultanéité sans verrouillage, ainsi que la possibilité de stocker un grand nombre de données d’historique pouvant être interrogées facilement.

Vue d’ensemble

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

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 de mémoire RAM disponible. Pourtant, stocker dans la RAM des données d’historique en lecture seule qui sont moins sollicitées avec le temps n’est pas une solution optimale.

Les tables temporelles à système par version des tables optimisées en mémoire fournissent un débit transactionnel élevé et une simultanéité sans verrouillage, ainsi que la possibilité de stocker un grand nombre de données d’historique 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 d’historique. 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. Architecture en mémoire temporelle

Informations d’implémentation

Les points suivants sur les tables temporelles à système par version avec tables optimisées en mémoire sont des considérations à connaître lors de la création d’une table à système par version à mémoire optimisée. Pour connaître les options de syntaxe et un exemple, consultez CREATE TABLE (Transact-SQL).

  • Seules les tables durables optimisées en mémoire peuvent être des tables à système par version (DURABILITY = SCHEMA_AND_DATA).
  • La table d’historique de la table à système par version optimisée en mémoire doit être sur disque, qu’elle soit créée par l’utilisateur final ou 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. L’utilisation de la clause FOR SYSTEM TIME avec les tables optimisées en mémoire dans les requêtes ad hoc et les modules non natifs est prise en charge.
  • Quand SYSTEM_VERSIONING = ON, une table de mise en lots interne optimisée en mémoire est automatiquement créée pour accepter les dernières modifications apportées à la table à système par version qui résultent d’opérations de mise à jour et de suppression sur la table optimisée en mémoire actuelle.
  • Les données de la table de mise en lots interne optimisée en mémoire sont régulièrement déplacées vers la table d’historique sur disque par la tâche de vidage de données asynchrones. Ce mécanisme de vidage des données vise à maintenir les mémoires tampons internes à moins de 10 % 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 (Transact-SQL) 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 appliquer un vidage des données en appelant sp_xtp_flush_temporal_history.
  • Quand SYSTEM_VERSIONING = OFF ou quand le schéma de la table à système par version est modifié par l’ajout, la suppression ou le changement de colonnes, tout le contenu de la mémoire tampon de mise en lots interne est déplacé vers la table d’historique sur disque.
  • L’interrogation des données d’historique est en réalité sous le niveau d’isolement de capture instantanée et renvoie toujours une union entre la mémoire tampon de mise en lots en mémoire et la table sur disque sans doublons.
  • Les opérationsALTER 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

La table de mise en lots interne optimisée en mémoire est un objet interne créé par le système pour optimiser les opérations DML.

  • Le nom de la table est généré au format suivant : Memory_Optimized_History_Table_<ID_objet>, où <ID_objet> 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 la mémoire tampon de l’historique interne.
  • Le nom de la colonne supplémentaire est au format suivant : Change_ID[_< suffix>], où _<suffix> est également ajouté dans les cas où la table contient déjà une colonne Change_ID.
  • La taille de ligne maximale pour une table à système par version optimisée en mémoire est réduite de 8 octets en raison de la colonne BIGINT supplémentaire dans la table de mise en lots. 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 (Transact-SQL).

Tâche de vidage de données

Le vidage des données est une tâche régulièrement activée qui recherche si une table optimisée en mémoire répond à une condition basée sur la taille de mémoire pour 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 8 % 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. Sa fréquence peut aller de toutes les minutes pour une charge de travail légère à toutes les 5 secondes pour une charge de travail lourde. Un thread est généré pour chaque table de mise en lots interne optimisée en mémoire qui doit être nettoyée.

Le vidage de données supprime tous les enregistrements de la mémoire tampon interne en mémoire antérieurs à la plus ancienne des transactions en cours d’exécution et les déplace vers la table d’historique sur disque.

Vous pouvez appliquer un vidage des données en appelant sp_xtp_flush_temporal_history et en spécifiant le nom du schéma et de la table : sys.sp_xtp_flush_temporal_history @schema_name, @object_name. Cette commande exécutée par l’utilisateur fait appel au même processus de déplacement de données que lorsque la tâche de vidage de données est appelée par le système selon la planification interne.

Voir aussi