Início / dba / Perguntas / 132981
Accepted
Ian Kemp
Asked: 2016-03-23 04:13:03 +0800 CST

O tipo de dados da coluna de ida e volta faz com que o tamanho da tabela cresça

  • 772

Tabela LogCount:

Column1 int       not null
Column2 int       not null
Column3 int       not null
[Date]  datetime  not null
[Count] float(53) not null

Esta tabela contém aproximadamente 86 milhões de linhas e possui os seguintes índices:

alter table LogCount add constraint PK_LogCount_Id primary key clustered 
( [Date], Column1, Column2, Column3 )
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

sp_spaceuseddá o seguinte:

name        rows        reserved    data        index_size  unused  
LogCount    85800181    8089216 KB  4226664 KB  3860968 KB  1584 KB

A Countcoluna não armazena e nunca armazenará números de ponto flutuante, apenas inteiros, então mudei para um smallintque (eu esperava) economizará 6 bytes por linha ( float(53)= 8 bytes, smallint= 2 bytes):

drop index LogCount.IX_Column2_Date
go
alter table LogCount alter column [Count] smallint not null
go
create nonclustered index IX_Column2_Date on LogCount ( Column2, [Date] )
include ( [Count] )
go

Então eu recorri sp_spaceused:

name        rows        reserved    data        index_size  unused
LogCount    85800181    7670848 KB  5255528 KB  2414496 KB  824 KB

Como esperado, o tamanho do índice diminuiu drasticamente, mas o tamanho dos dados aumentou em um gigabyte!

Em seguida, executei novamente a instrução drop/alter/create acima, mas usando int(4 bytes) e obtive o seguinte resultado:

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848032 KB  5255528 KB  2591688 KB  816 KB

Então eu tentei float(1)(também 4 bytes):

name        rows        reserved    data        index_size  unused
LogCount    85800181    7848016 KB  5255528 KB  2591672 KB  816 KB

Finalmente voltei ao original float(53):

name        rows        reserved    data        index_size  unused
LogCount    85800181    10680584 KB 7726896 KB  2952464 KB  1224 KB

Em comparação com o original, o tamanho dos dados aumentou em aproximadamente 3,3 GB (quase dobrou), enquanto o tamanho do índice diminuiu em aproximadamente 900 MB.

Um colega sugeriu que o culpado poderia ser que o MSSQL está alocando páginas adicionais para a alter columninstrução e não as liberando depois, então também tentei executar dbcc shrinkdatabaseapós cada etapa, mas os resultados foram os mesmos.

Então minhas perguntas:

  1. Por que alterar uma coluna de um tipo de dados maior para um menor faz com que mais espaço de dados seja usado?
  2. é sp_spaceusedconfiável? Se não, o que devo usar em vez disso? Se não houver uma opção melhor, como determinar se a alteração do tipo de dados de uma coluna terá um efeito positivo ou negativo no uso do espaço em disco?
sql-server sql-server-2014

2 respostas

  1. Best Answer

    Ao alterar o tipo de dados de uma coluna, o SQL Server escolherá:

    • Altere apenas os metadados;
    • Altere os metadados e leia todos os valores existentes para garantir que eles se encaixem; ou
    • Altere cada linha fisicamente

    Mesmo que cada linha deva ser alterada, o SQL Server ainda toma medidas (quando possível) para priorizar a velocidade sobre o tamanho final, com base no fato de que queremos que as alterações DDL sejam concluídas o mais rápido possível. A otimização do espaço de armazenamento pode esperar por uma janela de manutenção.

    Alterar float para smallint pode ser acomodado dentro do espaço existente alocado para a linha, mas deixa algum espaço não utilizado. Como foi mencionado, isso pode ser recuperado por meio da reconstrução total da estrutura alterada.

    • 3

  2. Você precisa executar ALTER TABLE ... REBUILDpara liberar o espaço não utilizado pelas modificações DDL.

    Consulte MSDN para obter detalhes em https://msdn.microsoft.com/en-us/library/ms190273.aspx?f=255&MSPPError=-2147217396

    • 2

relate perguntas