Heaps (Tabelas sem índices clusterizados)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureBanco de dados SQL no Microsoft Fabric

Uma heap é uma tabela que não possui índice clusterizado. Você pode criar um ou mais índices não agrupados em tabelas armazenadas como um heap. O heap armazena dados sem especificar uma ordem. Normalmente, o heap inicialmente armazena os dados na ordem em que você insere as linhas. No entanto, o Mecanismo de Banco de Dados pode mover dados no heap para armazenar as linhas com eficiência. Nos resultados das consultas, você não pode prever a ordem dos dados. Para garantir a ordem de linhas retornadas de um heap, use a cláusula ORDER BY. Para especificar uma ordem lógica permanente para armazenar as linhas, crie um índice agrupado na tabela, para que a tabela não seja um heap.

Quando usar um heap

Um heap é ideal para tabelas que você frequentemente trunca e recarrega. O Mecanismo de Banco de Dados otimiza o espaço em um heap preenchendo o espaço mais antigo disponível.

Considere o seguinte:

• Localizar espaço livre em um heap pode ser caro, especialmente se ocorrerem muitas deleções ou atualizações.
• Índices agrupados oferecem desempenho estável para tabelas que você não faz com frequência.

Para tabelas que você trunca ou recria regularmente, como tabelas temporárias ou de staging, usar um heap costuma ser mais eficiente.

A escolha entre usar um heap e um índice clusterizado pode afetar significativamente o desempenho e a eficiência do banco de dados.

Quando você armazena uma tabela como um heap, identifica linhas individuais por referência a um identificador de linha de 8 bytes (RID) que consiste no número do arquivo, número da página de dados e slot na página (FileID:PageID:SlotID). A ID da linha é uma estrutura pequena e eficiente.

Use heaps como tabelas de staging para operações de inserção grandes e desordenadas. Como os heaps não aplicam uma ordem de inserção rígida, a operação de inserção geralmente é mais rápida do que uma inserção equivalente em um índice agrupado. Se você ler e processar os dados do heap em um destino final, considere criar um índice restrito e não agrupado que cubra o predicado de busca que a consulta usa.

Você também pode usar heaps quando sempre acessa dados por meio de índices não agrupados e o RID é menor que uma chave de índice clusterizada.

Se uma tabela for um heap e não tiver índices não agrupados, então você deve ler a tabela inteira (uma varredura de tabela) para encontrar qualquer linha. O SQL Server não pode buscar um RID diretamente no heap. Esse comportamento pode ser aceitável quando a tabela é pequena.

Quando não usar um heap

Não use um heap quando os dados são frequentemente retornados em ordem ordenada. Um índice agrupado na coluna de ordenação pode evitar a operação de ordenação.

Não use um heap quando os dados estão frequentemente agrupados. Os dados precisam ser ordenados antes de serem agrupados, e um índice agrupado na coluna de ordenação pode evitar essa operação.

Não use um heap quando intervalos de dados são frequentemente consultados da tabela. Um índice clusterizado na coluna de intervalo evita a classificação do heap inteiro.

Não use um heap quando não há índices não agrupados e a tabela é grande. A única aplicação para esse design é retornar todo o conteúdo da tabela sem uma ordem especificada. Em um heap, o Mecanismo de Banco de Dados lê todas as linhas para localizar qualquer linha.

Não use um heap se atualizar os dados com frequência. Se você atualiza um registro e a atualização usa mais espaço nas páginas de dados do que atualmente usa, o registro se move para uma página de dados que tem espaço livre suficiente. Essa movimentação cria um registro encaminhado apontando para a nova localização dos dados. O ponteiro de encaminhamento é escrito na página que continha os dados anteriormente, para indicar a nova localização física. Esse movimento introduz fragmentação no heap. Quando o Mecanismo de Banco de Dados verifica um heap, ele segue esses ponteiros. Essa ação limita o desempenho da leitura antecipada e pode gerar I/O extra, o que reduz o desempenho da varredura.

Gerenciar heaps

