Partager via

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

  • Article

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Base de données Azure SQL Azure 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.

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 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 en version système pour les tables à mémoire optimisée offrent un débit transactionnel élevé et une concurrence 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.

Diagramme de l'architecture temporelle en mémoire.

Informations d’implémentation

Lorsque vous créez une table à mémoire optimisée avec version système, tenez compte des considérations suivantes. Pour connaître les options de syntaxe et un exemple, consultez CREATE TABLE.

  • 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 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.

  • 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é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 la mémoire tampon de l’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 pour une table mémoire optimisée avec contrôle de version du système 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.

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