Até onde eu sei, você pode otimizar uma inserção em massa de uma maneira muito semelhante à que você otimizaria uma inserção regular. Normalmente, um plano de consulta para uma inserção simples não é muito informativo, portanto, não se preocupe em não ter o plano. Abordarei algumas maneiras de otimizar uma inserção, mas a maioria delas provavelmente não se aplica à inserção especificada na pergunta. No entanto, eles podem ser úteis se, no futuro, você precisar carregar grandes quantidades de dados.

1. Insira os dados na ordem das chaves de cluster

O SQL Server geralmente classifica os dados antes de inseri-los em uma tabela com um índice clusterizado. Para algumas tabelas e aplicativos, você pode melhorar o desempenho classificando os dados no arquivo simples e informando ao SQL Server que os dados são classificados por meio do ORDERargumento de BULK INSERT:

ORDEM ( { coluna [ ASC | DESC ] } [ ,... n ] )

Especifica como os dados no arquivo de dados são classificados. O desempenho da importação em massa é aprimorado se os dados importados forem classificados de acordo com o índice clusterizado na tabela, se houver.

Como você está usando uma IDENTITYcoluna como chave clusterizada, não precisa se preocupar com isso.

2. Use TABLOCKse possível

Se você tiver a garantia de ter apenas uma sessão inserindo dados em sua tabela, você pode especificar o TABLOCKargumento para BULK INSERT. Isso pode reduzir a contenção de bloqueio e pode levar ao registro mínimo em alguns cenários. No entanto, você está inserindo em uma tabela com um índice clusterizado que já contém dados para que você não obtenha log mínimo sem o sinalizador de rastreamento 610, mencionado posteriormente nesta resposta.

Se TABLOCKnão for possível, porque você não pode alterar o código , nem toda a esperança está perdida. Considere usar sp_table_option:

EXEC [sys].[sp_tableoption]
    @TableNamePattern = N'dbo.BulkLoadTable' ,
    @OptionName = 'table lock on bulk load' , 
    @OptionValue = 'ON'

Outra opção é habilitar o sinalizador de rastreamento 715 .

3. Use um tamanho de lote apropriado

Às vezes, você poderá ajustar as inserções alterando o tamanho do lote.

ROWS_PER_BATCH = rows_per_batch

Indica o número aproximado de linhas de dados no arquivo de dados.

Por padrão, todos os dados no arquivo de dados são enviados ao servidor como uma única transação e o número de linhas no lote é desconhecido para o otimizador de consulta. Se você especificar ROWS_PER_BATCH (com um valor > 0), o servidor usará esse valor para otimizar a operação de importação em massa. O valor especificado para ROWS_PER_BATCH deve ser aproximadamente igual ao número real de linhas. Para obter informações sobre considerações de desempenho, consulte "Comentários", posteriormente neste tópico.

Aqui está a citação de mais tarde no artigo:

Se o número de páginas a serem liberadas em um único lote exceder um limite interno, uma varredura completa do conjunto de buffers poderá ocorrer para identificar quais páginas devem ser liberadas quando o lote for confirmado. Essa verificação completa pode prejudicar o desempenho da importação em massa. Um caso provável de exceder o limite interno ocorre quando um conjunto de buffers grande é combinado com um subsistema de E/S lento. Para evitar estouros de buffer em máquinas grandes, não use a dica TABLOCK (que removerá as otimizações em massa) ou use um tamanho de lote menor (que preserva as otimizações em massa).

Como os computadores variam, recomendamos que você teste vários tamanhos de lote com sua carga de dados para descobrir o que funciona melhor para você.

Pessoalmente, eu apenas inseriria todas as 695 linhas em um único lote. Ajustar o tamanho do lote pode fazer uma grande diferença ao inserir muitos dados.

4. Certifique-se de que você precisa da IDENTITYcoluna

Não sei nada sobre seu modelo de dados ou requisitos, mas não caia na armadilha de adicionar uma IDENTITYcoluna a cada tabela. Aaron Bertrand tem um artigo sobre isso chamado Maus hábitos para chutar: colocar uma coluna IDENTIDADE em cada tabela . Para ser claro, não estou dizendo que você deve remover a IDENTITYcoluna desta tabela. No entanto, se você determinar que a IDENTITYcoluna não é necessária e removê-la, isso pode melhorar o desempenho da inserção.

5. Desabilitar índices ou restrições

