Como posso fazer com que meu processo SSIS consuma mais recursos e seja executado com mais rapidez?

Question

Tenho um processo diário de ETL no SSIS que cria meu warehouse para que possamos fornecer relatórios diários.

Tenho dois servidores - um para SSIS e outro para banco de dados SQL Server. O servidor SSIS (SSIS-Server01) é uma caixa de 8 CPU e 32 GB de RAM. O banco de dados SQL Server (DB-Server) é outra caixa de 8 CPU e 32 GB de RAM. Ambas são máquinas virtuais VMWare.

Em sua forma simplificada, o SSIS lê 17 milhões de linhas (cerca de 9 GB) de uma única tabela no servidor de banco de dados, descentraliza-as para 408 milhões de linhas, faz algumas pesquisas e uma tonelada de cálculos e, em seguida, agrega de volta a cerca de 8 milhões de linhas. que são gravadas em uma tabela totalmente nova no mesmo servidor de banco de dados todas as vezes (essa tabela será então movida para uma partição para fornecer relatórios diários).

Eu tenho um loop que processa dados de 18 meses por vez - um total de 10 anos de dados. Escolhi 18 meses com base na minha observação do uso de RAM no servidor SSIS - aos 18 meses ele consome 27 GB de RAM. Qualquer valor superior a isso e o SSIS inicia o buffer no disco e o desempenho despenca.

Aqui está meu fluxo de dadoshttp://img207.imageshack.us/img207/4105/dataflow.jpg

estou usandoDistribuidor de dados balanceados da Microsoftpara enviar dados por 8 caminhos paralelos para maximizar o uso de recursos. Faço uma união antes de começar a trabalhar nas minhas agregações.

Aqui está o gráfico do gerenciador de tarefas do servidor SSIS

Aqui está outro gráfico mostrando as 8 CPUs individuais

Como você pode ver nessas imagens, o uso de memória aumenta lentamente para cerca de 27G à medida que mais e mais linhas são lidas e processadas. Porém o uso da CPU é constante em torno de 40%.

O segundo gráfico mostra que estamos usando apenas 4 (às vezes 5) CPUs de 8.

Estou tentando acelerar o processo (está usando apenas 40% da CPU disponível).

Como faço para que esse processo seja executado com mais eficiência (menos tempo, mais recursos)?

Answer 1

Depois de boas sugestões do bilinkc, e sem saber onde está o gargalo, tentaria mais algumas coisas.

Como você já observou, você deve trabalhar no paralelismo, não processando mais dados (meses) no mesmo fluxo de dados. Você já fez as transformações rodarem em paralelo, mas a origem e o destino (bem como a agregação) não estão rodando em paralelo! Portanto, leia até o fim e tenha em mente que você também deve fazê-los rodar em paralelo para utilizar a energia da CPU. E não esqueça que você élimite de memória (não é possível agregar um número infinito de meses em um lote), então o caminho a seguir ("expansão") é obter um pedaço de dados, processá-lo e colocá-lo no banco de dados de destino o mais rápido possível. Isso requer a eliminação de componentes comuns (uma fonte, uma união, todos) porque cada bloco de dados é limitado à velocidade desses componentes comuns.

Otimização relacionada à fonte:

• tente várias fontes (e destinos) no mesmo fluxo de dados em vez do Balanced Data Distributor - você usa índice clusterizado na coluna de data para que seu servidor de banco de dados seja capaz de recuperar dados rapidamente em intervalos baseados em datas; se você executar o pacote em um servidor diferente daquele em que o banco de dados reside, você aumentará a utilização da rede

Otimização relacionada a transformações:

• Você realmente precisa fazer Union All antes de Aggregate? Caso contrário, dê uma olhada na otimização relacionada ao destino em relação a vários destinos
• DefinirChaves, KeyScale e AutoExtendFactorpara o componente Aggregate para evitar novo hash - se essas propriedades forem definidas incorretamente, você verá um aviso durante a execução do pacote; observe que prever valores ideais é mais fácil para lotes de número fixo de meses do que para números infinitos (como no seu caso 18 e aumentando)
• Considere agregar e (des) dinamizar no SQL Server em vez de fazê-lo no pacote SSIS - o SQL Server supera o Integration Services nessas tarefas; é claro, a lógica das transformações pode ser tal que proíbe a agregação antes de realizar algumas transformações no pacote
• se você puder agregar (e dinamizar/desdinamizar) (por exemplo) dados mensais no banco de dados, tente fazê-lo na consulta de origem ou no banco de dados de destino com SQL; dependendo do seu ambiente, escrever em uma tabela separada no banco de dados de destino, construir índice, SELECT INTO com agregação com SQL pode ser mais rápido do que fazê-lo no pacote; observe que paralelizar essas atividades colocará muita pressão em seu armazenamento
• Você tem um multicast no final; Não sei quantas linhas chegam lá, mas considere o seguinte: escreva no destino à direita (na captura de tela) e preencha os registros no destino à esquerda na consulta SQL (para eliminar a segunda agregação e liberar recursos - SQL Server provavelmente fará isso muito mais rápido)

Otimização relacionada ao destino:

