Détermination du nombre correct de compartiments pour les index de hachage

Vous devez spécifier une valeur pour le BUCKET_COUNT paramètre lorsque vous créez la table optimisée en mémoire. Cette rubrique fournit des recommandations pour déterminer la valeur appropriée pour le BUCKET_COUNT paramètre. Si vous ne pouvez pas déterminer le nombre de compartiments correct, utilisez plutôt un index non cluster. Une valeur incorrecte BUCKET_COUNT , en particulier une valeur trop faible, peut avoir un impact significatif sur les performances de la charge de travail, ainsi que sur le temps de récupération de la base de données. Il est préférable de surestimer le nombre de seaux.

Les clés d’index en double peuvent réduire les performances avec un index de hachage, car les clés sont hachées dans le même compartiment, ce qui allonge la chaîne de ce compartiment.

Pour plus d’informations sur les index de hachage non constellés, consultez Index de hachage et Lignes directrices pour l’utilisation des index sur les tables Memory-Optimized.

Une table de hachage est allouée pour chaque index de hachage sur une table optimisée en mémoire. La taille de la table de hachage allouée pour un index est spécifiée par le BUCKET_COUNT paramètre dans CREATE TABLE (Transact-SQL) ou CREATE TYPE (Transact-SQL). Le nombre de compartiments sera arrondi en interne jusqu’à la puissance suivante de deux. Par exemple, la spécification d’un nombre de compartiments de 300 000 entraîne un nombre réel de compartiments de 524 288.

Pour obtenir des liens vers un article et une vidéo sur le nombre de compartiments, consultez Comment déterminer le nombre de compartiments approprié pour les index de hachage (In-Memory OLTP).

Recommandations

Dans la plupart des cas, le nombre de compartiments devrait idéalement être égal à 1 à 2 fois le nombre de valeurs distinctes présentes dans la clé d'index. Si la clé d’index contient beaucoup de valeurs en double, en moyenne, il y a plus de 10 lignes pour chaque valeur de clé d’index, utilisez plutôt un index non cluster.

Vous ne pourrez peut-être pas toujours prédire le nombre de valeurs qu’une clé d’index particulière peut avoir ou aura. Les performances doivent être acceptables si la BUCKET_COUNT valeur est comprise entre 5 fois le nombre réel de valeurs de clé.

Pour déterminer le nombre de clés d’index uniques dans les données existantes, utilisez des requêtes similaires aux exemples suivants :

Clé primaire et index uniques

Étant donné que l’index de clé primaire est unique, le nombre de valeurs distinctes dans la clé correspond au nombre de lignes de la table. Pour obtenir un exemple de clé primaire sur (SalesOrderID, SalesOrderDetailID) dans la table Sales.SalesOrderDetail dans la base de données AdventureWorks, émettez la requête suivante pour calculer le nombre de valeurs de clé primaire distinctes, qui correspond au nombre de lignes de la table :

SELECT COUNT(*) AS [row count]   
FROM Sales.SalesOrderDetail  

Cette requête affiche un nombre de lignes de 121 317. Utilisez 240 000 comme nombre de compartiments si le nombre de lignes ne changera pas considérablement. Utilisez un nombre de buckets de 480 000 si le nombre de commandes dans la table est prévu de quadrupler.

Index non unique

Pour d’autres index, par exemple un index à plusieurs colonnes sur (SpecialOfferID, ProductID), émettez la requête suivante pour déterminer le nombre de valeurs de clé d’index uniques :

SELECT COUNT(*) AS [SpecialOfferID_ProductID index key count]  
FROM   
   (SELECT DISTINCT SpecialOfferID, ProductID   
    FROM Sales.SalesOrderDetail) t  

Cette requête retourne un nombre de clés d’index pour (SpecialOfferID, ProductID) de 484, indiquant qu’un index non cluster doit être utilisé au lieu d’un index de hachage non cluster.

Détermination du nombre de doublons

Pour déterminer le nombre moyen de valeurs dupliquées pour une valeur de clé d’index, divisez le nombre total de lignes par le nombre de clés d’index uniques.

Pour l’exemple d’index sur (SpecialOfferID, ProductID), cela conduit à 121317 / 484 = 251. Cela signifie que les valeurs de clé d’index ont une moyenne de 251, ce qui doit être un index non cluster.

Résolution des problèmes liés au nombre de compartiments

Pour résoudre les problèmes de nombre de compartiments dans les tables mémoire optimisées, utilisez sys.dm_db_xtp_hash_index_stats (Transact-SQL) pour obtenir des statistiques sur les compartiments vides et la longueur des chaînes de lignes. La requête suivante peut être utilisée pour obtenir des statistiques sur tous les index de hachage dans la base de données active. La requête peut prendre plusieurs minutes pour s’exécuter s’il existe de grandes tables dans la base de données.

SELECT   
   object_name(hs.object_id) AS 'object name',   
   i.name as 'index name',   
   hs.total_bucket_count,  
   hs.empty_bucket_count,  
   floor((cast(empty_bucket_count as float)/total_bucket_count) * 100) AS 'empty_bucket_percent',  
   hs.avg_chain_length,   
   hs.max_chain_length  
