Schätzen der Arbeitsspeicheranforderungen speicheroptimierter Tabellen

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

Speicheroptimierte Tabellen benötigen ausreichend verfügbaren Arbeitsspeicher, um alle Zeilen und Indizes im Arbeitsspeicher ablegen zu können. Da Arbeitsspeicher eine endliche Ressource ist, ist es wichtig, dass Sie die Speicherauslastung auf Ihrem System verstehen und verwalten. Die Themen in diesem Abschnitt behandeln allgemeine Szenarien zur Speichernutzung und -verwaltung.

Ganz gleich, ob Sie eine neue speicheroptimierte Tabelle erstellen oder eine vorhandene datenträgerbasierte Tabelle zu einer im Arbeitsspeicher optimierten OLTP-Tabelle migrieren, es ist wichtig, eine angemessene Schätzung der Speicheranforderungen der einzelnen Tabellen zu erhalten, damit Sie den Server mit ausreichendem Arbeitsspeicher bereitstellen können. In diesem Abschnitt wird beschrieben, wie die Speichermenge geschätzt wird, die für die Daten einer speicheroptimierten Tabelle benötigt wird.

Wenn Sie die Migration von datenträgerbasierten Tabellen zu speicheroptimierten Tabellen in Betracht ziehen, lesen Sie vor dem Fortfahren in diesem Thema das Thema "Bestimmen, ob eine Tabelle oder gespeicherte Prozedur zu IN-Memory OLTP portiert werden sollte, um zu erfahren, welche Tabellen am besten migriert werden sollten. Alle Themen unter Migrieren zu In-Memory OLTP bieten eine Anleitung zum Migrieren von datenträgerbasierten zu speicheroptimierten Tabellen.

Grundlegende Anleitung zum Schätzung der Speicheranforderungen

Ab SQL Server 2016 (13.x) gibt es keine Beschränkung für die Größe von speicheroptimierten Tabellen, obwohl die Tabellen in den Arbeitsspeicher passen müssen. In SQL Server 2014 (12.x) beträgt die unterstützte Datengröße 256 GB für SCHEMA_AND_DATA Tabellen.

Die Größe einer speicheroptimierten Tabelle entspricht die Größe der Daten zuzüglich eines Mehraufwands für die Zeilenüberschriften. Beim Migrieren einer datenträgerbasierten Tabelle zu einer speicheroptimierten Tabelle wird die Größe der speicheroptimierten Tabelle ungefähr der Größe des gruppierten Index oder Heap der ursprünglichen datenträgerbasierten Tabelle entsprechen.

Indizes für speicheroptimierte Tabellen sind tendenziell kleiner als nicht gruppierte Indizes für datenträgerbasierte Tabellen. Die Größe eines nicht gruppierten Index bewegt sich in der Größenordnung von [primary key size] * [row count]. Die Größe von Hashindizes beträgt [bucket count] * 8 bytes.

Wenn eine aktive Workload vorhanden ist, ist zusätzlicher Arbeitsspeicher erforderlich, um Zeilenversionsverwaltung und verschiedene Vorgänge zu berücksichtigen. Wie viel Arbeitsspeicher in der Praxis benötigt wird, hängt von der Arbeitsauslastung ab, aber es wird sicherheitshalber empfohlen, mit dem Zweifachen der erwarteten Größe für speicheroptimierte Tabellen und Indizes zu beginnen, um dann zu beobachten, welche Speicheranforderungen sich in der Praxis ergeben. Der Mehraufwand für die Zeilenversionsverwaltung hängt immer von den Merkmalen der Arbeitsauslastung ab – insbesondere lang andauernde Transaktionen erhöhen den Mehraufwand. Bei den meisten Workloads, die größere Datenbanken verwenden (z. B. >100 GB), ist der Overhead tendenziell begrenzt (25 % oder weniger).

Detaillierte Berechnung der Speicheranforderungen

• Beispiel für eine speicheroptimierte Tabelle

• Arbeitsspeicher für die Tabelle

• Arbeitsspeicher für Indizes

• Arbeitsspeicher für die Zeilenversionsverwaltung

• Arbeitsspeicher für Tabellenvariablen

• Arbeitsspeicher für zukünftiges Wachstum

Beispiel für eine speicheroptimierte Tabelle

Betrachten Sie das folgende speicheroptimierte Tabellenschema:

CREATE TABLE t_hk
(  
  col1 int NOT NULL  PRIMARY KEY NONCLUSTERED,  

  col2 int NOT NULL  INDEX t1c2_index   
      HASH WITH (bucket_count = 5000000),  

  col3 int NOT NULL  INDEX t1c3_index   
      HASH WITH (bucket_count = 5000000),  

  col4 int NOT NULL  INDEX t1c4_index   
      HASH WITH (bucket_count = 5000000),  

  col5 int NOT NULL  INDEX t1c5_index NONCLUSTERED,  

  col6 char (50) NOT NULL,  
  col7 char (50) NOT NULL,   
  col8 char (30) NOT NULL,   
  col9 char (50) NOT NULL  

)   WITH (memory_optimized = on)  ;
GO  

