Heap tables in SQL Server

in de laatste post van deze blog-serie besprak ik wat een B-Tree index was en legde ik kort de geschiedenis ervan uit.

voordat we ingaan op de indexen die SQL Server gebruikt, is het belangrijk om de basis van datastructuren te leggen. De meest elementaire tabel die u kunt maken is een hoop. Een hoop is een ongesorteerde structuur van pagina ‘ s die niet aan elkaar zijn gekoppeld.

Wanneer moet u stapels gebruiken?

de beste praktijkaanbeveling is om in de meeste gevallen stapels SQL Server te vermijden. Kimberly Tripp legt een aantal van de redenen en het debat hier uit, maar Ik zal het op hoog niveau behandelen. Heaps, de meest elementaire opslagstructuur, ontbreekt functies die de prestaties voor normale (OLTP) workloads verbeteren. Sommigen beweren dat hopen beter zijn voor extract, transformatie en laden (ETL) operaties omdat er minder pagina ‘ s te onderhouden zijn tijdens het schrijven operaties (bijwerken/invoegen/verwijderen). In het bijzonder kunnen hopen sneller zijn dan een B-tree index als pagina splitsingen optreden en vele niveaus van de index moeten worden bijgewerkt. Daarnaast is de row identifier (RID), die hieronder meer wordt besproken, slechts 8 bytes. Dit kan beter presteren dan een geclusterde index met veel sleutelkolommen omdat niet-geclusterde indexen de geclusterde sleutel of RID voor elke geïndexeerde rij moeten opslaan. Meestal adviseer ik het gebruik van een geclusterde index (meer op geclusterde indexen als we doorgaan met deze serie) in plaats van een heap, maar als je ze gebruikt, gebruik ze alleen als je hebt bewezen dat ze efficiënter zullen zijn dan een geclusterde index. Na het bespreken van de structuur van een hoop, zal ik de prestaties zorgen van het gebruik van hopen schetsen.

Heapstructuur

een heap is een groep ongesorteerde pagina ‘ s die niet zijn gekoppeld. Pagina anatomie is buiten het bereik van deze serie omdat alle soorten geïndexeerde en niet-geïndexeerde tabellen dezelfde paginastructuur gebruiken, maar ik moedig u aan om hier en hier te controleren om meer te leren.

een heap bestaat uit een of meer pagina ’s met een indexallocatiekaart (iam) die verwijzen naar de pagina’ s met gegevens waaruit de heap bestaat. De enige uitzondering hierop is wanneer u een rij hebt die is bijgewerkt en niet meer in de pagina kon passen. In dat geval krijgt u een doorstuurwijzer naar de rij die is verplaatst naar een bestaande pagina met spatie of een nieuwe pagina. Het is mogelijk voor u om een keten van forwarding records produceren als de rij blijft verplaatsen door verdere operaties nodig.

heap-structure-1

afbeeldingsreferentie: MSDN

een heap heeft één rij in sys.partities per partitie en zijn index_id zal gelijk zijn aan nul. In dit record wijst de first_iam_page naar de eerste van de index allocation map (IAM) pagina ‘ s. Een IAM-pagina brengt pagina ‘ s in kaart voor elke toewijzingseenheid en beheert 4 GB-stukken van de tabel. Allocatieeenheden omvatten:

  1. IN_ROW_DATA allocation unit
  2. LOB_DATA allocation unit
  3. ROW_OVERFLOW_DATA allocation unit

de IAM-pagina bevat de standaard 96-byte header gevolgd door de IAM-header. De IAM header begint met acht slots voor gemengde extent toewijzingen en vervolgens een 8000-byte bit-map om uniforme extents toegewezen aan de tabel te lokaliseren.

een gemengde omvang is één die pagina ‘ s uit meer dan één tabel bevat. Dit wordt ook wel een gedeelde omvang genoemd. Het doel van een gemengde mate is om ruimte te besparen met tabellen die kleiner zijn dan 64 KB. Een uniforme omvang is een waar alle pagina ‘ s in de omvang tot die ene tabel behoren.

mixed-extent

afbeeldingsreferentie: de mate

om onze IAM-pagina te onderzoeken, moeten we deze eerst lokaliseren.

DBCC IND (“demo”, ” dbo.demo’,1)

dbcc-ind

met DBCC IND hebben we 17 data pagina ‘ s en 1 IAM pagina. IAM-pagina ‘ s verwijzen niet naar zichzelf, daarom zijn de IAMFID (iam file Id) en IAMPID (iam page Id) NULL. Dit betekent dat onze IAM pagina is in file Id (PageFID) 1 en is Pagina Id (PagePID) 297. DBCC pagina zal ons toelaten om onze IAM pagina te bekijken.

DBCC-TRACEON(3604);

DBCC pagina (‘demo’,1,297,3);

dbcc-page-iam

in het geel zijn er acht enkele pagina toewijzingen in onze gemengde omvang. In oranje worden de uniforme extents weergegeven in bereiken.

Prestatieimplicaties

