Estimer les besoins en mémoire des tables mémoire optimisées

S’applique à :SQL ServerAzure SQL DatabaseAzure 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.

Il est important d’avoir une estimation raisonnable des besoins en mémoire de chaque table optimisée en mémoire afin de pouvoir approvisionner le serveur avec suffisamment de mémoire. Cela s’applique aux nouvelles tables et aux tables migrées à partir de tables sur disque. 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 une migration de tables sur disque vers des tables mémoire optimisées, consultez Déterminer si une table ou une procédure stockée doit être transférée vers In-Memory OLTP pour obtenir des conseils sur les tables qui sont les mieux à 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

Dans SQL Server 2016 (13.x) et les versions ultérieures, il n’existe aucune limite sur la taille des tables optimisées en mémoire, bien que les tables doivent s’adapter à la mémoire. Dans SQL Server 2014 (12,x), la taille de données prise 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. La taille de la table optimisée en mémoire correspond approximativement à la taille de l’index clusterisé ou de l’ensemble 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 requise dépend de la charge de travail, mais pour être sécurisée, la recommandation consiste à commencer par deux fois la taille attendue des tables et des index optimisés en mémoire, et observer la consommation réelle de mémoire. 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 utilisant des bases de données plus volumineuses (par exemple, supérieures à 100 Go), la surcharge a tendance à être limitée (25 pour cent ou moins).

Pour plus d’informations sur la surcharge de mémoire potentielle dans le moteur OLTP In-Memory, consultez fragmentation de la mémoire.

Calcul détaillé des besoins en mémoire

• Exemple de table optimisée en mémoire

• Mémoire pour la table

• Mémoire pour les index

• Mémoire pour le contrôle de version de ligne

• Mémoire pour les variables de table

• Mémoire pour la croissance

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  

À l’aide de ce schéma, nous allons déterminer la mémoire minimale nécessaire pour cette table optimisée en mémoire.

Mémoire pour la table

Une ligne de table optimisée en mémoire comporte 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 existe quatre index, chaque ligne alloue 32 octets pour les pointeurs d’index (un 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é que nous avons 5 millions de lignes, la table consomme 5 000 000 * 256 octets, ou 1 280 000 000 octets - 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. Dans l’exemple actuel, le nombre de valeurs Col2 uniques est un bon point de départ pour la taille du tableau pour le t1c2_index. Un tableau de hachage qui est trop volumineux gaspille de la mémoire. Un tableau de hachage trop petit ralentit les performances, car il y a trop de collisions des valeurs d'index qui hachent vers la même entrée d'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 devez estimer la taille du tableau ou collecter des données à partir de vos tests 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-feuilles
  Pour une configuration classique, la mémoire allouée aux nœuds non-feuilles est un petit pourcentage de la mémoire totale 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 avec la même clé (autrement dit, vous avez un index non cluster unique), il n’y a qu’une seule ligne dans le nœud feuille d’index qui pointe vers l’une des lignes avec les autres lignes liées les unes aux autres. Ainsi, la mémoire totale requise peut être estimée par :

  • mémoirePourIndexNonCluster = (tailleDuPointeur + somme(tailleDesTypesDeDonnéesDesColonnesClés)) * lignesAvecClésUniques

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 anciennes versions des lignes (y compris celles qui ont été supprimées) disponibles jusqu'à ce que toutes les transactions susceptibles d'utiliser ces versions aient terminé leur exécution.

É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

Les besoins en mémoire des lignes obsolètes sont ensuite estimés en multipliant le nombre de lignes obsolètes par la taille d’une ligne de table optimisée en mémoire. Pour plus d’informations, consultez Mémoire pour la table.

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 lot SQL volumineux au lieu d’une procédure stockée et utilisées dans de nombreuses transactions peuvent consommer une grande quantité 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 précédents évaluent vos besoins en mémoire pour 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 prévoyez une croissance de 10%, vous devez multipler les résultats précédents de 1,1 pour obtenir la mémoire totale nécessaire pour votre table.

Fragmentation de la mémoire

Pour éviter la surcharge des appels d’allocation de mémoire et améliorer les performances, le moteur In-Memory OLTP demande toujours de la mémoire à partir du système d’exploitation SQL Server (SQLOS) à l’aide de blocs de 64 Ko, appelés superblocks.

Chaque superblock contient des allocations de mémoire uniquement dans une plage de tailles spécifique, appelée classe de taille. Par exemple, le superblock A peut avoir des allocations de mémoire dans la classe de taille de 1 à 16 octets, tandis que le superblock B peut avoir des allocations de mémoire dans la classe de taille de 17 à 32 octets, et ainsi de suite.

Par défaut, les superblocks sont également partitionnés par processeur logique. Cela signifie que pour chaque processeur logique, il existe un ensemble distinct de superblocks, plus décomposé par classe de taille. Cela réduit la contention d’allocation de mémoire entre les requêtes s’exécutant sur différents processeurs.

Lorsque le moteur OLTP In-Memory effectue une nouvelle allocation de mémoire, il tente d’abord de trouver de la mémoire libre dans un superblock existant pour la classe de taille demandée et pour le traitement du processeur de la requête. Si cette tentative réussit, la valeur de la used_bytes colonne dans sys.dm_xtp_system_memory_consumers pour un consommateur de mémoire spécifique augmente par la taille de mémoire demandée, mais la valeur de la allocated_bytes colonne reste la même.

S’il n’y a pas de mémoire libre dans les superblocks existants, un nouveau superblock est alloué et la valeur dans les used_bytes augmente de la taille de mémoire demandée, tandis que la valeur dans la colonne allocated_bytes augmente de 64 Ko.

Au fil du temps, étant donné que la mémoire dans les superblocks est allouée et libérée, la quantité totale de mémoire consommée par le moteur OLTP In-Memory peut devenir beaucoup plus grande que la quantité de mémoire utilisée. En d’autres termes, la mémoire peut devenir fragmentée.

La ramasse-miettes peut réduire la mémoire utilisée, mais elle réduit uniquement la mémoire allouée si un ou plusieurs superblocks deviennent vides et sont désalloués. Cela s’applique à la fois au ramasse-miettes automatique et au ramasse-miettes forcé à l’aide de la procédure stockée système sys.sp_xtp_force_gc.

Si la fragmentation de la mémoire In-Memory du moteur OLTP et l’utilisation de la mémoire allouée deviennent plus élevées que prévu, vous pouvez activer l’indicateur de trace 9898. Cela modifie le schéma de partitionnement des superblocs de par CPU à par nœud NUMA (Non-Uniform Memory Access), ce qui réduit le nombre total de superblocs et le potentiel de fragmentation de mémoire élevée.

Cette optimisation est plus pertinente pour les machines volumineuses avec de nombreuses UC logiques. L’inconvénient de cette optimisation est une augmentation potentielle de la contention d’allocation de mémoire résultant d'un nombre réduit de superblocks, ce qui pourrait réduire la performance globale de la charge de travail. Selon les modèles de charge de travail, la réduction du débit due à l'utilisation du partitionnement de mémoire par NUMA peut être perceptible ou non.

Contenu connexe

• Migration vers OLTP en mémoire