Ssis moving average

Importando dados do SQL Server usando SSIS - qual opção é mais rápida Por: Daniel Calbimonte Leia comentários (27) Dicas relacionadas: mais desenvolvimento de serviços de integração Este artigo é útil para desenvolvedores SSIS que não sabem quais tarefas são melhores para usar em projetos do SSIS. O principal problema é que no final do desenvolvimento se o desempenho é lento, então você precisará reconstruir o projeto e alterar os componentes. Este artigo mostra várias maneiras de importar dados e mostra quais tipos de componentes melhor desempenho no SSIS. O concurso será entre os seguintes componentes: Tarefas do ODBC Tarefas do ADO NET Tarefa OLEDB Tarefas do SQL Server Destination T-SQL Criei pacotes SSIS diferentes para testar o desempenho. Nesta demonstração usei o SSIS 2017 eo banco de dados Adventureworks 2017. Nesta demonstração vou importar a tabela AdventureWorks2017.Sales. SalesOrderDetail para o banco de dados test2 que está na mesma instância do SQL Server. SalesOrderDetails é a tabela com mais linhas no AdventureWorks2017. Para criar o banco de dados test2 ea tabela de destino dbo. OrderDetails, use este código T-SQL: Teste 1 - Tarefas ODBC O primeiro exemplo usará Origem ODBC e Destino ODBC como mostrado abaixo: Quando executamos o pacote, notamos a média Tempo é de 5 minutos 57 segundos para importar as linhas: Teste 2 - Tarefas ADO NET Como observado, ODBC é muito lento. Vamos tentar outra abordagem. Vamos truncar primeiramente a tabela de destino: vamos tentar tarefas ADO para importar os mesmos dados e verificar se esses componentes são mais rápidos: O tempo médio decorrido no meu teste foi de 11 segundos. Isto é muito melhor. Teste 3 - Tarefas OLEDB Desta vez, vamos importar os mesmos dados usando as Tarefas OLEDB. Novamente vamos truncar a tabela no banco de dados test2 primeiro. O tempo médio decorrido é de 5 segundos. Observe que estou usando a opção de carregamento rápido com a opção de bloqueio de tabela na tarefa de destino OLE DB: Se não usamos a opção de carregamento rápido, o tempo médio decorrido foi de 2 minutos e 21 segundos: OK. A opção de carga rápida realmente melhora o desempenho. Vou voltar para essa configuração. E sobre a fonte OLE DB. Por padrão, estou usando a opção Tabela ou exibição na fonte OLE DB como mostrado abaixo: Permite usar um comando SQL, em vez disso, como mostrado abaixo. O tempo médio decorrido é de 2,85 segundos. Teste 4 - Destino do SQL Server Agora, vamos tentar usar o Destino de SQL como destino em vez de OLE DB Destino: O tempo médio decorrido é de 2,5 segundos. Neste ponto é a melhor opção. Teste 5 - Executar tarefa T-SQL Finalmente, algumas pessoas pensam que a melhor opção é usar a Tarefa Executar T-SQL: Estou usando uma simples instrução inserir para importar dados de uma fonte para outra: O tempo médio decorrido é de 1,8 segundos Finalmente eu tenho dito que se a consulta é executada dentro de um procedimento armazenado é ainda mais rápido: Vamos criar um procedimento armazenado: Depois de criar o procedimento armazenado vamos chamá-lo na Execute T-SQL Tarefa: O tempo médio decorrido é de 2,12 segundos . Os procedimentos armazenados não melhoram o desempenho. Vamos rever a tabela com os resultados: Você pode pensar que o moral da história é usar a Tarefa Executar T-SQL em vez de outras tarefas SSIS. Neste exemplo, estávamos importando dados na mesma instância, mas isso nem sempre será o caso. Portanto, o moral da história é que existem muitas alternativas ao criar um projeto SSIS e temos que estudar cuidadosamente as alternativas em diferentes cenários. Existem excelentes ferramentas do SSIS e nem sempre usamos as melhores opções. Com cada nova versão do SSIS novas tarefas são adicionadas eo desempenho pode ser melhorado com as tarefas existentes. As principais alterações no SSIS para SQL 2008 e 2017 estão relacionadas a melhorias de desempenho. Próximas etapas Se você estiver trabalhando em um projeto SSIS certifique-se de que está usando as melhores tarefas e também verificar se existem outras tarefas SSIS que podem ser usadas em seu projeto. Também certifique-se de que você está seguindo as melhores práticas recomendadas pelos especialistas: Última atualização: 7132017 Grande leitura e análise, mas eu tenho uma advertência para adicionar. Se você precisar mover uma grande quantidade de dados, você precisará cuidar do crescimento do log de transações. Isso não é uma grande preocupação usando o SSIS. Por exemplo, eu precisava mover 1,3 bilhões de linhas (15 colunas) e comecei a usar o TSQL, que rapidamente encheu meus logs. No entanto, usando OLE DB origem e destino (Bulk Inserts) com carga rápida, havia pouco impacto para o arquivo de log. Quinta-feira, setembro 20, 2017 - 9:19:12 AM - vinodhkumar É muito útil. Grande trabalho. Obrigado Segunda-feira, agosto 27, 2017 - 10:54:42 AM - Orlando Colamatteo Concordo com alguns outros que o testbed é um pouco artificial. Se você estiver olhando para mover dados de uma tabela para outra na mesma instância, em seguida, SSIS raramente será uma opção viável. Alguma forma de T-SQL irá quase certamente superar uma operação SSIS. Um cenário mais realista é mover dados entre duas fontes de dados diferentes. É surpising como mal o Destination ODBC executa, especialmente à luz do que a Microsoft publicamente disse que eles vão se afastar de interfaces OLE DB e padronização em ODBC em futuros produtos: No Destino ODBC eu esperava que a Microsoft para implementar o carregamento de Dados através da API de carregamento em massa como fizeram com a opção FastLoad do destino OLE DB. Em uma nota separada sobre o carregamento de dados no MySQL com o SSIS: No passado eu fiz alguns testes de desempenho com o driver OLE DB Cherry City para o MySQL e é horrivelmente lento como ele só insere uma linha de cada vez. Isso não é para mencionar o fato de que ele caiu BIDS regularmente ao desenvolver com ele. Dada a falta de um benefício que eu faria com as ferramentas construídas em SSIS e evitar o aborrecimento de instalar e configurar um driver de terceiros. Se você estiver usando o SSIS 2005, recomendaria usar um componente de script como um destino e emitir batch-inserts contra uma conexão feita usando o driver ODBC do MySQL: msdn. microsoften-uslibraryms135939.aspx Se você estiver usando o SSIS 2008, eu recomendaria usar um destino ADO NET Com o driver ODBC do MySQL. Em meus testes só conseguiu atingir cerca de 240 rowsminute throughput para o MySQL que é bastante decepcionante: msdn. microsoften-uslibrarybb895291 (vsql.105).aspx Se você está usando o SSIS 2017, eu recomendaria usar um destino ODBC com o driver ODBC do MySQL. Em meus testes, ele superou o destino ADO NET por 3 a 1, mas ainda conseguiu apenas cerca de 800 rowsminute throughput, que ainda era bastante decepcionante: msdn. microsoften-uslibraryhh758691 (vsql.110).aspxIntrodução Com o lançamento do SQL Server 2017 Service Pack 1 A tecnologia ColumnStore na memória agora também está disponível nas edições Standard, Web e até Express e LocalDB. Além do benefício de apenas 1 base de código para manter, essa mudança na política também se tornará um espaço de armazenamento de disco claro, devido a sua alta taxa de dados de duplicação e taxas de compressão e, por último mas não menos importante, seu também um desempenho de consulta ad hoc sério Booster A principal diferença entre os sabores SQL é a quantidade de energia e memória da CPU alocada para tarefas como (re) construção do Clustered ColumnStore Index. Por exemplo: com a Edição Standard um único núcleo (máximo de 100 Processadores no processo sqlservr) está sendo usado e a consulta de um CCI acontece com um máximo de 2 CPUs (MAXDOP2), em vez de alavancar todas as CPUs disponíveis na Enterprise Edition. Construindo um Clustered ColumnStore Index (CCI) com SQL Server 2017 Standard Edition: Construindo um CCI com todos os 4 núcleos disponíveis com o SQL Server 2017 Enterprise Edition: Os tempos base para carregar 7.2 GB 60 Milhões de linhas de um único TPCH lineItem arquivos doesnt mostram muito Uma diferença entre os sabores quando Bulk inserir os dados diretamente em uma tabela de heap ou uma tabela com um CCI a diferença tornam-se claros quando comparamos o tempo necessário para construir um CCI em uma tabela de heap ou reconstruir um CCI: Para resumir, o absoluto A forma mais rápida de ter dados disponíveis em uma tabela com um Índice de ColumnStore em Cluster é: carregar em compilação de pilha o CCI posteriormente com SQL 2017 Ent. Ed. Carregamento direto no CCI Para tabelas com um Índice ColumnStore em Cluster já criado, assegure-se de que você flua diretamente em Grupos de Linha Comprimidos para maximizar a taxa de transferência. Para isso, o tamanho do lote Inserir deve ser igual ou maior que 100K Linhas (102400 para ser preciso). Os lotes menores serão gravados em tabelas comprimidas de armazenamento delta primeiro antes de serem movidas para os seus segmentos de Grupo de Linhas compactas finais, o que significa que o SQL Server tem de tocar nos dados duas vezes: Existem várias opções para carregar dados e iremos sobre os mais usados Como o comando Bulk Insert, BCP e SSIS. Vamos ver o que é necessário para obter o melhor desempenho e como monitorar 1) Inserção em massa T-SQL Vamos começar com um comando BULK INSERT: Verificando o Progresso de Carregamento de Dados Para verificar o Número de Linhas que já foram carregadas no CCI, mesmo quando A opção de bloqueio de tabela está sendo usada, consulta um novo dmv chamado sys. dmdbcolumnstorerowgroupphysicalstats: Este DMV também revelará os possíveis estados do grupo de recursos em mais detalhes durante o carregamento. Existem quatro estados possíveis do Grupo de Linhas durante o carregamento de dados. Quando você vê o estado INVISBILE como na imagem abaixo significa que os dados estão sendo compactados em um RowGroup. 1: OPEN160160160160160160160 (RowGroup está aceitando novos registros) 2: CLOSED160160160 (RowGroup está preenchido, mas ainda não compactado pelo processo de movimento de tupla) 3: COMPRESSED160 (RowGroup está em processo de construção a partir de dados no armazenamento delta) RowGroup é preenchido e compactado). 4 TOMBSTONE160 (RowGroup está pronto para ser coleta de lixo e removido) Especificando o tamanho do lote com um valor de 102400 ou superior, você obterá o máximo desempenho e os dados serão transmitidos e diretamente compactados em seu RG final este comportamento aparecerá COMPRIMIDO. Você também pode verificar um DMV que foi introduzido com SQL2017 para verificar o Estado RowGroup, que é o sys. columnstorerowgroups DMV: Resultado do teste Bulk inserir dados em uma tabela com CCI através do comando Bulk Insert pode ser melhorado, adicionando o Batchsize102400 e TABLOCK opções. Isso traz uma melhoria 8 no throughput. 2) BCP. exe O utilitário BCP ainda está sendo usado bastante em muitos ambientes de produção, por isso vale a pena verificar rapidamente: por padrão, o BCP senta 1000 linhas no momento para o SQL Server. O tempo que leva para carregar 7.2GB de dados via BCP: 530 segundos. Or160 113K rowssec O estado RowGroup mostra NVISIBLE o que significa que com as configurações padrão o Delta Store está sendo usado. Para certificar-se de que o comando BCP transmita os dados diretamente para os RGs compactados, você tem que adicionar a opção batchsize b com um valor de pelo menos 102400. Eu executei vários testes com tamanhos de lote maiores: até 1048576, mas o 102400 me deu o melhor resultado. BCP DB. dbo. LINEITEMCCI em F: TPCHlineitem. tbl S. - c - T - tquotquot - b 102400 h tablock O estado RowGroup agora mostra COMPRESSED, o que significa que ignoramos o Delta Store e os fluxos de dados nos RGs compactados: Resultado: o BCP Concluída em 457 segundos ou 133K linhas por segundo ou Durante os testes, observei que as configurações padrão do SSIS 2017 usam tamanhos de buffer de memória que também podem limitar o tamanho do lote para se tornarem menos de 100K Rows. No exemplo abaixo você vê que os dados desembarcados em lojas delta: os estados RG são fechados e os campos deltastorehobtid são preenchidos, o que significa que as lojas delta são alavancadas. Este foi o momento de estender a mão e verificar com meus colegas que felizmente já perceberam isso e uma solução já está lá (veja: Capacidade de Captura Automática de Fluxo de Dados, capacidade de carregar os dados no CCI). Para aproveitar totalmente as capacidades de transmissão em fluxo contínuo do CCI, você precisa aumentar a memória padrão BufferSize amplificador Configurações de MaxRows: Altere esses valores em 10x maiores: 8211 DefaultMaxBufferRows de 10000 para 1024000 e o mais importante: 8211 DefaultBufferSize de 10485760 para 104857600. Observação: a nova configuração AutoAdjustBufferSize deve ser definida como True quando você carregar linhas de dados muito grandes. Altere também os valores para o adaptador de destino: 8211 Linhas por lote: 160 de nenhum em 102400 8211 Máximo tamanho de confirmação Inserir: de 2147483647 para 102400 A paridade de recursos introduzida com o SQL Server 2017 SP1 abre uma nova gama de possibilidades para se beneficiar de Esperançosamente As orientações acima ajudá-lo a máximo fora Bulk Insert, BCP e SSIS desempenho ao carregar dados em um Índice ColumnStore Clustered Qual será a maneira mais rápida absoluta para carregar dados de um flatfile em uma tabela dentro do SQL Server 2017 Muito mudou desde a minha inicial Post sobre este tópico há muitos anos, como a introdução de tabelas otimizadas na memória e índices de tabela Update Update Columnstore. Além disso, a lista de veículos de transporte de dados para escolher está crescendo: além do BCP, o comando T-SQL Bulk Insert, SSIS como ferramenta ETL e PowerShell, há alguns novos adicionados, como PolyBase, R Script Externo ou ADF. Neste post eu vou começar com a verificação de quanto mais rápido o novo durável durável não durável em tabelas de memória estão definindo a linha de base para esses testes Im usando um Azure DS4V2 Standard VM com 8 cores28 GB de RAM e 2 HDD Volumes com cache do host RW ativado. (Ambos Luns fornecem 275 MBsec RW throughput embora a GUI indica um limite de 60MBsec). Eu gerado um único 60 milhões de linhas 7,2 Gigabyte TPCH lineitem arquivo plano como dados para carregar. Como linha de base para usar para comparação, usaremos o tempo que leva para carregar o arquivo em uma tabela Heap: Esse comando regular Bulk Insert é concluído em 7 minutos com uma média de 143K rowssec. Habilitando o banco de dados de teste para tabelas de memória otimizada O (em SQL20172017 Enterprise amp desenvolvimento Edição) introduziu tabelas em memória são projetados para OLTP muito rápido com muitas pequenas transações e alta simultaneidade, que é um tipo completamente diferente de carga de trabalho como inserir em massa, mas apenas Fora de curiositylets dar-lhe uma tentativa Existem 2 tipos de tabelas em memória: tabelas duráveis ​​e não duráveis. Os duráveis ​​persistirão dados no disco, os non-duráveis ​​não. Para habilitar esta opção, temos de fazer algumas tarefas domésticas e atribuir um volume de disco rápido para hospedar esses arquivos. Primeiro, altere o banco de dados para habilitar a opção Contém MEMORYOPTIMIZEDDATA seguida de adicionar um local de arquivo e um grupo de arquivos que conterá as tabelas de memória otimizada: A terceira coisa a fazer é adicionar um pool de memória separado à instância do SQL Server para que ele possa manter todos Os dados serão carregados em tabelas em memória separadas de seu pool de memória padrão: Vinculando um banco de dados a um pool de memória As etapas para definir um pool de memória separado e para ligar um banco de dados a ele são listadas abaixo: SQL Resource Governador. A 4ª e última etapa é ligar o banco de dados de teste ao novo pool de memória com o comando sys. spxtpbinddbresourcepool.160 Para que a ligação se torne efetiva, precisamos retirar o banco de dados off-line e colocá-lo novamente online. Uma vez ligado, podemos alterar dinamicamente a quantidade de memória atribuída ao seu pool através do comando ALTER RESOURCE POOL PoolHk WITH (MAXMEMORYPERCENT 80). Inserção em massa na tabela de memória em durável Agora estamos todos definidos com a opção de memória habilitada, podemos criar uma tabela na memória. Cada tabela otimizada para a memória deve ter pelo menos um índice (um índice Range ou Hash) que são completamente (re-) compostas na memória e nunca são armazenadas no disco. Uma tabela durável deve ter uma chave primária declarada, que poderia então ser suportada pelo índice necessário. Para suportar uma chave primária, adicionei uma coluna ROWID1 de rownumber extra à tabela: Especificar um tamanho de lote de 1 (até 5) Milhões de linhas para o comando de inserção em massa ajuda a persistir dados no disco enquanto a inserção em massa está em andamento Tudo no final) fazendo isso minimiza a pressão de memória no pool de memória PookHK que criamos. A carga de dados na tabela em memória durável é concluída em 5 minutos 28 segundos ou 183K Rowssec. Isso é um bom tempo, mas não muito mais rápido do que o nosso baseline. Olhando para o sys. dmoswaitstats mostra que o waitstat no.1 é IMPPROVIOWAIT que ocorre quando o SQL Server aguarda uma IO de carregamento em massa para concluir. Olhando para o contador de desempenho Bulk Copy Rowssec e Disk Write Bytessec mostra o flushing para picos de disco de 275 MBsec uma vez que um lote entrou (os picos verdes). Esse é o máximo do que o disco pode entregar, mas não explica tudo. Dado o menor ganho, vamos estacionar esta para investigação futura. Monitorando o Pool de Memória Através do sys. dmresourcegovernorresourcepools dmv podemos verificar se a nossa tabela em memória alavanca o pool de memória PoolHK recém-criado: A saída mostra que este é o caso o 7.2GB (algum extra para o Rowid) ficou descompactado carregado na memória PoolHk pool: Se você tentar carregar mais dados do que você tem memória disponível para o pool você receberá uma mensagem adequada como esta: A declaração foi encerrada. Msg 701, nível 17, estado 103, linha 5 Existe memória de sistema insuficiente no conjunto de recursos 8216PookHK para executar esta consulta. Para olhar um nível mais profundo na alocação de espaço de memória em uma base de tabela em memória-In-você pode executar a seguinte consulta (tomada do SQL Server in-memory OLTP interno para SQL Server 2017 documento): Os dados que acabamos carregado são armazenados como um Varheap estrutura com um índice de hash: Até agora tão bom Agora vamos seguir em frente e verificar como encenação em uma tabela não-durável executa Bulk Inserir em Non-Durable na tabela de memória Para tabelas IMND não precisamos de uma chave primária assim nós apenas Adicionar e índice de Hash Non-clustered e ajustar DURABILITY SCHEMAONLY. A inserção em massa Carregamento de dados na tabela não durável é concluída em 3 minutos com um throughput de 335K rowssec (vs 7 minutos) Isso é 2.3x mais rápido, em seguida, inserindo em uma tabela de heap. Tradicionalmente SSIS é a maneira mais rápida de carregar um arquivo rapidamente no SQL Server porque o SSIS irá lidar com todos os dados de pré-processamento para que o mecanismo do SQL Server pode Gastar sua CPU ticks em persistindo os dados para o disco. Será que isso ainda será o caso ao inserir os dados em uma tabela não durável Abaixo um resumo dos testes que corri com o SSIS para esta postagem: a opção Fastparse do SSIS e as configurações defaultBufferMaxRows e DefaultBufferSize são os impulsionadores principais do desempenho. Além disso, o provedor Native OLE DB (SQLOLEDB.1) executa um pouco melhor do que o SQL Native Client (SQLNCLI11.1). Quando você executa o SSIS e o SQL Server lado a lado, aumentar o tamanho do pacote de rede não é necessário.160160 Resultado líquido: um pacote SSIS básico que lê uma fonte de arquivo simples e grava os dados diretamente na tabela Non-Durable via um destino OLE DB Executa semelhante ao comando Bulk Insert em uma tabela IMND: as linhas de 60 Milhões são carregadas em 2minutes 59seconds ou 335K rowssec, idêntico ao comando Bulk insert. SSIS com distribuidor de dados equilibrado Mas wait8230160 as tabelas em memória são projetadas para trabalhar trava trava amp livre assim isso significa que podemos carregar dados também através de vários fluxos Que é fácil de alcançar com SSIS o distribuidor de dados equilibrado trará apenas isso (o BDD Está listado na seção Comum da caixa de ferramentas do SSIS) Adicionar o componente BDD e inserir os dados na mesma tabela não durável com 3 fluxos fornece a melhor taxa de transferência: estamos agora até 526000 Rowssec Olhando para esta linha muito plana, com apenas 160 do tempo de CPU usado pelo SQLServer, parece que estamos atingindo algum gargalo: Eu rapidamente tentei ser criativo, alavancando a função módulo e adicionado mais 2 fluxos de dados dentro do pacote (cada um processando 13 dos dados) 160, mas isso não está melhorando Muito (1 min52sec), portanto, um ótimo tópico para investigar para um futuro post160160 A opção de tabela na memória não duráveis ​​traz algumas melhorias de desempenho graves para estadiamento de dados de carregamento de dados 1.5x mais rápido com um regular Bulk inser T e até 3.6x vezes mais rápido com SSIS. Esta opção, principalmente projetada para acelerar OLTP, também pode fazer uma enorme diferença para encolher sua janela de lote rapidamente (Para ser continuado) Leitura o mais rápido possível a partir de uma tabela com o SSIS (Parte II) Recentemente eu bloguei sobre como ser tão seletivo Como possível durante a leitura de e fonte de dados OLE DB (parte I) e como carregar os dados de um único arquivo plano o mais rápido possível em uma tabela do SQL Server. Mas você provavelmente notou que Out of the box SSIS lê dados mais rápido de um arquivo simples do que de uma tabela SQL. Neste artigo vou compartilhar meu truque mais recente sobre como acelerar a leitura de uma única tabela, normalmente pelo menos duas vezes mais rápido Tempo para bater, a velocidade da fonte Native OLE DB O primeiro passo, como sempre, é verificar a taxa de transferência padrão E duração contando linhas da fonte de dados Native OLE DB. No nosso caso demora 14 minutos e 6 segundos para ler todos os aprox. 180 milhões de linhas ou 12,89 GB a partir de uma tabela do SQL Server com 16 colunas de dados. Para comparação ler os mesmos dados de um único arquivo plano demora apenas 7 minutos e 57 segundos. Então, onde é essa diferença que vem de primariamente por causa do IO Packet tamanho thats sendo usado eu diria. SSIS lê com blocos de 128 KB de um arquivo simples e usa por padrão 4KB (o tamanho de pacote de rede padrão) que é alterável em max. 32 KB para solicitar dados do SQL Server (32KB também é o máximo para a nova versão do SQL2008 R2). Para recuperar os dados mais rapidamente, temos de encontrar uma maneira de lidar com mais pedidos de E / S em paralelo Para definir a linha de base basta criar um pacote e contar as linhas da fonte OLEDB nativo. (Não se esqueça de alterar o tamanho do pacote de 0 para 32767 no Gerenciador de conexões). Os contadores do Monitor de desempenho do Windows para verificar são a carga da CPU do processo SSIS (DtsDebughost ao executar o pacote de BIDS ou DTEXEC quando iniciado a partir da linha de comando) eo processo do SQL Server. Além disso, verifique a quantidade de bytes que estamos lendo: selecione o contador IO Read bytessec do processo.160 Adicionando paralelismo usando o algoritmo Modulo O truque de otimização que eu gostaria de chamar a atenção é baseado no uso do operador aritmético Modulo que pode ser Usado com o tipo de dados numérico. Retorna o restante de um número dividido por outro. Por exemplo, se você quiser ler com 3 fluxos paralelos use use modulo 3 (3). Você pode pegar os 3 fluxos de saída especificando como os valores restantes 0,1 e 2,160 (Usar valores menores que 0 ou maiores que 2 retornará 0 linhas quando o módulo 3 for usado.) Você pode verificar a saída também executando a consulta No SSMS ou com a opção BIDS Preview do OLEDB Source Editor. Selecione do dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 2 Quando você constrói esta consulta em um pacote SSIS, você vai notar que inicialmente cria paralelismo as linhas são bastante agradáveis ​​dividido e lido a partir das duas fontes de dados, Mas, infelizmente, o tempo total de execução do pacote não descer , Que é um pouco estranho.160 (Afinal, 160 adicionar mais fontes é um truque de otimização que descobrimos há muito tempo atrás, em 2004, ao testar uma versão Beta 2 do SSIS). Especifique no seu pacote as múltiplas fontes de dados e mescle o resultado com o componente União Todos: O Monitor de Atividade mostra-nos rapidamente que, de fato, vários SPIDS são disparados pelo pacote SSIS, mas muitos são Suspensos e realmente causam sincronização desnecessária: Felizmente isso pode ser resolvido rapidamente Especificando a dica de consulta OPTION (MAXDOP 1). Isso remove a sobrecarga de sincronização sem sacrificar a taxa de transferência neste caso. Lendo várias vezes da mesma tabela SQL Tempo para colocá-lo para o teste e aumentar o número de componentes de origem de dados no pacote. Cada fonte de dados OLE DB deve apontar para a mesma tabela de entrada, 160 apenas modificar o fator modulo eo separador de saída ao adicionar mais. O melhor resultado que eu normalmente obter ao ler a mesma fonte de dados 3 ou 4 vezes em paralelo: Quando verificamos os contadores perfmon mais uma vez, você verá o IO throughput realmente subiu de inicialmente 21. MBsec para 46,6 MBsec em média, por isso Ele tem mais do que duplicado O consumo de CPU do processo SSIS e SQLServer também aumentaram 160 SSIS de 100 para 350 (3,5 CPU) e SQLServer está usando também uma CPU extra. A duração total da execução do pacote para ler as mesmas 180 milhões de linhas (12,89 GB) 160 diminuiu de inicialmente 14 minutos 6 segundos para apenas 6 minutos e 33 segundos ao ler os dados com 3 fluxos paralelos Quando você tem que acelerar a leitura de grandes quantidades de dados A partir de uma única tabela no seu pacote SSIS, você pode diminuir a duração em mais da metade, adicionando alguma forma de paralelismo em seu pacote usando este truque Módulo 160. Normalmente, ler dados160 da mesma tabela do SQL Server com 3 ou 4 fluxos paralelos fornece o melhor resultado160 Leitura o mais rápido possível de uma tabela com SSIS (Parte II). 5.0 de 5 com base em 22 avaliações 2 Rob Volk 2018-5-26 03:27 Você pode explicar por que você usa esta cláusula WHERE: WHERE (CAST (LORDERKEY AS VARCHAR) 3) 2 Lançar a coluna para varchar é inútil se você quiser Para fazer uma operação de módulo sobre ele, uma vez que terá que fazer um elenco implícito (voltar) para um tipo numérico. Ele pode não afetar as operações do SSIS, mas provavelmente diminuirá a parte do SQL Server. Henk 2018-5-26 10:31 Olá Rob, a explicação é o título deste post, 8220para ler o mais rápido possível8221 -) usando o elenco para o varchar torna um pouco mais rápido, usando 10 recursos extras da CPU para o SQLServer processo. Mas você está certo, é um pouco confuso remover o elenco da consulta também irá fazer truque: SELECT de dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 0 opção (maxdop 1) 3 Henk 2018-11-8 21:24 se você receber apenas 13 Dos dados. Pode ser que você tenha esquecido de definir a função do módulo correto em cada uma das árvores. Como: selecione a partir de dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 0 selecione de dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 1 selecione de dbo. LINEITEMHash96KeySSD WHERE (LORDERKEY 3) 2 (Você diz que você está processando 50 linhas de moinho de um staging db em 6 Mins 138K rowssec com um componente de rowcount SSIS você pode verificar se o número total de linhas coincidir com o número de linhas em seu banco de dados de estágio. Ele também irá dizer o quão rápido você pode ler a partir de sua fonte de dados e MBytesec eficaz. Talvez there8217s mais espaço Para otimizações). 4 Dan 2017-2-26 13:08 I8217m novo paralelismo tão firgive me se a minha pergunta é elementar. Mas estou confuso sobre como a dica de consulta OPTION (MAXDOP 1) faria com que os spids para todos os executar ao mesmo tempo. Eu li que este 8220 suprime a geração de planos paralelos. A operação será executada serially8221 (Encontrado em stackoverflowquestions163917optionmaxdop-1-in-sql-server). Isso me faz pensar que OPTION (MAXDOP 1) só permite uma CPU. Wouldn8217t é melhor usar todas as possíveis CPU8217s Henk 2017-2-26 22:38 o truque é que você iniciar várias sessões separadas para o servidor sql, cada sessão está servindo uma porção dos dados, and8230 cada sessão (SPID) é De fato tratada por apenas 1 CPU (scheduler SQL), suprimindo o paralelismo dentro de cada sessão que você ganha desempenho (como mostrado na imagem, caso contrário, os SPIDS têm primairly status 8220suspended8221, por isso não fazer nada usefull) No entanto, quando você lê de uma tabela particionada, Que é uma operação multithreaded por natureza, você verá que usando um número maxdop maior pode aumentar throughput. (Leia mais sobre este tópico em: henkvandervalkoptimizing-sql-in-memory-table-scan-processar-velocidade.) Leitura de uma tabela particionada usa 88 cores-)) Espero que isso ajude Brgds, Henk 5 Dan 2017-3-1 20 : 37 Assim, o (MAXDOP 1) pára a CPU de trabalhar com outro processo ao mesmo tempo e se concentrar na consulta atual Henk 2017-3-2 10:38 Dan, você não pode saltar para essa conclusão: com MAXDOP 1 a consulta Será tratado por um único SQL Scheduler que usa uma única CPU. No entanto, se houver várias consultas a serem veiculadas, elas também serão agendadas. 6 Dan 2017-3-4 21:44 Henk, eu tenho um pkg ssis onde eu tenho 29 tabelas I8217m querendo recarregar a maneira you8217ve descrito acima. I8217m adicionando as árvores uma árvore de cada vez e observando o preformance. A primeira árvore correu por si mesmo bem, mas quando I8217ve adicionado outro há 6 ids sessão, mas apenas 3 estão em execução, 1 diz suspenso e os outros 2 não têm nada no status. Eu definir a propriedade pkg MaxConcurrentExecutables 3 (só tem 2 DataFlows no momento) e ter definido cada dataflow8217s EngineThread 3 para lidar com os 3 dados pipelines por fluxo de dados. I8217m executando isso em um trabalho em uma caixa do SQL Server 2005 com 8 3.2 Gig processadores e 64 Gig de Ram (que I8217ve nunca visto ir acima de 50, talvez a configuração de memória de memória alocada para SQL Server precisa ser ajustada). Olhando para o monitor de atividade meu tempo de processador praticamente fica na faixa de 30 a 50 que me leva louco pensando que there8217s muito tempo de processador disponível, mas minhas sessões estão em estado suspenso. Qualquer idéia que I8217m está fazendo errado 7 Steve 2017-4-2 19:06 Henk, grandes sugestões, particularmente como o operador de módulo para dividir em exatamente quantos córregos paralelos quer usar. Eu tenho usado um contêiner de seqüência com várias fontes de dados dentro dele para tê-lo executar segmentos separados para cargas paralelas (de origem remota do Oracle) em vez de seu método acima com a união tudo e maxdop. Você sabe se há alguma desvantagem em usar esse método (faz com que cxpacket sync aguarda) Parece funcionar corretamente, e I8217ll tentar executar alguns testes, mas apenas no caso de você tentou isso e sabia. Henk 2017-4-2 22:35 se você estiver lendo de tabelas diferentes usando várias fontes de dados é perfeitamente bem. Pelo que eu vi nos sites de clientes, normalmente a leitura de tabelas do oracle é um pouco mais lenta do que a escrita real em uma tabela SQLServer, você pode verificar isso usando o truque do componente Rowcount para verificar quão rápido você pode realmente ler com apenas os datasources. (Você está usando separável downloadable Attunity Microsoft Connectors para Oracle a partir do Centro de Download da Microsoft) Se necessário, você pode escrever os dados em vários partitionstables do SQL Server usando o truque modulo. (Por favor don8217t esqueça também verificar com Taskmanager os números de throughput rede :-)) Deixe-me saber seus resultados boa sorte Henk 8 bender 2017-10-26 20:22 9 Nik - Shahriar Nikkhah 2017-7-31 19:14 Olá Henk Você acha que o 8220OFFSET e FETCH8221 vai ajudar neste exemplo eu assumo que vai, basicamente, wil desempenhar o papel de 8220WHERE (LORDERKEY 3) 28221. Eu não testei ainda. Eu fiz o que a otimização e carregou um arquivo CSV de 10 GB em 7m51s em um Fast Track. O que você acha Henk 2017-4-12 19:28 10GB 471 Segundos 21.7 MBsec isso parece uma única tarefa Bulk Insert somente É este tempo de processamento aceitável para você Se não, você já tentou algumas opções para criar paralelismo (com SSIS e O modulo truque, ou carregar vários arquivos menores em paralelo) Brgds, Henk Isso funciona muito bem. Tão feliz por ter encontrado esta página. Eu tenho usado MAXDOP 1 em todos os meus grandes puxa tabela por anos. Recently, I started a job where there are quite a few legacy DTS packages that do massive table to table transfers for ETL processes. Splitting the tables using the 3(0-2) worked great. I am even tempted to split them out more to see how many times I can split my source before my netapp bricks itself. 12 Kamil 2017-4-30 08:51 I run some tests of data extraction with the method presented here. I checked it on smaller table 1.5 GB (12 mln rows) on my virtual machine with 2 procesors. The ssis package consists only from ole db data sources, 1 union all and 1 multicast componets. In properties MaximumErrorCount set to 4 (I have only 2 processors) and EngineThreads to default 10. In standard scenario with only 1 data source the ssis pulled data in 30sec. I got the best result with only 2 pararell data sources with time 25sec. I can8217t go to 50 of performance improvment. Have I missed something or this solution works great only for realy big tables Henk 2017-5-8 10:45 Hi Kamil, (1.5 GB 25 seconds 60 MBsec this is close to the limit of a 1Gbit network connection are you using iSCSI for your VM8217s) Just doublecheck the maximum source speed by reading from the source table with the modulo option (2 for 2 cpu8217s) and maxdop 1 with the rowcount as destination (as described in my blog). this will tell you how fast you can read. If you can read much faster than 60 MBsec than this means you have to optimize some other portions of the package 13 GowriShankar 2017-10-27 14:34 Hi Henk, Any idea for non numeric key fields where modulo is not applicable. Henk 2017-10-29 09:54 Hi Gowri, that is indeed a tricky one. would you have the option to add an extra column (Either as part of your source data or generate one on the fly in SSIS in the second please also add doublecheck the total number of rows read..)