het uitvoeren van verschillende data manipulation language (DML) – bewerkingen op een heap heeft deze effecten.

  • invoegen-nieuwe rijen kunnen met voldoende ruimte op de eerst beschikbare pagina worden geplaatst. Dus wanneer een nieuwe rij wordt ingevoegd, zal deze waarschijnlijk worden toegevoegd aan de laatste pagina.
  • UPDATE-rijen kunnen op dezelfde pagina blijven als het past in de pagina na de update, zo niet zal het worden verwijderd van de huidige pagina en geplaatst op de eerste beschikbare pagina met voldoende ruimte en een forwarding pointer wordt geschreven in de oorspronkelijke pagina.
  • verwijderen-gegevens worden niet overschreven, spatie is alleen gemarkeerd als beschikbaar voor hergebruik. Dit kan ervoor zorgen dat uw tafel aanzienlijk meer schijfruimte verbruikt dan nodig is.
  • SELECT-een tabelscan op de gehele tabel moet voor de meeste query ‘ s worden gelezen. De uitzondering hierop is wanneer er een geschikte niet-geclusterde index beschikbaar is. We zullen niet-geclusterde indexen later in deze serie bespreken.

leest

zonder enige ondersteunende index, vereist een heap een volledige tabelscan voor elke query. Dit komt omdat de fysieke structuur niet gesorteerd is en er geen pagina is die linkt naar range scans ondersteunt.

stel statistieken in IO op

DBCC IND (‘demo’, ‘ dbo.demo’,1)

selecteer COUNT (*)
uit dbo.demo

het herhalen van ons DBCC IND commando van boven herinnert ons eraan dat we 18 pagina ‘ s hebben voor deze tabel. 17 data pagina ‘ s en 1 IAM pagina.

– DBCC-IND-resultaten verwijderd omdat het hierboven al is weergegeven.
-18 rijresultaat, geeft 18 pagina ‘ s aan.

(18 betrokken Rij (n))
DBCC-uitvoering voltooid. Als DBCC foutmeldingen heeft afgedrukt, neem dan contact op met de systeembeheerder.

aantal rijen
—-
5031
(1 betrokken rij (en)

tabel “Demo”. Aftastentelling 1, logische leest 17, fysieke leest 0, read-ahead leest 0, lob logische leest 0, lob fysieke leest 0, lob read-ahead leest 0.
(1 betrokken rij (en))

let op de 17 logische reads uit onze query. Dit komt omdat de IAM pagina niet wordt geteld in de logische leest metrische en we een volledige scan van de tabel met 18 pagina ‘ s uitgevoerd.

schrijft en vooruitwijzers

zoals hierboven vermeld, zijn INSERTs en verwijderingen vrij eenvoudig. Een invoeging vindt plaats op de eerste pagina met beschikbare ruimte, zelfs als dat betekent dat de creatie van een nieuwe pagina. Verwijderingen worden uitgevoerd door de-toewijzing van de ruimte die de rij bezet was.

UPDATEs zijn waar dingen bergafwaarts gaan voor hopen. Wanneer een gegevenstype met vaste lengte wordt bijgewerkt, vindt de update plaats. Als een gegevenstype met variabele lengte wordt bijgewerkt en de lengte is toegenomen, bestaat de kans dat de rij niet meer op dezelfde pagina past. Wanneer de rij niet meer past, wordt een forwarding pointer geplaatst in de ruimte waar de oorspronkelijke rij was en de rij wordt verplaatst naar een nieuwe pagina. Dit proces heeft een negatieve invloed op de leesprestaties. Laten we het demonstreren.

oorspronkelijk al onze records in de dbo.demo tafel bevatte VARCHARs met 9 tekenreeksen.

selecteer TOP 5 *
uit dbo.demo

demo tabelgegevens

om enkele voorwaartse aanwijzers te maken zullen we al onze rijen bijwerken en de lengte van de varLen kolom vergroten.

update dbo.demo
set varLen = replicate (‘a’,100)

we hebben nu 95 pagina ’s in onze hoop omdat al onze rijen zijn verplaatst naar nieuwe pagina’ s met vooruitwijzers achtergelaten.

DBCC IND (“demo”, ” dbo.demo’,1)

dbcc-output-1

zoals je je kunt voorstellen, met hopen die volledige tabel scans, zullen we nodig hebben om veel meer pagina ‘ s te lezen voor dezelfde hoeveelheid records nu.

stel statistieken in IO op

select COUNT (*)
FROM dbo.demo
(1 betrokken rij (en))

tabel “Demo”. Aftastentelling 1, logische leest 4405, fysieke leest 0, read-ahead leest 0, lob logische leest 0, lob fysieke leest 0, lob read-ahead leest 0.

onze reads namen drastisch toe omdat de originele pagina ‘ s moesten worden gelezen en vervolgens de forward pointers moesten worden gevolgd. De performance impact en verspilling van ruimte in het geheugen en op de schijf is waarom we geclusterde indexen nodig hebben.

de volgende keer

de heap – gegevensstructuur is geen index en is vaak de slechtst presterende gegevensstructuur voor uw tabellen in SQL Server. We hebben het vandaag behandeld als een basis van begrip die nuttig zal zijn bij het bespreken van indexen. In het volgende deel van deze reeks zullen geclusterde indexen, een B+-boomstructuur voor tabelgegevens, worden behandeld.

Geef een antwoord

Het e-mailadres wordt niet gepubliceerd.