Se você estiver carregando uma grande quantidade de dados em uma tabela em comparação com o que já possui, pode ser mais rápido desabilitar índices ou restrições antes do carregamento e habilitá-los após o carregamento. Para grandes quantidades de dados, geralmente é mais ineficiente para o SQL Server criar um índice de uma só vez, em vez de quando os dados são carregados na tabela. Parece que você inseriu 695 linhas em uma tabela com 11.500 linhas, então eu não recomendaria essa técnica.

6. Considere TF 610

O sinalizador de rastreamento 610 permite o registro mínimo em alguns cenários adicionais. Para sua tabela com uma IDENTITYchave clusterizada, você obteria um registro mínimo para quaisquer novas páginas de dados, desde que seu modelo de recuperação fosse simples ou registrado em massa. Acredito que esse recurso não esteja ativado por padrão porque pode prejudicar o desempenho em alguns sistemas. Você precisaria testar cuidadosamente antes de habilitar esse sinalizador de rastreamento. A referência recomendada da Microsoft ainda parece ser The Data Loading Performance Guide

Impacto de E/S do Registro Mínimo sob Sinalizador de Rastreamento 610

Quando você confirma uma transação de carregamento em massa que foi minimamente registrada, todas as páginas carregadas devem ser liberadas para o disco antes que a confirmação seja concluída. Quaisquer páginas liberadas não capturadas por uma operação de ponto de verificação anterior podem criar uma grande quantidade de E/S aleatória. Compare isso com uma operação totalmente registrada, que cria E/S sequencial nas gravações de log e não exige que as páginas carregadas sejam liberadas para o disco no momento da confirmação.

Se o seu cenário de carregamento for pequenas operações de inserção em btrees que não cruzam os limites do ponto de verificação e você tiver um sistema de E/S lento, o uso de log mínimo pode diminuir a velocidade de inserção.

Tanto quanto posso dizer, isso não tem nada a ver com o sinalizador de rastreamento 610, mas com o próprio registro mínimo. Acredito que a citação anterior sobre o ROWS_PER_BATCHajuste estava chegando a esse mesmo conceito.

Em conclusão, provavelmente não há muito que você possa fazer para ajustar seu BULK INSERT. Eu não estaria preocupado com a contagem de leitura que você observou com sua inserção. O SQL Server relatará as leituras sempre que você inserir dados. Considere o seguinte muito simples INSERT:

DROP TABLE IF EXISTS X_TABLE;

CREATE TABLE X_TABLE (
VAL VARCHAR(1000) NOT NULL
);

SET STATISTICS IO, TIME ON;

INSERT INTO X_TABLE WITH (TABLOCK)
SELECT REPLICATE('Z', 1000)
FROM dbo.GetNums(10000); -- generate 10000 rows

Saída de SET STATISTICS IO, TIME ON:

Tabela 'X_TABLE'. Contagem de varredura 0, leituras lógicas 11428

Eu tenho 11.428 leituras relatadas, mas isso não é uma informação acionável. Às vezes, o número de leituras relatadas pode ser reduzido por um registro mínimo, mas é claro que a diferença não pode ser traduzida diretamente em um ganho de desempenho.

Vou começar a responder a essa pergunta, com a intenção de atualizar continuamente essa resposta à medida que construo uma base de conhecimento de truques. Espero que outros se deparem com isso e me ajudem a melhorar meu próprio conhecimento no processo.

1. Gut Check: Seu firewall está fazendo uma inspeção profunda de pacotes com estado? Você não encontrará muito na Internet sobre isso, mas se suas inserções em massa forem cerca de 10 vezes mais lentas do que deveriam, é provável que você tenha um dispositivo de segurança fazendo inspeção profunda de pacotes de nível 3-7 e verificando "Prevenção de injeção de SQL genérica ".

