Pilhas (tabelas sem índices agrupados)

Aplica-se a:SQL ServerBase de Dados SQL do AzureAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Uma pilha é uma tabela sem um índice agrupado. Pode criar um ou mais índices não agrupados em tabelas armazenadas como heap. O heap armazena dados sem especificar uma ordem. Normalmente, o heap inicialmente armazena os dados pela ordem em que insere as linhas. No entanto, o Mecanismo de Banco de Dados pode mover dados no amontoado para armazenar as linhas eficientemente. Nos resultados das consultas, não se pode prever a ordem dos dados. Para garantir a ordem das linhas retornadas de uma pilha, use a cláusula . 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 frequentemente truncas e recarregas. O Database Engine otimiza o espaço num heap preenchendo o espaço mais antigo disponível.

Considere o seguinte:

• Localizar espaço livre num heap pode ser dispendioso, especialmente se ocorrerem muitas eliminações ou atualizações.
• Índices agrupados oferecem desempenho estável para tabelas que não se truncam frequentemente.

Para tabelas que se truncam ou recriam regularmente, como tabelas temporárias ou de staging, usar um heap é frequentemente 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 armazena uma tabela como um heap, identifica as linhas individuais por referência a um identificador de linha (RID) de 8 bytes que consiste no número do ficheiro, número da página de dados e slot na página (FileID:PageID:SlotID). O 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 impõem uma ordem de inserção estrita, a operação de inserção é geralmente mais rápida do que uma inserção equivalente num índice agrupado. Se ler e processar os dados do heap para um destino final, considere criar um índice restrito e não agrupado que cubra o predicado de pesquisa que a consulta utiliza.

Também pode usar heaps quando acede sempre aos dados através de índices não agrupados e o RID é menor do que uma chave de índice clusterizada.

Se uma tabela for um heap e não tiver índices não agrupados, então deve ler toda a tabela (uma varredura de tabela) para encontrar 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 uses um heap quando os dados são frequentemente devolvidos numa 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 têm de ser ordenados antes de serem agrupados, e um índice agrupado na coluna de ordenação pode evitar a operação de ordenação.

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

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

Não uses um heap se atualizares os dados com frequência. Se atualizares um registo e a atualização ocupar mais espaço nas páginas de dados do que o que ocupa atualmente, o registo move-se para uma página de dados com espaço livre suficiente. Esta movimentação cria um registo encaminhado que aponta 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. Este movimento introduz fragmentação no heap. Quando o Mecanismo de Banco de Dados varre uma pilha, ele segue esses ponteiros. Esta ação limita o desempenho da leitura antecipada e pode incorrer em I/O extra, 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 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 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 tem uma ou mais unidades de alocação para armazenar e gerir os dados de uma partição específica. No mínimo, cada heap tem uma IN_ROW_DATA unidade de alocação por partição. A estrutura de heap também tem uma LOB_DATA unidade de alocação por partição, se contiver colunas de objetos grandes (LOB). Tem também 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.

A coluna first_iam_page na sys.system_internals_allocation_units vista do sistema aponta para a primeira página do Mapa de Alocação de Índices (IAM) na cadeia de páginas IAM que gerem o espaço alocado ao heap numa 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 numa ordem específica nem estão ligadas. A única conexão lógica entre as páginas de dados são as informações registradas nas páginas do IAM.

Pode realizar varreduras de tabela ou leituras seriais de um heap escaneando as páginas IAM para encontrar as extensões que guardam as páginas do heap. Como o IAM representa as extensões na mesma ordem em que existem nos ficheiros de dados, esta estrutura significa que as varreduras de heap serial progridem sequencialmente por cada ficheiro. Usar as páginas IAM para definir a sequência de varrimento também significa que as linhas do heap normalmente não são devolvidas 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

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