Résolution des problèmes courants de performances avec les index de hachage Memory-Optimized

Cette rubrique se concentre sur la résolution et l’utilisation des problèmes courants liés aux index de hachage.

La recherche nécessite un sous-ensemble des colonnes clés de l'index de hachage.

Émettre: Les index de hachage nécessitent des valeurs pour toutes les colonnes de clé d’index afin de calculer la valeur de hachage et de localiser les lignes correspondantes dans la table de hachage. Par conséquent, si une requête inclut des prédicats d’égalité uniquement pour un sous-ensemble des clés d’index dans la clause WHERE, SQL Server ne peut pas utiliser une recherche d’index pour localiser les lignes correspondant aux prédicats dans la clause WHERE.

En revanche, les index ordonnés comme les index non cluster basés sur disque et les index non cluster optimisés en mémoire prennent en charge la recherche d’index sur un sous-ensemble des colonnes clés d’index, à condition qu’ils soient les colonnes principales de l’index.

Symptôme: Cela entraîne une dégradation des performances, car SQL Server doit exécuter des analyses de table complètes plutôt qu’une recherche d’index, ce qui est généralement une opération plus rapide.

Comment résoudre les problèmes suivants : Outre la dégradation des performances, l’inspection des plans de requête affiche une analyse au lieu d’une recherche d’index. Si la requête est assez simple, l’inspection du texte de la requête et de la définition d’index indique également si la recherche nécessite un sous-ensemble des colonnes clés d’index.

Considérez le tableau et la requête suivants :

CREATE TABLE [dbo].[od]  
(  
     o_id INT NOT NULL,  
     od_id INT NOT NULL,  
     p_id INT NOT NULL,  
     CONSTRAINT PK_od PRIMARY KEY NONCLUSTERED HASH (o_id, od_id) WITH (BUCKET_COUNT = 10000)  
)  
WITH (MEMORY_OPTIMIZED = ON)  
  
 SELECT p_id  
 FROM dbo.od  
 WHERE o_id=1  

La table a un index de hachage sur les deux colonnes (o_id, od_id), tandis que la requête a un prédicat d’égalité sur (o_id). Étant donné que la requête a des prédicats d’égalité uniquement sur un sous-ensemble des colonnes clés d’index, SQL Server ne peut pas effectuer une opération de recherche d’index à l’aide de PK_od ; à la place, SQL Server doit revenir à une analyse complète de l’index.

Solutions de contournement : Il existe un certain nombre de solutions de contournement possibles. Par exemple:

• Recréez l’index en tant que type non cluster au lieu de hachage non cluster. L’index non cluster optimisé en mémoire est ordonné et SQL Server peut donc effectuer une recherche d’index sur les colonnes clés d’index principales. La définition de clé primaire résultante pour l’exemple serait constraint PK_od primary key nonclustered.

• Modifiez la clé d’index actuelle pour qu’elle corresponde aux colonnes de la clause WHERE.

• Ajoutez un nouvel index de hachage qui correspond aux colonnes de la clause WHERE de la requête. Dans l’exemple, la définition de table résultante se présente comme suit :

  CREATE TABLE dbo.od  
   ( o_id INT NOT NULL,  
   od_id INT NOT NULL,  
   p_id INT NOT NULL,  
  
   CONSTRAINT PK_od PRIMARY KEY   
   NONCLUSTERED HASH (o_id,od_id) WITH (BUCKET_COUNT=10000),  
  
   INDEX ix_o_id NONCLUSTERED HASH (o_id) WITH (BUCKET_COUNT=10000)  
  
   ) WITH (MEMORY_OPTIMIZED=ON)  
  

Notez qu’un index de hachage à mémoire optimisée ne fonctionne pas de manière optimale s’il existe un grand nombre de lignes en double pour une valeur de clé d’index donnée : dans l’exemple, si le nombre de valeurs uniques pour la colonne o_id est beaucoup plus petit que le nombre de lignes de la table, il n’est pas optimal d’ajouter un index (o_id) ; Au lieu de cela, la modification du type de l’index PK_od de hachage en non cluster serait la meilleure solution. Pour plus d’informations, consultez Détermination du nombre de compartiments correct pour les index de hachage.

Voir aussi

Indexation sur les tables Memory-Optimized