Heaps (tabellen zonder geclusterde indexen)

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

Een heap is een tabel zonder een geclusterde index. Je kunt één of meer niet-geclusterde indexen maken op tabellen die als heap zijn opgeslagen. De heap slaat gegevens op zonder een volgorde te specificeren. Meestal slaat de heap aanvankelijk data op in de volgorde waarin je de rijen invoegt. De Database Engine kan echter gegevens binnen de heap verplaatsen om de rijen efficiënt op te slaan. In zoekresultaten kun je de volgorde van de data niet voorspellen. Gebruik de ORDER BY component om de volgorde van rijen te garanderen die worden geretourneerd door een heap. Om een permanente logische volgorde voor het opslaan van de rijen te specificeren, maak je een geclusterde index op de tabel, zodat de tabel geen heap is.

Note

Soms zijn er goede redenen om een tabel als heap te laten in plaats van een geclusterde index te maken. Het effectief gebruiken van heaps is echter een geavanceerde vaardigheid. De meeste tabellen moeten een zorgvuldig gekozen geclusterde index hebben, tenzij er een goede reden bestaat om de tabel als heap te behouden.

Wanneer een heap gebruiken

Een heap is ideaal voor tabellen die je vaak afsnijdt en opnieuw laadt. De Database Engine optimaliseert de ruimte in een heap door de vroegst beschikbare ruimte te vullen.

Houd rekening met het volgende:

• Het vinden van vrije ruimte in een heap kan kostbaar zijn, vooral als er veel verwijderingen of updates plaatsvinden.
• Geclusterde indexen bieden stabiele prestaties voor tabellen die je niet vaak afkort.

Voor tabellen die je regelmatig afsnijdt of opnieuw maakt, zoals tijdelijke of staging-tabellen, is het gebruik van een heap vaak efficiënter.

De keuze tussen het gebruik van een heap en een geclusterde index kan de prestaties en efficiëntie van uw database aanzienlijk beïnvloeden.

Wanneer je een tabel als heap opslaat, identificeer je individuele rijen aan de hand van een 8-byte rij-identificatie (RID) bestaande uit het bestandsnummer, het datapaginanummer en de slot op de pagina (FileID:PageID:SlotID). De rij-ID is een kleine en efficiënte structuur.

Gebruik heaps als stagingtabellen voor grote, ongeordende insert-operaties. Omdat heaps geen strikte insertievolgorde afdwingen, is de insert-operatie meestal sneller dan een equivalente insert in een geclusterde index. Als je de data van de heap leest en verwerkt tot een eindbestemming, overweeg dan een smalle, niet-geclusterde index te maken die het zoekpredicaat van de query omvat.

Note

Je haalt data op uit een heap in volgorde van datapagina's, maar niet per se in de volgorde waarin je data hebt ingevoegd.

Je kunt ook heaps gebruiken wanneer je altijd data benadert via niet-geclusterde indexen en de RID kleiner is dan een geclusterde indexsleutel.

Als een tabel een heap is en geen niet-geclusterde indexen heeft, moet je de hele tabel (een tabelscan) lezen om een rij te vinden. SQL Server kan geen RID direct op de heap zoeken. Dit gedrag kan acceptabel zijn wanneer de tabel klein is.

Wanneer u geen heap gebruikt

Gebruik geen heap als de data vaak in een gesorteerde volgorde wordt teruggegeven. Een geclusterde index op de sorteerkolom kan de sorteeroperatie vermijden.

Gebruik geen heap als de data vaak wordt gegroepeerd. Gegevens moeten gesorteerd worden voordat ze worden gegroepeerd, en een geclusterde index op de sorteerkolom kan de sorteeroperatie vermijden.

Gebruik geen heap wanneer databereiken vaak uit de tabel worden geraadpleegd. Een geclusterde index op de bereikkolom voorkomt het sorteren van de hele heap.

Gebruik geen heap als er geen niet-geclusterde indexen zijn en de tabel groot is. De enige toepassing voor dit ontwerp is het teruggeven van de volledige tabelinhoud zonder een gespecificeerde volgorde. In een heap leest de Database Engine alle rijen om willekeurige rijen te vinden.

