Heap-Tabellen in SQL Server

Im letzten Beitrag dieser Blogserie habe ich diskutiert, was ein B-Tree-Index war, und kurz seine Geschichte erklärt.

Bevor wir uns mit den Indizes befassen, die SQL Server verwendet, ist es wichtig, die Grundlage für Datenstrukturen festzulegen. Die grundlegendste Tabelle, die Sie erstellen können, ist ein Heap. Ein Heap ist eine unsortierte Struktur von Seiten, die nicht miteinander verknüpft sind.

Wann sollten Sie Heaps verwenden?

In den meisten Fällen wird empfohlen, SQL Server-Heaps zu vermeiden. Kimberly Tripp erklärt hier einige der Gründe und Debatten, aber ich werde auf hoher Ebene behandeln. Heaps, der grundlegendsten Speicherstruktur, fehlen Funktionen, die die Leistung für normale (OLTP-) Workloads verbessern. Einige argumentieren, dass Heaps für ETL-Vorgänge (Extract, Transformation, and Loading) besser geeignet sind, da während der Schreibvorgänge (UPDATE / INSERT / DELETE) weniger Seiten verwaltet werden müssen. Insbesondere können Heaps schneller sein als ein B-Tree-Index, wenn Seitenaufteilungen auftreten und viele Ebenen des Index aktualisiert werden müssen. Darüber hinaus beträgt der Zeilenbezeichner (RID), der weiter unten näher erläutert wird, nur 8 Byte. Dies kann eine bessere Leistung erbringen als ein gruppierter Index mit vielen Schlüsselspalten, da nicht gruppierte Indizes den gruppierten Schlüssel oder das RID für jede indizierte Zeile speichern müssen. Normalerweise empfehle ich die Verwendung eines Clustered Index (mehr zu Clustered Indizes, wenn wir diese Serie fortsetzen) anstelle eines Heaps, aber wenn Sie sie verwenden, verwenden Sie sie nur, wenn Sie bewiesen haben, dass sie effizienter sind als ein Clustered Index. Nachdem ich die Struktur eines Heaps besprochen habe, werde ich die Leistungsprobleme bei der Verwendung von Heaps skizzieren.

Heap-Struktur

Ein Heap ist eine Gruppierung von unsortierten Seiten, die nicht verlinkt sind. Die Seitenanatomie ist für diese Serie nicht möglich, da alle Arten von indizierten und nicht indizierten Tabellen dieselbe Seitenstruktur verwenden.

Ein Heap besteht aus einer oder mehreren IAM-Seiten (Index Allocation Map), die auf die Datenseiten verweisen, aus denen der Heap besteht. Die einzige Ausnahme ist, wenn Sie eine Zeile haben, die aktualisiert wurde und nicht mehr in ihre Seite passen konnte. In diesem Fall erhalten Sie einen Weiterleitungszeiger auf die Zeile, die auf eine vorhandene Seite mit Leerzeichen oder eine neue Seite verschoben wurde. Sie können eine Kette von Weiterleitungsdatensätzen erstellen, wenn die Zeile weiterhin durch weitere Vorgänge verschoben werden muss.

heap-structure-1

Bildreferenz: MSDN

Ein Heap hat eine Zeile in sys.partitionen pro Partition und ihre index_id sind gleich Null. In diesem Datensatz zeigt die first_iam_page auf die erste der IAM-Seiten (Index Allocation Map). Eine IAM-Seite ordnet Seiten für jede Zuordnungseinheit zu und verwaltet 4 GB-Teile der Tabelle. Zuteilungseinheiten umfassen:

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

Die IAM-Seite enthält den Standard-96-Byte-Header, gefolgt vom IAM-Header. Der IAM-Header beginnt mit acht Steckplätzen für gemischte Extent-Zuordnungen und einer 8000-Byte-Bitmap zum Auffinden einheitlicher Extents, die der Tabelle zugewiesen sind.

Eine gemischte Ausdehnung enthält Seiten aus mehr als einer Tabelle. Dies wird auch als Shared Extent bezeichnet. Der Zweck einer gemischten Erweiterung besteht darin, Platz mit Tabellen zu sparen, die kleiner als 64 KB sind. Eine einheitliche Ausdehnung ist eine, bei der alle Seiten in der Ausdehnung zu dieser einzelnen Tabelle gehören.

mixed-extent

Bildreferenz: Der Umfang

Um unsere IAM-Seite zu untersuchen, müssen wir sie zuerst lokalisieren.

DBCC IND (‚demo‘,’dbo.vorführung‘,1)

dbcc-ind

Mit DBCC IND haben wir 17 Datenseiten und 1 IAM-Seite. IAM-Seiten verweisen nicht selbst, daher sind IAMFID (IAM-Datei-ID) und IAMPID (IAM-Seiten-ID) NULL. Dies bedeutet, dass unsere IAM-Seite die Datei-ID (PageFID) 1 und die Seiten-ID (PagePID) 297 hat. Die DBCC-SEITE ermöglicht es uns, unsere IAM-Seite anzuzeigen.

DBCC TRACEON(3604);

DBCC-SEITE (‚Demo‘,1,297,3);

dbcc-page-iam

In GELB gibt es acht Einzelseitenzuordnungen in unserem gemischten Umfang. In ORANGE werden die einheitlichen Ausdehnungen in Bereichen angezeigt.

Auswirkungen auf die Leistung

