255
8.1 Lecture d’un plan d’exécution
D’où le nom de boucles imbriquées. Cet algorithme fonctionne très bien pour les
petits volumes, mais son coût augmente proportionnellement aux nombres de lignes
à traiter.
Les bookmark lookups sont exprimés en boucles imbriquées dans les plans depuis
SQL Server 2005.
La fusion (merge join) consiste à prendre deux tables triées selon les mêmes
colonnes, et à extraire les correspondances entre la première table et la seconde.
Comme elles sont triées, le parcours se fait toujours vers l’avant, chaque correspondance est conservée, et les lignes suivantes sont testées. C’est donc un algorithme
très rapide, mais il implique un tri préalable, et une clause d’équijointure (on ne peut
comparer de cette façon que des égalités). Le tri étant très coûteux, la jointure de
fusion est donc utilisée principalement sur les tables ordonnées par un index clustered, ou sur les nœuds feuilles d’index.
Le hachage (hash join) est privilégié sur les jeux de résultats importants, car il permet, après préparation, de réaliser de grosses jointures relativement rapidement. Il
fonctionne en deux phases : il crée premièrement une table de hachage sur les
colonnes de la condition de recherche sur la première table. Ensuite, il parcourt la
deuxième table, crée un hachage pour chaque condition de recherche et effectue la
comparaison avec la table de hachage. Cet algorithme ne fonctionne qu’avec des
clauses d’équijointures. Il souffre de quelques désavantages : il est « bloquant », car
la première étape doit être complètement terminée avant de passer à la seconde
(alors que le nested loop et le merge peuvent travailler au fil de l’eau), et il est très
consommateur de mémoire. SQLOS lui réserve une quantité de mémoire estimée
pour son travail, mais si cette estimation était trop optimiste, il doit, pendant l’exécution, « baver » sur tempdb (on parle de spilling). Cela ralentit bien sûr nettement
l’opération. Le hachage est utilisé aussi pour les calculs d’agrégations, et ce débordement peut aussi se produire dans ce cas.
Débordements de hachages
Vous avez un événement de trace qui vous permet de détecter les débordements dans
tempdb dus à des hachages (Hash Aggregate et Hash Join) : Errors and Warnings :
Hash Warning. Vous pouvez utiliser des indicateurs de requête pour forcer un algorithme de calcul d’agrégation :
• option(order group) – force le Stream Aggregate ;
• option(hash group) – force le Hash Aggregate.
Attention à l’option hash group : un Hash Aggregate n’est possible qu’en présence
d’un GROUP BY. Si vous utilisez cet indicateur dans une agrégation scalaire (un calcul
d’agrégat sans regroupements, donc sans clause GROUP BY), une erreur vous sera renvoyée à la compilation : « Query processor could not produce a query plan because of the
hints defined in this query... ». 1
8.1 Lecture d’un plan d’exécution
D’où le nom de boucles imbriquées. Cet algorithme fonctionne très bien pour les
petits volumes, mais son coût augmente proportionnellement aux nombres de lignes
à traiter.
Les bookmark lookups sont exprimés en boucles imbriquées dans les plans depuis
SQL Server 2005.
La fusion (merge join) consiste à prendre deux tables triées selon les mêmes
colonnes, et à extraire les correspondances entre la première table et la seconde.
Comme elles sont triées, le parcours se fait toujours vers l’avant, chaque correspondance est conservée, et les lignes suivantes sont testées. C’est donc un algorithme
très rapide, mais il implique un tri préalable, et une clause d’équijointure (on ne peut
comparer de cette façon que des égalités). Le tri étant très coûteux, la jointure de
fusion est donc utilisée principalement sur les tables ordonnées par un index clustered, ou sur les nœuds feuilles d’index.
Le hachage (hash join) est privilégié sur les jeux de résultats importants, car il permet, après préparation, de réaliser de grosses jointures relativement rapidement. Il
fonctionne en deux phases : il crée premièrement une table de hachage sur les
colonnes de la condition de recherche sur la première table. Ensuite, il parcourt la
deuxième table, crée un hachage pour chaque condition de recherche et effectue la
comparaison avec la table de hachage. Cet algorithme ne fonctionne qu’avec des
clauses d’équijointures. Il souffre de quelques désavantages : il est « bloquant », car
la première étape doit être complètement terminée avant de passer à la seconde
(alors que le nested loop et le merge peuvent travailler au fil de l’eau), et il est très
consommateur de mémoire. SQLOS lui réserve une quantité de mémoire estimée
pour son travail, mais si cette estimation était trop optimiste, il doit, pendant l’exécution, « baver » sur tempdb (on parle de spilling). Cela ralentit bien sûr nettement
l’opération. Le hachage est utilisé aussi pour les calculs d’agrégations, et ce débordement peut aussi se produire dans ce cas.
Débordements de hachages
Vous avez un événement de trace qui vous permet de détecter les débordements dans
tempdb dus à des hachages (Hash Aggregate et Hash Join) : Errors and Warnings :
Hash Warning. Vous pouvez utiliser des indicateurs de requête pour forcer un algorithme de calcul d’agrégation :
• option(order group) – force le Stream Aggregate ;
• option(hash group) – force le Hash Aggregate.
Attention à l’option hash group : un Hash Aggregate n’est possible qu’en présence
d’un GROUP BY. Si vous utilisez cet indicateur dans une agrégation scalaire (un calcul
d’agrégat sans regroupements, donc sans clause GROUP BY), une erreur vous sera renvoyée à la compilation : « Query processor could not produce a query plan because of the
hints defined in this query... ». 1