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. Étant donné que la mémoire est une ressource finie, 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.

Que vous créez une table optimisée en mémoire ou que vous migrez une table sur disque existante vers une table oltp mémoire optimisée en mémoire, il est important d’avoir une estimation raisonnable des besoins en mémoire de chaque table afin de pouvoir 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 de migrer des tables sur disque vers des tables mémoire optimisées, avant de continuer dans cette rubrique, consultez la rubrique Déterminant si une table ou une procédure stockée doit être transférée vers OLTP en mémoire 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

À compter de SQL Server 2016 (13.x), 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. 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 existe une charge de travail active, une mémoire supplémentaire est nécessaire pour tenir compte du contrôle de version des lignes et de 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 utilisant des bases de données plus volumineuses (par exemple, >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

• 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 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 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é 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. Tableau de hachage trop volumineux qui gaspille 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 sur disque, vous pouvez utiliser ce qui suit pour déterminer le nombre de valeurs uniques pour 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, 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 ce n’est pas une puissance de 2, la bucket_count réelle est arrondie à la puissance la plus proche suivante de 2. Dans notre tableau d’exemple, (bucket_count = 50000000), puisque 5 000 000 n’est pas une puissance de 2, le nombre réel de compartiments arrondit jusqu’à 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 ou environ 64 Mo.

Étant donné que nous avons 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 de taille de compartiment fixe. 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 avec la même clé (c’est-à-dire que 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 :

  • 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 en mémoire à tout moment en attendant que le cycle de garbage collection libère leur mémoire, vous devez disposer d’une mémoire suffisante pour prendre en charge ces autres lignes.

Le nombre de lignes supplémentaires peut être estimé en calculant le nombre maximal de mises à jour et de suppressions de lignes par seconde, puis en multipliant cela par le nombre de secondes que la transaction la plus longue prend (minimum de 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, y compris les lignes supprimées dans le cadre d’une mise à jour, d’une variable de table ne sont pas soumises au 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 collectées par la mémoire, les lignes supprimées d’une variable de table peuvent consommer beaucoup de mémoire et dégrader 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.

Voir aussi

Migration vers OLTP en mémoire