Pilhas (tabelas sem índices agrupados)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Uma pilha é uma tabela sem um índice agrupado. Um ou mais índices não clusterizados podem ser criados em tabelas armazenadas como um heap. Os dados são armazenados no heap sem especificar uma ordem. Normalmente, os dados são inicialmente armazenados na ordem em que as linhas são inseridas. No entanto, o Mecanismo de Banco de Dados pode mover dados no amontoado para armazenar as linhas eficientemente. Nos resultados da consulta, a ordem dos dados não pode ser prevista. Para garantir a ordem das linhas retornadas de uma pilha, use a cláusula . Para especificar uma ordem lógica permanente de armazenamento das linhas, crie um índice agrupado na tabela, para que a tabela não seja um heap.

Quando usar um heap

Uma pilha é ideal para tabelas que são frequentemente truncadas e recarregadas. O motor de base de dados otimiza o espaço em um amontoado preenchendo o espaço disponível mais cedo.

Considere o seguinte:

• Localizar espaço livre num heap pode ser caro, especialmente se houve muitas eliminações ou atualizações.
• Os índices agrupados oferecem um desempenho estável para tabelas que não são frequentemente truncadas.

Para tabelas que são regularmente truncadas ou recriadas, como tabelas temporárias ou de preparação, o uso de uma pilha geralmente é 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 uma tabela é armazenada como uma pilha, linhas individuais são identificadas 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). O ID da linha é uma estrutura pequena e eficiente.

Heaps podem ser usadas como tabelas de preparação para grandes operações de inserção não ordenadas. Como os dados são inseridos sem impor uma ordem estrita, a operação de inserção geralmente é mais rápida do que a inserção equivalente em um índice clusterizado. Se os dados do heap forem lidos e processados num destino final, pode ser útil criar um índice não-clusterizado estreito que cubra o predicado de pesquisa usado pela consulta.

Às vezes, os profissionais de dados também usam heaps quando os dados são sempre acessados por meio de índices não clusterizados e o RID é menor do que uma chave de índice clusterizada.

Se uma tabela for uma pilha e não tiver nenhum índice não clusterizado, toda a tabela deverá ser lida (uma verificação de tabela) para localizar qualquer linha. O SQL Server não pode procurar 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 montículo quando os dados forem retornados com frequência em ordem ordenada. Um índice agrupado na coluna de classificação pode evitar a operação de classificação.

Não use um montículo quando os dados são frequentemente agrupados juntos. Os dados devem ser classificados antes de serem agrupados, e um índice clusterizado na coluna de classificação pode evitar a operação de classificação.

Não use uma pilha quando intervalos de dados são frequentemente consultados a partir da tabela. Um índice agrupado na coluna de intervalo evita classificar a pilha inteira.

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

Não use um heap se os dados forem atualizados com frequência. Se você atualizar um registro e a atualização usar mais espaço nas páginas de dados do que estão usando atualmente, o registro deverá ser movido para uma página de dados que tenha espaço livre suficiente. Isso cria um registro encaminhado apontando para o novo local dos dados, e o ponteiro de encaminhamento deve ser escrito na página que continha esses dados anteriormente, para indicar o novo local físico. Isso introduz fragmentação no heap. Quando o Mecanismo de Banco de Dados varre uma pilha, ele segue esses ponteiros. Essa ação limita o desempenho de leitura antecipada e pode incorrer em E/S adicionais, o que reduz o desempenho da varredura.

Gerir montes

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

Para remover uma pilha, crie um índice clusterizado na pilha.

Para reconstruir uma pilha para recuperar espaço desperdiçado:

• Crie um índice clusterizado no heap e, em seguida, descarte esse índice clusterizado.
• Use o comando ALTER TABLE ... REBUILD para reconstruir a pilha.

Identificar pilhas

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

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

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 t
INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
        AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
    ON p.partition_id = a.container_id
LEFT JOIN sys.schemas 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 pilha é uma tabela sem um índice agrupado. As tabelas heap têm uma linha em sys.partitions, com index_id = 0 para cada partição utilizada pela tabela heap. Por padrão, um heap tem uma única partição. Quando uma pilha tem várias partições, cada partição tem uma estrutura de pilha que contém os dados para essa partição específica. Por exemplo, se uma pilha tem quatro partições, há quatro estruturas de pilha; um em cada partição.

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

A coluna first_iam_page na visualização do sistema sys.system_internals_allocation_units aponta para a primeira página IAM na cadeia de páginas IAM que gerem o espaço alocado para o heap em uma partição específica. O SQL Server usa as páginas do 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 do IAM.

Varreduras de tabela ou leituras seriais de um montão podem ser realizadas percorrendo as páginas do IAM para encontrar os extents que estão armazenando páginas para o montão. Como o IAM representa extensões na mesma ordem em que existem nos arquivos de dados, isso significa que o heap serial verifica o progresso sequencialmente em cada arquivo. Usar as páginas do 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 do IAM para recuperar linhas de dados em um único heap de partição.

Conteúdo relacionado

CRIAR ÍNDICE (Transact-SQL)
ELIMINAR ÍNDICE (Transact-SQL)
Índices agrupados e não agrupados descritos