2. Meça o tamanho dos dados que você planeja inserir em massa, em bytes, por lote. E verifique se você está armazenando algum dado LOB, pois essa é uma operação de busca e gravação de página separada.

   Várias razões pelas quais você deve fazer isso dessa maneira:

   uma. Na AWS, as IOPS do Elastic Block Storage são divididas em bytes, não em linhas.

   1. Consulte Desempenho de volume do Amazon EBS em instâncias do Linux » Características e monitoramento de E/S para obter uma explicação sobre o que é uma unidade EBS IOPS
   2. Especificamente, os volumes de SSD de uso geral (gp2) têm o conceito de "créditos de E/S e desempenho de intermitência" e é comum que o processamento ETL pesado esgote os créditos de saldo de intermitência. Sua duração de intermitência é medida em bytes, não em linhas do SQL Server :)

   b. Enquanto a maioria das bibliotecas ou whitepapers testam com base no número de linhas, é realmente o número de páginas que podem ser gravadas para esse assunto e, para calcular isso, você precisa saber quantos bytes por linha e o tamanho da página (geralmente 8 KB , mas sempre verifique se você herdou o sistema de outra pessoa.)

   SELECT *
   FROM 
   sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID(N'YourTable'), NULL, NULL, 'DETAILED')
   

   Preste atenção em avg_record_size_in_bytes e page_count.

   c. Como Paul White explica em https://sqlperformance.com/2019/05/sql-performance/minimal-logging-insert-select-heap , "Para habilitar o log mínimo com INSERT...SELECT, o SQL Server deve esperar mais de 250 linhas com um tamanho total de pelo menos uma extensão (8 páginas)."

3. Se você tiver índices com restrições de verificação ou restrições exclusivas, use SET STATISTICS IO ONand SET STATISTICS TIME ON(ou SQL Server Profiler ou SQL Server Extended Events) para capturar informações como se sua inserção em massa tem alguma operação de leitura. As operações de leitura são devidas ao mecanismo de banco de dados do SQL Server, garantindo que as restrições de integridade sejam aprovadas.

4. Tente criar um banco de dados de teste em que o PRIMARYFILEGROUP esteja montado em uma unidade RAM. Isso deve ser um pouco mais rápido que o SSD, mas também elimina qualquer dúvida sobre se o seu controlador RAID pode estar adicionando sobrecarga. Em 2018, não deveria, mas ao criar várias linhas de base diferenciais como esta, você pode ter uma ideia geral de quanta sobrecarga seu hardware está adicionando.

5. Coloque também o arquivo de origem em uma unidade RAM.

   Colocar o arquivo de origem em uma unidade RAM descartará quaisquer problemas de contenção se você estiver lendo o arquivo de origem da mesma unidade em que o FILEGROUP do servidor de banco de dados está.

6. Verifique se você formatou seu disco rígido usando extensões de 64 KB.

7. Use UserBenchmark.com e compare seu SSD. Isso vai:

   1. Adicione mais conhecimento a outros aficionados de desempenho sobre qual desempenho esperar de um dispositivo
   2. Ajudá-lo a descobrir se o desempenho da sua unidade está com desempenho inferior aos pares com a mesma unidade exata
   3. Ajudá-lo a descobrir se o desempenho da sua unidade está abaixo do desempenho de outras unidades da mesma categoria (SSD, HDD, etc.)

8. Se você estiver chamando "INSERT BULK" de C# por meio de Entity Framework Extensions, certifique-se de "aquecer" o JIT primeiro e "jogar fora" os primeiros resultados.

9. Tente criar contadores de desempenho para o seu programa. Com o .NET, você pode usar o benchmark.NET e ele criará automaticamente o perfil de várias métricas básicas. Você pode então COMPARTILHAR suas tentativas de criação de perfil com a comunidade de código aberto e ver se as pessoas que executam hardware diferente relatam as mesmas métricas (viz. do meu ponto anterior sobre o uso do UserBenchmark.com para comparar).

10. Tente usar pipes nomeados e executá-lo como localhost.

11. Se você estiver direcionando o SQL Server e usando o .NET Core, considere a possibilidade de criar um Linux com SQL Server Std Edition - isso custa menos de um dólar por hora, mesmo para hardware sério. A principal vantagem de tentar o mesmo código com o mesmo hardware com um sistema operacional diferente é verificar se a pilha TCP/IP do kernel do sistema operacional está causando problemas.

12. Use as consultas de diagnóstico do SQL Server de Glen Barry para medir a latência da unidade que armazena o FILEGROUP da tabela do banco de dados.

    uma. Certifique-se de medir antes do teste e após o teste. O "antes do teste" apenas informa se você tem características de E/S horríveis como linha de base.

    b. Para medir "durante o teste", você realmente precisa usar os contadores de desempenho PerfMon.

    Por quê? Porque a maioria dos servidores de banco de dados usa algum tipo de armazenamento conectado à rede (NAS). Na nuvem, na AWS, o Elastic Block Storage é exatamente isso. Você pode estar vinculado ao IOPS de sua solução de volume/NAS EBS.

