Artigo
09/28/2010

Diretrizes para otimizar a importação em massa

Este tópico trata das diretrizes para aperfeiçoamento do desempenho em vários cenários de importação em massa:

Importação de dados de um único cliente (ou fluxo) em uma tabela vazia.

Importação de dados de um único cliente (ou fluxo) em uma tabela parcialmente preenchida, não-vazia.

Observação
A importação de dados em uma tabela não-vazia é conhecida como uma importação em massa incremental. A pergunta fundamental para uma importação em massa incremental é se os índices deveriam ser derrubados anteriormente.

Importação de dados em paralelo com bloqueio de nível de tabela de clientes múltiplos (ou fluxos).
Copiando dados entre instâncias de MicrosoftSQL Server.

Este tópico também provê um resumo de bloqueio e registro de tabela durante operações de importação em massa.

Importando dados de um único cliente (ou fonte) em uma tabela vazia

Quando você importar dados em uma tabela vazia de um único cliente (ou fluxo), o Microsoft recomenda que você faça o seguinte

Especifique o qualificador de TABLOCK (que está disponível como uma dica ou opção em todos os três métodos da importação em massa). Usar TABLOCK causa um bloqueio em nível de tabela efetuado por toda a duração da operação em massa e elimina a sobrecarga do bloqueio de linhas individuais. Para obter mais informações, consulte Controlando o comportamento de bloqueio para a importação em massa.
Minimizar registro. Para obter mais informações, consulte Pré-requisitos para log mínimo em importação em massa.

Manipule índices como se segue.

Quando você estiver usando bcp, BULK INSERT ou INSERT... SELECT * FROM OPENROWSET (BULK...), se a tabela estiver vazia e tiver um índice clusterizado e os dados no arquivo de dados estiverem ordenados para corresponder às colunas fundamentais do índice clusterizado, adicionalmente faça o seguinte:

Importe em massa os dados com o índice clusterizado já existente.
Especifique a dica de ORDER, como também a dica de TABLOCK. Para obter mais informações, consulte Controlando a ordem de classificação durante a importação em massa de dados.

Para uma tabela vazia, essa abordagem é significativamente mais rápida do que criar o índice clusterizado depois de importar os dados, uma vez que ela elimina a etapa da classificação.

Observação
Se uma tabela não-vazia tiver índices, as importações em massa serão completamente registradas, até mesmo no modelo bulk-logged. Para decidir se você remove os índices, considere se o benefício de importação em massa em uma tabela sem índices é maior que o custo de derrubá-los e recriá-los.

Se você aumentar dados de importação em uma tabela vazia com índices e especificar o tamanho do lote, a tabela fica não-vazia depois do primeiro lote. Ao iniciar o segundo lote, os dados são completamente registrados. Para tabelas indexadas vazias, considere executar a importação em massa em um único lote.

Observação
Quando um tamanho de lote não é especificado, por padrão, o otimizador de consulta SQL Server assume que um tamanho de padrão é o tamanho do arquivo de dados. Para melhorar o desempenho, você pode usar o ROWS_PER_BATCH ou qualificador de KILOBYTES_PER_BATCH como uma dica para o otimizador sobre o número aproximado de linhas no arquivo de dados. Para obter mais informações, consulte Gerenciando tamanhos de lote para cópia em massa.

Quando um tamanho de lote não é especificado, por padrão, o otimizador de consulta SQL Server assume que um tamanho de padrão é o tamanho do arquivo de dados. Para melhorar o desempenho, você pode usar o ROWS_PER_BATCH ou qualificador de KILOBYTES_PER_BATCH como uma dica para o otimizador sobre o número aproximado de linhas no arquivo de dados. Para obter mais informações, consulte Gerenciando tamanhos de lote para cópia em massa.

