Estimer les besoins en mémoire des tables mémoire optimisées
S’applique à : 
SQL Server base de données Azure SQL Azure SQL Managed Instance
Les tables mémoire optimisées nécessitent suffisamment de mémoire pour conserver tous les index et les lignes en mémoire. Dans la mesure où la mémoire est une ressource limitée, il est important de comprendre et de gérer l'utilisation de la mémoire sur votre système. Les rubriques de cette section traitent de scénarios courants d'utilisation et de gestion de la mémoire.
Si vous créez une table à mémoire optimisée ou migrez une table existante basée sur disque vers une table à mémoire optimisée OLTP en mémoire, il est important d’estimer avec justesse les besoins en mémoire de chaque table, de façon à provisionner le serveur avec suffisamment de mémoire. Cette section explique comment estimer la quantité de mémoire nécessaire pour accueillir les données d'une table mémoire optimisée.
Si vous envisagez d’effectuer une migration à partir de tables sur disque vers des tables optimisées en mémoire, avant de poursuivre la lecture de cette rubrique, consultez Déterminer si une table ou une procédure stockée doit être déplacée vers l’OLTP en mémoire pour obtenir des conseils sur les tables les plus judicieuses à faire migrer. Toutes les rubriques sous Migration vers OLTP en mémoire fournissent des conseils sur la migration à partir de tables sur disque vers des tables optimisées en mémoire.
Conseils de base pour l’estimation des besoins en mémoire
À partir de SQL Server 2016 (13.x), il n’y a pas de limite à la taille des tables optimisées pour la mémoire, bien que les tables aient besoin de tenir en mémoire. Dans SQL Server 2014 (12.x), la taille des données prises en charge est de 256 Go pour les tables SCHEMA_AND_DATA.
La taille d’une table optimisée en mémoire correspond à celle des données plus une surcharge pour les en-têtes de ligne. Quand vous faites migrer une table sur disque vers une table optimisée en mémoire, la taille de la table optimisée en mémoire correspond à peu près à la taille de l’index cluster ou du segment de mémoire de la table sur disque d’origine.
Les index des tables optimisées en mémoire ont tendance à être plus petits que les index non-cluster des tables sur disque. La taille d’un index non-cluster est de l’ordre de [primary key size] * [row count]. La taille des index de hachage s’élève à [bucket count] * 8 bytes.
Lorsqu’il y a une charge de travail active, une mémoire supplémentaire est nécessaire pour prendre en compte le contrôle de version de lignes et diverses opérations. La quantité de mémoire nécessaire dans la pratique dépend de la charge de travail, mais par sécurité, il est recommandé de commencer par utiliser deux fois la taille attendue des tables optimisées en mémoire et des index, puis d’observer les besoins en mémoire à l’usage. La surcharge liée au contrôle de version de ligne dépend toujours des caractéristiques de la charge de travail (les transactions dont l’exécution est particulièrement longue augmentent cette surcharge). Pour la plupart des charges de travail qui utilisent des bases de données volumineuses, (par ex., >100 Go), la surcharge a tendance à être limitée (25 % ou moins).
Calcul détaillé des besoins en mémoire
Exemple de table optimisée en mémoire
Prenons le schéma de table mémoire optimisée suivant :
CREATE TABLE t_hk
(
col1 int NOT NULL PRIMARY KEY NONCLUSTERED,
col2 int NOT NULL INDEX t1c2_index
HASH WITH (bucket_count = 5000000),
col3 int NOT NULL INDEX t1c3_index
HASH WITH (bucket_count = 5000000),
col4 int NOT NULL INDEX t1c4_index
HASH WITH (bucket_count = 5000000),
col5 int NOT NULL INDEX t1c5_index NONCLUSTERED,
col6 char (50) NOT NULL,
col7 char (50) NOT NULL,
col8 char (30) NOT NULL,
col9 char (50) NOT NULL
) WITH (memory_optimized = on) ;
GO
Ce schéma va permettre de déterminer la mémoire minimale requise pour cette table mémoire optimisée.
Mémoire pour la table
Une ligne de table mémoire optimisée est composée de trois parties :
Horodatages
En-tête de ligne/horodateurs = 24 octets.Pointeurs d’index
Pour chaque index de hachage dans la table, chaque ligne a un pointeur d'adresse 8 octets vers la ligne suivante dans l'index. Étant donné qu'il y a quatre index, chaque ligne alloue 32 octets pour les pointeurs d'index (pointeur de 8 octets pour chaque index).Données
La taille de la partie données de la ligne est déterminé en additionnant la taille du type pour chaque colonne de données. Dans notre table, il y a cinq entiers de 4 octets, trois colonnes de type caractère de 50 octets et une colonne de type caractère de 30 octets. Par conséquent, la partie données de chaque ligne est de 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50 ou 200 octets.
Voici un calcul de taille de 5 millions de lignes dans une table mémoire optimisée : La quantité totale de mémoire utilisée par les lignes de données estimée est la suivante :
Mémoire pour les lignes de la table
Selon les calculs ci-dessus, la taille de chaque ligne de la table mémoire optimisée est de 24 + 32 + 200, ou 256 octets. Étant donné qu’il y a 5 millions de lignes, la table consommera 5 000 000 * 256 octets, ou 1 280 000 000 octets (soit environ 1,28 Go).
Mémoire pour les index
Mémoire pour chaque index de hachage
Chaque index de hachage est un tableau de hachage de pointeurs d'adresse 8 octets. La taille du tableau est mieux déterminée par le nombre de valeurs d’index uniques pour cet index, par exemple le nombre de valeurs uniques Col2 est un bon point de départ pour la taille du tableau de t1c2_index. Un tableau de hachage qui est trop volumineux gaspille de la mémoire. Un tableau de hachage qui est trop petit ralentit les performances, car il y a trop de collisions par valeurs d'index qui hachent au même index.
Les index de hachage exécutent des recherches d'égalité rapides, notamment :
SELECT * FROM t_hk
WHERE Col2 = 3;
Les index non cluster sont plus rapides pour les recherches de plage suivantes :
SELECT * FROM t_hk
WHERE Col2 >= 3;
Si vous migrez une table basée sur disque, utilisez les éléments suivants pour déterminer le nombre de valeurs uniques de l'index t1c2_index.
SELECT COUNT(DISTINCT [Col2])
FROM t_hk;
Si vous créez une table, vous devrez évaluer la taille du tableau ou regrouper les données de votre test avant le déploiement.
Pour plus d’informations sur le fonctionnement des index de hachage dans les tables optimisées en mémoire OLTP en mémoire, consultez Index de hachage.
Définition de la taille du tableau d'index de hachage
La taille du tableau d’index de hachage est définie par (bucket_count= value) où value est un entier supérieur à zéro. Si value n’est pas une puissance de 2, le nombre réel de compartiments (bucket_count) est arrondi à la puissance de 2 supérieur la plus proche. Dans notre exemple (bucket_count = 5000000), comme 5 000 000 n’est pas une puissance de 2, le nombre réel de compartiments est arrondi à 8 388 608 (2^23). Vous devez utiliser ce nombre, et non pas 5 000 000, lorsque vous calculez la mémoire nécessaire pour le tableau de hachage.
Ainsi, dans notre exemple, la mémoire nécessaire pour chaque tableau de hachage est :
8 388 608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67 108 864, soit environ 64 Mo.
Comme il y a trois index de hachage, la mémoire nécessaire pour les index de hachage est de 3 * 64 Mo = 192 Mo.
Mémoire pour les index non-cluster
Les index non cluster sont implémentés en tant qu’arbres Bw (Bw-tree) avec des nœuds internes contenant la valeur des index et les pointeurs vers les nœuds suivants. Les nœuds terminaux contiennent la valeur d'index et un pointeur vers la ligne de table en mémoire.
Contrairement aux index de hachage, les index non-cluster n’ont pas une taille fixe de compartiment. L'index augmente et se réduit de façon dynamique avec les données.
La mémoire nécessaire pour les index non-cluster peut être calculée de la façon suivante :
Mémoire allouée aux nœuds non terminaux
Pour une configuration spécifique, la mémoire allouée aux nœuds non terminaux représente un tout petit pourcentage de la mémoire globale utilisée par l'index. Il est si petit qu'il peut être ignoré sans risque.Mémoire allouée aux nœuds terminaux
Les nœuds terminaux ont une ligne pour chaque clé unique dans la table et elle pointe vers les lignes de données avec cette clé unique. Si vous avez plusieurs lignes comportant la même clé (c’est-à-dire que vous avez un index non-cluster non unique), il n’y a qu’une seule ligne dans le nœud terminal d’index qui pointe vers l’une des lignes, et les autres lignes sont liées entre elles. Ainsi, la mémoire totale requise peut être estimée par :- memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys
Les index non-cluster sont préférables lorsqu’ils sont utilisés pour les recherches de plage, comme l’illustre la requête suivante :
SELECT * FROM t_hk
WHERE c2 > 5;
Mémoire pour le contrôle de version de ligne
Pour éviter les verrous, OLTP en mémoire utilise l'accès concurrentiel optimiste lors de la mise à jour ou de la suppression des lignes. Cela signifie que lorsqu'une ligne est mise à jour, une autre version de la ligne est créée. Par ailleurs, les suppressions sont logiques : la ligne existante est marquée comme supprimée, mais n’est pas supprimée immédiatement. Le système conserve les versions précédentes des lignes (lignes supprimées incluses) tant que toutes les transactions susceptibles d’utiliser la version ne sont pas exécutées.
Étant donné qu'il peut y avoir beaucoup plus de lignes supplémentaires en mémoire à tout moment lors de l'attente du cycle de garbage collection pour libérer la mémoire, vous devez disposer de suffisamment de mémoire pour gérer ces autres lignes.
Le nombre de lignes en plus peut être estimé en calculant le nombre maximal de mises à jour et de suppressions de ligne par seconde, puis en le multipliant par le nombre de secondes nécessaires pour la plus longue transaction (au moins 1).
Cette valeur est ensuite multipliée par la taille de ligne pour obtenir le nombre d'octets nécessaires pour le contrôle de version de ligne.
rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond
La mémoire nécessaire pour les lignes obsolètes est ensuite estimée en multipliant le nombre de lignes obsolètes par la taille d’une ligne de table optimisée en mémoire (consultez Mémoire pour la table ci-dessus).
memoryForRowVersions = rowVersions * rowSize
Mémoire pour les variables de table
La mémoire utilisée pour une variable de table est libérée uniquement lorsque la variable de table sort de l'étendue. Les lignes supprimées d'une variable de table, y compris les lignes supprimées dans le cadre d'une mise à jour, ne sont pas concernées par l'opération de garbage collection. Aucun volume de mémoire n'est libéré avant que la variable de table sorte de l'étendue.
Les variables de table définies dans un grand lot SQL, par opposition à une étendue de procédure, et qui sont utilisées par plusieurs transactions, peuvent consommer beaucoup de mémoire. Étant donné qu'elles ne sont pas nettoyées, les lignes supprimées d'une variable de table peuvent utiliser beaucoup de mémoire et réduire les performances, car les opérations de lecture doivent analyser au-delà des lignes supprimées.
Mémoire pour la croissance
Les calculs ci-dessus estiment les besoins en mémoire de la table, telle qu'elle existe actuellement. Outre cette mémoire, vous devez évaluer la croissance de la table et fournir suffisamment de mémoire pour gérer cette croissance. Par exemple, si vous anticipez une croissance de 10 %, vous devez multiplier le résultat ci-dessus par 1,1 pour obtenir la mémoire totale nécessaire pour votre table.