Data Flow do SSIS até 3× mais rápido: ajustando o buffer e o Fast Load

Tem um cenário que se repete em quase todo projeto de ETL no SQL Server: um pacote do SSIS que "sempre funcionou" começa a demorar demais à medida que o volume cresce. A primeira reação costuma ser culpar o hardware ou reescrever o Data Flow. Na maioria das vezes, o problema é bem mais barato de resolver — está no dimensionamento do buffer, uma configuração que quase todo mundo deixa no padrão.

Neste guia eu mostro os três ajustes que mais rendem por minuto investido: AutoAdjustBufferSize, DefaultBufferMaxRows e o Fast Load no destino. Numa carga de 10 milhões de linhas, essa combinação pode derrubar o tempo de execução de cerca de 2 minutos para 44 segundos — sem trocar uma linha de lógica.

Como o Data Flow do SSIS move dados

O Data Flow não processa linha a linha: ele trabalha com buffers — blocos de memória que carregam um conjunto de linhas por vez, da origem até o destino. Quanto mais eficiente for o preenchimento desses buffers, menos "viagens" o motor faz e maior o throughput.

O tamanho de cada buffer é definido por duas propriedades do Data Flow Task:

• DefaultBufferMaxRows — número máximo de linhas por buffer. Padrão: 10.000.
• DefaultBufferSize — limite de memória por buffer. Padrão: 10 MB.

O SSIS coloca no buffer o maior número de linhas possível sem estourar nenhum dos dois limites. O detalhe importante: esses padrões vêm de uma época em que memória de servidor era escassa. Em máquinas atuais, eles são pequenos e seguram a performance sem dar nenhum sinal óbvio.

Passo 1 — Ligue o AutoAdjustBufferSize

Como as duas propriedades acima se limitam mutuamente, é fácil ajustar uma e a outra "cortar" o efeito. Foi por isso que o SQL Server 2016 introduziu a propriedade AutoAdjustBufferSize.

Quando você a define como True (o padrão é False), o SSIS passa a calcular o tamanho do buffer a partir do DefaultBufferMaxRows e simplesmente ignora o DefaultBufferSize. Ou seja: você diz quantas linhas quer por buffer e o motor ajusta a memória sozinho.

-- Propriedades do Data Flow Task
AutoAdjustBufferSize = True
DefaultBufferMaxRows = 50000

Isso elimina a briga entre as duas propriedades e torna o tuning previsível: você raciocina em linhas, não em bytes.

Passo 2 — Use Fast Load e controle o commit

Preencher buffers maiores só ajuda se o destino também aceitar dados em lote. No OLE DB Destination, garanta:

• Access Mode = "Table or view - fast load" — usa a interface de BULK INSERT em vez de inserir linha a linha.
• MaxInsertCommitSize = 100000 — controla quantas linhas entram em cada transação (commit). Lotes muito pequenos geram overhead de transação; lotes gigantes seguram o log e a memória. Um valor na casa das centenas de milhares costuma ser um bom ponto de partida.

-- OLE DB Destination
AccessMode = Table or view - fast load
MaxInsertCommitSize = 100000
Table lock = True   -- quando a janela de carga permite

Passo 3 — Meça, ajuste e monitore

O ganho depende do formato dos seus dados, então meça antes e depois. Em um teste clássico documentado pela comunidade, uma carga de 10 milhões de linhas caiu de pouco mais de 2 minutos para 44 segundos apenas subindo DefaultBufferMaxRows para 50.000 (com AutoAdjust ligado) e o commit para 100.000.

Regras de bolso para o DefaultBufferMaxRows:

• Linhas largas (muitas colunas / tipos grandes): use menos linhas por buffer. Cada linha ocupa mais memória, então buffers cheios estouram rápido.
• Linhas estreitas: use mais linhas por buffer para aproveitar a memória.
• Buffers grandes demais também prejudicam — o objetivo é equilíbrio entre encher o buffer e manter uma taxa de commit saudável.

Como saber se passou do ponto: acompanhe o contador "Buffers Spooled" no log do Data Flow. Se ele subir de zero, o SSIS não está conseguindo manter os buffers em memória e começou a paginar em disco — sinal claro para reduzir DefaultBufferMaxRows (ou liberar mais memória para o serviço).

Checklist rápido

1. Data Flow Task → AutoAdjustBufferSize = True.
2. Data Flow Task → DefaultBufferMaxRows calibrado pela largura das linhas (comece em 50.000).
3. OLE DB Destination → fast load + MaxInsertCommitSize (comece em 100.000).
4. Rode com dados reais, compare o tempo e vigie Buffers Spooled.

Conclusão

Antes de reescrever pacotes ou pedir mais servidor, gaste dez minutos revisando o buffer do Data Flow. AutoAdjustBufferSize, um DefaultBufferMaxRows bem calibrado e o Fast Load no destino formam um dos ajustes de melhor custo-benefício em ETL no SQL Server: o pacote continua o mesmo, mas a carga termina em uma fração do tempo. É dimensionamento, não mágica — e vale para todo pipeline que ainda roda no padrão de fábrica.