13. Use alguma ferramenta para medir as estatísticas de espera. Red Gate SQL Monitor , SolarWinds Database Performance Analyzer , ou mesmo consultas de diagnóstico SQL Server de Glen Barry, ou consulta de estatísticas de espera de Paul Randal .

    uma. Os tipos de espera mais comuns provavelmente serão Memory/CPU, WRITELOG, PAGEIOLATCH_EX e ASYNC_NETWORK_IO .

    b. Você pode incorrer em tipos de espera adicionais se estiver executando grupos de disponibilidade.

14. Meça os efeitos de vários INSERT BULKcomandos simultâneos com TABLOCKdesabilitado (TABLOCK provavelmente forçará a serialização de comandos INSERT BULK). Seu gargalo pode estar esperando INSERT BULKa conclusão de um; você deve tentar enfileirar tantas dessas tarefas quanto o modelo de dados físico do seu servidor de banco de dados pode manipular.

15. Considere particionar sua tabela. Como um exemplo específico: se sua tabela de banco de dados for somente anexada, Andrew Novick sugeriu criar um "TODAY" FILEGROUPe particionar em pelo menos dois grupos de arquivos, TODAY e BEFORE_TODAY. Dessa forma, se seus INSERT BULKdados são apenas dados de hoje, você pode filtrar em um campo CreatedOn para forçar todas as inserções a atingir um único FILEGROUPe, assim, reduzir o bloqueio ao usar TABLOCK. Essa técnica é descrita com mais detalhes em um whitepaper da Microsoft: Estratégias de tabela e índice particionadas usando o SQL Server 2008

16. Se você estiver usando índices columnstore, desative TABLOCKe carregue dados em 102.400 linhas Tamanho do lote. Você pode então carregar todos os seus dados em paralelo diretamente em rowgroups columnstore. Esta sugestão (e documentada racional) vem dos índices Columnstore da Microsoft - Orientação de carregamento de dados :

    O carregamento em massa tem estas otimizações de desempenho internas: Carregamentos
    
    paralelos: você pode ter vários carregamentos em massa simultâneos (bcp ou inserção em massa) que estão carregando um arquivo de dados separado. Ao contrário dos carregamentos em massa de rowstore no SQL Server, você não precisa especificar TABLOCKporque cada thread de importação em massa carregará dados exclusivamente em um rowgroups separado (rowgroups compactados ou delta) com bloqueio exclusivo nele. O uso TABLOCKforçará um bloqueio exclusivo na tabela e você não poderá importar dados em paralelo.
    
    Registro mínimo:Um carregamento em massa usa log mínimo em dados que vão diretamente para rowgroups compactados. Todos os dados que vão para um rowgroup delta são totalmente registrados. Isso inclui todos os tamanhos de lote com menos de 102.400 linhas. No entanto, com o carregamento em massa, o objetivo é que a maioria dos dados ignore os rowgroups delta.
    
    Otimização de bloqueio: Ao carregar no rowgroup compactado, o bloqueio X no rowgroup é adquirido. No entanto, ao carregar em massa no grupo de linhas delta, um bloqueio X é adquirido no grupo de linhas, mas o SQL Server ainda bloqueia os bloqueios PAGE/EXTENT porque o bloqueio do grupo de linhas X não faz parte da hierarquia de bloqueio.

17. A partir do SQL Server 2016, não é mais necessário habilitar o sinalizador de rastreamento 610 para log mínimo na tabela indexada . Citando o engenheiro da Microsoft Parikshit Savjani ( grifo meu ):

    Um dos objetivos de design do SQL Server 2016 era melhorar o desempenho e a escalabilidade do mecanismo pronto para uso para torná-lo mais rápido sem a necessidade de botões ou sinalizadores de rastreamento para os clientes. Como parte dessas melhorias, uma das melhorias feitas no código do mecanismo do SQL Server foi ativar o contexto de carregamento em massa (também conhecido como inserções rápidas ou contexto de carregamento rápido) e log mínimo por padrão ao executar operações de carregamento em massa no banco de dados com modelo de recuperação registrado em massa. Se você não estiver familiarizado com o log mínimo, recomendo ler esta postagem de blog de Sunil Agrawal, onde ele explica como o log mínimo funciona no SQL Server. Para que as inserções em massa sejam minimamente registradas, ele ainda precisa atender às condições de pré-requisito que estão documentadas aqui.
    
    Como parte desses aprimoramentos no SQL Server 2016, você não precisa mais habilitar o sinalizador de rastreamento 610 para log mínimo na tabela indexadae se junta a algumas das outras bandeiras de rastreamento (1118, 1117, 1236, 8048) para fazer parte da história. No SQL Server 2016, quando a operação de carregamento em massa faz com que uma nova página seja alocada, todas as linhas que preenchem sequencialmente essa nova página são minimamente registradas se todos os outros pré-requisitos para log mínimo discutidos anteriormente forem atendidos. As linhas inseridas em páginas existentes (sem nova alocação de página) para manter a ordem do índice ainda são totalmente registradas, assim como as linhas que são movidas como resultado de divisões de página durante o carregamento. Também é importante ter ALLOW_PAGE_LOCKS ativado para índices (que é ativado por padrão) para que a operação de registro mínima funcione, pois os bloqueios de página são adquiridos durante a alocação e, portanto, apenas as alocações de página ou extensão são registradas.

