Esta publicación es la tercera parte de la serie sobre operadores de unión (asegúrese de leer la parte 1: combinaciones de bucles anidados y la parte 2: combinaciones de fusión ). La traducción del artículo fue preparada específicamente para estudiantes del curso "MS SQL Server Developer" .
Hash Match Joins son los caballos de batalla confiables de los operadores de conexión física.
Si bien Nested Loops Join fallará si hay demasiados datos para caber en la memoria, y Merge Join requerirá que se ordene la entrada, Hash Match conectará todos los datos que envíe (siempre que se ejecuta un predicado de igualdad para la conexión, y hasta ahora hay suficiente espacio libre en su tempdb).
Mira videos relacionados con YouTube
El algoritmo de coincidencia hash consta de dos etapas, que funcionan de la siguiente manera:
Durante la primera fase de compilación, SQL Server crea una tabla hash en la memoria a partir de una de las tablas de entrada (generalmente la más pequeña de las dos). Los hash se calculan en función de las teclas de entrada y luego se almacenan junto con la línea en la tabla de hash en el bloque correspondiente. En la mayoría de los casos, solo hay una fila de datos en cada bloque, excepto cuando:
- Hay líneas con claves duplicadas.
- La función hash crea una colisión, y las teclas completamente diferentes reciben el mismo hash (esto es raro, pero posible).
Después de crear la tabla hash, comienza la etapa "Sonda" (verificación). En el segundo paso, SQL Server calcula el hash de clave para cada fila en la segunda tabla de entrada y comprueba si existe en la tabla de hash creada en el primer paso. Si hay una coincidencia para este hash, entonces se verifica si las claves de la (s) fila (s) en la tabla hash y las filas de la segunda tabla realmente coinciden (esta verificación debe realizarse debido a posibles colisiones).
Una versión común del algoritmo de coincidencia hash se produce cuando, en la etapa de construcción, no es posible crear una tabla hash que se pueda almacenar por completo en la memoria:
Esto sucede cuando hay más datos de los que se pueden almacenar en la memoria o cuando SQL Server proporciona memoria insuficiente para una conexión de coincidencia hash .
Cuando SQL Server no tiene suficiente memoria para almacenar la tabla hash durante la fase de compilación, continúa funcionando, almacenando algunos bloques en la memoria y colocando otros bloques en tempdb.
En la fase de validación, SQL Server concatena las filas de datos de la segunda tabla en bloques desde la fase de compilación en la memoria. Si el bloque al que corresponde potencialmente esta línea está actualmente sin memoria, SQL Server escribe esta línea en tempdb para una comparación posterior.
Cuando se completan las coincidencias para un bloque, SQL Server borra estos datos de la memoria y carga los siguientes bloques en la memoria. Luego compara las filas de la segunda tabla (actualmente ubicada en tempdb) con los nuevos bloques en la memoria.
Al igual que con cada declaración de combinación física en esta serie, los detalles sobre la declaración de coincidencia hash se pueden encontrar en la ayuda de Hugo Kornelis sobre la coincidencia hash .
¿Qué muestra Hash Match Join?
Conocer las características internas de cómo funciona la combinación hash match nos permite determinar qué piensa el optimizador sobre nuestros operadores de datos y conexiones ascendentes, lo que nos ayuda a centrarnos en el ajuste del rendimiento.
Aquí hay algunos escenarios para considerar la próxima vez que vea que la combinación hash match se usa en su plan de ejecución:
- Si bien la combinación de coincidencias hash puede combinar grandes conjuntos de datos, la construcción de una tabla hash a partir de la primera tabla de entrada es una operación de bloqueo que impide la ejecución de declaraciones posteriores. En este sentido, siempre verifico si hay una manera fácil de convertir la coincidencia hash en bucles anidados o fusionar combinación. A veces esto no es posible (demasiadas filas para bucles anidados o datos sin clasificar para combinación de combinación), pero siempre vale la pena verificar si un simple cambio de índice o estimaciones mejoradas resultarán de actualizar estadísticas al hecho de que SQL Server selecciona una declaración de combinación de coincidencia hash no bloqueante
- Las uniones de coincidencia de hash son excelentes para conexiones grandes, ya que pueden transferirse a tempdb, esto les permite hacer conexiones a grandes conjuntos de datos, lo que puede conducir a una conexión fallida en la memoria usando bucles anidados o fusionar declaraciones de unión.
- Si ve una declaración de unión de coincidencia hash , significa que SQL Server piensa que la entrada es demasiado grande. Si sabemos que nuestros datos de entrada no deberían ser tan grandes, entonces vale la pena verificar si hay problemas con las estadísticas o la estimación, debido a que SQL Server selecciona incorrectamente la combinación de coincidencia hash .
- Cuando se ejecuta en la memoria, la combinación hash match es bastante eficiente. Los problemas surgen cuando la fase de compilación va a tempdb.
- Si noto un pequeño triángulo amarillo que indica que la conexión va a tempdb, veo por qué sucedió esto: si hay más datos que memoria disponible, es poco lo que se puede hacer, pero si la memoria asignada parece irrazonablemente pequeña, esto puede significar que Probablemente tenemos un problema más con las estadísticas que conduce a estimaciones demasiado bajas del optimizador de SQL Server.
Gracias por leer el artículo. También te puede gustar mi Twitter .
Cubrimos este tema en una lección abierta anterior. Esperando sus comentarios!