Ce message est la troisième partie de la série sur les opérateurs de jointure (assurez-vous de lire la partie 1 - jointures de boucles imbriquées et la partie 2 - jointures de fusion ). La traduction de l'article a été préparée spécialement pour les étudiants du cours "MS SQL Server Developer" .
Les Hash Match Joins sont les chevaux de bataille fiables des opérateurs de connexion physique.
Alors que la jointure par boucles imbriquées échouera s'il y a trop de données pour tenir dans la mémoire, et que la jointure par fusion nécessitera le tri de l'entrée, Hash Match combinera toutes les données que vous soumettez (à condition que un prédicat d'égalité est exécuté pour la connexion, et jusqu'à présent il y a suffisamment d'espace libre dans votre tempdb).
Regarder des vidéos liées à YouTube
L'algorithme de correspondance de hachage se compose de deux étapes, qui fonctionnent comme suit:
Au cours de la première phase de génération, SQL Server crée une table de hachage en mémoire à partir de l'une des tables d'entrée (généralement la plus petite des deux). Les hachages sont calculés sur la base des touches d'entrée, puis stockés avec la ligne dans la table de hachage dans le bloc correspondant. Dans la plupart des cas, il n'y a qu'une seule ligne de données dans chaque bloc, sauf lorsque:
- Il y a des lignes avec des clés en double.
- La fonction de hachage crée une collision et des clés complètement différentes reçoivent le même hachage (c'est rare, mais possible).
Après avoir créé la table de hachage, l'étape «Sonde» (vérification) commence. Dans la deuxième étape, SQL Server calcule le hachage de clé pour chaque ligne de la deuxième table d'entrée et vérifie s'il existe dans la table de hachage créée à la première étape. S'il existe une correspondance pour ce hachage, il est vérifié si les clés des lignes de la table de hachage et des lignes de la deuxième table correspondent vraiment (cette vérification doit être effectuée en raison de possibles collisions).
Une version courante de l'algorithme de correspondance de hachage se produit lorsque, au stade de la construction, il n'est pas possible de créer une table de hachage qui peut être entièrement stockée en mémoire:
Cela se produit lorsqu'il y a plus de données que ce qui peut être stocké en mémoire, ou lorsque SQL Server fournit une mémoire insuffisante pour une connexion de hachage .
Lorsque SQL Server ne dispose pas de suffisamment de mémoire pour stocker la table de hachage pendant la phase de génération, il continue de fonctionner, stockant certains blocs en mémoire et plaçant d'autres blocs dans tempdb.
Dans la phase de validation, SQL Server concatène les lignes de données de la deuxième table en blocs de la phase de génération en mémoire. Si le bloc auquel cette ligne correspond potentiellement est actuellement en mémoire insuffisante, SQL Server écrit cette ligne dans tempdb pour une comparaison ultérieure.
Lorsque les correspondances pour un bloc sont terminées, SQL Server efface ces données de la mémoire et charge les blocs suivants en mémoire. Il compare ensuite les lignes de la deuxième table (actuellement située dans tempdb) avec les nouveaux blocs en mémoire.
Comme pour chaque instruction de jointure physique de cette série, les détails de l'instruction de correspondance de hachage peuvent être trouvés dans l' aide de Hugo Kornelis sur la correspondance de hachage .
Que montre Hash Match Join?
La connaissance des fonctionnalités internes du fonctionnement de la jointure par correspondance de hachage nous permet de déterminer ce que l'optimiseur pense de nos données et des opérateurs de connexion en amont, ce qui nous aide à nous concentrer sur le réglage des performances.
Voici quelques scénarios à considérer la prochaine fois que la jointure de correspondance de hachage sera utilisée dans votre plan d'exécution:
- Bien que la jointure par correspondance de hachage puisse combiner d'énormes ensembles de données, la construction d'une table de hachage à partir de la première table d'entrée est une opération de blocage qui empêche l'exécution des instructions suivantes. À cet égard, je vérifie toujours s'il existe un moyen simple de convertir une correspondance de hachage en boucles imbriquées ou de fusionner une jointure. Parfois, cela n'est pas possible (trop de lignes pour les boucles imbriquées ou les données non triées pour la jointure de fusion), mais il vaut toujours la peine de vérifier si une simple modification d'index ou des estimations améliorées résulteront de la mise à jour des statistiques du fait que SQL Server sélectionne une instruction de jointure de correspondance de hachage non bloquante
- Les jointures par correspondance de hachage sont idéales pour les grandes connexions, car elles peuvent être transférées vers tempdb, ce qui leur permet de se connecter à de grands ensembles de données, ce qui peut entraîner une connexion défaillante en mémoire à l'aide de boucles imbriquées ou de fusionner des instructions de jointure.
- Si vous voyez une instruction de jointure par correspondance de hachage , cela signifie que SQL Server pense que l'entrée est trop grande. Si nous savons que nos données d'entrée ne doivent pas être si volumineuses, il vaut la peine de vérifier s'il y a des problèmes de statistiques ou d'estimation, à cause desquels SQL Server sélectionne incorrectement la jointure de correspondance de hachage .
- Lorsqu'elle est exécutée en mémoire, la jointure de match de hachage est assez efficace. Des problèmes surviennent lorsque la phase de génération passe à tempdb.
- Si je remarque un petit triangle jaune indiquant que la connexion va à tempdb, je vois pourquoi cela s'est produit: s'il y a plus de données que de mémoire disponible, il y a peu de choses à faire, mais si la mémoire allouée semble déraisonnablement petite, cela peut signifier que nous avons probablement un autre problème avec les statistiques qui conduit à des estimations trop faibles de l'optimiseur SQL Server.
Merci d'avoir lu l'article. Vous pouvez également aimer mon Twitter .
Nous avons couvert ce sujet dans une précédente leçon ouverte . En attendant vos commentaires!