Eu tenho uma tabela grande com 7,5 bilhões de linhas e 5 índices. Quando excluo aproximadamente 10 milhões de linhas, percebo que os índices não clusterizados parecem aumentar o número de páginas em que estão armazenados.
Eu escrevi uma consulta dm_db_partition_statspara relatar a diferença (depois - antes) nas páginas:
O índice 1 é o índice clusterizado, o índice 2 é a chave primária. Os outros são não agrupados e não exclusivos.
Por que as páginas estão aumentando nesses índices não agrupados?
Eu esperava que os números, na pior das hipóteses, permanecessem os mesmos.
Vejo que os contadores de desempenho relatam um aumento nas divisões de página durante a exclusão.
Ao excluir, o registro fantasma precisa ser movido para outra página? Isso tem a ver com "singulares"?
Estamos no meio do lançamento do RCSI, mas agora o RCSI está desativado.
É um nó primário em um grupo de disponibilidade. Eu sei que o instantâneo é usado de alguma forma em secundários. Eu ficaria surpreso se isso fosse relevante. Eu pretendo investigar isso (procurando a saída da página dbcc) para saber mais. Aqui está esperando que alguém tenha visto algo semelhante.
Um cenário possível que me diverte muito:
Como esse servidor é primário em um AG, ele é afetado da mesma forma que os secundários. As informações da versão são adicionadas à primária - as páginas de dados são exatamente as mesmas nas primárias e secundárias. Os secundários aproveitam o armazenamento de versão para fazer suas leituras enquanto as linhas estão sendo atualizadas pelo AG, mas os secundários não gravam suas próprias versões do carimbo de data/hora na página. Eles apenas herdam as versões do trabalho do primário.
Para demonstrar o crescimento, peguei a exportação do banco de dados Stack Overflow (que não tem RCSI habilitado) e criei vários índices na tabela Posts. Verifiquei os tamanhos dos índices com sp_BlitzIndex @Mode = 2 (copiar/colar em uma planilha e limpar um pouco para maximizar a densidade das informações):
Em seguida, excluí cerca de metade das linhas:
Curiosamente, enquanto as exclusões aconteciam, o arquivo de dados crescia para acomodar os timestamps também! O Relatório de uso de disco do SSMS mostra os eventos de crescimento - aqui está apenas o topo para ilustrar:
(Tenho que adorar uma demonstração em que as exclusões fazem o banco de dados crescer.) Enquanto a exclusão estava em execução, executei sp_BlitzIndex novamente. Observe que o índice clusterizado tem menos linhas, mas seu tamanho já aumentou cerca de 1,5 GB. Os índices não clusterizados em AcceptedAnswerId cresceram dramaticamente - eles são índices em um valor pequeno que é praticamente nulo, então seus tamanhos de índice quase dobraram!
Eu não tenho que esperar que a exclusão termine para provar isso, então vou parar a demonstração por aí. O ponto é: quando você faz grandes exclusões em uma tabela que foi implementada antes de RCSI, SI ou AGs serem habilitados, os índices (incluindo o clusterizado) podem realmente crescer para acomodar a adição do registro de data e hora do armazenamento de versão.