Melhorando o desempenho do fluxo de dados

A propriedade MaxConcurrentExecutables é uma propriedade do próprio pacote. Esta propriedade define quantas tarefas podem ser executadas simultaneamente. O valor padrão é -1, que significa o número de processadores físicos ou lógicos mais 2.

Para compreender como esta propriedade trabalha, considere um pacote de exemplo que tenha três tarefas Fluxo de Dados. Se você definir MaxConcurrentExecutables como 3, todas as três tarefas Fluxo de Dados poderão ser executadas simultaneamente. Entretanto, assuma que cada tarefa Fluxo de Dados tenha 10 árvores de execução de origem para destino. Se você definir MaxConcurrentExecutables como 3, não será possível garantir que a execução das árvores dentro de cada Fluxo de Dados será executada na forma paralela.

A propriedade EngineThreads é uma propriedade de cada tarefa Fluxo de Dados. Esta propriedade define quantos threads o mecanismo de fluxo de dados poderá criar e executar na forma paralela. A propriedade EngineThreads se aplica igualmente a ambos os threads de origem que o mecanismo de fluxo de dados cria para origens e os threads de trabalho que o mecanismo cria para transformações e destinos. Portanto, definir EngineThreads como 10 significa que o mecanismo pode criar até dez threads de origem e até dez threads de trabalho.

Para compreender como esta propriedade trabalha, considere o pacote de exemplo com três tarefas Fluxo de Dados. Cada tarefa Fluxo de Dados contém dez árvores de execução de origem para destino. Se você definir EngineThreads como 10 em cada tarefa Fluxo de Dados, todas as 30 árvores de execução poderão ser executadas simultaneamente.

Observação
Existe uma discussão sobre threading que traz mais informações. Entretanto, a regra geral indica para não executar mais threads na forma paralela que o número de processadores disponíveis. Executar mais threads que o número de processadores disponíveis pode impedir o desempenho devido à alternância de contexto frequente entre os threads.

Um determinado número de componentes de fluxo de dados usa consultas, tanto quando extraem dados das origens quanto em operações de pesquisa para criar tabelas de referências. A consulta padrão usa a sintaxe SELECT * FROM <tableName>. Este tipo de consulta retorna todas as colunas na tabela de origem. Considerando que você tenha todas as colunas disponíveis no momento da criação, é possível escolher qualquer coluna como uma coluna de pesquisa, passagem ou origem. No entanto, depois de selecionar as colunas que serão usadas, será preciso revisar a consulta para incluir somente as colunas selecionadas. Remover as colunas supérfluas faz com que o fluxo de dados torne-se mais eficaz, pois quanto menor for o número de colunas, menor será a linha criada. Uma linha pequena significa que mais linhas podem ser ajustadas no buffer e o trabalho para processar todas as linhas no conjunto de dados é menor.

Para construir uma consulta, é preciso inseri-la ou usar o Construtor de Consultas.

Observação

Ao executar um pacote no Business Intelligence Development Studio, a guia Progresso do Designer SSIS indicará alguns avisos. Esses avisos incluem a identificação da coluna de dados que uma origem disponibiliza para o fluxo de dados, mas que não é usada de forma subsequente pelos componentes de fluxo de dados do downstream. Você pode usar a propriedade RunInOptimizedMode para remover essas colunas automaticamente.

A classificação é inerentemente uma operação lenta e, evitando uma classificação desnecessária, você aumenta o desempenho do fluxo de dados do pacote.

Algumas vezes os dados de origem já foram classificados antes de serem usados por um componente do downstream. Essa pré-classificação pode ocorrer quando a consulta SELECT usar uma cláusula ORDER BY ou quando os dados forem inseridos na origem na ordem classificada. Para os dados de origem pré-classificados, é possível mencionar uma dica de que a classificação dos dados é viável, evitando, assim, o uso de uma transformação Classificar para atender aos requisitos de classificação de determinadas transformações de downstream. (Por exemplo, as transformações Mesclar e Mesclar Junção exigem entradas classificadas.) Para fornecer uma dica de que os dados são classificados, execute as seguintes tarefas:

