Este vídeo demonstra como melhorar o desempenho do fluxo de dados em um pacote do Integration Services. Neste vídeo, você aprenderá a ajustar as seguintes fases do fluxo de dados:

Extração
Transformação
Carregamento

Você poderá aplicar estas dicas de ajuste de desempenho ao projetar, desenvolver e executar o fluxo de dados.

Agradecimentos

Obrigado a Thomas Kejser pela contribuição no material para a Série de vídeos SSIS: projetando e ajustando o desempenho do SQL Server. Este vídeo é o segundo da série.

Obrigado a Carla Sabotta e a Douglas Laudenschlager pela orientação e pelos comentários valiosos.

Transcrição do vídeo

Marcação de tempo do vídeo	Áudio
00:00	Olá, aqui é David Noor, líder em desenvolvimento sênior do SQL Server Integration Services na Microsoft. Bem-vindo a este vídeo sobre o ajuste do fluxo de dados de pacote SSIS na empresa. Este vídeo é a segunda parte de uma série de quatro partes intitulada SSIS: projetando e ajustando o desempenho. Na primeira parte da série, Denny analisou a melhor maneira de medir e compreender o desempenho de pacotes SSIS. Nesta parte, usaremos isso como base e veremos como melhorar o desempenho do fluxo de dados de pacotes SSIS. Vamos começar identificando os temas comuns em cada fluxo de dados, e como escolher as partes em que você deverá trabalhar primeiro ao tentar melhorar o desempenho. Uma vez que você entenda onde estão os problemas de desempenho, terá várias opções de tornar a execução dos processos mais rápida e eficiente nos seus fluxos de dados. Veremos uma série de dicas específicas que você poderá aplicar ao projetar, desenvolver e até executar seu fluxo de dados. Vamos começar! A maioria dos fluxos de dados de pacotes se concentra em extrair, transformar e carregar dados críticos, o que é conhecido como ETL. Ajustar seus fluxos de dados significa ajustar cada uma destas fases do fluxo de dados: extração, transformação e carregamento. Para a extração, falaremos sobre como ajustar componentes da origem SSIS e as consultas que eles executam para oferecer a você o melhor desempenho e o impacto mínimo nos sistemas de origem. Ajustar as transformações no fluxo de dados significa analisar o trabalho que você precisa realizar e escolher o método ideal para realizá-lo, removendo etapas desnecessárias e, às vezes, alterando as consultas originais para fazer transformações em seu lugar. Por último, ajustar a carga do fluxo de dados significa ajustar componentes de destino SSIS, redes, bloqueio e outros fatores que possam impedir que seu fluxo de dados avance à medida que tenta carregar dados no destino. Neste vídeo, falaremos sobre dicas práticas que você poderá usar para procurar oportunidades de ajuste no seu fluxo de dados em todas as três fases. Antes de começarmos a examinar essas dicas, é importante lembrar que, em geral, não existe uma solução genial quando se trata de problemas de desempenho. Nenhuma dica encontrada aqui corrigirá todos os problemas de desempenho que seu fluxo de dados possa ter. As dicas que mostraremos funcionam bem em casos comuns, mas não é sua intenção simplesmente despejar um monte de dicas no seu problema. A melhor opção é sempre entender qual é o propósito dos seus fluxos de dados, ter certeza de que eles foram bem projetados para atender a esses objetivos, medir seu desempenho atual e, interativamente, fazer alterações no fluxo de dados e medir se a sua alteração melhorou ou não o desempenho. Além disso, quando você está analisando um problema de desempenho, é fácil se fixar em uma tecnologia ou um componente em particular, algumas vezes devido à familiaridade com a tecnologia ou o código. Mas seja qual for o motivo, tente pensar no seu fluxo de dados no contexto de um ecossistema inteiro — provavelmente vários bancos de dados relacionais, uma rede, sistemas operacionais, sistemas de arquivos, inúmeros elementos. Quanto mais você conhecer todo esse ecossistema, maior será a ideia que você terá quanto aos desafios reais de desempenho que um elemento está enfrentando, e poderá fazer alterações mais eficientes e menos arriscadas.
03:38	Vamos começar no mesmo ponto em que o fluxo de dados começa: na extração. Ao usar o SQL Server ou qualquer outro banco de dados com uma interface em massa como a origem, você deve experimentar aumentar o tamanho do pacote. Normalmente, o padrão de 4.096 trabalhos do SQL Server funciona bem, mas como a sua extração moverá grandes quantidades de dados, o aumento deverá ajudar. Para que isso entre em vigor, peça também ao administrador da rede para habilitar “Quadros Jumbo” em sua rede. No entanto, você deverá testar que efeito isso terá no seu pacote. Se estiver usando o mesmo gerenciador de conexões para operações em massa, como uma origem OLE DB, e para operações de linha única, como um comando OLE DB, você poderá considerar a criação de um segundo gerenciador de conexões para as operações de comandos OLE DB, e usar aí um tamanho de pacote menor. Como observado, se a sua ETL estiver em execução no Windows 2008 e você estiver em um computador de vários núcleos com diversas NICs, poderá obter um desempenho de rede um pouco melhor relacionando placas de rede a núcleos individuais. Para obter mais informações, procure uma postagem em blog intitulada Scaling Heavy Network Traffic with Windows no site MSDN. Uma das tarefas mais fáceis que se espera no ajuste da extração é ajustar as consultas que você está usando. Às vezes é tentador simplesmente selecionar a tabela da qual você quer extrair e permitir que todas as colunas sejam recuperadas, mas os resultados serão bem melhores se você selecionar somente as colunas de que realmente precisa. Isso resultará não apenas em menor tráfego na rede e memória utilizada, mas o servidor de banco de dados poderá fazer muito menos E/Ss para atender à sua consulta. Como é mostrado aqui, no SQL Server, você também poderá usar uma dica para indicar que nenhum bloqueio compartilhado deverá ser emitido durante a seleção, assim a sua consulta lerá dados sujos ou potencialmente não confirmados. Reserve isso para casos em que você deve ter absolutamente o melhor desempenho, e tem certeza de que a leitura de dados sujos sempre valerá para seu trabalho de ETL. As pesquisas podem ser interpretadas como extração ou transformação. De qualquer forma, você deverá usar algumas das mesmas ideias mostradas no slide anterior. Aqui, é ainda mais crítico selecionar apenas as colunas necessárias, não somente para otimização da rede, mas também para a otimização de seu cache na memória. Se estiver usando o SQL Server 2008, um dos melhores recursos para o desempenho é a adição do novo cache de pesquisa compartilhado. O compartilhamento de um cache de pesquisa permite que você busque seus dados de referência uma vez e, em seguida, reutilize-os em várias operações de pesquisa no seu pacote, ou até em outros pacotes salvando o cache em um arquivo. Se você tiver vários componentes de pesquisa que referenciem a mesma tabela, poderá verificar essa nova opção como um meio de aumentar expressivamente a velocidade de seus pacotes. O uso pode ser muito simples. Crie um Gerenciador de conexões do cache no seu pacote, popule o cache usando a nova transformação de cache e altere suas pesquisas para que consultem essa conexão do cache para seus dados de referência.
06:29	Vamos passar para a segunda fase de ETL: transformação. Na transformação, você trabalha com dados já carregados no fluxo de dados e tenta colocá-los no formato correta antes de carregá-los. No SSIS, cada transformação aparece como um objeto que você pode arrastar para o seu design, mas nem todas as transformações são feitas da mesma forma. Vamos examinar os três tipos de transformações que existem no SSIS: As transformações síncronas, ou baseadas em linha, operam em buffers de dados in-loco. Elas não criam cópias de buffers de dados ou linhas à medida que fluem. Apenas transformam os dados diretamente no buffer. Isso torna as transformações síncronas razoavelmente rápidas. Exemplos de transformações síncronas incluem conversão de dados, coluna derivada e pesquisa. As transformações assíncronas de bloqueio parcial são diferentes. À medida que os dados entram, a transformação precisa retê-los para realizar seu trabalho. Para isso, ela irá copiar os dados de entrada em buffers internos e consumir memória, às vezes um volume imenso. Enquanto os dados continuam a fluir, essas transformações podem perceber que é possível ir em frente e gravar dados de saída. Com isso, será liberada a memória interna correspondente que era retida pelas transformações. Quando o fluxo de dados é concluído, elas liberam toda a memória que usavam. Mas, até lá, geralmente consomem uma quantidade considerável dos seus dados. Exemplos desses tipos de transformações incluem Mesclagem, Junção de Mesclagem e Unir Tudo. Quando você tiver essas transformações no seu fluxo de dados, procure meios de otimizar. Existem cópias redundantes? Existem Junções de Mesclagem ou Uniões que você possa “inserir” no sistema de origem? Já vi pacotes em que, em vez de fazerem um JOIN em uma consulta de origem, buscavam todos os dados em duas tabelas usando origens OLE DB e, em seguida, usavam uma Junção de Mesclagem no pacote para fazer a junção, mesmo que pudessem ter apenas gravado uma junção SQL na origem, e teria sido muito mais rápida devido à capacidade de o banco de dados otimizar essa consulta. Procure essas oportunidades para consolidar e reduzir o número de transformações assíncronas. No SQL Server 2008, trabalhamos muito no agendador de tarefas de fluxo de dados para criar fluxos de dados complexos com melhor rendimento e utilizar as CPUs de uma forma melhor. Se você começou a usar o SSIS no SQL Server 2005, talvez tenha introduzido uma transformação Unir Tudo no seu fluxo de dados para artificialmente dividir e paralelizar árvores de execução. Você não deverá mais precisar fazer isso. Devido às melhorias que fizemos no SQL Server 2008, isso não é mais necessário nem recomendado. Remova esses componentes Unir Tudo artificiais, e você perceberá o melhor desempenho. O terceiro grupo, transformações assíncronas de bloqueio, são como versões extremas do grupo anterior – precisam reter TODOS os dados de entrada para que possam gravar dados de saída. O uso dessas transformações em fluxos de dados com grandes lotes de dados costuma tornar o fluxo de dados significativamente lento. Se você as usar em fluxos de dados grandes, verifique se não as utiliza de forma redundante. Se você tiver duas agregações ou duas classificações nos mesmos dados do mesmo fluxo, retrabalhe seu pacote para tentar dar toda a atenção a uma delas. Com as noções básicas abordadas, vamos examinar outras dicas específicas: É muito comum os fluxos de dados precisarem transformar o tipo de dados de uma coluna. Tente fazer isso apenas uma vez, e use os tipos mais reduzidos possíveis para manter os buffers de dados compactos. A conversão de dados também utiliza o tempo de CPU e, se todo o seu fluxo de dados puder usar um único tipo para uma determinada coluna, considere a conversão dessa coluna em sua fonte de dados usando as funções SQL CAST ou CONVERT, ou o equivalente no dialeto SQL dos seus bancos de dados. Já mencionei isso alguns slides atrás, mas você deverá otimizar as transformações refletindo com inteligência sobre aonde elas precisam ir, e não ter medo de tirar proveito do uso do SQL em suas fontes. Por exemplo, se estiver fazendo classificações nos seus dados de origem, considere inseri-las em cláusulas ORDER BY no SQL em sua fonte. Seu banco de dados talvez retorne dados classificados para você com muito mais eficiência do que os fluxos de dados. Salve aqui o componente de classificação para casos em que você precise classificar dados que foram mesclados a partir de várias fontes. Da mesma forma, algumas agregações poderão ser feitas com mais rapidez na origem usando uma função de agregação GROUP BY e SQL. Se estiver usando o SQL Server 2008 e o componente SCD (dimensão de alteração lenta), examine a funcionalidade MERGE no SQL Server. O MERGE pode realizar praticamente o mesmo trabalho que o SCD, mas sem o mesmo número de viagens de ida e volta na rede. Não se esqueça também da utilidade da funcionalidade INSERT INTO do SQL. Se o seu fluxo de dados for simples, e a origem e o destino estiverem na mesma instância do banco de dados, você talvez possa fazer a mesma movimentação de dados com muito mais rapidez por meio de uma única instrução SQL, fazendo com que a movimentação de dados ocorra inteiramente no banco de dados. Nesses casos, a função INSERT INTO poderá ser executada muito mais rápido do que um fluxo de dados, pois os dados nunca precisam sair do servidor. Por último, mas não menos importante, se você estiver fazendo carregamentos incrementais, examine a alternativa de simplesmente recarregar. Alguns sistemas que vi passam muito tempo fazendo a detecção delta para evitar o recarregamento de dados, mas a E/S e a CPU usada para fazer isso acabam tornando o trabalho mais lento no final.
11:59	Vamos passar para a fase de carregamento. Ao carregar no SQL Server, você tem duas opções de alto desempenho: A primeira opção é um componente Destino do SQL Server. Esse componente utiliza memória compartilhada entre o fluxo de dados e o mecanismo de banco de dados para carregar rapidamente os dados, mas só funcionará se o seu fluxo de dados sempre for executado no mesmo computador que o próprio SQL Server. Além disso, o destino do SQL Server tem algumas limitações documentadas com relação à manipulação de erros. Sua outra opção de carregamento rápido no SQL Server é o destino OLE DB, que costuma ser executado quase tão rápido quanto o destino SQL. Para todos esses casos, o uso de um tamanho de confirmação 0 resultará na carga mais rápida. É uma prática muito comum também descartar índices no sistema de destino ao fazer carregamentos grandes, mas você deverá usar algumas diretrizes para informá-lo quando fazer isso. Uma diretriz comum é optar por descartar índices com base no aumento percentual que a carga é prevista de gerar, e nos tipos de índices que você tem na tabela: Se você tiver um único índice clusterizado em sua tabela, não o descarte. Os dados de sua tabela são ordenados por essa chave, e o tempo usado para descarte, inserção e reconstrução praticamente nunca será menor do que o de carregamento com o índice clusterizado no local. Se você tiver um único índice não clusterizado em sua tabela, considere descartá-lo se sua carga representar uma aumento de cerca de cem por cento no tamanho dos dados. Esse é um princípio básico, não um valor exato, mas, de uma forma geral, provavelmente não valerá a pena descartar e reconstruir o índice se você não for dobrar o tamanho da tabela. Se você tiver vários índices em sua tabela, será muito mais difícil criar um princípio básico. Minha tendência é pensar em aumento na faixa de 10%. Por exemplo, as cargas menores do que 10% do volume atual poderão deixar os índices no local. Mas sua melhor opção aqui é experimentar e medir. Se você estiver carregando em uma tabela que tenha outra atividade ocorrendo nela, precisará planejar uma estratégia. Os carregamentos em massa costumam travar a maior parte, se não toda, a tabela de destino. Portanto, você deverá ter certeza de que isso não será problema, ou usar o particionamento. Se você precisar carregar em um banco de dados operacional, poderá configurar uma partição na qual carregar, para que os dados operacionais atuais possam permanecer ativos. Se o seu carregamento estiver lento, verifique qual é a outra atividade que está ocorrendo na tabela ou partição, e assegure-se de que nada mais esteja na disputa. Para obter um excelente guia sobre aumento do desempenho de carregamentos em massa, procure o artigo do SQLCAT intitulado The Data Loading Performance Guide, disponível no MSDN. Além disso, ao fazer recarregamentos, assegure-se de usar TRUNCATE, não DELETE, para limpar dados, para que a exclusão não seja transacional. Os destinos que usam uma conexão de rede também estão sujeitos aos mesmos problemas de rede que descrevi anteriormente. Considere aumentar o tamanho do pacote e habilitar "Quadros Jumbo" em sua rede para obter o desempenho ideal no momento do carregamento.
15:02	Isso conclui a segunda parte desta série sobre desempenho. Um agradecimento especial a Thomas por todas as informações úteis que formaram a base desta série de vídeos, e também a Carla e a Douglas por toda a ajuda na criação da série. Para obter mais informações sobre os tópicos, consulte Top 10 SQL Server Integration Services Best Practices. Obrigado por assistir a este vídeo. Não deixe de assistir às outras partes desta série de vídeos intitulada SSIS: projetando e ajustando o desempenho. Esperamos que ele tenha sido útil e gostaríamos muito de saber sua opinião. Na home page do vídeo, procure o link para avaliar e enviar comentários no canto superior direito.

Ajustando seu fluxo de dados de pacotes SSIS na empresa (vídeo do SQL Server)

Resumo do vídeo

Agradecimentos

Transcrição do vídeo

Consulte também

Outros recursos

Ajuda e informações

Recursos adicionais