Compressão em uma pilha

Embora eu não conheça os mecanismos internos específicos responsáveis ​​pelas diferenças, posso dizer que os Heaps são gerenciados (internamente) de maneira ligeiramente diferente dos índices clusterizados (e possivelmente também índices não clusterizados):

• A exclusão de linhas de um Heap de forma que uma ou mais páginas de dados estejam vazias (sem linhas alocadas) não necessariamente libera esse espaço. Você provavelmente precisará criar e, em seguida, descartar um índice clusterizado na tabela ou chamar ALTER TABLE [TableName] REBUILD;(a partir do SQL Server 2014?). Consulte a página Microsoft Docs para DELETE para obter mais detalhes e opções.

• Inserir linhas individuais (ou seja, não um conjunto baseado INSERTem ) em um Heap não preenche as páginas de dados tão completamente quanto ocorre com índices agrupados. Os índices agrupados caberão nas linhas, desde que haja espaço para a linha (dados e sobrecarga de linha) mais a sobrecarga de 2 bytes da matriz de slots. As páginas de dados em Heaps, no entanto, não usam o número de bytes restantes na página, mas usam um indicador muito generalizado de quão cheia a página está e não há muitos níveis relatados. Os níveis são algo como: 0%, 20%, 50%, 80% e 100% cheio. E ele mudará para 100% enquanto ainda houver espaço para outra linha (e, de fato, se esse mesmo número de linhas tivesse sido inserido em uma operação baseada em conjunto, ele teria preenchido a página o máximo possível). Claro, assim como com oDELETEoperações, a reconstrução do Heap compactará quantas linhas couberem na página de dados.

Agora, considere as seguintes informações, extraídas da seção "Quando ocorre a compactação de página" da página do Microsoft Docs para a implementação da compactação de página :

... À medida que os dados são adicionados à primeira página de dados, os dados são compactados em linha. ... Quando a página está cheia, a próxima linha a ser adicionada inicia a operação de compactação da página. A página inteira é revisada; ...

Portanto, parece totalmente alinhado com esse outro comportamento do heap que eles exigiriam um ALTER TABLE REBUILD, CREATE / DROP de um índice clusterizado ou uma alteração na configuração de compactação de dados (todos os quais reconstroem o heap) antes que as páginas de dados sejam gravadas Otimamente. Se os Heaps não estiverem totalmente cientes de "páginas inteiras" (até que o Heap seja reconstruído) e não souberem quando a página está definitivamente cheia, eles não saberão quando iniciar a operação de compactação da página (ao lidar com atualizações e -inserções de linha).

Outro tecnicismo que limitaria ainda mais a autoaplicação de compactação de página de alguns Heaps (mesmo que pudessem) é que a aplicação da compactação exigiria que todos os índices não agrupados para esse heap (se houver) fossem reconstruídos. Como a página vinculada para "Compressão de dados" também afirma:

Alterar a configuração de compactação de um heap requer que todos os índices não clusterizados na tabela sejam reconstruídos para que tenham ponteiros para os novos locais de linha no heap.

Os "ponteiros" referidos são os Row IDs (RIDs), que são uma combinação de: FileID, PageID e slot/posição na página. Esses RIDs são copiados em índices não clusterizados. Sendo um local físico preciso, às vezes são pesquisas mais rápidas do que atravessar uma b-tree com as chaves do Clustered Index. Mas, uma desvantagem de um local físico é que ele pode mudar, e esse é o problema aqui. Os índices agrupados, no entanto, não sofrem desse problema porque seus valores de chave são copiados para os índices não agrupados como o ponteiro de volta para o índice agrupado. E os valores-chave permanecem os mesmos, mesmo quando sua localização física muda.

Veja também:

• a seção "Gerenciando Heaps" da página Microsoft Docs para Heaps (Tabelas sem Índices Clusterizados) :

  Para reconstruir um heap para recuperar o espaço desperdiçado, crie um índice clusterizado no heap e, em seguida, elimine esse índice clusterizado.

• a seção "Considerações para quando você usa compactação de linha e página" da página Microsoft Docs para compactação de dados :

  Quando um heap é configurado para compactação em nível de página, as páginas recebem compactação em nível de página apenas das seguintes maneiras:

  • Os dados são importados em massa com otimizações em massa ativadas.
  • Os dados são inseridos usando a sintaxe INSERT INTO ... WITH (TABLOCK) e a tabela não possui um índice não clusterizado.
  • Uma tabela é reconstruída executando a instrução ALTER TABLE ... REBUILD com a opção de compactação PAGE.

  E a declaração citada na pergunta.

Nem todo mecanismo no SQL Server é como acreditamos que deveria ser.

Paul Randal forneceu uma forte recomendação sobre o gerenciamento do problema.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2930-fixing-heap-fragmentation/