• Defina a propriedade IsSorted na saída de um componente de fluxo de dados de upstream como True.

• Especifique as colunas chave de classificação nas quais os dados serão classificados.

Para obter mais informações, consulte Como classificar dados para as Transformações Mesclar e Junção de Mesclagem.

Se precisar classificar os dados no fluxo de dados, melhore o desempenho fazendo com que o fluxo de dados use o menor número possível de operações de classificação. Por exemplo, o fluxo de dados usa uma transformação Multicast para copiar o conjunto de dados. Classifique o conjunto de dados uma vez antes de a transformação Multicast ser executada em vez de classificar as diversas saídas após a transformação.

Para obter mais informações, consulte Transformação Classificação, Transformação Mesclar, Transformação Mesclar Junção e Transformação Difusão Seletiva.

Ao usar uma origem de OLE DB para recuperar os dados de uma exibição, selecione "Comando SQL" como o modo de acesso aos dados e insira uma instrução SELECT. Acessar os dados usando uma instrução SELECT faz com que a "Tabela ou Exibição" selecionada seja executada melhor no modo de acesso de dados.

A transformação Agregação inclui as propriedades Keys, KeysScale, CountDistinctKeys e CountDistinctScale. Essas propriedades melhoram o desempenho permitindo que transformação pré-aloque a quantidade de memória necessária para os dados armazenados em cache pela transformação. Se você souber o número exato ou aproximado de grupos esperados como resultado de uma operação Group by, defina as propriedades Keys e KeysScale respectivamente. Se você souber o número exato ou aproximado de valores distintos que são esperados como resultado de uma operação Distinct count, defina as propriedades CountDistinctKeys e CountDistinctScale, respectivamente.

Se tiver criado várias agregações em um fluxo de dados, considere a criação de várias agregações que usam uma transformação Agregação em vez de criar várias transformações. Esse procedimento melhora o desempenho quando uma agregação for um subconjunto de outra agregação porque a transformação pode otimizar o armazenamento interno e analisar os dados de entrada apenas uma vez. Por exemplo, se uma agregação usa uma cláusula GROUP BY e uma agregação AVG, combiná-las em uma transformação pode melhorar o desempenho. Entretanto, executar várias agregações dentro de uma transformação Agregação serializa as operações de agregação e não melhoraram o desempenho quando várias agregações devem ser computadas de forma independente.

Para obter mais informações, consulte Transformação Agregação.

Para obter informações mais detalhadas sobre as transformações Pesquisa Difusa e Agrupamento Difuso, consulte a documentação Pesquisa Difusa e Agrupamento Difuso no SQL Server Integration Services 2005.

Minimize o tamanho dos dados de referência na memória usando uma instrução SELECT que seja capaz de pesquisar somente as colunas necessárias. Esta é a melhor opção do que selecionar uma tabela ou exibição inteira, que retorna uma quantidade grande de dados desnecessários.

A transformação Mesclar Junção inclui a propriedade do MaxBuffersPerInput, que especifica o número de máximo de buffers que podem estar ativos para cada entrada simultaneamente. Você pode usar esta propriedade para ajustar a quantidade de memória que os buffers de transformação utilizam e, consequentemente, o desempenho da transformação. Quanto maior o número de buffers, mais memória a transformação utiliza e melhor é o desempenho. O valor padrão do MaxBuffersPerInput é 5, que é o número de buffers que funciona bem na maioria dos cenários. Para ajustar o desempenho, você pode tentar usar um número ligeiramente diferente de buffers tais como 4 ou 6. Se possível, você deve evitar utilizar um número muito pequeno de buffers. Por exemplo, definir oMaxBuffersPerInput como 1 em vez de 5 tem um impacto significante no desempenho. Além disso, você não deve definir MaxBuffersPerInput como 0 ou menos. Esse intervalo de valores significa que não há estrangulamentos e, dependendo da carga de dados e do volume de memória disponível, o pacote pode não ser concluído.

Para evitar um deadlock, a transformação Mesclar Junção pode aumentar temporariamente o número de buffers utilizados além do valor de MaxBuffersPerInput. Depois que a condição de deadlock estiver resolvida, o MaxBuffersPerInput retorna ao seu valor configurado.