Geralmente, é mais rápido importar em massa em uma tabela sem qualquer índice, do que importar em massa para uma tabela com índices. Então, se uma tabela vazia tiver qualquer índice, você deveria derrubá-los antes de importar dados na tabela e recriá-los depois. Se os dados não estiverem ordenados em uma coluna de chave clusterizada e se a tabela estiver vazia, derrube todos os índices, importe os dados e então crie índices novos.

Importando dados de um único cliente (ou fluxo) em uma tabela não-vazia

Importar dados para uma tabela que já contém dados (uma tabela não-vazia) é conhecido como uma importação em massa incremental. A pergunta fundamental para uma importação em massa incremental é se os índices deveriam ser derrubados anteriormente. Você tem duas opções. Você ou pode manter os índices, ou pode derrubá-los e recriá-los depois.

Quando você importa dados de um único cliente (ou fluxo) em uma tabela não-vazia, manter os índices depende da quantia de dados novos importados relativos à quantia de dados existentes na tabela:

Se você estiver importando uma quantia pequena de dados novos relativos à quantia de dados existentes, cancelar e recriar os índices pode ser contraprodutivo. É provável que o tempo exigido para recriar os índices seja maior do que a hora economizada durante a operação em massa.
Ao contrário, se você estiver importando uma quantia relativamente grande de dados novos, cancelar os índices na tabela antes de executar a operação em massa poderá aumentar o desempenho, sem aumentar substancialmente o tempo exigido para a indexação.

A tabela seguinte relaciona a quantia mínima de dados novos que deveria estar em uma tabela antes de você cancelar índices. A quantia mínima está em proporção com os dados totais na tabela. A quantia difere dependendo dos tipos e combinações de índices. Se os dados novos excederem a porcentagem sugerida para um determinado tipo de índice ou um agrupamento de índices, considere cancelar os índices antes de uma operação em massa e sua recriação posterior. Estes números são sensíveis ao padrão de dados dos dados existentes e dos dados a carregar. Então, números só provêem como uma diretriz geral.

Índices	Quantia relativa de dados novos
Apenas índice clusterizado	30 por cento
Apenas índices clusterizados e um não-clusterizados	25 por cento
Apenas índices clusterizados e dois não-clusterizados	25 por cento
Apenas um único índice não-clusterizado	100 por cento
Dois índices não-clusterizados	60 por cento

Importando dados em paralelo com bloqueio de nível de tabela de clientes múltiplos (ou fluxos)

Se o SQL Server estiver executando em um computador que tem mais de um processador e os dados a serem importados em massa para uma tabela podem ser divididos em arquivos de dados separados, você pode melhorar o desempenho importando os dados para a tabela de clientes múltiplos em paralelo. Ao importar dados em massa de clientes múltiplos para uma tabela, cada cliente deve ter seu próprio arquivo de dados de entrada.

Ao importar dados para uma tabela de clientes múltiplos, considere os seguintes:

Fluxos da importação em massa múltiplos podem bloquear um ao outro potencialmente.

Para impedir isso, o SQL Server provê um bloqueio interno especial, chamado de bloqueio de atualização em massa (BU). Para adquirir bloqueio de atualização em massa, você precisa especificar a opção de TABLOCK com cada fluxo de importação em massa sem bloquear outros fluxos de importação em massa. Isto evita conflitos do acesso de tabela entre os clientes. Porém, um bloqueio de atualização em massa só está disponível em uma tabela (vazia ou não-vazia) que não tem nenhum índice. Se você especificar TABLOCK em uma tabela com índices, a importação em massa paralela não será possível. Para obter mais informações, consulte Controlando o comportamento de bloqueio para a importação em massa.

Se a tabela tiver qualquer índice, você poderá tirar proveito do bloqueio de atualização em massa cancelando todos os índices antes da importação em massa de dados. Você pode então importar em massa dados em paralelo usando TABLOCK e a seguir recriar o índice ou índices. Observe também que se uma tabela não-vazia tiver índices, as importações em massa serão completamente registradas, até mesmo sob o modelo de recuperação bulk-logged. Para decidir se remove os índices, considere se o benefício de importação em massa para uma tabela sem índices é maior que o custo de derrubá-los e recriá-los.

