Início / dba / Perguntas / 4506
Accepted
sh-beta
Asked: 2011-08-10 10:35:02 +0800 CST

Adicionando colunas a tabelas de produção

  • 772

Qual é a melhor maneira de adicionar colunas a grandes tabelas de produção no SQL Server 2008 R2? De acordo com os livros online da Microsoft:

As alterações especificadas em ALTER TABLE são implementadas imediatamente. Se as alterações exigirem modificações nas linhas da tabela, ALTER TABLE atualizará as linhas. ALTER TABLE adquire um bloqueio de modificação de esquema na tabela para garantir que nenhuma outra conexão faça referência até mesmo aos metadados da tabela durante a alteração, exceto operações de índice online que exigem um bloqueio SCH-M muito curto no final.

(http://msdn.microsoft.com/en-us/library/ms190273.aspx)

Em uma tabela grande com milhões de linhas, isso pode demorar um pouco. Fazer uma interrupção é a única opção? Qual é a melhor maneira de lidar com esse tipo de situação?

sql-server sql-server-2008-r2

4 respostas

  1. Best Answer

    "Depende"

    Se você adicionar uma coluna que não requer a adição de dados às linhas, isso pode ser bastante rápido.

    Por exemplo, adicionar um int ou char requer movimentos físicos de linha. Adicionar um varchar anulável sem padrão não deveria (a menos que o bitmap NULL precise expandir)

    Você precisa experimentá-lo em uma cópia restaurada da produção para obter uma estimativa

    Criar uma nova tabela, copiar, renomear pode demorar mais se você precisar adicionar novamente índices e chaves em uma tabela de bilhões de linhas.

    Eu alterei bilhões de tabelas de linhas que levaram alguns segundos para adicionar uma coluna anulável.

    Eu disse para fazer um backup primeiro?

    • 27

  2. Se a coluna for NULLable, o impacto deve ser insignificante. Se a coluna não pode ser NULL e o valor deve ser definido, então pode ser bem diferente. O que eu faria neste caso é, em vez de adicionar uma restrição não nula e padrão de uma só vez, efetivamente adicionando dados a cada linha:

    • adicione a coluna como NULLable - deve ser rápido na maioria dos casos
    • atualize os valores para o padrão
      • você pode fazer isso em lotes, se necessário
      • você também pode usar isso para aplicar lógica condicional onde algumas linhas podem não obter o padrão
    • adicione as restrições não nulas/padrão
      • isso será mais rápido quando nenhum dos dados for NULL, mas ainda deve ser mensurável

    Concordo com @gbn que você pode testar isso restaurando uma cópia da produção e testando lá ... você terá uma boa ideia do tempo (supondo que o hardware seja um pouco semelhante) e também poderá ver o impacto no log de transações.

    • 21

  3. Você considerou:

    1. Criando uma nova tabela que inclui as mudanças na definição da tabela.
    2. Inserindo na nova definição de tabela selecionando da tabela original.
    3. Renomear a tabela original para _orig e, em seguida, renomear a nova tabela para o nome da tabela original.

    A desvantagem aqui é que você precisa ter espaço suficiente no banco de dados para fazer essa alteração. Você ainda pode exigir um bloqueio de leitura na mesa para evitar leituras sujas.

    No entanto, você minimiza o impacto para os usuários finais se houver uma chance ou necessidade de a tabela original ser acessada simultaneamente. Também deve minimizar as durações de bloqueio.

    • 4

  4. Tenho uma exceção especial que acho que deveria ser mencionada.

    Com o SQL Server 2012 Enterprise e mais recente, adicionar uma nova coluna NOT NULL com uma constante de tempo de execução é uma operação online que é concluída instantaneamente e não depende do número de linhas na tabela.

    Mais informações sobre isso são encontradas no MSDN

    Vou reproduzir a seção importante

    A partir do SQL Server 2012 (11.x) Enterprise Edition, adicionar uma coluna NOT NULL com um valor padrão é uma operação online quando o valor padrão é uma constante de tempo de execução. Isso significa que a operação é concluída quase instantaneamente, apesar do número de linhas na tabela. Porque as linhas existentes na tabela não são atualizadas durante a operação. Em vez disso, o valor padrão é armazenado apenas nos metadados da tabela e o valor é pesquisado, conforme necessário, em consultas que acessam essas linhas.

    • 2

relate perguntas