• usarDestino do SQL Serverse possível (o pacote deve ser executado no mesmo servidor do banco de dados e o banco de dados de destino deve ser SQL Server); observe que isso requer correspondência exata do tipo de dados das colunas (pipeline -> colunas da tabela)
• considere definirModelo de recuperaçãopara Simples no banco de dados de destino (data warehouse)
• paralelizar destinos - em vez de unir todos + agregado + destino use agregados separados e destinos separados (para a mesma tabela); aqui você deve considerarparticionamentosua tabela de destino e colocação de partições em grupos de arquivos separados; se você processar dados mês a mês, faça partições por mês e use comutação de partição

Parece que não fiquei claro sobre qual caminho seguir com o paralelismo. Podes tentar:

• colocar várias fontes em um único fluxo de dados exige que você copie e cole a lógica de transformação e o destino para cada origem
• executando vários fluxos de dados em paralelo, onde cada fluxo de dados processa apenas um mês
• executar vários pacotes em paralelo, onde cada pacote possui um fluxo de dados que processa apenas um mês; e um pacote mestre para controlar a execução de cada pacote (mês) - esta é a forma preferida porque você provavelmente executará o pacote apenas por um mês depois de entrar em produção
• ou igual ao anterior, mas com Balanced Data Distributor e Union All and Aggregate

Antes de fazer qualquer outra coisa, você pode querer fazer um teste rápido: pegue seu pacote original, altere-o para usar 1 mês, faça uma cópia exata que processe outro mês e execute esses pacotes em paralelo. Compare-o com o processamento do pacote original por 2 meses. Faça o mesmo para 2 pacotes separados de 6 meses e um pacote único de 12 meses por vez. Ele deve executar seu servidor com uso total da CPU.

Tente não paralelizar demais porque você terá várias gravações no destino, portanto, não deseja iniciar 18 pacotes mensais paralelos, mas sim 3 ou 4 para começar.

E, finalmente, acredito fortemente que as pressões de E/S de memória e de destino são as que devem ser eliminadas.

Por favor, informe-nos sobre seu progresso.

Answer 2

UsarExplorador de processospara revelar mais algum uso de recursos (memória e IO). Observe que o gráfico Disk-IO pode ser um pouco enganador, pois os picos no gráfico geralmente são devidos aos recursos de cache dos discos rígidos; portanto, quando o disco IO é o gargalo, ele nem sempre se revela imediatamente no gráfico.

Em alguns casos, você pode se beneficiar instalando uma unidade RAM e colocando os diretórios temporários lá. Eu usei com sucessoEstepara reduzir o tempo que nossa máquina de construção costumava fazer uma compilação noturna completa e executar testes. Não tenho certeza se o SSIS se beneficiaria.

Answer 3

(repostando minha resposta inicial, não levei em consideração o BDD)

No final das contas, todo o processamento está vinculado a um dos quatro fatores

• Memória
• CPU
• Disco
• Rede

O primeiro passo é identificar qual é o fator limitante e então determinar se você pode influenciá-lo (adquirir mais ou reduzir o uso)

Escolhas de componentes

O motivo pelo qual a memória do seu servidor acaba quando você faz mais de 18 meses está relacionado ao motivo pelo qual demora tanto para ser processado. OTransformações dinâmicas e agregadassão componentes assíncronos. Cada linha proveniente do componente de origem possui N bytes de memória alocados. Esse mesmo balde de dados visita todas as transformações, tem suas operações aplicadas e é esvaziado no destino. Esse balde de memória é reutilizado continuamente.

Quando um componente assíncrono entra na arena, o pipeline é dividido. O bucket que transportava aquela linha de dados agora deve ser esvaziado em um novo bucket para concluir o pipeline. Essa cópia de dados entre árvores de execução é uma operação cara em termos de tempo de execução e memória (pode dobrar). Isso também reduz a oportunidade do mecanismo paralelizar algumas das oportunidades de execução enquanto aguarda a conclusão das operações assíncronas. Uma desaceleração adicional nas operações é encontrada devido à natureza das transformações. O Aggregate é um componente totalmente bloqueador, entãotodosos dados precisam chegar e ser processados ​​antes que a transformação libere uma única linha para as transformações downstream.

Se for possível, você pode enviar o pivô e/ou agregado para o servidor? Isso deve diminuir o tempo gasto no fluxo de dados, bem como os recursos consumidos.

Você pode tentar aumentar a quantidade de operações paralelas que o mecanismo pode escolher.Artigo de Jamie,Artigo do SQL CAT

Se você realmente deseja saber onde seu tempo está sendo gasto no fluxo de dados, registre OnPipelineRowsSent para uma execução. Então você pode usar issoconsultapara separá-lo (depois de substituir o sysssislog pelo sysdtslog90)

Transferência de rede

Com base em seus gráficos, não parece que a CPU ou a memória sejam tributadas em nenhuma das caixas. Acredito que você indicou que o servidor de origem e de destino está em uma única caixa, mas o pacote SSIS está hospedado e processado em outra caixa. Você está pagando um custo não insignificante para transferir esses dados pela rede e vice-versa. É possível processar os dados no servidor de origem? Você precisaria alocar mais recursos para essa caixa e estou cruzando os dedos, pois é uma VM grande e robusta e isso não é um problema.

Se isso não for uma opção, tente configurar oTamanho do pacotepropriedade do gerenciador de conexões para 32767 e converse com as operações de rede sobre se os jumbo frames são adequados para você. Ambas as dicas estão na seção Ajuste sua rede.

Eu sou péssimo em contadores de disco, mas você deve conseguir ver se os tipos de espera estão relacionados ao disco.