Temos uma tabela em um banco de dados SQL do Azure que costumava ter uma coluna nvarchar(max) armazenando arquivos PDF. (As maravilhas dos desenvolvedores externos.) A mesa cresceu para 156 GB. Possui 476.000 linhas. Depois de mudar a lógica, não precisamos mais da coluna pdf. A única maneira razoável de se livrar dos dados contidos nela era descartar a coluna e recriá-la (caso algum processo estranho ainda estivesse fazendo referência a ela).
No entanto, o tamanho da tabela ainda é relatado como 156 GB. A tabela de backup que acabei de criar (SELECT INTO) tem 128 MB, então esse parece ser o tamanho real dos dados.
Deixei uma reconstrução de índice (ONLINE) ser executada durante a noite no índice PK clusterizado. Ele falhou com um erro de TCP entre 8 e 12 horas. O índice ainda está 95% fragmentado, o tamanho ainda é relatado como 156 GB.
Existe uma explicação para que isso seja tão lento? Existe uma maneira melhor? Banco de dados de produção, tabela usada por um site, tem que estar acessível, então não pode fazer isso OFFLINE a menos que demore menos de 10 minutos - o que ninguém pode garantir.
Posso simplesmente construir todos os índices na tabela de backup, eliminar a tabela original e renomear o backup? Isso parece arriscado (pequeno risco de perder um registro criado na hora errada).
Estou tentando fazer o Azure perceber que não é mais usado. Alocado, estou bem com isso. Usado, nem tanto:
A tabela em questão:
Novamente, o problema não é o espaço reservado, mas o espaço usado.
Sim, isso é esperado.
Por que?
A forma como a alocação de espaço funciona no SQL Server é à medida que os dados crescem, o espaço é alocado fora do disco para o arquivo interno desse banco de dados dentro da instância do SQL Server, em pedaços com o tamanho definido nas propriedades do banco de dados, em Arquivo -> Crescimento automático configurações:
Isso reserva os dados fora do disco de forma proativa e garante que eles estejam disponíveis à medida que mais dados forem adicionados a esse banco de dados no futuro. As operações de crescimento de arquivos são operações bastante pesadas, e é por isso que o arquivo de banco de dados não cresce apenas na mesma taxa e quantidade que o próprio crescimento de dados.
Isso também se aplica à redução de arquivos. É uma operação cara. O banco de dados foi projetado para não ser reduzido automaticamente por padrão, quando os dados são removidos. A suposição é que o espaço já reivindicado do disco será eventualmente reutilizado. Portanto, em vez de liberá-lo de volta ao disco quando os dados são removidos, ele é apenas marcado internamente para ser reutilizado em dados futuros para sobrescrevê-lo. Isso pode parecer um desperdício, mas o disco é barato e o desempenho não, que é o que se ganha implicitamente ao não crescer e diminuir continuamente sempre que os dados são alterados.
Uma ferramenta útil é o procedimento armazenado do sistema
sp_spaceused. Se você executá-lo no contexto de um banco de dados específico, sem nenhum parâmetro, ele informará o tamanho total desse banco de dados -database_sizeno primeiro conjunto de resultados e como esse espaço em disco está atualmente distribuído no arquivo do banco de dados -reservedvsdatavsindex_sizeem o segundo conjunto de resultados. Anteriormente, você provavelmente tinha cerca de 156 GB de espaço nadatacoluna que agora seria exibidareservede significa que está pronto para ser consumido pelo crescimento futuro de dados.Agora, para resolver seu problema
O SQL Server possui um comando de redução (que também pode ser executado por meio da GUI do SSMS) que liberará o espaço reservado do arquivo de banco de dados de volta ao disco (tanto quanto possível, há alguns se, e ou mas nisso). Geralmente não é recomendado porque, novamente, a redução é uma operação pesada no disco e pode afetar o desempenho simultâneo do banco de dados até que seja concluída, e porque o espaço que está sendo liberado terá que passar por uma operação de crescimento pesado novamente quando mais dados forem adicionados ao banco de dados . Mas em grandes alterações pontuais de dados, como o seu caso, provavelmente é sensato fazê-lo, se você realmente precisar.
Nota
As reconstruções de índice geralmente são um desperdício e uma operação inútil - causa o crescimento do arquivo, o que basicamente desfaz parte do espaço liberado pela redução. É um ciclo vicioso . Se você fizer isso na esperança de obter ganhos de desempenho, é improvável que você esteja realmente ganhando alguma coisa com as próprias reconstruções (a fragmentação do índice realmente não importa mais no hardware moderno). As reconstruções acionam ações subsequentes que podem melhorar seu desempenho, como limpar o cache do plano e atualizar estatísticas. Mas você pode executar essas coisas individualmente, de forma mais granular e eficiente, sem a necessidade de reconstruções de índice inúteis.