SQL 2014 : Technologie “In-Memory”
“Hekaton” est le nom de code de la technologie “In-memory”, nativementintégrée à SQL Server 2014. On peut voir Hekaton comme un moteur chargé en mémoire optimisé pour le monde OLTP (On-Line Transactional Processing). Le but de cette technologie est de bénéficier de l’évolution des configurations matérielles actuelles (mémoire vive plus importante), et de réduire de manière significative les temps de latence des opérations OLTP courantes telles que les “SELECT”, “INSERT”, “UDAPTE” et “DELETE”.
Etant intégrée à SQL server 2014, cette technologie ne nécessite pas un langage différent. Le Transact SQL actuel fonctionnera avec le moteur Hekaton et bénéficiera d’extensions pour tirer pleinement parti de la technologie “In-Memory”
Plus de pages et plus de verrous (“Lock” et “Latch”)
Le moteur Hekaton a été pensé pour tirer parti de la mémoire vive disponible au sein des serveurs. Mais tant qu’à ré écrire un moteur, autant en profiter pour revoir certains principes sur le stockage des données et d’en optimiser l’accès, notamment au niveau des “lock” et des “latch”.
Plus de page et d’extension:
Pour les tables en mémoire, les données ne sont plus stockées dans des pages et il n’y a plus besoin de réserver de l’espace pour les extensions. Les données résident en mémoire sous forme de ligne. La structure des lignes a été modifiée et les lignes appartenant à la même valeur d’index seront liées entre elles. Nous verrons ces points plus bas.
Plus de “Loch” et “Latch”:
Dans SQL Server (hors Hekaton), les “lock” font partie du process d’accès aux données. Lorsqu’un accès en lecture aux données est réalisé, un “lock” partagé doit être attribué à la requête qui effectue l’opération de lecture. Dans le cas d’un accès en écriture, il est alors nécessaire d’obtenir un “lock” exclusif. En ce qui concerne la structure interne des accès, ce sont les mécanismes de “latch” qui sont invoqués.
Le moteur en mémoire “Hekaton” a été prévu afin d’être libre de tous verrous. L’accès aux données utilise le principe du “Multi-Version Concurrency Control (MVCC). Pour le process d’accès interne, le moteur “Hekaton” à sa propre allocation de mémoire qui ne nécessite plus l’utilisation des “latch”
Les données résident en mémoire….tout le temps
L’idée de la technologie en mémoire, est de pouvoir stocker une ou plusieurs tables en mémoire…..mais de garantir aussi la durabilité des données en cas de pannes. Pour des raisons de restauration des données, le moteur “Hekaton” stocke aussi les données sur disque. Cependant les données résident tout le temps en mémoire.
Au démarrage du serveur SQL ou quand une base de données est mise en ligne, le moteur “Hekaton” lit les données se trouvant sur disque et les montent en mémoire. Dans le même temps, le moteur construit les index qui sont aussi stockés en mémoire, car ceux-ci ne sont pas stockés sur disque.
Les tables “Hekaton” existent sous 2 formes : Durable ou non-durable. Le contenu des tables durables est maintenu en cas de défaillance du système. Dans le cas des tables non-durables, seul le schéma de la table est conservé en cas de défaillance. Les tables “non-durables” seront donc vides après un redémarrage. Ces 2 formes de table partagent cependant les mêmes codes pour l’atomicité, l’isolation et la consistance des transactions. La seule différence est que les tables durables possèdent un mécanisme de “log” et de “checkpoint”. (Nous verrons ce système un peu plus loin).
Indexe obligatoire
Lorsque les données sont montées en mémoire, la seule solution que possède le moteur “Hekaton” pour accéder aux données est l’utilisation des index. Par conséquent, chaque table en mémoire doit avoir impérativement au moins un index. Les tables durables doivent avoir une clef primaire. Les index contiennent uniquement des pointeurs vers les lignes stockées en mémoire.
Structure des données
Les données en mémoire ne sont plus stockées par page de 8k mais par ligne dont la structure est la suivante : une entête (Row header) et la donnée (Payload):
Schéma 1 : Structure des données
Entête
Un entête de ligne occupe au moins 30 Octets et est composé de :
- Begin-Timestamp : Défini l’instant où la transaction a inséré la ligne
- End-Timestamp : Défini l’instant où la ligne a été supprimée
- StmID : au sein d’une transaction, chaque instruction SQL possède un identifiant unique (StmID). Lorsqu’une ligne est créée, le StmID est inscrit dans l’entête
- IdxLinkCount : Référence le nombre d’index qui pointe sur cette ligne
- Index Pointers : Espace réservé pour les pointeurs d’index, cet espace comprend 64 bits multiplié par le nombre d’index. Il prend donc au minimum 64 bits vu qu’il est obligatoire d’avoir au moins un index. C’est via cet “index pointer” que les lignes ayant la même valeur de “bucket” seront liées dans la même chaîne.
Données
La partie “Payload” du Schéma 1 contient en fait les données elle–même (les colonnes clefs et les autres colonnes), et dépend de la définition de la table. Les dlls* générées lors de la création de la table “savent” comment accéder aux données et comment les interpréter. Le moteur Hekaton, au final, ne sait rien de plus sur les données.
* ( je n’en parlerai pas dans cet article, mais le moteur “Hekaton” crée des dlls pour effectuer des opérations sur les tables en mémoire. Le moteur n’accède donc pas en direct aux tables mais passe par ces dlls. Plus de détails ici)
Les Index
Comme nous avons vu précédemment, les index ne sont plus stockés sur disque lorsque l’on utilise une table en mémoire. Ils sont construits à la volée et résident en mémoire. Il existe 2 sortes d’index : Les “Hash index” et les “Range index”. Pour une table en mémoire, il est possible de définir jusqu’à 8 index.
Hash Index (indexe de hachage)
Les “Hash Index” contiennent un nombre fixe d’espace (“bucket”). Le nombre d’espace est précisé lors de la création de la table ou de l’index. Dans ces espaces, il sera stocké un pointeur vers une chaîne de lignes. Lors de la création d’un “Hash index”, il est nécessaire de définir cet espace via la commande “Bucket-count”. Cela permet de réserver la place nécessaire pour créer l’index. Attention, cela peut avoir des impacts sur la performance. Si la valeur du “bucket_count” est trop petite, de trop nombreuses lignes seront “enchainées” à partir de la même valeur d’index et cela réduira la performance générale des requêtes. Dans le cas contraire, si le “Bucket_count” est trop important, il y’a un risque d’utiliser de la mémoire vive pour rien. Il faut aussi savoir que le nombre spécifié dans le “Bucket_count” est arrondi à la puissance 2 supérieure.
D’après les informations que j’ai pu avoir, l’idéal est de définir un “Bucket_count” égal au nombre de valeurs distinctes.
Pour un index sur une colonne, la requête suivante peut aider :
Select Count(distinct [MaColonne]) from MaTable
Pour un index composite :
Select count (*) from
( Select distinct MaColonne1, MaColonne2 from MaTable) Nb
Les index de hachage sont plutôt performants si les requêtes contiennent des clauses d’égalité.
La DMV “sys.dm_db_xtp_hash_index_stats” permet d’obtenir des informations sur les hash index.
Range Index (index non-cluster)
Les “range index” s’appuient sur le principe des “BW-tree”, afin de créer une structure ordonnée, permettant un scan efficace d’une large plage de données. Par exemple lors d’une requête avec une clause comme MaValeur > 30.
Imaginons la table en mémoire ci-dessous et son allocation mémoire:
L’index non-cluster sera de la forme ci-dessous, trié sur la colonne nom :
Et les données seront liées entre elles via la partie “Index pointer” de l’entête :
Ci-dessous, voici une représentation de l’index non-cluster (niveau feuille) et la correspondance avec les données. L’index pointe sur le début de la chaîne (quand il y en a une) et le pointeur d’index permet de retrouver les autres membres de la chaîne :
En fouillant un peu sur les ranges index, j’ai trouvé que les “Range Index” utilisent le principe des “skip-list” pour accélérer l’accès aux données. Ci-dessous, une explication rapide de ce qu’est une “skiplist”.
“Skip list” (Liste à enjambements)
Un “range index” s’implémente sur ce que l’on appelle une “skip-list”. Une structure “skip-list” est une liste d’éléments. Ces éléments sont liés entre eux par ordre croissant. Certains nœuds de cette liste seront plus grands que d’autres et seront appelés “tours” (towers). Les tours contiennent des liens vers les autres tours de même taille. Ces liens permettent un passage rapide entre les différents niveaux lors de la recherche d’une information. D’où le nom de “skip list”.
L’idée générale de la “skip-list” est de partir du niveau le plus haut du nœud de tête et, en restant sur le même niveau, de suivre les liens entre les nœuds de même niveau jusqu’à la valeur souhaitée, ou jusqu’à la fin du nœud si la valeur n’a pas été trouvée. Si la valeur en fin de nœud est inférieure à la valeur cherchée, tout en restant sur le même nœud on passe au niveau inférieur et on continue la recherche sur le nouveau niveau. Afin d’essayer d’être plus clair, voici un exemple. Ci-dessous une représentation d’une “skip-list” avec 10 éléments (sur un schéma simplifié) :
Dans le cas d’une recherche de l’élément 12, voici les grandes étapes de recherche :
- En commençant par l’élément haut du nœud de la racine (root), la première flèche pointe sur le nœud 6. Donc 6 est plus petit que 12. C’est le dernier nœud de ce niveau. A partir du nœud 6, on passe au niveau inférieur
- A partir de ce nouveau niveau, depuis le nœud 6, on passe au nœud suivant qui est le nœud 25. 25 est supérieur à 12, donc on reste sur le nœud 6 et on passe encore au niveau inférieur.
- A partir de ce niveau, en étant toujours au nœud 6, on passe au nœud suivant qui est le numéro 9. 9 est inférieur à 12. On définit le nœud de départ à 9
- En restant sur le nœud 9, on passe au niveau inférieur et on trouve le nœud 12.
Voilà pour une présentation rapide du range index et du principe de la “skip list”. Pour plus de détail sur les skip-lists, un article est disponible ici
Liens entre les données et les indexes
Comme nous avons vu plus haut, le seul moyen pour retrouver les données est de passer par un index, d’où l’obligation d’avoir au moins un index. Chaque ligne est constituée d’une entête en plus des données, et cette entête possède des informations de liaison vers les autres lignes possédant la même information d’index.
La figure ci-dessous, que l’on retrouve dans de nombreuses documentations, illustre comment les lignes sont liées via un index “Hash index” et un “Range index”. Une ligne de données est constituée d’un Nom et d’une Ville. Un “Hash index est créé sur la colonne Ville et un “Range index” est créé sur la colonne Nom.
Un “hash index” sur la colonne Ville. Le premier “Bucket” pointe sur le nom de la ville qui commence par la lettre B. Il pointe sur l’enregistrement Susan/Bogota, ce denier pointant à son tour sur l’enregistrement suivant dont le nom de la ville commence aussi par B. Les enregistrements appartenant au même “bucket” sont donc liés entre eux. Le principe est le même pour les villes dont le nom commence par P. (Même si sur le schéma B et P sont visuellement ordonnés, il n’ y a en réalité aucun classement sur un “Hash index”).
Un “Range index” (skip list) sur la colonne Nom. Dans le schéma ci-dessous, seule une liste ordonnée des index est représentée et liée aux données :
La durabilité des données
Nous avons vu plus haut qu’il existe 2 types de table : Durable et non-durable. Dans le cas des premières, il est donc nécessaire de prévoir un mécanisme d’écriture sur disque afin de conserver les données même en cas de défaillance du serveur.
Les tables “Hekaton” sont optimisées pour résider en mémoire et n’ont pas besoin d’avoir accès aux disques durs (La fameuse garantie que les données résident toujours en mémoire). Mais pour des raisons de récupération des données, il est nécessaire de rendre les données persistantes. Le moteur “Hekaton” n’utilise pas le “Buffer pool” et ne stocke pas les données sur disque sous forme de pages de 8k. En réalité, “Hekaton” ne stocke rien dans des fichiers de données, mais s’appuie sur la technologie “filestream” et stocke les données sous forme de blob (Binary Large OBject).
Pour les tables en mémoire, les logs sont aussi écrits sur disque au moment où une transaction est terminée avec succès. Ce comportement est identique à celui des tables classiques (stockées sur disque ou “disk-based table”), au détail prêt que ce mécanisme est plus optimisé et que les index ne sont pas logués dans le cas des tables en mémoire.
On a vu que “Hekaton” ne stocke pas les données sous forme de pages mais que les données sont éparpillées en mémoire (les données ne sont pas regroupées par page). Au moment du “Checkpoint”, les logs de transactions (ou le cache log) sont scannés et les lignes sont stockées sur disque dans des fichiers de “chekpoint”. Ces fichiers de “checkpoint” sont en fait des fichiers “FileStream” et ne servent que pour restaurer les données. Le moteur “Hekaton” possède un mécanisme appelé “Offline Chekpoint” qui lit de manière continue les logs et stocke sur disque les modifications réalisées sur les données.
Il y a 3 types de fichier “Checkpoint” :
- Data file : Le “data file” est organisé de manière chronologique et contient uniquement une insertion d’une version de ligne pour une plage de temps donnée. Cette insertion dans le fichier est “provoquée” soit par un “INSERT” ou un “UPDATE”. Un “data file” est caractérisé par la plage de temps qu’il couvre et ne contient donc que des transactions comprises dans cette plage de temps. La taille typique de “data file” est de 128MB. (pour des serveurs avec moins de 16GB de RAM, ce fichier est souvent à 16 MB). Ce fichier peut parfois être plus grand que 128 MB car une transaction ne peut pas être stockée dans 2 “data file” différents.
- Delta File : Ce fichier ne contiendra que les IDs des lignes effacées. Lors de la restauration, ce fichier servira de filtre afin d’éviter de recharger la mémoire avec les lignes effacées. Un fichier “delta file” à une taille typique de 8MB (pour les serveurs de moins de 16 GB de RAM, la taille du “delta file” peut être de 1 MB). Chose importante, un “delta file” est associé à un seul “data file”
- Checkpoint File Directory (CFD) : Ce fichier contient les références de tous les “data file” et “delta file”
C’est lors du process de “checkpoint” que les données sont écrites dans les fichiers. De plus, lorsqu’une ligne est effacée, “Hekaton” ne revient pas dans le fichier “Data file” pour supprimer physiquement la ligne, mais rajoute cette information dans le fichier “delta file”.
Donc plus le temps passe, plus les fichiers “data file” risquent de se retrouver avec plus de lignes supprimées que de lignes encore “actives”. Cela peut poser des problèmes d’utilisation inutile d’espace disque et de temps perdu lors d’un process de restauration des données.
Pour optimiser l’utilisation des fichiers “chekpoint”, il existe un process appelé “Merge” qui périodiquement va nettoyer les fichiers “data file” et les fusionner entre eux. Cette opération de “merge” est détaillée ici.
Durant les ITCamps, la question suivante m’a été posée : “Est-il possible de réaliser une opération de “merge” manuellement? . La réponse est oui. En voici un exemple :
Pour réaliser un “merge” manuel il faut utiliser la procédure suivante : sys.sp_merge_xtp_checkpoint_files.
Avant de faire le merge nous allons regarder l’utilisation de nos fichiers “chekpoint” avec la procédure sys.dm_db_xtp_checkpoint_files.
SELECT checkpoint_file_id, file_type_desc, internal_storage_slot, file_size_in_bytes, file_size_used_in_bytes, inserted_row_count, deleted_row_count, lower_bound_tsn, upper_bound_tsn
FROM
sys.dm_db_xtp_checkpoint_files
WHERE [state]=2
ORDER BY file_type_desc, upper_bound_tsn
Admettons que nous souhaitons fusionner les 3 fichiers sélectionnés, nous allons utiliser le script suivant. Dans cet exemple j’utilise la base de données en mémoire “TicketReservations”:
EXEC sys.sp_xtp_merge_checkpoint_files 'TicketReservations', 19487, 77807.
Si on ré-exécute le script (celui avec sys.dm_db_xtp_checkpoint_files) pour regarder l’utilisation des fichiers “checkpoint”, nous pouvons voir que nos 3 fichiers ont bien été fusionnés :
Ensuite il est nécessaire d’exécuter manuellement le “garbage collection” avec la procédure suivante :
EXEC sp_filestream_force_garbage_collection
Pour info, le trace flag 9851 permet de désactiver la fusion des “checkpoint file”
Création d’une table en mémoire
Bon tout ça, c’est bien mais la question est “Comment crée-t-on une table en mémoire?”. Tout simplement avec un CREATE TABLE et quelques instructions propres aux tables en mémoires
Dans un premier temps, il faut créer une base de données avec un groupe de fichier FileStream
Création de la base de données :
CREATE DATABASE TicketReservations2
ON
PRIMARY(NAME = [TicketReservations2_data],
FILENAME = 'D:\DemoHekaton\TicketReservations2_data.mdf', size=2GB)
LOG ON (name = [TicketReservations_log], Filename='D:\DemoHekaton\TicketReservations2_log.ldf', size=5GB)
COLLATE Latin1_General_100_BIN2;
go
print 'done...'
go
ALTER DATABASE TicketReservations2 set recovery full
goParamétrage du FileStream :
use TicketReservations2
go
ALTER DATABASE TicketReservations2 ADD FILEGROUP [TicketReservations2_hk_fs_fg] CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE TicketReservations2
ADD FILE (NAME = [TicketReservations2_fs_dir],
FILENAME = 'D:\DemoHekaton\TicketReservations2_fs_dir') to FILEGROUP [TicketReservations2_hk_fs_fg];
goCréation d’une table en mémoire :
CREATE TABLE TicketReservationDetail (
iteration int not null,
lineId int not null,
col3 nvarchar(1000) not null, -- updatable column
ThreadID int not null
constraint sql_ts_th primary key
nonclustered hash (iteration, lineId) with (bucket_count = 10000000)) with (memory_optimized = on, durability = SCHEMA_AND_DATA)
goCi-dessus nous avons donc créé une table en mémoire avec la commande “memory_optimized = on”. De plus, cette table est durable car nous avons défini la durabilité à “SCHEMA_AND_DATA”. Si l’on ne souhaite pas avoir de durabilité alors on utilise le mot clef : “SHEMA_ONLY” .
A ce stade-là, nous n’avons toujours pas évoqué les procédures stockées. Ce qui fait que pour le moment nous sommes en mode interopérabilité . C’est à dire que les tables sont en mémoire mais les procédures stockées n’ont pas bougées, et conservent leur fonctionnement originel. De plus, ces dernières sont capables, dans le mode interopérabilité, d’accéder aussi bien aux tables classiques (“disk_based table”) qu’aux tables en mémoire.
Il existe un second mode qui est le mode natif, c’est à dire que tout est en mémoire, procédure stockée comprise. Cela permet d’améliorer encore les performances d’accès aux données. Ci-dessous un exemple réalisé sur ma machine de démo (Lenovo W530 avec 32Go de RAM), dans les 3 modes:
En mode natif, j’obtiens un gain multiplié par 66 par rapport à une table normale (“disk-based table”).
Pour plus de détail sur la technologie en mémoire incluse nativement dans SQL Server Enterprise Edition, vous pouvez consulter l’article suivant :
https://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx
Voilà pour cet article présentant rapidement les tables en mémoire de SQL Server 2014. Afin d’agrémenter cet article, nous avons réalisé une vidéo avec Sébastien Pertus:
Dans cette vidéo nous présenterons aussi l’outil AMR, dont je n’ai pas parlé dans ce billet, qui permet de récolter des informations sur l’usage de vos bases de données et vous proposer les tables qui tireraient un bénéfice de migrer en table en mémoire. Vous trouverez plus d’informations sur l’outil AMR (Analyse, Migrate and Report) dans cet article :
https://msdn.microsoft.com/en-us/library/dn205133(v=sql.120).aspx
Au plaisir de vous voir lors d’un IT Camp SQL 2014/Power BI.
Pour tester Windows Server 2012, Windows 8, SQL Server 2012, SQL Server 2014 CTP2 et Power BI, vous pouvez télécharger gratuitement la version d’évaluation disponible sous la forme :
Windows Server 2012 :
- d'une image ISO : https://aka.ms/jeveuxwindows2012
- d'un fichier VHD avec un système préinstallé : https://aka.ms/jeveuxwindows2012
SQL Server 2012 :
Evaluation SQL Server 2014 :
Evaluation Power BI :
Testez Azure gratuitement pendant un mois :