18. Se você estiver usando SqlBulkCopy em C# ou EntityFramework.Extensions (que usa SqlBulkCopy nos bastidores), verifique sua configuração de compilação. Você está executando seus testes no modo Release? A arquitetura de destino está definida como Qualquer CPU/x64/x86?

19. Considere usar sp_who2 para ver se a transação INSERT BULK está SUSPENDED. Pode ser SUSPENSO porque está bloqueado por outro spid. Considere a leitura de Como minimizar o bloqueio do SQL Server . Você também pode usar o sp_WhoIsActive de Adam Machanic, mas sp_who2 fornecerá as informações básicas que você precisa.

20. You might just have bad disk I/O. If your doing a bulk insert and your disk utilization is not hitting 100%, and is stuck at around 2%, then you probably have either bad firmware, or defective I/O device. (This happened to a coworker of mine.) Use [SSD UserBenchmark] to compare with others for hardware performance, especially if you can replicate the slowness on your local dev machine. (I put this last in the list because most companies do not allow developers to run databases on their local machine due to IP risk.)

21. If your table uses compression, you can try running multiple sessions, and in each session, start off with using an existing transaction and run this before the SqlBulkCopy command:

    ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=AUTO;

22. For Continuous Loading, one stream of ideas, first outlined in a Microsoft whitepaper, Partitioned Table and Index Strategies Using SQL Server 2008:

    Continuous Loading

    In an OLTP scenario, new data may be coming in continuously. If users are querying the newest partition as well, inserting data continuously may lead to blocking: User queries may block the inserts, and similarly, inserts may block the user queries.

    Contention on the loading table or partition can be reduced by using snapshot isolation—in particular, the READ COMMITTED SNAPSHOT isolation level. Under READ COMMITTED SNAPSHOT isolation, inserts into a table do not cause activity in the tempdb version store, so the tempdb overhead is minimal for inserts, but no shared locks will be taken by user queries on the same partition.

    In other cases, when data is being inserted into a partitioned table continuously at a high rate, you may still be able to stage the data for short periods of time in staging tables and then insert that data into the newest partition repeatedly until the window for the current partition passes and data then is inserted into the next partition. For example, suppose you have two staging tables that receive 30 seconds worth of data each, on an alternate basis: one table for the first half of a minute, the second table for the second half of a minute. An insert stored procedure determines which half of the minute the current insert is in, and then it inserts into the first staging table. When 30 seconds is up, the insert procedure determines it must insert into the second staging table. Another stored procedure then loads the data from the first staging table into the newest partition of the table, and then it truncates the first staging table. After another 30 seconds, the same stored procedure inserts the data from the second stored procedure and puts it into the current partition, and then it truncates the second staging table.

23. Microsoft CAT Team's The Data Loading Performance Guide

24. Make sure your statistics are up to date. Use FULLSCAN if you can after each index build.

25. SAN Performance Tuning with SQLIO and also make sure if you are using mechanical disks that your disk partitions are aligned. See Microsoft's Disk Partition Alignment Best Practices.

26. COLUMNSTORE INSERT/UPDATE performance

The reads are likely to be the unique & FK constraints being checked during insert - you may get an speed improvement if you can disable/drop them during the insert & enable/recreate them afterwards. You'll need to test if this makes it slower overall compared to keeping them active. This also may not be a good idea if other processes are writing to the same table concurrently. - Gareth Lyons

According to the Q & A Foreign keys become untrusted after bulk insert, FK constraints become untrusted after a BULK INSERT with no CHECK_CONSTRAINTS option (my case as I ended with untrusted constraints). It is not clear, but it would not make sense to check them and still make them untrusted. However, PK and UNIQUE will still be checked (see BULK INSERT (Transact-SQL)). - Alexei