Estou planejando a migração ao vivo de um banco de dados de 2 TB para tabelas particionadas. O sistema é basicamente um armazenamento de documentos, sendo a maior parte do espaço alocado para LOBs entre 50kb e 500kb, com uma pequena porcentagem na faixa de 500kb a 1MB. Parte da migração envolverá dados BCPing do banco de dados antigo para o novo.
O BCP é a abordagem preferida, pois a divisão atual/histórica nos dados permite extrair os dados mais antigos em estágios (durante períodos mais silenciosos) antes de uma troca final, minimizando o impacto no sistema ativo. O volume de dados e a disponibilidade de armazenamento impedem uma reconstrução in-situ para um esquema de partição .
Suspeito que haja alguns ganhos de desempenho ao experimentar KILOBYTES_PER_BATCH em vez de ROWS_PER_BATCH, devido ao conteúdo BLOB. É sugerido na documentação do BCP que o SQL pode otimizar as operações com base nesse valor.
O que não consigo encontrar é nenhuma orientação sobre a natureza dessas otimizações ou por onde começar meus testes. Na ausência de sugestões, tentarei corridas curtas nos limites de 4/8/16/32/64 MB para começar.
Provavelmente, alguns ganhos são decorrentes da alteração do tamanho do pacote (parâmetro BCP -a, em vez da configuração do nível do servidor), mas estou inclinado a aumentar isso para o máximo de 65535, a menos que alguém tenha uma abordagem mais estereotipada.
Esta não é uma resposta direta à sua pergunta, mas existem alguns artigos que você se beneficiaria em lê-los (caso não os tenha encontrado primeiro :-)). Eles são sobre o carregamento de muitos dados usando bcp/cópia em massa. Eu li todos eles e não encontrei nada detalhado sobre KILOBYTES_PER_BATCH, todos eles estão usando ROWS_PER_BATCH, mas tenho certeza que você encontrará outras informações úteis.
Carregue 1 TB em menos de 1 hora (da equipe SQL CAT) - lista de conselhos daqui (citação):
Execute tantos processos de carga quantas CPUs disponíveis. Se você tiver 32 CPUs, execute 32 carregamentos paralelos. Se você tiver 8 CPUs, execute 8 carregamentos paralelos.
Se você tiver controle sobre a criação de seus arquivos de entrada, crie-os com um tamanho que seja divisível uniformemente pelo número de encadeamentos de carregamento que deseja executar em paralelo. Certifique-se também de que todos os registros pertençam a uma partição se desejar usar a estratégia de troca de partição.
Use BULK insert em vez de BCP se estiver executando o processo na máquina do SQL Server.
Use o particionamento de tabela para ganhar outros 8-10%, mas somente se seus arquivos de entrada forem GARANTIDOS para corresponder à sua função de particionamento, o que significa que todos os registros em um arquivo devem estar na mesma partição.
Use TABLOCK para evitar bloqueio de linha por vez.
Use ROWS PER BATCH = 2500 ou algo próximo disso se estiver importando vários fluxos para uma tabela.
As 10 melhores práticas para construir um data warehouse relacional de grande escala (da equipe SQL CAT) - conselhos (citação):
Use o modelo de recuperação SIMPLE ou BULK LOGGED durante o carregamento de dados inicial.
Crie a tabela de fatos particionada com o índice Clustered.
Crie tabelas de preparação não indexadas para cada partição e separe os arquivos de dados de origem para preencher cada partição.
Preencha as tabelas de preparação em paralelo (use várias tarefas BULK INSERT, BCP ou SSIS)
Crie um índice clusterizado em cada tabela de preparação e, em seguida, crie as restrições CHECK apropriadas.
TROQUE todas as partições na tabela particionada.
Crie índices não clusterizados na tabela particionada.
O Guia de desempenho de carregamento de dados (da equipe SQL CAT)
Carregando dados em massa em uma tabela particionada - Artigo de práticas recomendadas do SQL Server (artigo da Technet)
Estudo de caso de carregamento em massa incremental do SQL Server 2000 (artigo da Technet)
Lições aprendidas e descobertas de um grande POC Fast-Track (da equipe SQL CAT)
Dicas de ajuste de desempenho para SQL Server BCP (por Brad McGehee)
Impacto no desempenho: encontrando o tamanho de lote ideal (por Linchi Shea)
e as referências óbvias do MSDN:
Em minha experiência pessoal, consegui fazer um carregamento rápido de dados usando carga paralela e testando com vários tamanhos de lote. Eu acho que apenas testes pessoais serão adequados para você. Espero que você encontre alguns bons conselhos nas referências.