Das Ausführen verschiedener DML-Operationen (Data Manipulation Language) auf einem Heap hat diese Auswirkungen.

  • EINFÜGEN – Neue Zeilen können auf der ersten verfügbaren Seite mit ausreichend Platz platziert werden. Wenn also eine neue Zeile eingefügt wird, wird sie wahrscheinlich zur letzten Seite hinzugefügt.
  • UPDATE – Zeilen können auf derselben Seite bleiben, wenn sie nach dem Update in die Seite passen, andernfalls werden sie von der aktuellen Seite entfernt und mit ausreichend Platz auf der ersten verfügbaren Seite platziert.
  • LÖSCHEN – Daten werden nicht überschrieben, Speicherplatz wird nur als zur Wiederverwendung verfügbar markiert. Dies kann dazu führen, dass Ihre Tabelle erheblich mehr Speicherplatz belegt als erforderlich.
  • SELECT – Für die meisten Abfragen muss ein Tabellenscan für die gesamte Tabelle gelesen werden. Die Ausnahme ist, wenn ein geeigneter nicht gruppierter Index verfügbar ist. Wir werden später in dieser Serie auf nicht gruppierte Indizes eingehen.

Liest

Ohne unterstützenden Index erfordert ein Heap für jede Abfrage einen vollständigen Tabellenscan. Dies liegt daran, dass die physische Struktur nicht sortiert ist und keine Seitenverknüpfung zur Unterstützung von Bereichsscans vorhanden ist.

STATISTIK-E/A AUF

DBCC IND (‚demo‘,’dbo.vorführung‘,1)

WÄHLEN SIE COUNT(*)
AUS dbo.demo

Das Wiederholen unseres DBCC IND Befehls von oben erinnert uns daran, dass wir 18 Seiten für diese Tabelle haben. 17 Datenseiten und 1 IAM-Seite.

– DBCC IND-Ergebnisse wurden entfernt, da sie bereits oben angezeigt werden.
-18 Zeilenergebnis, zeigt 18 Seiten an.

(18 Zeile(n) betroffen)
DBCC-Ausführung abgeschlossen. Wenn DBCC Fehlermeldungen ausgibt, wenden Sie sich an Ihren Systemadministrator.

Zeilenanzahl
—-
5031
(1 zeile(n) betroffen)

Tabelle ‚Demo‘. Scan zählen 1, logische liest 17, physikalische liest 0, lesen-ahead liest 0, lob logische liest 0, lob physikalische liest 0, lob lesen-ahead liest 0.
(1 Zeile (n) betroffen)

Beachten Sie die 17 logischen Lesevorgänge aus unserer Abfrage. Dies liegt daran, dass die IAM-Seite nicht in der Metrik logical Reads gezählt wird und wir einen vollständigen Scan der Tabelle mit 18 Seiten durchgeführt haben.

Schreibt und leitet Zeiger weiter

Wie oben erwähnt, sind Einfügungen und Löschungen ziemlich einfach. Eine EINFÜGUNG erfolgt auf der ersten Seite mit verfügbarem Speicherplatz, auch wenn dies die Erstellung einer neuen Seite bedeutet. Löschungen werden durchgeführt, indem der Speicherplatz, den die Zeile belegt hat, freigegeben wird.

Updates sind, wo die Dinge bergab für Haufen gehen. Wenn ein Datentyp mit fester Länge aktualisiert wird, erfolgt die Aktualisierung an Ort und Stelle. Wenn ein Datentyp mit variabler Länge aktualisiert wird und die Länge zugenommen hat, besteht die Möglichkeit, dass die Zeile nicht mehr in dieselbe Seite passt. Wenn die Zeile nicht mehr passt, wird ein Weiterleitungszeiger in den Bereich eingefügt, in dem sich die ursprüngliche Zeile befand, und die Zeile wird auf eine neue Seite verschoben. Dieser Vorgang wirkt sich negativ auf die Leseleistung aus. Lassen Sie uns demonstrieren.

Ursprünglich alle unsere Datensätze im dbo.die Demo-Tabelle enthielt VARCHARs mit 9 Zeichenfolgen.

WÄHLEN SIE TOP 5 *
AUS dbo.demo

demo table data

Um einige Vorwärtszeiger zu erstellen, aktualisieren wir alle unsere Zeilen und erhöhen die Länge der varLen Spalte.

dbo aktualisieren.demo
set varLen = replizieren(‚a‘,100)

Wir haben jetzt 95 Seiten in unserem Heap, da alle unsere Zeilen auf neue Seiten verschoben wurden und Vorwärtszeiger zurückbleiben.

DBCC IND (‚demo‘,’dbo.vorführung‘,1)

dbcc-output-1

Wie Sie sich vorstellen können, müssen wir bei Heaps, die vollständige Tabellenscans erfordern, jetzt viel mehr Seiten für die gleiche Anzahl von Datensätzen lesen.

SET STATISTICS IO ON

WÄHLEN SIE COUNT(*)
AUS dbo.demo
(1 Zeile(n) betroffen)

Tabelle ‚Demo‘. Scan zählen 1, logische liest 4405, physikalische liest 0, read-ahead liest 0, lob logische liest 0, lob physikalische liest 0, lob read-ahead liest 0.

Unsere Lesevorgänge nahmen drastisch zu, da die Originalseiten gelesen und dann die Vorwärtszeiger befolgt werden mussten. Die Auswirkungen auf die Leistung und die Verschwendung von Speicherplatz im Speicher und auf der Festplatte sind der Grund, warum wir gruppierte Indizes benötigen.

Nächstes Mal

Die Heap-Datenstruktur ist kein Index und häufig die Datenstruktur mit der schlechtesten Leistung für Ihre Tabellen in SQL Server. Wir haben es heute als Grundlage des Verständnisses behandelt, das bei der Diskussion von Indizes nützlich sein wird. Im nächsten Teil dieser Serie werden Clustered Indizes, eine B + -Baumstruktur für Tabellendaten, behandelt.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.