Tabellen- und Zeilengröße in speicheroptimierten Tabellen

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Vor SQL Server 2016 (13.x) konnte die Größe von Daten in Zeilen einer speicheroptimierten Tabelle nicht größer als 8.060 Bytes sein. Jedoch ist es in SQL Server 2016 (13.x) und Azure SQL-Datenbank möglich, eine speicheroptimierte Tabelle mit mehreren großen Spalten (z. B. mehreren varbinary(8000)-Spalten) und LOB-Spalten (z. B. varbinary(max), varchar(max) und nvarchar(max)) zu erstellen sowie Vorgänge für sie mithilfe nativ kompilierter Transact-SQL(T-SQL)-Module und Tabellentypen auszuführen.

Spalten, die nicht in das Zeilenlimit von 8 060 Bytes passen, werden außerhalb der Zeile in einer separaten internen Tabelle platziert. Jede Spalte außerhalb einer Zeile verfügt über eine entsprechende interne Tabelle, die wiederum über einen einzelnen, nicht gruppierten Index verfügt. Informationen zu diesen internen Tabellen für Spalten außerhalb von Zeilen finden Sie unter sys.memory_optimized_tables_internal_attributes.

Es gibt bestimmte Szenarios, in denen es nützlich ist, die Größe der Zeile und der Tabelle zu berechnen:

• Wie viel Arbeitsspeicher eine Tabelle verbraucht.

  • Der Arbeitsspeicher, der für die Tabelle verwendet wird, kann nicht genau berechnet werden. Viele Faktoren beeinflussen den verwendeten Arbeitsspeicher. Faktoren wie seitenbasierte Speicherzuweisung, Lokalität, Caching und Pufferung. Darüber hinaus mehrere Versionen von Zeilen, denen entweder aktive Transaktionen zugeordnet sind oder die auf die Garbage Collection warten.

  • Die minimale Größe, die für die Daten und Indizes in der Tabelle erforderlich ist, wird durch die Berechnung von <table size> angegeben, wie weiter unten in diesem Artikel beschrieben.

  • Da die Berechnung der Arbeitsspeicherverwendung bestenfalls eine Näherung darstellt, ist es ratsam, auch eine Kapazitätsplanung in den Bereitstellungsplänen vorzusehen.

• Die Datengröße einer Zeile und die Frage, ob die maximale Zeilengröße von 8 060 Bytes eingehalten wird. Um diese Fragen zu beantworten, verwenden Sie das Berechnungsergebnis für <row body size>, das weiter unten in diesem Artikel erläutert wird.

Eine speicheroptimierte Tabelle besteht aus einer Auflistung von Zeilen und Indizes, die Zeiger auf die Zeilen enthalten. Die folgende Abbildung zeigt eine Tabelle mit Indizes und Zeilen, die wiederum Zeilenüberschriften und Inhaltsbereiche haben:

Berechnen der Tabellengröße

Die Größe einer Tabelle im Arbeitsspeicher in Bytes wird wie folgt berechnet:

<table size> = <size of index 1> + ... + <size of index n> + (<row size> * <row count>)

Die Größe eines Hashindexes wird bei Erstellung der Tabelle festgelegt und hängt von der tatsächlichen Bucketanzahl ab. Die in der Indexdefinition angegebene bucket_count wird auf die nächste Zweierpotenz aufgerundet, um die tatsächliche Anzahl der Buckets zu erhalten. Wenn das angegebene bucket_count beispielsweise 100.000 beträgt, ist die tatsächliche Anzahl der Buckets für den Index 131.072.

<hash index size> = 8 * <actual bucket count>

Die Größe eines nicht gruppierten Index bewegt sich in der Größenordnung von <row count> * <index key size>.

Die Zeilengröße wird berechnet, indem die Überschrift und der Text addiert werden:

<row size> = <row header size> + <actual row body size>
<row header size> = 24 + 8 * <number of indexes>

Größe des Zeileninhalts berechnen

Die Zeilen in einer speicheroptimierten Tabelle verfügen über folgende Komponenten:

• Die Zeilenüberschrift enthält den Zeitstempel, der erforderlich ist, um Zeilenversionsverwaltung zu implementieren. Die Zeilenüberschrift enthält auch den Indexzeiger, um die Zeilenverkettung in den Hashbuckets zu implementieren (wie zuvor beschrieben).

