Início / dba / Perguntas / 90439
Accepted
nateirvin
Asked: 2015-01-29 10:01:11 +0800 CST

Que efeito a redução do tamanho de uma coluna varchar terá no arquivo de banco de dados?

  • 772

Temos várias tabelas em nosso banco de dados que possuem VARCHAR(MAX)colunas onde a VARCHAR(500)(ou algo muito menor que max) será suficiente. Naturalmente, quero limpá-los e reduzir os tamanhos para níveis mais razoáveis. O 'como' fazer isso eu entendo: minha pergunta é o que a alteração dessas colunas fará nas páginas e existentes no disco? (Há muitas informações por aí sobre o que acontece quando você aumenta uma coluna, mas é difícil encontrar informações sobre o que acontece quando você diminui uma.)

Algumas das tabelas têm uma contagem de linhas muito pequena, então não estou preocupado com o custo da alteração, mas algumas são muito grandes e estou preocupado com a possibilidade de serem reorganizadas e causar muito bloqueio/tempo de inatividade. Em termos práticos, quero apenas uma maneira de estimar uma janela de manutenção. Em geral, gostaria de entender melhor como o mecanismo de banco de dados se comporta neste caso.

Desde já, obrigado!

EDITAR:

Estou examinando 20 tabelas, embora apenas metade delas tenha contagens de linhas superiores a 1.000. O maior tem quase um milhão de linhas. O pior infrator é uma tabela com 350.000 linhas e quatro VARCHAR(MAX)colunas que podem ser reduzidas ao VARCHAR(500)nível.

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Primeiras coisas primeiro: quantos dados existem na tabela? Número de linhas e tamanho da tabela?

    Segundo: você pode fazer backup e restaurar esta tabela para um servidor de teste e executar a instrução alter para ver o impacto (supondo que não seja inviável devido ao fato de a tabela ser muito grande para caber em um sistema que não seja de produção)? Sempre acho que testar em meu ambiente é mais preciso do que conselhos da internet, pois existem vários fatores que podem influenciar o resultado que podem não ser fornecidos na pergunta simplesmente por não saber que esses fatores podem afetar o resultado.

    Terceiro: aumentar o tamanho de um campo de comprimento variável é (supondo que você não exceda o limite de 8060 bytes) uma operação simples de metadados, pois nenhum dado real seria alterado para tal operação. MAS, por outro lado, reduzir o tamanho de um campo de comprimento variável, mesmo para algo que funcionará mais do que obviamente, não é uma simples alteração de metadados porque o SQL Server não sabe, antes de verificar todas as linhas , que o tamanho recém-solicitado é válido.

    Portanto: Sim, isso bloqueará a mesa por um período de tempo . Quanto tempo? Bem, aqui está o teste que acabei de fazer:

    Eu tinha, de alguns outros testes, uma tabela com um único INT NOT NULLcampo e 1 milhão de linhas. Copiei para uma nova tabela com a finalidade de fazer este teste via:

    SELECT *, CONVERT(NVARCHAR(MAX), NEWID()) AS [StringField]
INTO dbo.ResizeTest
FROM dbo.ClusteredUnique;

    Dessa forma, eu estava começando com um cenário semelhante de ter um MAXcampo (acabei de perceber que você tem VARCHARe estou usando NVARCHAR, mas isso não deve alterar o comportamento que estou vendo) que eu poderia mudar para 500. E contém dados que cabem facilmente em 500 caracteres. Isso levou alguns minutos.

    Eu então corri:

    ALTER TABLE dbo.ResizeTest ALTER COLUMN [StringField] NVARCHAR(500) NULL;

    E isso levou pouco mais de 11 minutos.

    Acabei de refazer o teste, desta vez descartando a [ResizeTest]tabela e alterando os dois NVARCHARs para apenas VARCHAR, apenas para ter certeza de que estou comparando maçãs com algo que pelo menos se parece com uma maçã ;-).

    A criação inicial da tabela levou 20 segundos, enquanto a ALTER TABLEde 2 minutos.

    Portanto, em termos de estimativa de tempo de inatividade, isso é realmente difícil de fazer, pois é baseado nas velocidades de E/S do disco, se alguma operação de crescimento automático precisa ou não acontecer no arquivo de dados e/ou no log de transações, etc. é provavelmente uma grande parte do motivo pelo qual meu primeiro teste levou 11 minutos para alterar e o segundo, mesmo com VARCHARmetade do tamanho dos NVARCHARdados, levou apenas 2 minutos (ou seja, os arquivos foram pré-criados naquele ponto). Mesmo assim, você deve ter em mente que meu teste está sendo executado em meu laptop, que não é o disco mais rápido, mas também tinha apenas 1 milhão de linhas de 2 colunas pequenas (22 ou mais bytes por linha).

    E já que você perguntou o que isso fará com as páginas de dados, aqui está sua resposta. Fiz um sp_spaceuseddepois de criar a tabela, depois de fazer o ALTER COLUMNe depois de fazer o ALTER TABLE dbo.ResizeTest REBUILD;. Os resultados (os números a seguir são baseados no segundo teste usando VARCHAR, não no primeiro teste usando NVARCHAR):

    After initial table creation:        526,344 KB
After ALTER COLUMN VARCHAR(500):   1,031,688 KB  <--- !! Yikes!!
After ALTER REBUILD:                 526,472 KB

    Se você está preocupado em precisar manter a operação no menor tempo possível, confira um artigo que escrevi sobre fazer exatamente isso: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! (cadastro gratuito necessário).

    • 12

  2. Pelo que descobri, a execução da instrução alter não deve demorar muito, desde que a mesa não esteja bloqueada por outro processo. De acordo com gbn, é apenas uma alteração de metadados: https://stackoverflow.com/questions/7261909/is-it-bad-to-use-alter-table-to-resize-a-varchar-column-to-a-larger -Tamanho

    Além disso, quanto à forma como é armazenado, parece que o SQL Server armazenou os dados varchar em uma página de 8k até preencher uma página inteira, que neste ponto substitui por um ponteiro e os armazena como um BLOB.

    Estou assumindo que, quando você alterar o comprimento, não truncará nenhum registro. Nesse caso, no máximo, os dados que você está convertendo em varchar(500) devem ter, no máximo, 502 bytes de comprimento e não devem ter um ponteiro.

    Portanto, para encurtar a história, não deve mudar muito, desde que você não trunque nenhum dado.

    • 0

relate perguntas