Observação

Se você cancelar índices secundários, considere recriá-los em paralelo criando cada índice secundário de um cliente separado.

Para evitar o cancelamento e recriação de índices, você pode executar uma importação paralela sem especificar a dica de TABLOCK. Neste caso, porém, os fluxos da importação em massa múltiplos podem bloquear um ao outro potencialmente e também otimizações de registro em massa estarão indisponíveis. Para minimizar o bloqueio, você pode especificar um tamanho de lote menor e usar a dica de ORDER para eliminar o passo de classificação durante a operação de importação em massa.
Os dados devem ser divididos em arquivos de entradas múltiplas, um arquivo por cliente. Para usar a CPU de maneira mais eficaz, os arquivos de dados deveriam ser semelhantes em tamanho.

Observação
Se você cancelar índices secundários, considere recriá-los em paralelo criando cada índice secundário de um cliente separado.

Para obter mais informações, consulte Importando dados em paralelo com bloqueio em nível de tabela.

Bloqueio e registro de tabela durante importação em massa

A tabela seguinte resume como, durante uma operação de importação em massa, os tipos de bloqueios são determinados pelo esquema de tabela. A tabela seguinte também indica se a tabela está vazia, se o TABLOCK está definido para a operação, e que tipo de registro ocorrerá se o banco de dados estiver usando o modelo de recuperação de registro em massa.

Observação
Depois do primeiro lote bem-sucedido, a tabela já não estará mais vazia.

Tabela de destino para importação em massa	A tabela está vazia?	O TABLOCK está definido?	Bloqueios	Fazendo registro sob modelos de registro em massa e de recuperação simples
Pilha	Sim	Sim	Guia de atualização em massa	Registro em massa
Pilha	Sim	Não	Guia de IX	Registro completo
Pilha	Não	Sim	Guia de atualização em massa	Registro em massa
Pilha	Não	Não	Guia de IX	Registro completo
Pilha com um índice não-clusterizado	Sim	Sim	SCH-M	Registro em massa
Pilha com um índice não-clusterizado	Sim	Não	Guia de IX	Registro completo
Pilha com um índice não-clusterizado	Não	Sim	SCH-M	Registro em massa de dados Registro completo de índice
Pilha com um índice não-clusterizado	Não	Não	Guia de IX	Registro completo
Índice clusterizado	Sim	Sim	SCH-M	Registro em massa
Índice clusterizado	Sim	Não	Guia de IX	Registro completo
Índice clusterizado	Não	Sim	Guia de X	Registro completo
Índice clusterizado	Não	Não	Guia de IX	Registro completo

Copiando dados entre instâncias de SQL Server

Para cópia em massa de dados de uma instância de SQL Server para outro, use bcp para exportar os dados de tabela para um arquivo de dados. Então use um dos métodos para importar em massa para importar os dados do arquivo para uma tabela. Execute as operações de exportação em massa e importação em massa usando o formato nativo ou o nativo Unicode.

Observação
Para obter mais informações sobre esses formatos, consulteUsando formato nativo para importar ou exportar dados e Usando o formato nativo Unicode para importar ou exportar dados.

Se a tabela de origem tiver um índice clusterizado ou se você pretender importar em massa os dados para uma tabela com um índice clusterizado:

Exporte em massa os dados para fora da tabela de origem usando bcp com a opção query para uma instrução SELECT e usando uma cláusula ORDER BY apropriada para criar um arquivo de dados ordenado. Para obter mais informações, consulte Utilitário bcp.
Quando importar em massa dados para SQL Server. Use o qualificador de ORDER que só é suportado por bcp e BULK INSERT. Para obter mais informações, consulte Controlando a ordem de classificação durante a importação em massa de dados.

Para obter mais informações, consulte Copiando dados entre servidores.