reconstruir no índice clusterizado, por que o tamanho dos dados diminui?

Quando uma tabela tem um índice clusterizado, o índice são os dados da tabela (caso contrário, você tem uma tabela do tipo heap). Uma reconstrução do índice clusterizado (na verdade, qualquer índice, mas o espaço não seria contado como "dados" para um índice não clusterizado) resultará na mesclagem de páginas parcialmente usadas em um formato mais completo.

À medida que você insere dados em um índice (agrupado ou não), na ordem do índice, as páginas de folha são criadas conforme necessário e você sempre terá apenas uma página parcial: aquela no final. Conforme você insere dados fora da ordem do índice, uma página precisa ser dividida para que os dados caibam no lugar certo: você acaba com duas páginas que estão aproximadamente meio cheias e a nova linha vai para uma delas. Com o tempo, isso pode acontecer muito, consumindo uma boa quantidade de espaço extra, embora até certo ponto as inserções futuras preencham algumas das lacunas. As páginas que não são folhas também terão um efeito semelhante, mas as páginas de dados reais são muito mais significativas em tamanho do que são.

Além disso, exclusões podem resultar em páginas parciais. Se você remover todas as linhas em uma página, ela será contada como "não utilizada", mas se houver uma ou mais linhas de dados restantes, ainda será contada como em uso. Mesmo que haja apenas uma linha usando 10 bytes em uma página, essa página conta como 8192 bytes na contagem de espaço usado. Mais uma vez, inserções futuras podem preencher parte da lacuna.

Para linhas de comprimento variável, as atualizações também podem ter o mesmo efeito: à medida que uma linha fica menor, ela pode deixar espaço em sua página que não é fácil de reutilizar mais tarde e, se uma linha em uma página quase inteira crescer, pode forçar uma divisão de página .

O SQL Server não gasta tempo tentando normalizar os dados reorganizando como as páginas são usadas, até que seja explicitamente informado, como a ordem de reconstrução do índice, pois esses exercícios de coleta de lixo podem ser um pesadelo de desempenho.

Suspeito que seja isso que você está vendo, embora eu diga que ter espaço suficiente alocado para aproximadamente 2,7 vezes a quantidade de dados absolutamente necessária é um caso particularmente ruim. Isso pode significar que você tem algo aleatório como uma das chaves significativas no índice (talvez uma coluna UUID), o que significa que é improvável que novas linhas sejam adicionadas na ordem do índice e/ou que um número significativo de exclusões ocorreu recentemente.

Exemplo de divisão de página

Inserindo em ordem de índice com linhas de tamanho fixo, das quais quatro cabem em uma página:

Start with one empty page: 
        [__|__|__|__]
Add the first item in index order:
        [00|__|__|__]
Add the next three
        [00|02|04|06]
Adding the next will result in a new page:
        [00|02|04|06] [08|__|__|__]
And so on...
        [00|02|04|06] [08|10|12|14] [16|18|__|__]

Agora, para adicionar linhas fora da ordem do índice (é por isso que usei números pares apenas acima): Adicionar 11significaria estender a segunda página (não é possível, pois são de tamanho fixo), mover tudo acima de 11 para cima (muito caro em um índice grande) ou dividindo a página assim:

[00|02|04|06] [08|10|11|__] [12|14|__|__] [16|18|__|__]

A partir daqui, adicionar 13e 17não resultará em uma divisão, pois atualmente há espaço nas páginas relevantes:

[00|02|04|06] [08|10|11|__] [12|13|14|__] [16|17|18|__]

mas adicionar 03 irá:

[00|02|03|__] [04|06|__|__] [08|10|11|__] [12|13|14|__] [16|17|18|__]

Como você pode ver, após essas operações de inserção, temos atualmente 5 páginas de dados alocadas que podem caber em um total de 20 linhas, mas temos apenas 14 linhas lá ("desperdiçando" 30% do espaço).

Uma reconstrução com opções padrão (veja abaixo sobre "fator de preenchimento") resultaria em:

[00|02|03|04] [06|08|10|11] [12|13|14|16] [17|18|__|__]

salvando uma página neste exemplo simples. É fácil ver como as exclusões podem ter um efeito semelhante às inserções fora da ordem do índice.

Mitigação

Se você espera que os dados venham em uma ordem bastante aleatória em relação à ordem do índice, você pode usar a FILLFACTORopção ao criar ou reconstruir um índice para instruir o SQL Server a deixar lacunas artificialmente para preencher posteriormente - reduzindo as divisões de página a longo prazo, mas ocupando mais espaço inicialmente. É claro que errar esse valor pode piorar as coisas em vez de melhorar a situação, portanto, manuseie com cuidado.

A divisão de página, particularmente no índice clusterizado, pode ter uma implicação de desempenho para inserções/atualizações, portanto FILLFACTOR, às vezes é ajustada por esse motivo, em vez do problema de uso de espaço em bancos de dados que veem muita atividade de gravação (mas para a maioria dos aplicativos, onde as leituras superam as gravações por várias ordens de magnitude, geralmente é melhor deixar o fator de preenchimento em 100%, exceto em casos específicos, como quando você tem índices sobre colunas com conteúdo efetivamente aleatório).

Presumo que outros bancos de dados de grande nome tenham uma opção semelhante, se você também precisar desse nível de controle.

Atualizar

Em relação à ALTER INDEXdeclaração adicionada à pergunta depois que comecei a digitar o acima: presumo que as opções sejam as mesmas de quando o índice foi criado pela primeira vez (ou reconstruído pela última vez), mas, caso contrário, a opção de compactação pode ser muito significativa se for adicionada esta tempo ao redor. Também nessa instrução, o fator de preenchimento é definido como 85% e não 100%, portanto, cada página folha ficará ~ 15% vazia imediatamente após a reconstrução.

Quando você reconstrói um índice, ele literalmente coloca todos os dados em novas páginas. O que eu suspeito que aconteceu é que você removeu muitos dados antes da reconstrução, por exemplo, removeu uma coluna, atualizou uma coluna de largura variável para ter menos dados, alterou o tamanho de uma coluna de largura fixa ou excluiu muitas linhas. Qualquer uma dessas operações poderia deixar muito espaço vazio nas páginas, que não seriam recuperados até a reconstrução. A coluna "dados" sp_spaceusednão está medindo os dados reais, mas o número de páginas de 8K usadas para armazenar os dados. Essas páginas agora estão mais cheias por causa da reconstrução, então a mesma quantidade de dados cabe em um número menor de páginas.

O sp_spaceusedprocedimento armazenado não está examinando o tamanho cumulativo total das linhas no banco de dados. Ele está relatando o tamanho do espaço alocado para manter esses dados no tamanho cumulativo das extensões alocadas para os dados.

Se houver espaço livre significativo disponível, como de muitas linhas excluídas, uma reconstrução do índice clusterizado compactaria o espaço em páginas e extensões para ser mais eficiente (ou seja, menor) por motivos de desempenho.

Portanto, nenhum dado deveria ter sido descartado, mas o processo de reconstrução disponibilizou novamente aquele espaço livre que estava embutido nas páginas de dados.