Mithilfe dieses Schemas bestimmen wir den minimalen Arbeitsspeicher, der für diese speicheroptimierte Tabelle erforderlich ist.

Arbeitsspeicher für die Tabelle

Eine Zeile einer speicheroptimierten Tabelle umfasst drei Teile:

• Zeitstempel
  Zeilenkopf/Zeitstempel = 24 Bytes.

• Indexzeiger
  Für jeden Hashindex in der Tabelle weist jede Zeile einen 8-Byte-Adresszeiger auf die nächste Zeile im Index auf. Da vier Indizes vorhanden sind, weist jede Zeile 32 Byte für Indexzeiger zu (ein 8 Bytezeiger für jeden Index).

• Data
  Die Größe des Datenanteils der Zeile wird bestimmt, indem die Typgröße für jede Datenspalte summiert wird. Die Tabelle enthält fünf ganze 4-Byte-Zahlen, drei 50-Byte-Zeichenspalten und eine 30-Byte-Zeichenspalte. Daher beträgt der Datenanteil jeder Zeile 4 + 4 + 4 + 4 + 4 + 50 + 50 + 30 + 50 oder 200 Bytes.

Im Folgenden eine Größenberechnung für 5.000.000 (5 Millionen) Zeilen in einer speicheroptimierten Tabelle. Der von den Datenzeilen belegte Gesamtspeicher wird wie folgt geschätzt:

Arbeitsspeicher für die Tabellenzeilen

Aus den vorherigen Berechnungen ergibt sich für jede Zeile in der speicheroptimierten Tabelle eine Größe von 24 + 32 + 200 oder 256 Bytes. Da sie 5 Million Zeilen enthält, belegt die Tabelle 5.000.000 * 256 Bytes oder 1.280.000.000 Bytes, also ungefähr 1,28 GB.

Arbeitsspeicher für Indizes

Arbeitsspeicher für jeden Hashindex

Jeder Hashindex ist ein Hasharray aus 8-Byte-Adresszeigern. Die Größe des Arrays wird am besten anhand der Anzahl eindeutiger Indexwerte für diesen Index bestimmt. Beispielsweise ist die Anzahl eindeutiger Col2-Werte ein guter Ausgangspunkt für die Arraygröße von „t1c2_index“. Ein Hasharray, das zu groß ist, verschwendet Arbeitsspeicher. Ein zu kleines Hasharray verlangsamt die Leistung, da zu viele Konflikte durch Indexwerte entstehen, die demselben Hashindex zugeordnet sind.

Mit Hashindizes erzielen Sie sehr schnelle Übereinstimmungssuchen wie:

SELECT * FROM t_hk  
   WHERE Col2 = 3;

Nicht gruppierte Indizes liefern schneller Ergebnisse bei Bereichssuchen wie:

SELECT * FROM t_hk  
   WHERE Col2 >= 3;

Wenn Sie eine datenträgerbasierte Tabelle migrieren, können Sie folgendes verwenden, um die Anzahl eindeutiger Werte für den Index t1c2_index zu ermitteln.

SELECT COUNT(DISTINCT [Col2])  
  FROM t_hk;

Wenn Sie eine neue Tabelle erstellen, müssen Sie die Arraygröße schätzen oder Daten aus Ihren Tests vor der Bereitstellung sammeln.

Informationen dazu, wie Hashindizes in im Arbeitsspeicher optimierten OLTP-Tabellen funktionieren, finden Sie unter Hashindizes.

Festlegen der Arraygröße des Hashindexes

Die Hasharraygröße wird mit (bucket_count= value) festgelegt, wobei value eine ganze Zahl größer als 0 (null) ist. Wenn value es sich nicht um eine Potenz von 2 handelt, wird die tatsächliche bucket_count auf die nächste nächstgelegene Leistung von 2 aufgerundet. In unserer Beispieltabelle (bucket_count = 5000000), da 5.000.000 keine Leistung von 2 ist, wird die tatsächliche Bucketanzahl auf 8.388.608 (2^23) aufgerundet. Wenn Sie den vom Hasharray benötigten Arbeitsspeicher berechnen, müssen Sie diesen Wert und nicht 5.000.000 verwenden.

Daher beträgt der für jedes Hasharray erforderliche Arbeitsspeicher im Beispiel:

8,388,608 * 8 = 2^23 * 8 = 2^23 * 2^3 = 2^26 = 67.108.864 oder ca. 64 MB.

Da wir drei Hashindizes haben, beträgt der für die Hashindizes benötigte Speicher 3 * 64 MB = 192 MB.

Arbeitsspeicher für nicht gruppierte Indizes

Nicht gruppierte Indizes werden als Bw-Strukturen implementiert, deren innere Knoten den Indexwert und Zeiger auf nachfolgende Knoten enthalten. Blattknoten enthalten den Indexwert und einen Zeiger auf die Tabellenzeile im Arbeitsspeicher.

Im Gegensatz zu Hashindizes haben nicht gruppierte Indizes keine feste Bucketgröße. Der Index vergrößert bzw. verkleinert sich dynamisch mit den Daten.