Para criar um heap, crie uma tabela sem um índice clusterizado. Se uma tabela já tiver um índice clusterizado, descarte o índice clusterizado para retornar a tabela a um heap.

Para remover um heap, crie um índice clusterizado no heap.

Para recompilar um heap para recuperar o espaço desperdiçado:

• Crie um índice clusterizado no heap e descarte esse índice clusterizado.
• Use o comando ALTER TABLE ... REBUILD para recompilar o heap.

Identificar heaps

A consulta a seguir retorna uma lista de heaps do banco de dados atual. A lista inclui:

• Nomes de tabelas
• Nomes do esquema
• Número de linhas
• Tamanho da tabela em KB
• Tamanho do índice em KB
• Espaço não utilizado
• Uma coluna para identificar um heap

SELECT t.name AS 'Your TableName',
       s.name AS 'Your SchemaName',
       p.rows AS 'Number of Rows in Your Table',
       SUM(a.total_pages) * 8 AS 'Total Space of Your Table (KB)',
       SUM(a.used_pages) * 8 AS 'Used Space of Your Table (KB)',
       (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS 'Unused Space of Your Table (KB)',
       CASE
           WHEN i.index_id = 0 THEN 'Yes'
           ELSE 'No'
       END AS 'Is Your Table a Heap?'
FROM sys.tables AS t
     INNER JOIN sys.indexes AS i
         ON t.object_id = i.object_id
     INNER JOIN sys.partitions AS p
         ON i.object_id = p.object_id
        AND i.index_id = p.index_id
     INNER JOIN sys.allocation_units AS a
         ON p.partition_id = a.container_id
     LEFT OUTER JOIN sys.schemas AS s
         ON t.schema_id = s.schema_id
WHERE i.index_id <= 1 -- 0 for Heap, 1 for Clustered Index
GROUP BY t.name, s.name, i.index_id, p.rows
ORDER BY 'Your TableName';

Estruturas de heap

Uma heap é uma tabela que não possui índice clusterizado. Heaps têm uma linha em sys.partitions, com index_id = 0 para cada particionamento usado pelo heap. Por padrão, um heap tem um único particionamento. Quando um heap tem várias partições, cada partição tem uma estrutura de heap que contém os dados daquela partição específica. Por exemplo, se um heap tiver quatro particionamentos, haverá quatro estruturas de heap; uma em cada particionamento.

Dependendo dos tipos de dados no heap, cada estrutura de heap possui uma ou mais unidades de alocação para armazenar e gerenciar os dados de uma partição específica. No mínimo, cada heap possui uma IN_ROW_DATA unidade de alocação por partição. A estrutura de heap também possui uma LOB_DATA unidade de alocação por partição, se contiver colunas de objetos grandes (LOB). Também possui uma ROW_OVERFLOW_DATA unidade de alocação por partição, se contiver colunas de comprimento variável que excedam o limite de 8.060 bytes de tamanho de linha.

A coluna first_iam_page na sys.system_internals_allocation_units visualização do sistema aponta para a primeira página do Mapa de Alocação de Índice (IAM) na cadeia de páginas IAM que gerenciam o espaço alocado ao heap em uma partição específica. SQL Server usa as páginas IAM para percorrer o heap. As páginas de dados e as linhas dentro delas não estão em nenhuma ordem específica e não estão vinculadas. A única conexão lógica entre as páginas de dados são as informações registradas nas páginas IAM.

Você pode realizar varreduras de tabela ou leituras seriais de um heap escaneando as páginas IAM para encontrar as extensões que armazenam as páginas do heap. Como o IAM representa as extensões na mesma ordem em que existem nos arquivos de dados, essa estrutura significa que as varreduras de heap seriais progridem sequencialmente por cada arquivo. Usar as páginas IAM para definir a sequência de varredura também significa que as linhas do heap normalmente não são retornadas na ordem em que foram inseridas.

A ilustração a seguir mostra como o Mecanismo de Banco de Dados do SQL Server usa páginas IAM para recuperar linhas de dados em um único heap de partição.

Conteúdo relacionado

• CREATE INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Índices clusterizados e não clusterizados