Esta questão é a mais bem avaliada neste site sobre heaps. Ela pergunta sobre o caso de uso de heaps. No entanto, acredito que heaps muito grandes (por exemplo, milhares de gigabytes) são um caso especial que merece sua própria pergunta.
À medida que o tamanho do seu índice clusterizado cresce, também aumenta a penalidade do índice clusterizado , ou seja, o número de leituras lógicas necessárias para obter as chaves restantes dos seus índices não clusterizados. O mesmo não é verdade para um heap. Acredito que os heaps são injustamente descartados porque muitas pessoas aprenderam todo o design de seu banco de dados em data warehouses (onde varreduras de chave primária e, portanto, índices clusterizados, são geralmente uma boa ideia) ou nunca trabalharam em um banco de dados grande o suficiente para sentir o custo de ter uma tabela realmente grande.
Isso me faz perguntar: heaps muito grandes e bem indexados têm casos de uso práticos que não são encontrados em tabelas clusterizadas muito grandes e bem indexadas?
Com medo de tornar esta questão muito ampla, deixe as seguintes restrições se aplicarem. Suponha:
- Um heap de 1 TB
- Uma caixa da edição padrão
- Um ambiente OLTP ( não um data warehouse).
Cumulativamente, isso significa que você está limitado a 128 GB de RAM de pool de buffer e não fará varreduras grandes regularmente na chave primária. Ler todo o heap do disco será miserável, então quaisquer respostas sobre usar a tabela como uma tabela de preparação ou somente backup não devem se aplicar.
Claro. Tabelas clusterizadas têm muitas vantagens, mas nem sempre são a solução ideal. Note, porém, que alguns recursos do SQL Server exigem um índice clusterizado ou uma chave primária.
Um índice b-tree agrupado, amplamente materializado na ordenação lógica de suas páginas de nível folha, oferece alguns benefícios "de graça", mas também há desvantagens:
Você só pode ter um índice desse tipo, pois ele é o objeto de armazenamento principal.
Os níveis superiores da árvore b podem representar uma sobrecarga, conforme mencionado no artigo vinculado à pergunta.
O efeito extra de E/S é real, embora frequentemente exagerado. Os níveis superiores têm uma tendência a permanecer no cache, mas isso não é garantido. Em qualquer caso, as páginas extras devem ser travadas e navegadas para cada pesquisa.
O índice clusterizado inclui todas as colunas em linha e ponteiros fora da linha na folha. Isso torna o espaço entre as chaves do índice folha o mais grande possível. Em outras palavras, o índice clusterizado é geralmente o índice menos denso.
A mesma tabela organizada como um heap exigiria um índice não clusterizado para fornecer um método de acesso similar. Os níveis superiores desse índice não clusterizado serão muito similares ao caso clusterizado, mas as páginas do nível folha serão muito mais densas, em geral.
A maioria das tabelas OLTP muito grandes requer vários índices para suportar uma boa variedade de consultas. Esses índices não agrupados normalmente serão estreitos, preferindo pesquisas de índice altamente seletivas com um pequeno número de pesquisas de marcadores em vez de índices mais amplos e abrangentes. Índices de cobertura total geralmente não são práticos neste cenário.
Cada um desses caminhos de acesso se beneficia da maior densidade de folhas e/ou acesso direto fornecido pelo localizador de linhas RID, em vez de indireção por meio das chaves de agrupamento e da árvore b associada.
Houve um tempo em que
integerchaves de cluster de coluna única eram (muito) comuns, tornando o RID de 8 bytes duas vezes maior. Isso é muito menos o caso hoje em dia, já que até mesmo os chamados "substitutos" são geralmentebigint. Muitos índices clusterizados são de um tipo mais amplo, baseados em strings ou multicolunas. O tamanho de um RID é mais frequentemente uma vantagem agora.Substituir uma tabela clusterizada muito grande por um heap e um único índice não clusterizado geralmente não produzirá benefícios. A vantagem, se houver, vem de um número crescente de índices não clusterizados ideais necessários para suportar a carga de trabalho com desempenho aceitável. Cada método de acesso não clusterizado se beneficia do caminho de pesquisa mais eficiente.
No cenário limitado descrito na pergunta, vale a pena investigar o arranjo de heap se houver muitas consultas OLTP altamente seletivas que seriam melhor atendidas por um número médio a grande de índices não agrupados estreitos e altamente otimizados com pesquisas RID seletivas, bem como varreduras de intervalo somente de índice.
O quanto de benefício você vê depende das características de desempenho do hardware e dos detalhes finos dos planos de execução selecionados pelo otimizador (pré-busca em particular). Todas as advertências usuais com heaps ainda se aplicam. Esteja ciente dos problemas de gerenciamento de espaço e de como atualizações de ampliação podem resultar em registros encaminhados.
Dependendo de outras tabelas em sua carga de trabalho, o grande heap com índices secundários pode se beneficiar das otimizações de star join integradas . Esses planos geralmente apresentam pesquisas de tabela base (buscas).
Lembre-se de que as tabelas de heap também podem ser particionadas, o que às vezes é útil.
Dito isso, nada disso é novo. É bem raro que esse aspecto do desempenho supere todas as outras considerações. No entanto, é um fator genuíno que pode ser importante considerar. A maioria das pessoas acabará perfeitamente feliz com um esquema clusterizado, mesmo que um desempenho ligeiramente melhor seja tecnicamente atingível com uma base de heap.