Artigo
04/01/2012

Gerenciando lotes para importar em massa

Esta seção trata da administração de como deve ser a determinação do tamanho de lote deve para uma operação de importação em massa. Por padrão, todas as linhas de um arquivo de dados são importadas como um único lote de tamanho desconhecido em uma única transação. Nesse caso, se a importação falhar antes da conclusão, a transação inteira será revertida e nenhum dado será adicionado à tabela de destino. A operação com falha deve ser reinicializada no início do arquivo de dados.

A importação de um arquivo de dados grande como um lote único pode ser problemática, então bcp e BULK INSERT permitem que você importe dados em uma série de lotes, cada um dos quais é menor que o arquivo de dados. Cada lote é importado e registrado em uma transação separada e depois que uma determinada transação for confirmada, as linhas importadas por essa transação serão confirmadas. Se a operação falhar, só as linhas importadas do lote atual serão revertidas, e você pode retomar a importação de dados começando no início do lote com falha, em vez de começar no início do arquivo de dados.

Observação
Para obter informações sobre o funcionamento dos lotes, consulte Lotes.

Alternativamente, se você escolher não limitar o tamanho do lote, poderá melhorar o desempenho calculando o tamanho do arquivo de dados em seu comando. Sua estimativa é usada pelo processador de consulta na criação do plano de consulta para a operação.

Observação
Quando você especifica o tamanho de lote ou tamanho de arquivo de dados, a exatidão não é crítica.

A tabela a seguir resume os qualificadores que aceitam essas alternativas.

Comando	Tamanho do lote	Linhas enviadas por lote	Kilobytes enviados por lote
bcp1	-bbatch_size	-h "ROWS_PER_BATCH = bb"	- h "KILOBYTES_PER_BATCH = cc"
BULK INSERT2	BATCHSIZE = batch_size	ROWS_PER_BATCH = rows_per_batch	KILOBYTES_PER_BATCH = kilobytes_per_batch Observação Usar esta opção com BATCHSIZE gera um erro.
INSERT ... SELECT * FROM OPENROWSET (BULK...)	—3	ROWS_PER_BATCH = rows_per_batch	—3

1 Em um comando bcp, não use a opção -bbatch_size e a dica ROWS_PER_BATCH ou KILOBYTES_PER_BATCH juntos. Esta combinação causa um erro.

2 Em um comando BULK INSERT, se você usar BATCHSIZE com ROWS_PER_BATCH ou KILOBYTES_PER_BATCH, BATCHSIZE terá precedência.

3 OPENROWSET não tem nem BATCHSIZE nem opções KILOBYTES_PER_BATCH.

As seções a seguir descrevem o uso desses qualificadores.

Especificando o tamanho de lote aproximado

Se você importar um número muito grande de linhas, dividir os dados em lotes poderá oferecer vantagens. Depois que cada lote estiver completo, a transação será registrada. Se, por qualquer motivo, uma operação de importação em massa terminar antes da conclusão, só a transação atual (lote) será revertida.

Observação
O registro em massa inclui uma cópia dos dados importados no log de transações. Isso pode fazer o log crescer rapidamente, mas depois de cada lote, você pode fazer backup do log para reobter o espaço do log.

Para importar o arquivo de dados em uma série de lotes que tenham aproximadamente um tamanho especificado, use o seguinte qualificador:

Para bcp: -b
Para BULK INSERT: BATCHSIZE

Cada lote de linhas é inserido como uma transação separada. Se, por qualquer motivo, uma operação de importação em massa terminar antes da conclusão, só a transação atual (lote) será revertida. Por exemplo, se um arquivo de dados tiver 1000 linhas, e um tamanho de lote de 100 for usado, o Microsoft SQL Server registra a operação como 10 transações separadas; cada transação insere 100 linhas na tabela de destino. Se a operação de importação em massa terminar durante a carga da linha 750, só as 49 linhas anteriores serão removidas quando o SQL Server reverter a transação atual. A tabela de destino ainda contém as primeiras 700 linhas.

O SQL Server otimiza a carga automaticamente, de acordo com o valor de tamanho de lote, o que pode resultar em um melhor desempenho. De maneira geral, o tamanho do lote deve ser estabelecido tão grande quanto prático. Normalmente, quanto o maior o tamanho do lote, melhor é o desempenho da operação de importação em massa. Porém, há algumas exceções. Se um ou mais índices existirem na tabela de destino, então um tamanho de lote maior poderia exercer mais pressão de memória para classificação. Além disso, durante uma carga paralela executada sem usar a opção TABLOCK, um tamanho de lote maior poderia causar mais bloqueios.

Observação
Os tamanhos de lote não são aplicáveis quando você exporta dados em massa de uma instância do SQL Server para um arquivo de dados.

Especificando o tamanho aproximado de um arquivo de dados

Se você não especificar um tamanho de lote para a operação de importação em massa, poderá indicar o tamanho aproximado do arquivo de dados para permitir que o processador de consulta aloque recursos efetivamente no plano de consulta. Para indicar o tamanho aproximado do arquivo de dados, calcule o número de linhas ou o número de kilobytes de dados, desta forma:

Cálculo de linhas por lote

Para calcular o número de linhas, use a dica ou opção ROWS_PER_BATCH. Se você especificar um valor > 0, o processador de consulta usará o valor de ROWS_PER_BATCH como dica para alocar recursos no plano de consulta. Esse valor deve ser da mesma ordem que o número real de linhas.

Embora todas as linhas do arquivo de dados sejam copiadas em uma instância do SQL Server em um lote, bcp exibe a mensagem "1000 linhas enviadas ao SQL Server" após 1000 linhas. Essa mensagem é apenas informativa e sempre é exibida, não importando o tamanho do lote.
Cálculo de kilobytes por lote

Para calcular o tamanho do arquivo de dados em kilobytes, use a dica ou opção KILOBYTES_PER_BATCH. O SQL Server otimiza a operação de importação em massa de acordo com o valor especificado.

Observação
Quando você faz a importação em massa de um arquivo de dados grande sem especificar o tamanho do lote ou alguma otimização mínima de registro, o log de transações pode ficar cheio antes de a operação de importação em massa ser concluída. Para evitar essa situação, aumente o log de transações ou permita ele que cresça automaticamente.