Résoudre les problèmes de performance courants avec les index de hachage mémoire optimisés
Cette rubrique traite du dépannage et des solutions de contournement des problèmes couramment rencontrés avec les index de hachage.
La recherche nécessite un sous-ensemble de colonnes clés d'index de hachage
Question: 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 de la clause WHERE, SQL Server ne peut pas utiliser un index pour rechercher les lignes correspondant aux prédicats dans la clause WHERE.
Par opposition, les index triés comme les index non cluster sur disque et les index non cluster mémoire optimisés, prennent en charge la recherche d'index sur un sous-ensemble de colonnes clés d'index, tant qu'il s'agit des premières colonnes 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 : 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 relativement simple, l'inspection du texte de la requête et de la définition d'index montrera également si la recherche nécessite un sous-ensemble de colonnes clés d'index.
Prenons l'exemple de la table et de la requête suivantes :
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). Comme la requête a des prédicats d’égalité uniquement sur un sous-ensemble des colonnes de clé d’index, SQL Server ne peut pas effectuer une opération de recherche d’index à l’aide de PK_od ; au lieu de cela, SQL Server doit revenir à une analyse d’index complète.
Solutions: Il existe un certain nombre de solutions de contournement possibles. Par exemple :
Recréez l'index en tant que type non cluster au lieu d'un hachage non cluster. L’index non cluster optimisé en mémoire est trié et, par conséquent, SQL Server peut effectuer une recherche d’index sur les colonnes de clé d’index de début. La définition de clé primaire de l'exemple serait
constraint PK_od primary key nonclustered.Modifiez la clé d'index actuelle pour correspondre aux colonnes dans la clause WHERE.
Ajoutez un nouvel index de hachage qui correspond aux colonnes dans la clause WHERE de la requête. Dans l'exemple, la définition de table s'apparenterait à ce qui 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é ne fonctionne pas de façon optimale s'il existe de nombreuses lignes dupliquées 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 dans la table, il n'est pas conseillé d'ajouter un index sur (o_id) ; à la place, changer le type de l'index PK_od en le modifiant d'index de hachage en index non cluster est la meilleure solution. Pour plus d'informations, consultez Determining the Correct Bucket Count for Hash Indexes.