Esta postagem é a terceira parte da série sobre operadores de junção (não deixe de ler a parte 1 - junções aninhadas e a parte 2 - junções de mesclagem ). A tradução do artigo foi preparada especificamente para os alunos do curso "MS SQL Server Developer" .
As junções de combinação de hash são os cavalos de trabalho confiáveis dos operadores de conexão física.
Embora a junção de loops aninhados falhe se houver muitos dados para caber na memória, e a junção de mesclagem exigirá que a entrada seja classificada, o Hash Match conectará todos os dados que você enviar (desde que um predicado de igualdade é executado para a conexão e, até o momento, há espaço livre suficiente em seu tempdb).
Assista a vídeos relacionados ao YouTube
O algoritmo de combinação de hash consiste em dois estágios, que funcionam da seguinte maneira:
Durante a primeira fase de criação, o SQL Server cria uma tabela de hash na memória de uma das tabelas de entrada (geralmente a menor das duas). Os hashes são calculados com base nas teclas de entrada e, em seguida, armazenados junto com a linha na tabela de hash no bloco correspondente. Na maioria dos casos, há apenas uma linha de dados em cada bloco, exceto quando:
- Existem linhas com chaves duplicadas.
- A função hash cria uma colisão e chaves completamente diferentes recebem o mesmo hash (isso é raro, mas possível).
Após a criação da tabela de hash, o estágio “Probe” (verificação) é iniciado. Na segunda etapa, o SQL Server calcula o hash da chave para cada linha na segunda tabela de entrada e verifica se existe na tabela de hash criada na primeira etapa. Se houver uma correspondência para esse hash, será verificado se as chaves das linhas na tabela de hash e as linhas da segunda tabela realmente correspondem (essa verificação deve ser realizada devido a possíveis colisões).
Uma versão comum do algoritmo de correspondência de hash ocorre quando, no estágio de construção, não é possível criar uma tabela de hash que pode ser completamente armazenada na memória:
Isso acontece quando há mais dados que podem ser armazenados na memória ou quando o SQL Server fornece memória insuficiente para uma conexão de combinação de hash .
Quando o SQL Server não possui memória suficiente para armazenar a tabela de hash durante a fase de compilação, ele continua funcionando, armazenando alguns blocos na memória e colocando outros blocos no tempdb.
Na fase de validação, o SQL Server concatena as linhas de dados da segunda tabela em blocos da fase de construção na memória. Se o bloco ao qual essa linha potencialmente corresponder estiver atualmente sem memória, o SQL Server gravará essa linha no tempdb para comparação posterior.
Quando as correspondências para um bloco são concluídas, o SQL Server limpa esses dados da memória e carrega os seguintes blocos na memória. Em seguida, ele compara as linhas da segunda tabela (atualmente localizada em tempdb) com os novos blocos na memória.
Como em todas as declarações de junção física desta série, detalhes sobre a declaração de correspondência de hash podem ser encontrados na ajuda de Hugo Kornelis na correspondência de hash .
O que mostra o Hash Match Join?
Conhecer os recursos internos de como a junção de combinação de hash funciona, permite determinar o que o otimizador pensa sobre nossos dados e operadores de conexão upstream, ajudando-nos a focar no ajuste de desempenho.
Aqui estão alguns cenários a serem considerados na próxima vez em que a associação de combinação de hash for usada em seu plano de execução:
- Embora a junção de combinação de hash possa combinar grandes conjuntos de dados, a construção de uma tabela de hash a partir da primeira tabela de entrada é uma operação de bloqueio que impede a execução de instruções subseqüentes. Nesse sentido, eu sempre verifico se existe uma maneira fácil de converter a correspondência de hash em loops aninhados ou mesclar junção. Às vezes, isso não é possível (muitas linhas para loops aninhados ou dados não classificados para junção de mesclagem), mas sempre vale a pena verificar se uma alteração simples do índice ou estimativas aprimoradas resultarão da atualização de estatísticas, pois o SQL Server seleciona uma instrução de junção de combinação de hash sem bloqueio
- As junções de correspondência de hash são ótimas para conexões grandes porque podem ser transferidas para o tempdb, o que lhes permite fazer conexões com grandes conjuntos de dados, o que pode levar a uma falha na conexão na memória usando loops aninhados ou instruções de junção de mesclagem.
- Se você vir uma instrução de associação de hash match , isso significa que o SQL Server considera que a entrada é muito grande. Se sabemos que nossos dados de entrada não devem ser tão grandes, vale a pena verificar se há problemas com estatísticas ou estimativas, devido aos quais o SQL Server seleciona incorretamente a associação de combinação de hash .
- Quando executado na memória, a junção de combinação de hash é bastante eficiente. Problemas surgem quando a fase de compilação vai para tempdb.
- Se eu notar um pequeno triângulo amarelo indicando que a conexão vai para tempdb, entendo por que isso aconteceu: se houver mais dados do que memória disponível, pouco poderá ser feito, mas se a memória alocada parecer excessivamente pequena, isso pode significar que provavelmente temos mais um problema com estatísticas que levam a estimativas muito baixas do otimizador do SQL Server.
Obrigado por ler o artigo. Você também pode gostar do meu Twitter .
Abordamos esse tópico em uma lição aberta anterior. Aguardando seus comentários!