Quais são os cenários de uso válidos para tabelas HEAP?

Os únicos usos válidos são para

• tabelas de preparação usadas em processos de importação/exportação/ETL.
• backup ad-hoc, temporário e de curto prazo de tabelas usandoSELECT * INTO..

As tabelas de preparação são normalmente bastante planas e truncadas antes/depois do uso.

Observe que um índice clusterizado geralmente é pequeno em comparação com o tamanho dos dados: os dados são o nível mais baixo da estrutura do índice.

Tabelas heap também têm problemas. Pelo menos estes:

• não pode ser desfragmentado para reduzir o espaço em disco. Isso é importante porque as páginas de dados usadas serão espalhadas por todo o MDF, por exemplo, porque os dados não têm "ordem" do índice clusterizado
• o índice não clusterizado agora aponta para a linha, não para a entrada do índice clusterizado. Isso afeta o desempenho: Necessidade de alcançar dados por meio de índice clusterizado com um índice não clusterizado

Veja também

• http://www.sqlbadpractices.com/heap-tables/ (Má prática)
• http://msdn.microsoft.com/en-us/library/hh213609.aspx (recomendações do MS)
• http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2930)-fixing-heap-fragmentation.aspx (os mitos do DBA de Paul Randal)

Principais considerações

Vejo uma vantagem importante para heaps e outra para tabelas clusterizadas, além de uma terceira consideração que pode ser feita de qualquer maneira.

• Uma pilha economiza uma camada de indireção. Os índices contêm IDs de linha, apontando diretamente (bem, não realmente, mas o mais diretamente possível) para um local do disco. Assim, uma busca de índice em um heap deve custar aproximadamente metade de uma busca de índice não clusterizada em uma tabela clusterizada.

• Um índice clusterizado é classificado, per se, graças a um índice (quase) gratuito. Como o índice de agrupamento é refletido na ordem física dos dados, ele ocupa relativamente pouco espaço sobre os próprios dados reais, que obviamente você precisa armazenar de qualquer maneira. Por ser fisicamente ordenado, uma varredura de alcance contra esse índice pode buscar o ponto inicial e, em seguida, percorrer o ponto final com muita eficiência.

• Índices em heaps fazem referência a RIDs, que são 64 bits. Conforme mencionado, os índices não clusterizados em uma tabela clusterizada fazem referência à chave de clustering, que pode ser menor (uma de 32 bits INT), a mesma (uma de 64 bits BIGINT) ou maior (uma de 48 bits DATETIME2()mais uma de 32 bits INT, ou um GUID de 128 bits). Obviamente, uma referência mais ampla resulta em índices maiores e mais caros.

Requisitos de espaço

Com essas duas tabelas:

CREATE TABLE TmpClustered
(
ID1 INT NOT NULL,
ID2 INT NOT NULL
)
ALTER TABLE TmpClustered ADD CONSTRAINT PK_Tmp1 PRIMARY KEY CLUSTERED (ID1)
CREATE UNIQUE INDEX UQ_Tmp1 ON TmpClustered (ID2)

CREATE TABLE TmpNonClustered
(
ID1 INT NOT NULL,
ID2 INT NOT NULL
)
ALTER TABLE TmpNonClustered ADD CONSTRAINT PK_Tmp2 PRIMARY KEY NONCLUSTERED (ID1)
CREATE UNIQUE INDEX UQ_Tmp2 ON TmpNonClustered (ID2)

...cada um preenchido com 8,7 M de registros, o espaço necessário foi de 150 MB para dados para ambos; 120 MB para os índices da tabela clusterizada, 310 MB para os índices da tabela não clusterizada. Isso reflete que o índice clusterizado é mais restrito que um RID e que o índice clusterizado é principalmente um "brinde". Sem os índices exclusivos em ID2, o espaço de índice necessário cai para 155 MB para a tabela não clusterizada (metade, como seria de esperar), mas apenas 150 KB para o PK clusterizado - quase nada.

Portanto, um índice não agrupado de um campo de 32 bits em uma tabela agrupada com um índice de 32 bits (total de 64 bits, nominalmente) ocupava 120 MB, enquanto um índice de um campo de 32 bits em um heap com uma pilha de 64 bits O RID (total de 96 bits, nominalmente) ocupou 155 MB, um pouco menos do que o aumento de 50% que se esperaria ingenuamente de chaves de 64 bits para 96 ​​bits, mas é claro que há sobrecarga que reduz a diferença efetiva de tamanho.

Preencher as duas tabelas e criar seus índices levou o mesmo tempo para cada tabela. Executando testes simples envolvendo varreduras ou buscas, não encontrei diferenças materiais de desempenho entre as tabelas, o que corresponde ao white paper da Microsoft que o gbn vinculou de maneira útil. O referido artigo mostra uma diferença significativa para acesso altamente concorrente; Não sei por que isso acontece, espero que alguém com mais experiência do que eu com sistemas OLTP de alto volume possa nos dizer.

A adição de aproximadamente 40 bytes de dados aleatórios de comprimento variável não alterou significativamente essa equivalência. Substituir os INTs por UUIDs largos também não (cada tabela foi reduzida aproximadamente na mesma extensão). Sua milhagem pode variar, mas na maioria dos casos , a disponibilidade de um índice é mais importante do que o tipo.