Gebruik geen heap als je de data vaak bijwerkt. Als je een record bijwerkt en de update neemt meer ruimte in de datapagina's dan het huidige gebruik is, verplaatst het record naar een datapagina met genoeg vrije ruimte. Deze verplaatsing creëert een doorgestuurd record dat naar de nieuwe locatie van de data verwijst. De forwardingpointer wordt geschreven op de pagina waarop de gegevens eerder stonden, om de nieuwe fysieke locatie aan te geven. Deze beweging introduceert fragmentatie in de heap. Wanneer de Database Engine een heap scant, volgt deze pointers. Deze actie beperkt de leesprestaties en kan extra I/O veroorzaken, wat de scanprestaties vermindert.

Heaps beheren

Als u een heap wilt maken, maakt u een tabel zonder een geclusterde index. Als een tabel al een geclusterde index heeft, zet u de geclusterde index neer om de tabel terug te zetten naar een heap.

Als u een heap wilt verwijderen, maakt u een geclusterde index op de heap.

Een heap herbouwen om verspilde ruimte vrij te maken:

• Maak een geclusterde index op de heap en verwijder die geclusterde index.
• Gebruik de ALTER TABLE ... REBUILD opdracht om de heap opnieuw te bouwen.

Warning

Voor het maken of verwijderen van geclusterde indexen moet de hele tabel opnieuw worden geschreven. Als de tabel niet-geclusterde indexen heeft, moet je alle niet-geclusterde indexen opnieuw aanmaken telkens wanneer je de geclusterde index verandert. Daarom kan het overstappen van een heap naar een geclusterde indexstructuur of terug veel tijd kosten en schijfruimte vereisen om data in tempdbte herschikken.

Heaps identificeren

De volgende query retourneert een lijst van heaps uit de huidige database. De lijst bevat:

• Tabelnamen
• Schemanamen
• Aantal rijen
• Tabelgrootte in kB
• Indexgrootte in kB
• Ongebruikte ruimte
• Een kolom om een heap te identificeren

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';

Structuren van de heap

Een heap is een tabel zonder een geclusterde index. Heaps hebben één rij in sys.partitions, met index_id = 0 voor elke partitie die wordt gebruikt door de heap. Standaard heeft een heap één partitie. Wanneer een heap meerdere partities heeft, heeft elke partitie een heap-structuur die de gegevens voor die specifieke partitie bevat. Als een heap bijvoorbeeld vier partities heeft, zijn er vier heapstructuren; één in elke partitie.

Afhankelijk van de datatypes in de heap heeft elke heapstructuur één of meer allocatie-eenheden om de data voor een specifieke partitie op te slaan en te beheren. Minimaal heeft elke heap één IN_ROW_DATA allocatie-eenheid per partitie. De heapstructuur heeft ook één LOB_DATA allocatie-eenheid per partitie, als deze grote objectkolommen (LOB) bevat. Het heeft ook één ROW_OVERFLOW_DATA toewijzingseenheid per partitie, als het kolommen van variabele lengte bevat die de limiet van 8.060 bytes rijgrootte overschrijden.

De kolom first_iam_page in het sys.system_internals_allocation_units systeembeeld verwijst naar de eerste Index Allocation Map (IAM)-pagina in de keten van IAM-pagina's die de ruimte beheren die aan de heap in een specifieke partitie is toegewezen. SQL Server gebruikt de IAM-pagina's om door de heap te navigeren. De datapagina's en de rijen daarin staan niet in een specifieke volgorde en zijn niet gekoppeld. De enige logische verbinding tussen gegevenspagina's is de informatie die is vastgelegd op de IAM-pagina's.

Important

De sys.system_internals_allocation_units systeemweergave is uitsluitend voor intern gebruik. Toekomstige compatibiliteit is niet gegarandeerd.

Je kunt tabelscans of seriële lezingen van een heap uitvoeren door de IAM-pagina's te scannen om de extents te vinden die pagina's voor de heap bevatten. Omdat de IAM extents in dezelfde volgorde vertegenwoordigt als waarin ze in de databestanden voorkomen, betekent deze structuur dat seriële heap de voortgang sequentieel door elk bestand scant. Het gebruik van de IAM-pagina's om de scanvolgorde in te stellen betekent ook dat rijen uit de heap doorgaans niet in de volgorde worden teruggegeven waarin ze zijn ingevoegd.

In de volgende afbeelding ziet u hoe de SQL Server Database Engine IAM-pagina's gebruikt om gegevensrijen op te halen in één partitie-heap.

Verwante onderwerpen

• CREATE INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• geclusterde en niet-geclusterde indexen