Der von nicht gruppierten Indizes benötigte Arbeitsspeicher kann wie folgt berechnet werden:

• Arbeitsspeicher, der Nichtblattknoten zugeordnet ist
  Bei einer typischen Konfiguration hat der Arbeitsspeicher, der Nichtblattknotenden zugeordnet wird, einen geringen prozentualen Anteil am gesamten vom Index belegten Arbeitsspeicher, der so klein ist, dass er problemlos ignoriert werden kann.

• Arbeitsspeicher für Blattknoten
  Die Blattknoten weisen eine Zeile für jeden eindeutigen Schlüssel in der Tabelle auf, die auf die Datenzeilen mit dem eindeutigen Schlüssel verweist. Wenn Sie mehrere Zeilen mit demselben Schlüssel haben (d. h., Sie haben einen nicht eindeutigen nicht gruppierten Index), gibt es nur eine Zeile im Indexblattknoten, die auf eine der Zeilen mit den anderen Zeilen zeigt, die miteinander verknüpft sind. Folglich kann für den insgesamt erforderlichen Arbeitsspeicher wie folgt ein Näherungswert ermittelt werden:

  • memoryForNonClusteredIndex = (pointerSize + sum(keyColumnDataTypeSizes)) * rowsWithUniqueKeys

Nicht gruppierte Indizes eignen sich wie in der folgenden Abfrage veranschaulicht am besten für Bereichssuchen:

SELECT * FROM t_hk  
   WHERE c2 > 5;  

Arbeitsspeicher für die Zeilenversionsverwaltung

Um Sperren zu vermeiden, nutzt In-Memory OLTP beim Aktualisieren oder Löschen von Zeilen optimistische Nebenläufigkeit. Dies bedeutet, dass beim Aktualisieren einer Zeile eine andere Version der Zeile erstellt wird. Außerdem erfolgen Löschungen auf logischer Ebene – die vorhandene Zeile wird als gelöscht markiert, aber nicht sofort entfernt. Das System hält alte Zeilenversionen (einschließlich gelöschter Zeilen) verfügbar, bis alle Transaktionen, die eine bestimmte Version möglicherweise verwenden, abgeschlossen wurden.

Da es zu jedem Zeitpunkt viele weitere Zeilen im Arbeitsspeicher gibt, die auf den Garbage Collection-Zyklus warten, um den Speicher freizugeben, müssen Sie über genügend Arbeitsspeicher verfügen, um diese anderen Zeilen aufzunehmen.

Die Anzahl der zusätzlichen Zeilen kann geschätzt werden, indem die Spitzenanzahl von Zeilenaktualisierungen und Löschungen pro Sekunde berechnet wird, und dann mit der Anzahl der Sekunden multipliziert wird, die die längste Transaktion dauert (mindestens 1).

Anschließend wird dieser Wert mit der Zeilengröße multipliziert, um die Anzahl der Bytes zu erhalten, die für die Zeilenversionsverwaltung benötigt werden.

rowVersions = durationOfLongestTransactionInSeconds * peakNumberOfRowUpdatesOrDeletesPerSecond

Danach wird der Arbeitsspeicherbedarf für veraltete Zeilen geschätzt, indem die Anzahl veralteter Zeilen mit der Größe einer Zeile einer speicheroptimierten Tabelle multipliziert wird (siehe Arbeitsspeicher für die Tabelle oben).

memoryForRowVersions = rowVersions * rowSize

Arbeitsspeicher für Tabellenvariablen

Der für eine Tabellenvariable verwendete Arbeitsspeicher wird erst freigegeben, wenn die Tabellenvariable den Gültigkeitsbereich verlässt. Gelöschte Zeilen, einschließlich zeilen, die als Teil einer Aktualisierung gelöscht wurden, aus einer Tabellenvariablen unterliegen nicht der Garbage Collection. Es wird erst Arbeitsspeicher freigegeben, wenn die Tabellenvariable den Bereich verlässt.

Tabellenvariablen, die in einem umfangreichen SQL-Batch und nicht in einem Prozedurbereich definiert und in zahlreichen Transaktionen verwendet werden, können viel Arbeitsspeicher beanspruchen. Da sie nicht garbage collection sind, können gelöschte Zeilen in einer Tabellenvariable viel Arbeitsspeicher beanspruchen und die Leistung beeinträchtigen, da Lesevorgänge über die gelöschten Zeilen überprüft werden müssen.

Arbeitsspeicher für zukünftiges Wachstum

Mit den oben aufgeführten Berechnungen wird der Arbeitsspeicherbedarf für die derzeit bestehende Tabelle geschätzt. Zusätzlich zu diesem Arbeitsspeicher müssen Sie einplanen, dass die Tabelle anwächst, und ausreichend Arbeitsspeicher für zukünftiges Wachstum vorsehen. Wenn Sie beispielsweise ein zehnprozentiges Wachstum erwarten, müssen Sie die oben ermittelten Ergebnisse mit 1,1 multiplizieren, um den insgesamt erforderlichen Arbeitsspeicher für die Tabelle zu erhalten.

Siehe auch

Migrieren zu In-Memory OLTP