FROM sys.dm_db_xtp_hash_index_stats AS hs   
   JOIN sys.indexes AS i   
   ON hs.object_id=i.object_id AND hs.index_id=i.index_id  

Les deux indicateurs clés de la santé des index de hachage sont les suivants :

pourcentage_de_seau_vide
empty_bucket_percent indique le nombre de compartiments vides dans l’index de hachage.

Si empty_bucket_percent est inférieur à 10 pour cent, le nombre de compartiments est susceptible d’être trop faible. Dans l’idéal, le empty_bucket_percent doit être supérieur ou égal à 33 %. Si le nombre de compartiments correspond au nombre de valeurs de clé d’index, environ 1/3 des compartiments est vide, en raison de la distribution de hachage.

longueur_moyenne_de_chaîne
avg_chain_length indique la longueur moyenne des chaînes de lignes dans les compartiments de hachage.

Si avg_chain_length est supérieur à 10 et empty_bucket_percent est supérieur à 10 pour cent, il existe probablement de nombreuses valeurs de clé d’index en double et un index non cluster serait plus approprié. Une longueur moyenne de chaîne de 1 est idéale.

Il existe deux facteurs qui affectent la longueur de la chaîne :

1. Doublons; toutes les lignes dupliquées font partie de la même chaîne dans l’index de hachage.

2. Plusieurs valeurs de clé sont attribuées au même seau. Plus le nombre de compartiments est faible, plus les compartiments auront de multiples valeurs attribuées.

Par exemple, considérez le tableau et le script suivants pour insérer des exemples de lignes dans le tableau :

