Removendo Operador Eager Spool

Olá pessoal, tudo bem? 

Hoje vim compartilhar um caso do meu dia a dia com vocês, onde o operador de EAGER SPOOL impactou na quantidade de leituras e no tempo de execução de uma query.

Antes de apresentar o caso, vamos entender um pouco sobre este operador.

Table Spool (Eager Spool) 

Este operador recebe linha por linha de uma tabela, armazena em uma área de memória (Spool Cache) e libera somente depois que todas as linhas forem recebidas. Isso acontece para que a tabela que forneceu os dados não seja acessada novamente durante a execução da query, evitando que sejam lidos dados que já foram processados durante a execução.

Veja abaixo o fluxo: 

EXEMPLO: Aumentar R$100 no valor do condomínio, para os condomínios que tem valor maior que R$350.

Para este caso temos o seguinte índice: 

Visualizando a QUERY que foi montada:

Caso o otimizador de consultas escolha o índice criado, teremos o seguinte comportamento: 

1 º  O operador Index Seek faz o filtro com o predicado e passa linha a linha para o operador Eager Spool. 

Por ser do tipo BLOCKING, o operador de Eager Spool espera que o Index Seek envie todas as linhas para começar enviar para o operador seguinte. 

Predicado:  VALOR > 350 

2 º  O Eager Spool passa cada linha por todos os operadores abaixo até o UPDATE para fazer a atualização, neste caso, como temos 4 linhas, ele repete 4 vezes esse fluxo. 

Talvez você se pergunte: Por que ele precisa usar o operador de Eager Spool?  Por que não enviou as linhas direto do Index Seek? 

Vamos considerar essa hipótese, sabendo que ele está lendo o índice DEMO_EAGER que é ordenado por VALOR. 

Visualizando a ordenação: 

Quando ele enviar a primeira linha (420) e for feita a atualização, ocorre uma atualização no índice que está sendo usado e então teríamos a seguinte estrutura: 

Quando este índice for novamente acessado pela mesma query, o valor 500 irá ser atualizado para 600, em seguida 520 (antigo 420) para 620 e assim por diante, ficando em um loop infinito.  

Na maior parte das vezes este operador é necessário, mas em alguns casos, podemos evita-lo sem alterar o resultado da query.  

Vamos ver um exemplo prático, onde consegui remover o operador Eager Spool reescrevendo a query. 

Para simular esta situação irei utilizar os scripts contidos no seguinte link: 

https://github.com/Nusyc/ScriptsRemovendoEagerSpool

Lógica utilizada pelo desenvolvedor da query: 

A query consistia em fazer diversas inserções e atualizações em uma tabela temporária para depois afetar uma tabela física, estando ciente disto, vamos para o exemplo. 

Preparando o cenário:  

OBS: Nos scripts abaixo foram utilizados o HINT MAXDOP1 para que seja considerado somente 1 core, isto para diminuir a probabilidade de gerar planos diferentes de acordo com cada hardware, para que vocês possam obter o mesmo plano ou similar ao que obtive abaixo. 

Para chegar no ponto em questão, faremos uma carga na tabela temporária para que possamos ter uma quantia de dados considerável e faremos também a criação de um índice: 

 O código abaixo, tem como objetivo inserir todas as linhas que correspondem ao filtro (exists), sendo assim, foi feito uma verificação na tabela temporária #RESULTADO_FINAL antes de inserir. 

Vale destacar que foi feito o uso do BEGIN TRAN para que eu possa dar ROLLBACK após o teste, pois o exemplo seguinte precisará da mesma tabela com os mesmos dados. 

Ao executar esta query obtive as seguintes estatísticas:  

(Dados capturados abaixo são somente do trecho destacado acima). 

Visualizando o plano, notamos o uso do operador EAGER SPOOL com 0 %. 

Solução para remover o EAGER SPOOL: 

Para este caso, podemos evitar o uso do operador Table Spool como EAGER SPOOL alterando a escrita da query. 

Para isto, iremos utilizar uma tabela temporária para faze-la como ponte. 

Logo após, iremos inserir no destino consultando a tabela auxiliar.  

Estatísticas obtidas: 

Plano: 

Apesar do operador representar 0 % no custo do plano, com a solução aplicada tivemos a redução de 2.239.053 leituras lógicas e aproximadamente 4 segundos de redução no tempo de resposta da query. 

Conclusão: 

Como mencionado acima, vimos a importância do operador Eager Spool, onde é usado para evitar problemas de leituras ao mesmo registro, entretanto, ele pode também prejudicar na performance das queries, necessitando da reescrita para evita-lo. (Nem sempre é possível)

É isso pessoal, espero que tenha agregado. 

Até a próxima.