Recursos de desempenho de fluxo de dados

Este tópico fornece sugestões sobre como projetar pacotes do Integration Services para evitar problemas comuns de desempenho. 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

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 é 10, com um valor mínimo de 3. No entanto, o mecanismo não usará mais encadeamentos que o necessário, independente do valor dessa 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ção
  Uma propriedade com o mesmo nome, RunInOptimizedMode, pode ser definida no nível do projeto em SSDT (SQL Server Data Tools) 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 paginação está ocorrendo, monitore o contador de desempenho "Buffers em spool" no snap-in Desempenho do MMC (Console de Gerenciamento Microsoft).

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 oferecer suporte à 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. Há também diretrizes específicas para cada tipo de componente de fluxo de dados: fonte, transformação e destino.

Instruções gerais

Independente do componente de fluxo de dados, há duas diretrizes gerais que devem ser seguidas para melhorar o desempenho: otimizar as 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 SSDT (SQL Server Data Tools), 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 de chave de classificação nas quais os dados serão classificados.

Para obter mais informações, consulte Classificar dados para as transformações Mesclagem 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 Junção de Mesclagem 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 pode não melhorar o desempenho quando várias agregações devem ser computadas de forma independente.

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 Fuzzy Lookup and Fuzzy Grouping in SQL Server Integration Services 2005 (em inglês).

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 é uma opção melhor do que selecionar uma tabela ou exibição inteira, que retorna uma quantidade grande de dados desnecessários.

Transformação Junção de Mesclagem

Não é mais preciso configurar o valor da propriedade MaxBuffersPerInput, pois a Microsoft fez alterações que reduzem o risco de a transformação Junção de Mesclagem consumir memória excessiva. Esse problema algumas vezes ocorria quando as várias entradas da Junção de Mesclagem geravam dados a taxas irregulares.

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 uso geral 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 ver uma discussão e um exemplo dessa abordagem, consulte a seção "Unique dimension scenario" (Cenário de dimensão exclusiva) no white paper Project REAL: Business Intelligence ETL Design Practices (Projeto REAL: práticas de design ETL de 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.

Testando 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.

Analisar as informações na guia Progresso

O Designer SSIS fornece informações adicionais sobre o fluxo de controle e o fluxo de dados quando você executa um pacote no SSDT (SQL Server Data Tools). 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 ou 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 SQL Server Data Tools.

Tarefas relacionadas

• Classificar dados para as transformações Mesclagem e Junção de Mesclagem

Conteúdo relacionado

Artigos e postagens de blog

• Artigo técnico, SQL Server 2005 Integration Services: A Strategy for Performance (SQL Server 2005 Integration Services: uma estratégia de desempenho), em technet.microsoft.com

• Artigo técnico, Integration Services: Performance Tuning Techniques (Integration Services: técnicas de ajuste de desempenho), 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

• 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.

• Postagem do blog, Solução de problemas de desempenho do pacote SSIS, no blogs.msdn.com.

Vídeos

• Série de vídeos, Designing and Tuning for Performance your SSIS packages in the Enterprise (SQL Video Series) (Projetando e ajustando seus pacotes SSIS para desempenho no Enterprise [série de vídeos do SQL])

• 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

• 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.

• Vídeo, Balanced Data Distributor, em technet.microsoft.com

Mantenha-se atualizado com o Integration Services Para obter os downloads, artigos, exemplos e vídeos mais recentes da Microsoft, assim como soluções selecionadas pela comunidade, visite a página do Integration Services no MSDN: Visite a página do Integration Services no MSDN Para receber uma notificação automática dessas atualizações, assine os RSS feeds disponíveis na página.

Consulte também

Tarefas

Solucionando problemas de ferramentas para desenvolvimento de pacotes

Conceitos

Solucionando problemas de ferramentas para execução de pacotes