CREATE TABLE [Sales].[SalesOrderHeader_test]  
(  
   [SalesOrderID] [uniqueidentifier] NOT NULL DEFAULT (newid()),  
   [OrderSequence] int NOT NULL,  
   [OrderDate] [datetime2](7) NOT NULL,  
   [Status] [tinyint] NOT NULL,  
  
PRIMARY KEY NONCLUSTERED HASH ([SalesOrderID]) WITH ( BUCKET_COUNT = 262144 ),  
INDEX IX_OrderSequence HASH (OrderSequence) WITH ( BUCKET_COUNT = 20000),  
INDEX IX_Status HASH ([Status]) WITH ( BUCKET_COUNT = 8),  
INDEX IX_OrderDate NONCLUSTERED ([OrderDate] ASC),  
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
DECLARE @i int = 0  
BEGIN TRAN  
WHILE @i < 262144  
BEGIN  
   INSERT Sales.SalesOrderHeader_test (OrderSequence, OrderDate, [Status]) VALUES (@i, sysdatetime(), @i % 8)  
   SET @i += 1  
END  
COMMIT  
GO  

Le script insère 262 144 lignes dans la table. Il insère des valeurs uniques dans l’index de clé primaire et dans IX_OrderSequence. Il insère beaucoup de valeurs en double dans l’index IX_Status : le script génère uniquement 8 valeurs distinctes.

La sortie de la requête de résolution des problèmes de BUCKET_COUNT est la suivante :

Considérez les trois index de hachage sur cette table :

• IX_Status : 50 % des compartiments sont vides, ce qui est bon. Toutefois, la longueur moyenne de la chaîne est très élevée (65 536). Cela indique un grand nombre de valeurs en double. Par conséquent, l’utilisation d’un index de hachage non cluster n’est pas appropriée dans ce cas. Un index non cluster doit être utilisé à la place.

• IX_OrderSequence : 0 % des compartiments sont vides, ce qui est trop faible. En outre, la longueur moyenne de la chaîne est de 8. Comme les valeurs de cet index sont uniques, cela signifie qu’en moyenne 8 valeurs sont mappées à chaque compartiment. Le nombre de seaux doit être augmenté. Étant donné que la clé d’index a 262 144 valeurs uniques, le nombre de compartiments doit être d’au moins 262 144. Si la croissance future est attendue, le nombre devrait être plus élevé.

• Index de clé primaire (PK__SalesOrder...) : 36 % des compartiments sont vides, ce qui est bon. En outre, la longueur moyenne de la chaîne est 1, ce qui est également bon. Aucune modification n’est nécessaire.

Pour plus d’informations sur la résolution des problèmes liés à vos index de hachage à mémoire optimisée, consultez Résolution des problèmes courants de performances avec les index de hachage Memory-Optimized.

Considérations détaillées relatives à l’optimisation

Cette section décrit d’autres considérations relatives à l’optimisation du nombre de compartiments.

Pour obtenir les meilleures performances pour les index de hachage, équilibrez la quantité de mémoire allouée à la table de hachage et le nombre de valeurs distinctes dans la clé d’index. Il existe également un équilibre entre les performances des recherches de points et les analyses de table :

• Plus la valeur du nombre de compartiments est élevée, plus les compartiments vides sont présents dans l’index. Cela a un impact sur l’utilisation de la mémoire (8 octets par compartiment) et les performances des analyses de table, car chaque compartiment est analysé dans le cadre d’une analyse de table.

• Plus le nombre de compartiments est inférieur, plus les valeurs sont affectées à un seul compartiment. Cela réduit les performances des recherches de points et des insertions, car SQL Server peut avoir besoin de parcourir plusieurs valeurs dans un seul compartiment pour rechercher la valeur spécifiée par le prédicat de recherche.

Si le nombre de compartiments est nettement inférieur au nombre de clés d'index uniques, de nombreuses valeurs seront associées à chaque compartiment. Cela dégrade les performances de la plupart des opérations DML, en particulier les recherches pointées (recherches de clés d’index individuelles) et les opérations d’insertion. Par exemple, vous pouvez observer des performances médiocres des requêtes SELECT, ainsi que des opérations UPDATE et DELETE avec des prédicats d’égalité correspondant aux colonnes clés de l'index dans la clause WHERE. Un nombre de compartiments faible affecte également le temps de récupération de la base de données, car les index sont recréés au démarrage de la base de données.

Valeurs de clé d’index dupliquées

Les valeurs en double peuvent augmenter l'impact des collisions de hachage sur les performances. Cela n’est généralement pas un problème si chaque clé d’index a un faible nombre de doublons. Toutefois, cela peut être un problème si l’écart entre le nombre de clés d’index uniques et le nombre de lignes dans les tables devient très volumineux.

Toutes les lignes avec la même clé d'index passeront dans la même chaîne de doublons. Si plusieurs clés d’index se trouvent dans le même compartiment en raison d’une collision de hachage, les scanneurs d’index doivent toujours analyser la chaîne complète dupliquée pour la première valeur avant de pouvoir localiser la première ligne correspondant à la deuxième valeur. Les clés en double rendent également la collecte des déchets plus difficile à localiser la ligne. Par exemple, s’il existe 1 000 doublons pour une clé et qu’une des lignes est supprimée, le garbage collector doit analyser la chaîne de 1 000 doublons pour dissocier la ligne de l’index. Cela est vrai même si la requête qui a trouvé la suppression a utilisé un index plus efficace (un index de clé primaire) pour localiser la ligne, car le collecteur de déchets doit se dissocier de chaque index.

Pour les index de hachage, il existe deux façons de réduire le travail provoqué par des valeurs de clé d’index en double :

• Utilisez plutôt un index non cluster. Vous pouvez réduire les doublons en ajoutant des colonnes à la clé d’index sans nécessiter de modifications apportées à l’application.

• Spécifiez un nombre de catégories très élevé pour l’index. Par exemple, 20 à 100 fois le nombre de clés d’index uniques. Cela réduira les collisions de hachage.

Petites tables

Pour les tables plus petites, l’utilisation de la mémoire n’est généralement pas un problème, car la taille de l’index sera faible par rapport à la taille globale de la base de données.

Vous devez maintenant faire un choix en fonction du type de performance souhaité :

• Si les opérations critiques en matière de performances sur l’index sont principalement des recherches de points et/ou des opérations d’insertion, un nombre de compartiments plus élevé serait approprié pour réduire la probabilité de collisions de hachage. Trois fois le nombre de lignes ou encore plus serait la meilleure option.

• Si les analyses d'index complètes sont les opérations critiques pour les performances, utilisez un nombre de compartiments proche du nombre réel de valeurs de clé d'index.

Grandes tables

Pour les tables volumineuses, l'utilisation de la mémoire peut devenir un sujet de préoccupation. Par exemple, avec une table de 250 millions de lignes qui a 4 index de hachage, chacun avec un nombre de compartiments d’un milliard, la surcharge pour les tables de hachage est de 4 index * 1 milliard de compartiments * 8 octets = 32 gigaoctets d’utilisation de la mémoire. Lorsque vous choisissez un nombre de compartiments de 250 millions pour chacun des index, la surcharge totale pour les tables de hachage est de 8 gigaoctets. Notez que cela s’ajoute aux 8 octets d’utilisation de la mémoire que chaque index ajoute à chaque ligne individuelle, soit 8 gigaoctets dans ce scénario (4 index * 8 octets * 250 millions de lignes).

Les analyses complètes des tables ne sont généralement pas dans le chemin critique de performance pour les charges de travail OLTP. Par conséquent, le choix est entre l’utilisation de la mémoire et les performances des opérations de recherche de point et d’insertion :

• Si l’utilisation de la mémoire est un problème, choisissez un nombre de compartiments proche du nombre de valeurs de clé d’index. Le nombre de compartiments ne doit pas être nettement inférieur au nombre de valeurs de clé d’index, car cela affecte la plupart des opérations DML ainsi que le temps nécessaire pour récupérer la base de données après le redémarrage du serveur.

• Lors de l'optimisation des performances pour les recherches de points, il serait approprié d'avoir un nombre de compartiments égal à deux ou même trois fois le nombre de valeurs d'index uniques. Un nombre de compartiments plus élevé signifie une augmentation de l’utilisation de la mémoire et une augmentation du temps nécessaire pour une analyse d’index complète.

Voir aussi

Indices sur les tables Memory-Optimized