Como acelerar adicionando coluna à tabela grande no Sql Server

Question

Eu quero adicionar uma coluna a uma tabela do Sql Server com cerca de 10 milhões de linhas. Acho que essa consulta terminaria terminando de adicionar a coluna desejada:

alter table T
add mycol bit not null default 0

mas já dura várias horas. Existe algum atalho para obter uma coluna "não padrão nulo 0" inserida em uma tabela grande? Ou isso é inerentemente muito lento?

Este é o Sql Server 2000. Mais tarde tenho que fazer algo semelhante no Sql Server 2008.

    

Answer 1

Dependendo do tamanho da sua linha, tamanho da tabela, índices, etc., eu já vi o SQL Server 2000 trabalhar por várias horas (quatro a cinco dias) antes de FINALMENTE concluir.

A pior coisa que você pode fazer agora é "pânico" e matar a coisa. Deixe-o correr para fora.

No futuro, você pode tentar fazer o que o Farseeker mencionou e criar uma segunda estrutura (vazia) e copiar seus registros dessa maneira.

• Quanto mais longa a linha da tabela, mais tempo levará.
• Quanto mais índices você tiver nessa tabela, mais tempo levará.
• Se você adicionar um valor padrão (o que você fez), levará mais tempo.
• Se você tiver um uso pesado no servidor, levará mais tempo.
• Se você não bloquear esse banco de dados ou colocá-lo no modo de usuário único, levará mais tempo.

Quando tenho que fazer coisas feias como essa, tento fazê-lo à noite ... como 2h quando ninguém está nele (e a manutenção não está sendo executada no servidor).

Boa sorte! : -)

    

Answer 2

Hmm, 10M linhas são muito poucas, mas não estão fora do reino do MSSQL e isso parece muito lento.

Tivemos uma mesa com um enorme tamanho de linha (mal projetado) e mais de 10 milhões de linhas. Quando tivemos que modificar a estrutura, foi def. muito lento, então o que fizemos foi (manter a tabela on-line, e isso é difícil na memória porque foi há muito tempo):

• Criada nova tabela com o sufixo "C" (para conversão) e nova estrutura (ou seja, a mesma que a antiga, mas com nova coluna / índice / etc)
• SELECT * INTO tabela tableC FROM
• sp_rename 'table' 'tableOld'
• sp_rename 'tableC' 'table'

Dessa forma, não importa quanto tempo a conversão demore, pois os dados antigos estão on-line. Isso pode causar problemas com as linhas sendo gravadas na tabela enquanto a conversão ocorre (isso não era um problema para nós, pois os dados eram escritos apenas uma vez por dia, mas consultados milhares de vezes por hora), então você pode querer investigar isso .

    

Answer 3

Você pode tentar realizar cada etapa da operação em um lote separado, por exemplo,

alter table T add mycol bit null
go
update T set mycol = 0
go
alter table T alter column mycol bit not null
go
alter table T add default 0 for mycol
go

As vantagens são:

• Você obtém um feedback melhor sobre o andamento da operação, já que agora são 4 lotes separados, cada um levando aproximadamente 1/4 do tempo.
• Reduz a probabilidade de erros de tempo limite ao executá-lo a partir do código do lado do cliente.
• Descubro que, por vezes, melhora o desempenho.

Você também pode tentar eliminar todos os índices não clusterizados na tabela antes de fazer a alteração e restaurá-los posteriormente. Adicionar uma coluna pode envolver divisões de páginas em grande escala ou outros reajustes de baixo nível, e você poderia fazer sem a sobrecarga de atualizar índices não-clusterizados enquanto isso está acontecendo.

    

Answer 4

Isso vai demorar um pouco. É porque você está adicionando o valor padrão. Isso está fazendo com que o SQL Server atualize todas as linhas em uma única transação. Certifique-se de que ninguém mais esteja usando a tabela, pois isso causará o bloqueio do seu processo.

    

Answer 5

Eu fiz coisas semelhantes em uma tabela com pelo menos 65 milhões de linhas e não demorou muito. Você tem memória suficiente e um desempenho suficiente no sistema de disco

Se você quiser acelerar o processo, você pode remover todos os índices de execução de índice clusterizado e restrições de chave estrangeira antes de alterar a tabela, mas isso deve ser feito quando o sistema não é usado, ou então você pode acabar com inconsistências. dados. Mas, no final, você precisará aplicar as chaves estrangeiras e os índices antes de terminar, mas aliviará a dor do log de transações, pelo menos se for executado em um modelo de recuperação simples. E no SQL Server 2008 você pode construir os índices com ONLINE = on e SORT_IN_TEMPDB = on

Håkan Winther

    

Answer 6

Você não está realmente indo para um atalho como este - não importa o que você faça, o SQL Server terá que fazer algum processamento em todas as linhas da tabela.

Você pode garantir que ele seja executado o mais rápido possível, certificando-se de que seus arquivos e registros de dados estejam em unidades separadas e em outras recomendações usuais.

    

Answer 7

As horas para linhas de 10m são muito longas. Verifique se nada está segurando as fechaduras abertas na mesa.

    

Answer 8

Em um curso de treinamento, tive uma conversa com alguns DBAs do DoD. Eles gerenciam bancos de dados MySQL de 100 TB e mais. Mudanças na tabela são feitas com despejo e carga, mas isso obviamente requer algum tempo de inatividade. Eles também mencionaram que não gostam de fazer isso com bancos de dados acima de 10 TB, devido ao tempo gasto.

Os dados são despejados, eles não especificaram o que fazer, mas eu suponho arquivos SQL. As tabelas são então truncadas e o esquema é alterado conforme necessário. Os dados são então recarregados.

    

Answer 9

Por acaso você tem um número de índices para sua tabela e pode até ser um índice clusterizado em sua tabela T?

Também tive problema ao adicionar uma nova coluna (é uma coluna de identidade). A tabela tinha 9,3 milhões de linhas e tem um índice não agrupado na chave primária.

Por alguma razão, se eliminarmos o índice da tabela T , adicione a coluna e adicione novamente o índice da tabela T. Basicamente, foi 60X mais rápido no SQLServer Standard 2008.

Eu não descobri por que isso se acelerou tanto, espero que alguém possa me dar uma resposta para isso.