pedaços

Fazer uma varredura de intervalo em um índice não clusterizado - porque a tabela é um heap ou o índice não é o índice clusterizado - envolve a varredura do índice e, em seguida, fazer uma pesquisa na tabela para cada ocorrência. Isso pode ser muito caro, por isso às vezes é mais barato apenas escanear a tabela. Você pode contornar isso com um índice de cobertura, no entanto. Isso se aplica quer você tenha agrupado sua tabela ou não.

Como o @gbn apontou, não há uma maneira simples de compactar um heap. No entanto, se sua tabela aumentar gradativamente ao longo do tempo - um caso muito comum - haverá pouco desperdício, pois o espaço liberado por exclusões será preenchido por novos dados.

Várias das discussões heap versus tabela clusterizada que vi fazem um curioso argumento de espantalho de que uma heap sem índices é inferior a uma tabela clusterizada, pois sempre requer uma varredura de tabela. Isso certamente é verdade, mas a comparação mais significativa é "tabela clusterizada grande e bem indexada" versus "heap grande e bem indexado". Se sua tabela é muito pequena ou você sempre fará varreduras de tabela, não importa muito se você a agrupar ou não.

Como cada índice em uma tabela agrupada faz referência ao índice de clustering, eles são, na verdade, todos os índices de cobertura. Uma consulta que faz referência a uma coluna indexada e a(s) coluna(s) de agrupamento pode fazer uma varredura de índice sem nenhuma pesquisa de tabela. Isso geralmente não é valioso se o seu índice de clustering for uma chave sintética, mas se for uma chave de negócios que você precisa recuperar de qualquer maneira, é um bom recurso.

TL;DR

Sou um cara de data warehouse, não um especialista em OLTP. Para tabelas de fatos, quase sempre uso um índice de agrupamento no campo que provavelmente precisará de varreduras de intervalo, geralmente um campo de data. Para tabelas de dimensões, eu agrupo no PK para que seja pré-classificado para junções de mesclagem em tabelas de fatos.

Existem vários motivos para usar índices de agrupamento, mas se nenhum desses motivos se aplicar, a sobrecarga pode não valer a pena. Suspeito que haja muito "sempre fizemos dessa maneira" e "é apenas uma prática recomendada" por trás das pessoas que usam índices clusterizados universalmente. Experimente ambos com seus dados e sua carga e veja o que funciona melhor.

Acho que dizer "O único uso válido é para tabelas de teste usadas em processos de importação/exportação/ETL" é um pouco restritivo, para dizer o mínimo. Você deve pegar o caso de uso esperado de um determinado sistema e, em seguida, escolher com base nos méritos de pilhas ou tabelas organizadas por índice (eu sei, um termo do Oracle, mas o descreve bem).

Nosso warehouse carrega cerca de 1,5 bilhão de linhas por dia e precisa suportar gravações e processamentos altamente simultâneos, bem como leituras. O armazenamento relacional suporta um banco de dados OLAP e, portanto, as leituras tendem a ser principalmente varreduras de tabela. Os relatórios e feeds downstream gerados geralmente também não são seletivos o suficiente para que qualquer índice seja útil. O sistema suporta uma janela deslizante de dados e, portanto, uma vez que uma tabela é carregada, raramente a escrevemos novamente e, devido à implementação bastante ruim do particionamento de tabelas, requer bloqueios Sch-M para divisões de partições, comutações e mesclagens versus bloqueios Sch-S para leituras, etc. , o sistema teve que usar muitas tabelas, embora também tenhamos algumas tabelas particionadas. O uso de muitas tabelas facilita a segmentação de dados e os ciclos de limpeza, ao mesmo tempo em que reduz a contenção.

Como tal, a sobrecarga adicional de uma tabela organizada por índice (tabela agrupada) em algumas colunas arbitrárias versus ser capaz de bcp em uma pilha, processar as partições OLAP, executar algumas consultas de varredura de tabela e, em seguida, 3 dias depois, descartá-lo significa que simplesmente não vale a pena. Observe que, em nosso caso, os dados voltam de um grande cluster de grade, portanto, também não há ordenação dos dados; portanto, a inserção em uma tabela com um índice clusterizado pode apresentar outros problemas, como "pontos de acesso" e divisões de página e similares.

Além disso, acho que o argumento sobre páginas espalhadas é um pouco falso. Os índices clusterizados também podem ter suas páginas espalhadas pelo arquivo. É só que, após a reindexação (assumindo mais de 1000 páginas), isso pode ser melhor do que uma pilha, mas você também teve que reindexar também.

Também é possível economizar espaço usando colunas esparsas e compactação, se isso for uma preocupação. É verdade que, em alguns casos, as seleções em uma tabela com um índice clusterizado podem ser mais rápidas, mas você deve pesar isso com os recursos necessários para carregá-lo e mantê-lo.

[Editar] Eu provavelmente deveria deixar claro que apenas nossas tabelas de fatos não particionadas são heaps. Tabelas particionadas e tabelas de dimensão têm índices agrupados para oferecer suporte a pesquisas eficientes, etc. [Edit2] Corrigido 2,5 bilhões para 1,5 bilhão. Mas, esses dois números estando próximos um do outro. O que acontece ao digitar respostas em um telefone, eu acho...