Heap (tabelle senza indici clusterizzati)

Si applica a:SQL Serverdatabase SQL di AzureAzure SQL Istanza gestitaDatabase SQL in Microsoft Fabric

Un heap è una tabella senza un indice clusterizzato. Puoi creare uno o più indici non clusterizzati su tabelle memorizzate come heap. L'heap memorizza i dati senza specificare un ordine. Di solito, l'heap memorizza inizialmente i dati nell'ordine in cui inserisci le righe. Tuttavia, il motore di database può spostare i dati nell'heap per archiviare le righe in modo efficiente. Nei risultati delle query, non puoi prevedere l'ordine dei dati. Per garantire l'ordine delle righe restituite da un heap, utilizzare la clausola ORDER BY. Per specificare un ordine logico permanente per la memorizzazione delle righe, crea un indice raggruppato sulla tabella, in modo che la tabella non sia un heap.

Quando utilizzare un heap

Un heap è ideale per tabelle che si tronca e ricarica frequentemente. Il motore di database ottimizza lo spazio in un heap riempiendo lo spazio più precoce disponibile.

Considerare quanto segue:

• Trovare spazio libero in un heap può essere costoso, specialmente se avvengono molte cancellazioni o aggiornamenti.
• Gli indici clusterizzati offrono prestazioni costanti per tabelle che non si troncano frequentemente.

Per tabelle che si troncano o si ricreano regolarmente, come tabelle temporanee o di staging, l'uso di un heap è spesso più efficiente.

La scelta tra l'uso di un heap e un indice cluster può influire significativamente sulle prestazioni e sull'efficienza del database.

Quando memorizzi una tabella come heap, identifichi le singole righe tramite riferimento a un identificatore di riga (RID) di 8 byte costituito dal numero file, dal numero della pagina dati e dallo slot sulla pagina (FileID:PageID:SlotID). L'identificatore di riga è una struttura piccola ed efficiente.

Usa gli heap come tabelle di staging per operazioni di inserimento grandi e non ordinate. Poiché gli heap non applicano un ordine di inserimento rigoroso, l'operazione di inserimento è solitamente più veloce di un inserimento equivalente in un indice clusterizzato. Se leggi e elabori i dati dell'heap in una destinazione finale, considera di creare un indice ristretto non clusterizzato che copra il predicato di ricerca utilizzato dalla query.

Puoi anche usare gli heap quando accedi sempre ai dati tramite indici non clusterizzati e il RID è più piccolo di una chiave di indice clusterizzata.

Se una tabella è un heap e non ha indici non clusterizzati, allora devi leggere l'intera tabella (una scansione della tabella) per trovare una riga. SQL Server non può cercare un RID direttamente sull'heap. Questo comportamento può essere accettabile quando la tabella è di dimensioni ridotte.

Quando non utilizzare un heap

Non usare un heap quando i dati vengono frequentemente restituiti in ordine ordinato. Un indice raggruppato sulla colonna di ordinamento può evitare l'operazione di ordinamento.

Non usare un heap quando i dati sono spesso raggruppati insieme. I dati devono essere ordinati prima di essere raggruppati, e un indice raggruppato nella colonna di ordinamento può evitare l'operazione di ordinamento.

Non usare un heap quando intervalli di dati vengono frequentemente interrogati dalla tabella. Un indice cluster nella colonna dell'intervallo evita la necessità di ordinare l'intero heap.

Non usare un heap quando non ci sono indici non raggruppati e la tabella è grande. L'unica applicazione di questo design è restituire l'intero contenuto della tabella senza un ordine specificato. In un heap, il motore di database legge tutte le righe per trovare una qualsiasi riga.

Non usare un heap se aggiorni spesso i dati. Se aggiorni un record e l'aggiornamento utilizza più spazio nelle pagine dati di quanto ne consumi attualmente, il record si sposta in una pagina dati con spazio libero sufficiente. Questa mossa crea un record inoltrato che punta alla nuova posizione dei dati. Il puntatore di inoltro è scritto nella pagina che conteneva i dati in precedenza, per indicare la nuova posizione fisica. Questa mossa introduce frammentazione nel heap. Quando il motore di database analizza un heap, segue questi puntatori. Questa azione limita le prestazioni di lettura anticipata e può causare ulteriori I/O, riducendo le prestazioni di scansione.

Gestire gli heap

Per creare un heap, creare una tabella senza un indice cluster. Se una tabella dispone già di un indice clusterizzato, eliminare l'indice clusterizzato per ripristinare la tabella come heap.

Per rimuovere un heap, creare un indice cluster nell'heap.

Per ricostruire un heap per recuperare spazio sprecato:

• Creare un indice clusterizzato sull'heap e quindi eliminare tale indice clusterizzato.
• Usa il comando ALTER TABLE ... REBUILD per ricreare l'heap.

Identificare gli heap

La seguente query restituisce un elenco di heap dal database corrente. L'elenco include:

• Nomi di tabella
• Nomi degli schemi
• Numero di righe
• Dimensioni della tabella in KB
• Dimensioni dell'indice in KB
• Spazio inutilizzato
• Una colonna per identificare un 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';

Strutture heap

Un heap è una tabella senza un indice clusterizzato. Agli heap corrisponde una sola riga in sys.partitions, con index_id = 0 per ogni partizione usata dall'heap. Per impostazione predefinita, a ogni heap è associata una singola partizione. Se a un heap sono associate più partizioni, ognuna di esse ha una struttura di heap contenente i dati per la partizione specifica. Ad esempio, se a un heap sono associate quattro partizioni, saranno presenti quattro strutture di heap, una per ogni partizione.

A seconda dei tipi di dati presenti nell'heap, ogni struttura di heap ha una o più unità di allocazione per memorizzare e gestire i dati di una specifica partizione. Al minimo, ogni heap ha un'unità IN_ROW_DATA di allocazione per partizione. La struttura heap ha anche un'unità LOB_DATA di allocazione per partizione, se contiene colonne di oggetti grandi (LOB). Ha anche un'unità ROW_OVERFLOW_DATA di allocazione per partizione, se contiene colonne di lunghezza variabile che superano il limite di 8.060 byte di riga di dimensione.

La colonna first_iam_page nella sys.system_internals_allocation_units vista di sistema ripunta alla prima pagina Index Allocation Map (IAM) nella catena di pagine IAM che gestiscono lo spazio allocato all'heap in una partizione specifica. SQL Server usa le pagine IAM per spostarsi all'interno dell'heap. Le pagine dati e le righe al loro interno non sono in un ordine specifico e non sono collegate. L'unico collegamento logico tra le pagine di dati sono le informazioni registrate nelle pagine IAM.

Puoi effettuare scansioni di tabelle o letture seriali di un heap scansionando le pagine IAM per trovare le estensioni che contengono le pagine dell'heap. Poiché l'IAM rappresenta le estensioni nello stesso ordine in cui esistono nei file dati, questa struttura significa che le scansioni del mucchio seriale procedono in modo sequenziale attraverso ogni file. Usare le pagine IAM per impostare la sequenza di scansione significa anche che le righe dell'heap di solito non vengono restituite nell'ordine in cui sono state inserite.

La figura seguente illustra l'uso delle pagine IAM nel motore di database di SQL Server per recuperare le righe di dati di un heap relativo a una singola partizione.

Contenuti correlati

• CREATE INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Indici clusterizzati e non clusterizzati