Partilhar via

Recursos de desempenho de fluxo de dados

  • Artigo

se aplica a:SQL Server SSIS Integration Runtime no Azure Data Factory

Este tópico fornece sugestões sobre como projetar pacotes do Integration Services para evitar problemas comuns de desempenho. Este tópico também fornece informações sobre recursos e ferramentas que você pode usar para solucionar problemas de 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 as propriedades da tarefa de fluxo de dados

Observação

As propriedades discutidas nesta seção devem ser definidas separadamente para cada tarefa de Fluxo de Dados em um pacote.

Você pode configurar as seguintes propriedades da tarefa Fluxo de Dados, que afetam o desempenho:

  • Especifique os locais para armazenamento temporário de dados de buffer (propriedade BufferTempStoragePath) e de colunas que contêm dados de objeto binário grande (BLOB) (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 uma unidade de disco rígido diferente ou mais rápida 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.

  • Defina o tamanho padrão do buffer que a tarefa usa, definindo a propriedade DefaultBufferSize, e defina o número máximo de linhas em cada buffer, definindo a propriedade DefaultBufferMaxRows. Defina a propriedade AutoAdjustBufferSize para indicar se o tamanho padrão do buffer é calculado automaticamente a partir do valor da propriedade DefaultBufferMaxRows. O tamanho padrão do buffer é de 10 megabytes, com um tamanho máximo de buffer de 2^31-1 bytes. O número 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 para o mecanismo de fluxo de dados sobre o número de threads a serem usados. O padrão é 10, com um valor mínimo de 3. No entanto, o mecanismo não usará mais threads do que precisa, independentemente do valor dessa propriedade. O mecanismo também pode usar mais threads do que o especificado nesta propriedade, se necessário para evitar problemas de simultaneidade.

  • Indique se a tarefa Fluxo de Dados é executada no 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 no 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 de tarefas de fluxo de dados durante a fase de design.

Ajustar o dimensionamento de buffers

O mecanismo de fluxo de dados inicia a tarefa de dimensionar 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 AutoAdjustBufferSize estiver configurado para verdadeiro, o mecanismo de fluxo de dados usará o valor calculado como o tamanho do buffer e o valor de DefaultBufferSize será ignorado.

  • Se AutoAdjustBufferSize estiver definido como false, o mecanismo de fluxo de dados do mecanismo usará as seguintes regras para determinar o tamanho do buffer.

    • Se o resultado for maior que o valor de DefaultBufferSize, o mecanismo reduz o número de linhas.

    • Se o resultado for menor do que o tamanho mínimo de buffer calculado internamente, o mecanismo aumenta o número de linhas.

    • Se o resultado estiver entre o tamanho mínimo do buffer e o valor de DefaultBufferSize, o mecanismo dimensionará o buffer o mais próximo possível do tamanho estimado da linha vezes o valor de DefaultBufferMaxRows.

Quando começar a testar o desempenho de suas tarefas de fluxo de dados, use os valores padrão para DefaultBufferSize e DefaultBufferMaxRows. Habilite o registro em log na tarefa de fluxo de dados e selecione o evento BufferSizeTuning para ver quantas linhas estão contidas em cada buffer.

Antes de começar a ajustar o dimensionamento dos buffers, a melhoria mais importante que você pode fazer é reduzir o tamanho de cada linha de dados removendo colunas desnecessárias e configurando tipos de dados adequadamente.

Para determinar o número ideal de buffers e seu tamanho, experimente os valores de DefaultBufferSize e DefaultBufferMaxRows enquanto monitora o desempenho e as informações relatadas pelo evento BufferSizeTuning.

Não aumente o tamanho do buffer até o ponto em que a paginação para o disco começa a ocorrer. A paginação em disco prejudica o desempenho mais do que um tamanho de buffer que não foi otimizado. Para determinar se a paginação está ocorrendo, monitorize o contador de desempenho "Buffers spooled" no acessório de Performance do Console de Gestão da Microsoft (MMC).

Configurar o pacote para execução paralela

A execução paralela melhora o desempenho em computadores com vários processadores físicos ou lógicos. Para dar suporte à execução paralela de diferentes tarefas no pacote, o Integration Services usa duas propriedades: MaxConcurrentExecutables e EngineThreads.

A propriedade MaxConcurrentExecutables

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

Para entender como essa propriedade funciona, considere um pacote de exemplo que tenha três tarefas de Fluxo de Dados. Se você definir MaxConcurrentExecutables como 3, todas as três tarefas de fluxo de dados poderão ser executadas simultaneamente. No entanto, suponha que cada tarefa de Fluxo de Dados tenha 10 árvores de execução de origem para destino. Definir MaxConcurrentExecutables como 3 não garante que as árvores de execução dentro de cada tarefa de Fluxo de Dados sejam executadas em paralelo.

A propriedade EngineThreads

A propriedade EngineThreads é uma propriedade de cada tarefa de Fluxo de Dados. Essa propriedade define quantos threads o mecanismo de fluxo de dados pode criar e executar em paralelo. A propriedade EngineThreads aplica-se igualmente aos threads de origem que o mecanismo de fluxo de dados cria para fontes e aos threads de trabalho que o mecanismo cria para transformações e destinos. Portanto, definir EngineThreads para 10 significa que o mecanismo pode criar até dez threads de origem e até dez threads de trabalho.

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

Observação

Uma discussão sobre threading está além do escopo deste tópico. No entanto, a regra geral é não executar mais threads em paralelo do que o número de processadores disponíveis. A execução de mais threads do que o número de processadores disponíveis pode prejudicar o desempenho devido à frequente alternância de contexto entre threads.

Configurando componentes de fluxo de dados individuais

Para configurar componentes de fluxo de dados individuais para um melhor desempenho, há algumas diretrizes gerais que você pode seguir. Há também diretrizes específicas para cada tipo de componente de fluxo de dados: origem, transformação e destino.

Orientações Gerais

Independentemente do componente de fluxo de dados, há duas diretrizes gerais que você deve seguir para melhorar o desempenho: otimizar consultas e evitar cadeias de caracteres desnecessárias.

Otimizar consultas

Vários componentes de fluxo de dados usam consultas, seja quando extraem dados de fontes ou em operações de pesquisa para criar tabelas de referência. A consulta padrão usa a sintaxe SELECT * FROM <tableName>. Esse tipo de consulta retorna todas as colunas da tabela de origem. Ter todas as colunas disponíveis na fase de conceção torna possível escolher qualquer coluna como uma coluna de referência, de passagem direta ou de origem. No entanto, depois de selecionar as colunas a serem usadas, você deve revisar a consulta para incluir apenas as colunas selecionadas. A remoção de colunas supérfluas torna o fluxo de dados em um pacote mais eficiente porque menos colunas criam uma linha menor. Uma linha menor significa que mais linhas podem caber em um buffer e menos trabalho é processar todas as linhas no conjunto de dados.

Para construir uma consulta, você pode digitá-la ou usar o Construtor de Consultas.

Observação

Quando você executa um pacote no SSDT (SQL Server Data Tools), a guia Progresso do Designer SSIS lista avisos. Esses avisos incluem a identificação de qualquer coluna de dados que uma fonte disponibiliza para o fluxo de dados, mas não é usada posteriormente pelos componentes de fluxo de dados a jusante. Você pode usar a propriedade RunInOptimizedMode para remover essas colunas automaticamente.

Evite a classificação desnecessária

A classificação é inerentemente uma operação lenta, e evitar a classificação desnecessária pode melhorar o desempenho do fluxo de dados do pacote.

Às vezes, os dados de origem já foram classificados antes de serem usados por um componente a jusante. Essa pré-classificação pode ocorrer quando a consulta SELECT usou uma cláusula ORDER BY ou quando os dados foram inseridos na fonte em ordem ordenada. Para esses dados de origem pré-classificados, pode fornecer uma indicação de que os dados estão ordenados e, assim, evitar o uso de uma transformação Sort para satisfazer os requisitos de ordenação de determinadas transformações a jusante. (Por exemplo, as transformações Mesclar e Mesclar Junção exigem entradas classificadas.) Para fornecer uma dica de que os dados são classificados, você precisa executar as seguintes tarefas:

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

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

Para obter mais informações, consulte Classificar dados para as transformações de mesclagem e mesclagem de junção.

Se você tiver que classificar os dados no fluxo de dados, poderá melhorar o desempenho projetando o fluxo de dados para usar 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 da execução da transformação Multicast, em vez de classificar várias saídas após a transformação.

Para obter mais informações, consulte Sort Transformation, Merge Transformation, Merge Join Transformatione Multicast Transformation.

Fontes

Origem do OLE DB

Quando você usa uma fonte OLE DB para recuperar dados de uma exibição, selecione "Comando SQL" como o modo de acesso a dados e insira uma instrução SELECT. Acessar dados usando uma instrução SELECT tem um desempenho melhor do que selecionar "Tabela ou exibição" como o modo de acesso a dados.

Transformações

Use as sugestões desta seção para melhorar o desempenho das transformações Agregação, Pesquisa difusa, Agrupamento difuso, Pesquisa, Junção de mesclagem e Dimensão de mudança lenta.

Transformação agregada

A transformação Aggregate inclui as propriedades Keys, KeysScale, CountDistinctKeyse CountDistinctScale. Essas propriedades melhoram o desempenho, permitindo que a transformação pré-aloque a quantidade de memória de que a transformação precisa para os dados armazenados em cache. Se você souber o número exato ou aproximado de grupos que se espera que resultem de uma operação Grupo de , defina as propriedades Chaves e KeysScale, respectivamente. Se souber o número exato ou aproximado de valores distintos que se espera que resultem de uma operação de contagem de distintos , defina as propriedades CountDistinctKeys e CountDistinctScale, respectivamente.

Se você tiver que criar várias agregações em um fluxo de dados, considere criar várias agregações que usam uma transformação Agregada em vez de criar várias transformações. Essa abordagem melhora o desempenho quando uma agregação é um subconjunto de outra agregação porque a transformação pode otimizar o armazenamento interno e verificar os dados recebidos 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. No entanto, a execução de várias agregações em uma transformação agregada serializa as operações de agregação e, portanto, pode não melhorar o desempenho quando várias agregações devem ser calculadas independentemente.

Pesquisa difusa e transformações de agrupamento difuso

Para obter informações sobre como otimizar o desempenho das transformações Pesquisa Fuzzy e Agrupamento Fuzzy, consulte o white paper Pesquisa Fuzzy e Agrupamento Fuzzy no SQL Server Integration Services 2005.

Transformação de pesquisa

Minimize o tamanho dos dados de referência na memória inserindo uma instrução SELECT que procure apenas as colunas necessárias. Essa opção funciona melhor do que selecionar uma tabela ou exibição inteira, que retorna uma grande quantidade de dados desnecessários.

Transformação de Merge Join

Não é mais necessário configurar o valor da propriedade MaxBuffersPerInput porque a Microsoft fez alterações que reduzem o risco de que a transformação Merge Join consuma memória excessiva. Esse problema às vezes ocorreu quando as várias entradas do Merge Join produziram dados a taxas irregulares.

Transformação de dimensão em mudança lenta

O Assistente de Dimensão de Mudança Lenta e a Transformação de Dimensão de Mudança Lenta são ferramentas gerais que satisfazem as necessidades da maioria dos utilizadores. No entanto, o fluxo de dados que o assistente gera não é otimizado para desempenho.

Normalmente, os componentes mais lentos na transformação de dimensão de mudança lenta são as transformações de comando OLE DB que executam UPDATEs numa única linha de cada vez. Portanto, a maneira mais eficaz de melhorar o desempenho da transformação de dimensão de mudança lenta é substituir as transformações de comando OLE DB. Você pode substituir essas transformações por componentes de destino que salvem todas as linhas a serem atualizadas em uma tabela de preparação. Em seguida, é possível adicionar uma tarefa Execute SQL que executa uma única atualização baseada em conjunto Transact-SQL em todas as linhas simultaneamente.

Os usuários avançados podem projetar um fluxo de dados personalizado para o processamento de dimensões que muda lentamente e é otimizado para grandes dimensões. Para obter uma discussão e um exemplo dessa abordagem, consulte a seção "Cenário de dimensão exclusiva" no white paper Project REAL: Business Intelligence ETL Design Practices.

Destinos

Para obter um melhor desempenho com destinos, considere usar um destino do SQL Server e testar o desempenho do destino.

Destino do SQL Server

Quando um pacote carrega dados em uma instância do SQL Server no mesmo computador, use um destino do SQL Server. Este destino é otimizado para cargas a granel de alta velocidade.

Testando o desempenho dos destinos

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

Revise as informações na guia Progresso

O Designer SSIS fornece informações sobre o fluxo de controle e o fluxo de dados quando você executa um pacote no SSDT (SQL Server Data Tools). O separador Progresso enumera as tarefas e contentores por ordem de execução e inclui os horários de início e de fim, assim como os Avisos e as mensagens de erro para cada tarefa e contentor, incluindo o próprio pacote. Ele também lista os componentes de fluxo de dados em ordem de execução e inclui informações sobre o progresso, exibido como porcentagem concluída e o número de linhas processadas.

Para ativar ou desativar a exibição de mensagens no separador Progresso, alterne a opção Relatório de Depuração de Progresso no menu SSIS. Desabilitar o relatório de progresso pode ajudar a melhorar o desempenho durante a execução de um pacote complexo no SQL Server Data Tools.

Conteúdo relacionado

Artigos e Posts de Blog

Vídeos

Ver também

Ferramentas de solução de problemas para desenvolvimento de pacotes
Ferramentas de solução de problemas para execução de pacotes