Para obter mais informações, consulte Transformação Mesclar Junção.

O Assistente para Dimensão Alteração Lenta e a transformação Dimensão Alteração Lenta são ferramentas de propósitos gerais que atendem às necessidades da maioria dos usuários. Entretanto, o fluxo de dados gerado pelo assistente não é otimizado para o desempenho.

Normalmente, os componentes mais lentos na transformação Dimensão Alteração Lenta são as transformações Comando de OLE DB que executam UPDATEs (atualizações) em apenas uma linha por vez. Portanto, a forma mais eficaz de melhorar o desempenho da transformação Dimensão Alteração Lenta é substituir as transformações Comando de OLE DB. Essas transformações podem ser substituídas por componentes de destino que salvam todas as linhas que serão atualizadas para uma tabela de preparação. Por isso, é possível adicionar uma tarefa Executar SQL que desenvolva uma única instrução UPDATE Transact-SQL com base no conjunto em todas as linhas ao mesmo tempo.

Usuários avançados podem criar um fluxo de dados personalizado para alterar o processamento da dimensão que é otimizada lentamente em dimensões maiores. Para obter exemplos e informações a respeito desse procedimento, consulte a seção "Cenário de dimensão exclusiva" no white paper Projeto REAL: Práticas recomendadas ETL de criação no Business Intelligence.

Quando um pacote carregar dados para uma instância de SQL Server no mesmo computador, use um destino SQL Server. Este destino é otimizado para carregamento em massa de alta velocidade.

Você pode achar que salvar os dados em destinos leva mais tempo que o esperado. Para identificar se a lentidão é causada por uma incapacidade do destino em processar dados rápido o suficiente, você pode substituir temporariamente o destino por uma transformação Contagem de Linhas. Se a taxa de transferência melhorar significativamente, é provável que o destino que está carregando os dados esteja causando a lentidão.

Talvez a entrada de log personalizada mais útil seja o evento PipelineComponentTime. Essa entrada de log reporta o número de milissegundos que cada componente do fluxo de dados leva em cada uma das cinco principais etapas de processamento. A tabela a seguir descreve essas etapas de processamento. Os desenvolvedores do Integration Services reconhecerão essas etapas com os principais métodos de um PipelineComponent.

Quando você habilita o evento PipelineComponentTime, o Integration Services registra em log uma mensagem relativa a cada etapa de processamento executada por cada componente. As seguintes entradas de log mostram um subconjunto das mensagens registradas em log pela amostra do pacote CalculatedColumns do Integration Services:

The component "Calculate LineItemTotalCost" (3522) spent 356 milliseconds in ProcessInput.

The component "Sum Quantity and LineItemTotalCost" (3619) spent 79 milliseconds in ProcessInput.

The component "Calculate Average Cost" (3662) spent 16 milliseconds in ProcessInput.

The component "Sort by ProductID" (3717) spent 125 milliseconds in ProcessInput.

The component "Load Data" (3773) spent 0 milliseconds in ProcessInput.

The component "Extract Data" (3869) spent 688 milliseconds in PrimeOutput filling buffers on output "OLE DB Source Output" (3879).

The component "Sum Quantity and LineItemTotalCost" (3619) spent 141 milliseconds in PrimeOutput filling buffers on output "Aggregate Output 1" (3621).

The component "Sort by ProductID" (3717) spent 16 milliseconds in PrimeOutput filling buffers on output "Sort Output" (3719).

Essas entradas de log mostram que a tarefa de fluxo de dados gastou a maior parte do tempo nas seguintes etapas, mostradas aqui em ordem decrescente:

• A origem de OLE DB denominada "Extrair Dados" gastou 688 ms. carregando dados.

• A transformação Coluna Derivada denominada “Calcular LineItemTotalCost" gastou 356 ms. executando cálculos em linhas de entrada.

• A transformação Agregação denominada "Quantidade de Soma e LineItemTotalCost" gastou no total 220 ms — 141 em PrimeOutput e 79 em ProcessInput — executando cálculos e passando os dados para a próxima transformação.