ti-enxame.com

Maneiras de evitar operações de spool no SQL Server

Eu tenho um processo ETL que envolve um procedimento armazenado que faz uso intenso de SELECT INTO instruções (minimamente registradas e, portanto, mais rápidas, pois geram menos tráfego de log). Do lote de trabalho que ocorre em um determinado armazenamento armazenado, o procedimento armazenado várias das operações mais caras são carretéis ansiosos que parecem apenas amortecer os resultados da consulta e copiá-los na tabela que está sendo feita.

A documentação do MSDN em spools ansiosos é bastante esparsa. Alguém tem uma idéia mais profunda sobre se isso é realmente necessário (e sob quais circunstâncias)? Eu tenho algumas teorias que podem ou não fazer sentido, mas não obtive sucesso em eliminá-las das consultas.

Os arquivos .sqlplan são bastante grandes (160kb), então acho que provavelmente não é razoável publicá-los diretamente em um fórum.

Então, aqui estão algumas teorias que podem ser passíveis de respostas específicas:

  • A consulta usa alguns UDFs para transformação de dados, como analisar datas formatadas. Essa transformação de dados requer o uso de spools ansiosos para alocar tipos sensíveis (por exemplo, comprimentos de varchar) para a tabela antes de construí-la?
  • Como uma extensão da pergunta acima, alguém tem uma visão mais profunda do que dirige ou não essa operação em uma consulta?
37
ConcernedOfTunbridgeWells

Meu entendimento sobre spool é que é um pouco de um arenque vermelho no seu plano de execução. Sim, é responsável por grande parte do seu custo de consulta, mas na verdade é uma otimização que o SQL Server realiza automaticamente para evitar uma digitalização dispendiosa. Se você evitar spool, o custo da árvore de execução em que ele se localiza aumentará e quase certamente o custo de toda a consulta aumentará. Eu não tenho nenhuma percepção específica sobre o que, em particular, pode fazer com que o otimizador de consulta do banco de dados analise a execução dessa maneira, especialmente sem ver o código SQL, mas provavelmente é melhor confiar no seu comportamento.

No entanto, isso não significa que seu plano de execução não possa ser otimizado, dependendo exatamente do que você está fazendo e da volatilidade dos dados de origem. Quando você está fazendo um SELECT INTO, muitas vezes você verá itens em spool no seu plano de execução, e isso pode estar relacionado ao isolamento da leitura. Se for apropriado para sua situação específica, tente reduzir o nível de isolamento da transação para algo menos dispendioso e/ou use a dica NOLOCK. Descobri em consultas complicadas e críticas ao desempenho que NOLOCK, se seguras e adequadas para seus dados, podem aumentar bastante a velocidade de execução de consultas, mesmo quando não parece haver nenhum motivo.

Nesta situação, se você tentar READ UNCOMMITTED ou a dica NOLOCK, você poderá eliminar alguns dos Spools. (Obviamente, você não deseja fazer isso se for provável que o levem a um estado inconsistente, mas os requisitos de isolamento de dados de todos são diferentes). O operador TOP e o operador OR ocasionalmente podem causar spool, mas duvido que você esteja executando algum desses em um processo ETL ...

Você está certo ao dizer que seus UDFs também podem ser os culpados. Se você estiver usando cada UDF apenas uma vez, seria uma experiência interessante tentar colocá-los em linha para ver se você obtém um grande benefício de desempenho. (E se você não conseguir descobrir uma maneira de escrevê-los em linha com a consulta, provavelmente é por isso que eles podem estar causando spool).

Uma última coisa que eu observaria é que, se você estiver fazendo junções que possam ser reordenadas, tente usar uma dica para forçar a ordem de junção a acontecer da maneira que você sabe ser a mais seletiva. Esse é um alcance, mas não custa tentar se você já está otimizando.

30
Grank