Melhorando o desempenho do fluxo de dados
Este tópico provê sugestões sobre como projetar pacotes do Integration Services para evitar assuntos de desempenho comuns. Este tópico também provê informações sobre recursos e ferramentas que podem ser usados para diagnosticar o desempenho de pacotes.
Configurando o Fluxo de Dados
Para configurar a tarefa Fluxo de Dados para um melhor desempenho, você pode configurar as propriedades da tarefa, ajustar o tamanho do buffer e configurar o pacote para execução paralela.
Configurar Propriedades da Tarefa Fluxo de Dados
.gif "Observação") Observação |
|---|
As propriedades discutidas nesta seção devem ser definidas separadamente para cada tarefa Fluxo de Dados em um pacote. |
Todas as propriedades a seguir da tarefa Fluxo de Dados podem ser configuradas com relação ao desempenho:
Especifique os locais para armazenamento temporário dos dados do buffer (propriedade BufferTempStoragePath) e das colunas que contêm dados BLOB (objeto binário grande) (propriedade BLOBTempStoragePath). Por padrão, essas propriedades contêm os valores das variáveis de ambiente TEMP e TMP. Talvez você queira especificar outras pastas para colocar os arquivos temporários em um disco rígido mais rápido ou diferente ou para espalhá-los por várias unidades. Você pode especificar vários diretórios delimitando os nomes de diretório com ponto e vírgula.
Define o tamanho padrão do buffer usado pela tarefa, definindo a propriedade DefaultBufferSize, e defina o número máximo de linhas em cada buffer, definindo a propriedade DefaultBufferMaxRows. O tamanho padrão do buffer é 10 megabytes; o tamanho máximo é de 100 megabytes. O número de máximo padrão de linhas é 10.000.
Defina o número de threads que a tarefa pode usar durante a execução, definindo a propriedade EngineThreads. Esta propriedade fornece uma sugestão ao mecanismo de fluxo de dados com relação ao número de threads que será usado. O padrão é 5, com um valor mínimo de 3. Entretanto, o mecanismo não utilizará mais threads do que o necessário, independente do valor desta propriedade. O mecanismo também pode usar mais threads que o especificado nesta propriedade, se necessário, para evitar assuntos de simultaneidade.
Indique se a tarefa Fluxo de Dados executa em modo otimizado (propriedade RunInOptimizedMode). O modo otimizado melhora o desempenho removendo colunas, saídas e componentes não utilizados do fluxo de dados.
ObservaçãoUma propriedade com o mesmo nome, RunInOptimizedMode, pode ser definida no nível do projeto em Business Intelligence Development Studio para indicar que a tarefa Fluxo de Dados é executada no modo otimizado durante a depuração. Esta propriedade de projeto substitui a propriedade RunInOptimizedMode das tarefas Fluxo de Dados no momento da criação.
Ajustar o tamanho dos buffers
O mecanismo de fluxo de dados inicia a tarefa de dimensionamento de seus buffers, calculando o tamanho estimado de uma única linha de dados. Em seguida, ele multiplica o tamanho estimado de uma linha pelo valor de DefaultBufferMaxRows para obter um valor de trabalho preliminar para o tamanho do buffer.
Se o resultado for maior que o valor de DefaultBufferSize, o mecanismo reduzirá o número de linhas.
Se o resultado for menor que o tamanho do buffer mínimo calculado interiormente, o mecanismo aumentará o número de linhas.
Se o resultado estiver entre o tamanho de buffer mínimo e o valor de DefaultBufferSize, o mecanismo dimensiona o buffer o mais próximo possível do tamanho da linha estimado vezes o valor de DefaultBufferMaxRows.
Quando você começa a testar o desempenho das suas tarefas de fluxo de dados, use os valores padrão para DefaultBufferSize e DefaultBufferMaxRows. Habilite o registro em cada tarefa de fluxo de dados e selecione o evento BufferSizeTuning para ver quantas linhas estão em cada buffer.
Antes de começar a ajustar o tamanho dos buffers, o aprimoramento mais importante que pode ser feito é reduzir o tamanho de cada linha de dados, removendo colunas desnecessárias e configurando os tipos de dados corretamente.
Quando há memória suficiente disponível, você deve usar um número menor de buffers grandes, em vez de um número maior de buffers pequenos. Em outras palavras, você pode melhorar desempenho, reduzindo o número total de buffers exigido para manter seus dados e ajustando tantas linhas de dados em um buffer, conforme possível. Para determinar o número adequado de buffers e seus respectivos tamanhos, use os valores de DefaultBufferSize e DefaultBufferMaxRows durante o desempenho de monitoramento e as informações reportadas pelo evento BufferSizeTuning.
Não aumente o tamanho do buffer para o ponto em que a paginação para o disco começa a acontecer. A paginação para o disco impede mais o desempenho do que o tamanho do buffer não otimizado. Para determinar se a página está ocorrendo, monitore o contador de desempenho "Buffers em spool" no instantâneo Desempenho no MMC (Microsoft Management Console).
Configurar o pacote para execução paralela
A execução paralela melhora desempenho em computadores com vários processadores lógicos ou físicos. Para suportar a execução paralela de diferentes tarefas no pacote, o Integration Services usa duas propriedades: MaxConcurrentExecutables e EngineThreads.
Propriedade MaxConcurrentExcecutables
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.
Propriedade EngineThreads
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. |
Configurando componentes individuais de Fluxo de Dados
Para configurar componentes individuais de fluxo de dados para um melhor desempenho, existem algumas instruções gerais que podem ser consideradas. Também há instruções específicas para cada tipo de componente de fluxo de dados: origem, transformação e destino.
Instruções Gerais
Independente do componente de fluxo de dados, existem duas instruções gerais que devem ser seguidas para melhorar o desempenho: otimizar consultas e evitar cadeias de caracteres desnecessárias.
Otimizar Consultas
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. |
Evitar classificação desnecessária
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.
Origens
Origem de OLE DB
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.
Transformações
Nesta seção, use as sugestões para melhorar o desempenho das transformações Agregação, Pesquisa Difusa, Agrupamento Difuso, Pesquisa, Mesclar Junção e Dimensão de Alteração Lenta.
Transformação Agregação
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.
Transformações Pesquisa Difusa e Agrupamento Difuso
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.
Transformação Pesquisa
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.
Transformação Mesclar Junção
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.
Transformação Dimensão de Alteração Lenta
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.
Destinos
Para atingir um melhor desempenho com destinos, considere o uso de um destino SQL Server e teste o desempenho do destino.
Destino do SQL Server
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.
Testar o desempenho de destinos
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.
Monitorando o desempenho do pacote
O Integration Services inclui ferramentas e recursos que podem ser usados para monitorar o desempenho de um pacote. Por exemplo, o registro captura informações em tempo de execução sobre um pacote, e os contadores de desempenho permitem monitorar o mecanismo de fluxo de dados. Use as sugestões a seguir para determinar quais partes do pacote estão tendo o maior efeito sobre o desempenho.
Analisar as informações na guia Progresso
O Designer SSIS fornece informações adicionais sobre o fluxo de controle e de dados quando você executa um pacote no Business Intelligence Development Studio. A guia Progresso lista as tarefas e os contêineres em ordem de execução e inclui horários de início e término, avisos e mensagens de erro para cada tarefa e contêiner, inclusive do próprio pacote. Ela também lista os componentes de fluxo de dados em ordem de execução e inclui informações sobre o progresso exibidas como um percentual completo, e o número de linhas processadas.
Para habilitar ou desabilitar a exibição de mensagens na guia Progresso, marque/desmarque a opção Depurar Relatório do Progresso no menu SSIS. Desabilitar o relatório do progresso pode ajudar a melhorar o desempenho durante a execução de um pacote complexo no BI Development Studio.
Configurar o registro no pacote
O Integration Services inclui uma variedade de provedores de log que permitem que os pacotes registrem informações no tempo de execução em tipos diferentes de arquivos ou para o SQL Server. Você pode habitar as entradas de log para pacotes e para objetos de pacote individuais como tarefas e contêineres. O Integration Services inclui uma ampla variedade de tarefas e contêineres, e cada tarefa e contêiner têm seu próprio conjunto de entradas de log descritivas. Por exemplo, um pacote que inclui uma tarefa Executar SQL pode gravar uma entrada de log que liste a instrução SQL executada pela tarefa, incluindo os valores de parâmetro da instrução.
As entradas de log incluem informações como horários de início e término dos pacotes e objetos de pacote, tornando possível identificar contêineres e tarefas executando lentamente. Para obter mais informações, consulte Registro da execução de pacotes, Implementando log em pacotes e Mensagens personalizadas para log.
Configurar o registro para tarefas Fluxo de Dados
A tarefa Fluxo de Dados fornece muitas entradas de log personalizadas que podem ser usadas para monitorar e ajustar o desempenho. Por exemplo, você pode monitorar os componentes que podem causar vazamentos de memória ou controlar quanto tempo leva para executar um determinado componente. Para obter uma lista dessas entradas de log personalizadas e saída de exemplo de log, consulte Tarefa de Fluxo de Dados.
Usar o evento PipelineComponentTime
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.
Etapa |
Descrição |
|---|---|
Validate |
O componente verifica se há valores de propriedade válidos e os parâmetros de configuração. |
PreExecute |
O componente executa processamento único antes de começar a processar linhas de dados. |
PostExecute |
O componente executa processamento único depois de processar todas as linhas de dados. |
ProcessInput |
A transformação ou o componente de destino processa as linhas de dados de entrada recebidas de uma transformação ou origem upstream. |
PrimeOutput |
O componente de origem ou transformação preenche os buffers de dados a serem passados para uma transformação downstream ou um componente de destino. |
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.
Monitorar o desempenho do mecanismo de fluxo de dados
O Integration Services inclui um conjunto de contadores de desempenho para monitorar o desempenho do mecanismo de fluxo de dados. Por exemplo, você pode controlar a quantidade total de memória, em bytes, que todos os buffers usam e verificar se os componentes estão sem memória. Um buffer é um bloco de memória que um componente usa para armazenar dados. Para obter mais informações, consulte Monitorando o desempenho do mecanismo de fluxo de dados.
Recursos externos
Artigo técnico, SQL Server 2005 Integration Services: uma estratégia de desempenho, em technet.microsoft.com
Artigo técnico, Integration Services: técnicas de ajuste de desempenho, em technet.microsoft.com
Vídeo, Ajustando seu fluxo de dados de pacotes SSIS na empresa (vídeo do SQL Server), em technet.microsoft.com
Vídeo, Noções básicas sobre buffers de fluxo de dados SSIS (vídeo do SQL Server), em technet.microsoft.com
Artigo técnico sobre como aumentar o rendimento dos pipelines dividindo transformações síncronas em várias tarefas, em sqlcat.com
Vídeo, Padrões de design de desempenho do Microsoft SQL Server Integration Services, em channel9.msdn.com.
Apresentação, Como a TI da Microsoft aproveita os aprimoramentos do mecanismo de fluxo de dados SSIS do SQL Server 2008, em sqlcat.com.
Artigo técnico, The Data Loading Performance Guide, em msdn.microsoft.com.
Artigo técnico, We Loaded 1TB in 30 Minutes with SSIS, and So Can You, em msdn.microsoft.com.
Artigo técnico, Top 10 SQL Server Integration Services Best Practices, em sqlcat.com.
Artigo técnico e exemplo, The “Balanced Data Distributor” for SSIS, em sqlcat.com.
Vídeo, Balanced Data Distributor, em technet.microsoft.com
|
.gif "Ícone do Integration Services (pequeno)")
Fique atualizado com o Integration ServicesConsulte também