• Der Zeilenkörper enthält die eigentlichen Spaltendaten, dazu gehören einige Hilfsinformationen wie das NULL-Array für Spalten, die NULL-Werte zulassen, und das Offset-Array für Datentypen variabler Länge.

Die folgende Abbildung veranschaulicht die Zeilenstruktur für eine Tabelle mit zwei Indizes:

Die Zeitstempel für Beginn und Ende geben den Zeitraum an, in dem eine bestimmte Zeilenversion gültig ist. Für Transaktionen, die in diesem Intervall beginnen, ist diese Zeilenversion sichtbar. Weitere Informationen finden Sie unter Transaktionen mit speicheroptimierten Tabellen.

Die Indexzeiger zeigen auf die nächste Zeile in der Kette, die zu dem Hash-Bucket gehört. Die folgende Abbildung veranschaulicht die Struktur einer Tabelle mit zwei Spalten (Name, Ort) und mit zwei Indizes: einem für den Spaltennamen und einen für den Spaltenort.

In dieser Abbildung werden die Namen John und Jane zum ersten Hashbucket hinzugefügt. Susan wird dem zweiten Hashbucket hinzugefügt. Die Städte Beijing und Bogota werden dem ersten Hashbucket hinzugefügt. Paris und Prague werden dem zweiten Hashbucket hinzugefügt.

Somit ergeben sie folgende Ketten für den Hashindex für Namen:

• Erster Bucket: (John, Beijing); (John, Paris); (Jane, Prague)
• Zweiter Behälter: (Susan, Bogota)

Die Ketten für den Index für die Stadt lauten wie folgt:

• Erster Bucket: (John, Beijing), (Susan, Bogota)
• Zweiter Bucket: (John, Paris), (Jane, Prague)

Ein ∞-Endzeitstempel (unendlich) bedeutet, dass es sich um die derzeit gültige Version der Zeile handelt. Die Zeile wurde nicht aktualisiert oder gelöscht, seitdem diese Zeilenversion geschrieben wurde.

Für einen Zeitpunkt größer als 200 enthält die Tabelle die folgenden Zeilen:

Allerdings wird jeder aktiven Transaktion mit Startzeit 100 die folgende Version der Tabelle angezeigt:

Die Berechnung von <row body size> wird in der folgenden Tabelle erläutert.

Es gibt zwei verschiedene Berechnungen für die Zeilentextgröße: die berechnete Größe und die tatsächliche Größe:

• Die berechnete Größe, bezeichnet mit computed row body size, wird verwendet, um festzustellen, ob die Zeilengrößeneinschränkung von 8.060 Bytes überschritten wird.

• Die tatsächliche Größe, bezeichnet mit actual row body size, ist die tatsächliche Speichergröße des Zeilentexts im Arbeitsspeicher und in den Prüfpunktdateien.

Sowohl die berechnete Größe des Zeilenkörpers als auch die tatsächliche Größe des Zeilenkörpers werden ähnlich berechnet. Der einzige Unterschied ist die Berechnung der Größe von (n)varchar(i) und varbinary(i)-Spalten, wie unten in der folgenden Tabelle dargestellt. Bei der berechneten Zeilentextgröße wird die deklarierte Größe i als Größe der Spalte verwendet, während für die tatsächliche Zeilentextgröße die tatsächliche Größe der Daten verwendet wird.

Die folgende Tabelle beschreibt, wie die Größe des Zeilenkörpers berechnet wird, gegeben als <actual row body size> = SUM(<size of shallow types>) + 2 + 2 * <number of deep type columns>.

Beispiel: Tabellen- und Zeilengrößenberechnung

Bei Hash-Indizes wird die tatsächliche Anzahl der Buckets auf die nächsthöhere Zweierpotenz aufgerundet. Wenn der angegebene bucket_count beispielsweise 100000 ist, beträgt die tatsächliche Anzahl der Buckets für den Index 131072.

Betrachten Sie eine Orders-Tabelle mit folgender Definition:

CREATE TABLE dbo.Orders (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL INDEX IX_CustomerID HASH WITH (BUCKET_COUNT = 10000),
    OrderDate DATETIME NOT NULL,
    OrderDescription NVARCHAR(1000)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

Diese Tabelle weist einen Hashindex und einen nicht gruppierten Index (den Primärschlüssel) auf. Außerdem enthält sie drei Spalten mit fester Länge und eine Spalte mit variabler Länge, wobei eine der Spalten nullable (OrderDescription) ist. Angenommen, die Tabelle Orders hat 8.379 Zeilen, und die durchschnittliche Länge der Werte in der Spalte OrderDescription ist 78 Zeichen.

Um die Tabellengröße zu ermitteln, ermitteln Sie zuerst die Größe der Indizes. Der bucket_count für beide Indizes wird mit 10000 angegeben. Dies wird auf die nächste Potenz von 2 aufgerundet: 16384. Daher ergibt sich die Gesamtgröße der Indizes für die Orders-Tabelle wie folgt:

8 * 16384 = 131072 bytes

Was bleibt, ist die Tabellendatengröße:

<row size> * <row count> = <row size> * 8379

(Die Beispieltabelle enthält 8379 Zeilen.) Jetzt haben wir:

<row size> = <row header size> + <actual row body size>
<row header size> = 24 + 8 * <number of indices> = 24 + 8 * 1 = 32 bytes

Als Nächstes berechnen wir <actual row body size>:

• Spalten flacher Typen:

  SUM(<size of shallow types>) = 4 <int> + 4 <int> + 8 <datetime> = 16
  

• Das Padding der flachen Spalte ist 0, da die Gesamtgröße der flachen Spalte gerade ist.

• Offsetarray für Spalten tiefer Typen:

  2 + 2 * <number of deep type columns> = 2 + 2 * 1 = 4
  

• NULL Array = 1

• NULL Array-Auffüllung = 1, da die Größe des NULL-Arrays ungerade ist und eine Spalte des Typs Deep vorhanden ist.

• Auffüllen

  • 8 ist die größte Ausrichtungsanforderung
  • Die bisherige Größe ist 16 + 0 + 4 + 1 + 1 = 22
  • Das nächste Vielfache von 8 ist 24
  • Die gesamte Auffüllung beträgt 24 - 22 = 2 Bytes

• Es sind keine Spalten tiefer Typen mit fester Länge vorhanden (Spalten tiefer Typen mit fester Länge: 0.).

• Die tatsächliche Größe der Spalte vom Typ deep ist 2 * 78 = 156. Die einzelne Spalte tiefen Typs OrderDescription hat den Typ nvarchar.

<actual row body size> = 24 + 156 = 180 bytes

Um die Berechnung abzuschließen:

<row size> = 32 + 180 = 212 bytes
<table size> = 8 * 16384 + 212 * 8379 = 131072 + 1776348 = 1907420

Die gesamte Tabellengröße im Arbeitsspeicher entspricht daher ungefähr 2 MB. Dies berücksichtigt nicht den möglichen Mehraufwand, der durch die Speicherzuordnung und die für die Transaktionen, die auf diese Tabelle zugreifen, erforderliche Zeilenversionsverwaltung entsteht.

Der tatsächliche Arbeitsspeicher, der dieser Tabelle zugeordnet ist und von ihr und den zugehörigen Indizes verwendet wird, kann über die folgende Abfrage abgerufen werden:

SELECT * FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id = object_id('dbo.Orders');

Einschränkungen für Spalten außerhalb der Zeile

Bestimmte Beschränkungen und Vorbehalte bei der Verwendung von zeilenüberragenden Spalten in einer speicheroptimierten Tabelle sind wie folgt aufgeführt:

• Wenn es einen Columnstore-Index für eine speicheroptimierte Tabelle gibt, müssen alle Spalten in Zeilen passen.
• Alle Indexschlüsselspalten müssen in der Zeile gespeichert werden. Wenn eine Indexschlüsselspalte nicht in die Zeile passt, schlägt das Hinzufügen eines Index fehl.
• Vorbehalte für das Ändern einer speicheroptimierten Tabelle mit Spalten außerhalb von Zeilen.
• Für LOBs entspricht die Größenbeschränkung der von datenträgerbasierten Tabellen (2-GB-Grenze für LOB-Werte).
• Für eine optimale Leistung empfehlen wir, dass die meisten Spalten innerhalb von 8 060 Bytes liegen sollten.
• Daten außerhalb der Zeilen können eine übermäßige Speicher- und/oder Festplattennutzung verursachen.

Zugehöriger Inhalt

• Beispieldatenbank für IN-Memory-OLTP