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 nicht unbegrenzt verfügbar ist, sollten Sie die Arbeitsspeichernutzung in Ihrem System kennen und effizient verwalten. Die Themen in diesem Abschnitt behandeln allgemeine Szenarien zur Speichernutzung und -verwaltung.

Es ist wichtig, eine angemessene Schätzung der Speicheranforderungen jeder speicheroptimierten Tabelle zu haben, damit Sie den Server mit ausreichendem Arbeitsspeicher bereitstellen können. Dies gilt sowohl für neue Tabellen als auch für Tabellen, die aus datenträgerbasierten Tabellen migriert wurden. In diesem Abschnitt wird beschrieben, wie die Speichermenge geschätzt wird, die für die Daten einer speicheroptimierten Tabelle benötigt wird.

Wenn Sie eine Migration von datenträgerbasierten Tabellen zu speicheroptimierten Tabellen in Betracht ziehen, finden Sie informationen dazu, ob eine Tabelle oder gespeicherte Prozedur zu In-Memory OLTP portiert werden soll , um zu erfahren, welche Tabellen am besten migriert werden sollen. 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

In SQL Server 2016 (13.x) und höheren Versionen 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. Die Größe der speicheroptimierten Tabelle entspricht ungefähr der Größe des gruppierten Indexes oder Heaps der ursprünglichen datenträgerbasierten Tabelle.

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 Arbeitsauslastung vorliegt, ist zusätzlicher Arbeitsspeicher erforderlich, um die Zeilenversionsverwaltung und verschiedene Vorgänge zu berücksichtigen. Die erforderliche Arbeitsspeichermenge hängt von der Workload ab, aber um sicher zu sein, besteht die Empfehlung darin, zwei mal mit der erwarteten Größe von speicheroptimierten Tabellen und Indizes zu beginnen und die tatsächliche Speicherauslastung zu beobachten. 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. größer als 100 GB), ist der Mehraufwand tendenziell begrenzt (25 Prozent oder weniger).

Weitere Informationen zum potenziellen Arbeitsspeicheraufwand im In-Memory OLTP-Modul finden Sie unter Speicherfragmentierung.

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  

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

Arbeitsspeicher für die Tabelle

Eine speicheroptimierte Tabellenzeile weist drei Teile auf:

• 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-Byte-Zeiger für jeden Index).

• Daten
  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 wir 5 Millionen Zeilen haben, verbraucht die Tabelle 5.000.000 * 256 Bytes oder 1.280.000.000 Byte - ca. 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 durch die Anzahl eindeutiger Indexwerte für diesen Index bestimmt. Im aktuellen Beispiel ist die Anzahl eindeutiger Col2-Werte ein guter Ausgangspunkt für die Arraygröße für die t1c2_index. Durch ein übergroßes Hasharray wird Arbeitsspeicher vergeudet. Ein Hash-Array, das zu klein ist, verlangsamt die Leistung, da es zu viele Kollisionen durch Indexwerte gibt, die auf denselben Indexeintrag abgebildet werden.

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;

Beim Migrieren einer datenträgerbasierten Tabelle können Sie die Anzahl der eindeutigen Werte für den Index "t1c2_index" wie folgt bestimmen.

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 zur Funktionsweise von Hashindizes in speicheroptimierten In-Memory-OLTP-Tabellen 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 keine Zweierpotenz ist, wird der tatsächliche bucket_count-Wert auf die nächste Zweierpotenz aufgerundet. In der Beispieltabelle (bucket_count = 5000000) wird die tatsächliche Bucketanzahl auf 8.388.608 (2^23) aufgerundet, weil 5.000.000 keiner Zweierpotenz entspricht. 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 ungefähr 64 MB.

Da wir über drei Hashindizes verfügen, ist der für die Hashindizes erforderliche Arbeitsspeicher 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.

Nicht gruppierte Indizes verfügen im Gegensatz Hashindizes nicht über eine 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 Nicht-Blattknoten zugeordnet ist
  Bei einer typischen Konfiguration ist der Speicher, der nicht-blattförmigen Knoten zugeordnet ist, ein kleiner Teil des gesamten vom Index genutzten Speichers. 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 gruppierten Index), gibt es nur eine Zeile im Indexblattknoten, der 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:

  • SpeicherFürNichtGruppiertenIndex = (Zeigergröße + Summe(Schlüsselspaltendatentypgrößen)) * ZeilenMitEindeutigenSchlüsseln

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 bei jedem Zeilenupdate 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) bereit, bis alle Transaktionen abgeschlossen sind, die diese Version möglicherweise verwenden könnten.

Da der Arbeitsspeicher jederzeit weitaus mehr Zeilen enthalten kann, während darauf gewartet wird, dass der von den Zeilen belegte Speicher im nächsten Zyklus der Garbage Collection freigegeben wird, benötigen Sie genügend Arbeitsspeicher für diese zusätzlichen Zeilen.

Die Anzahl der zusätzlichen Zeilen kann geschätzt werden, indem Sie die Höchstanzahl von Zeilenupdates und -löschungen pro Sekunde berechnen und den Wert mit der Anzahl von Sekunden multiplizieren, die die längste Transaktion dauert (mindestens eine Sekunde).

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

Der Speicherbedarf für veraltete Zeilen wird dann geschätzt, indem die Anzahl veralteter Zeilen mit der Größe einer speicheroptimierten Tabellenzeile multipliziert wird. Weitere Informationen finden Sie unter "Arbeitsspeicher für die Tabelle".

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 der während eines Updates gelöschten Zeilen, aus einer Tabellenvariablen unterliegen nicht der Garbage Collection. Es wird erst Arbeitsspeicher freigegeben, wenn die Tabellenvariable den Bereich verlässt.

Tabellenvariablen, die in einem großen SQL-Batch und nicht in einer gespeicherten Prozedur definiert sind und in vielen Transaktionen verwendet werden, können eine große Menge Arbeitsspeicher beanspruchen. Da sie nicht von der Garbage Collection bereinigt werden, können gelöschte Zeilen in einer Tabellenvariablen viel Arbeitsspeicher beanspruchen und die Leistung beeinträchtigen, da Lesevorgänge auch die gelöschten Zeilen durchlaufen müssen.

Arbeitsspeicher für zukünftiges Wachstum

Die vorherigen Berechnungen schätzen die Speicheranforderungen für die Tabelle, wie sie derzeit vorhanden ist. 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 z. B. 10% Wachstum erwarten, müssen Sie die vorherigen Ergebnisse um 1,1 multizipieren, um den gesamt für ihre Tabelle benötigten Arbeitsspeicher zu erhalten.

Speicherfragmentierung

Um den Aufwand von Speicherzuweisungsaufrufen zu vermeiden und die Leistung zu verbessern, fordert das In-Memory OLTP-Modul immer Speicher vom SQL Server-Betriebssystem (SQLOS) mit 64-KB-Blöcken an, die als Superblocks bezeichnet werden.

Jeder Superblock enthält Speicherzuordnungen nur innerhalb eines bestimmten Größenbereichs, der als Größenklasse bezeichnet wird. Beispielsweise kann Superblock A Speicherzuweisungen in der 1-16 Byte-Größenklasse haben, während Superblock B Speicherzuweisungen in der 17-32 Byte-Größenklasse usw. haben kann.

Superblocks werden standardmäßig auch durch logische CPU partitioniert. Das bedeutet, dass für jede logische CPU eine separate Gruppe von Superblocks vorhanden ist, die nach Größenklasse weiter aufgeschlüsselt werden. Dies reduziert die Speicherzuweisungskonflikt zwischen Anforderungen, die auf verschiedenen CPUs ausgeführt werden.

Wenn das In-Memory OLTP-Modul eine neue Speicherzuweisung vornimmt, versucht es zunächst, freien Speicher in einem vorhandenen Superblock für die angeforderte Größenklasse und für die CPU-Verarbeitung der Anforderung zu finden. Wenn dieser Versuch erfolgreich ist, erhöht sich der Wert in der used_bytes Spalte in sys.dm_xtp_system_memory_consumers für einen bestimmten Speicherverbraucher um die angeforderte Arbeitsspeichergröße, der Wert in der allocated_bytes Spalte bleibt jedoch unverändert.

Wenn in vorhandenen Superblocks kein freier Arbeitsspeicher verfügbar ist, wird ein neuer Superblock zugewiesen, wobei der Wert in der used_bytes-Spalte um die angeforderte Speichergröße erhöht wird, während der Wert in der allocated_bytes-Spalte um 64 KB erhöht wird.

Im Laufe der Zeit, wenn Arbeitsspeicher in Superblocks zugeordnet und freigegeben wird, kann die Gesamtmenge des von der In-Memory OLTP-Engine verbrauchten Arbeitsspeichers erheblich größer werden als die Menge des verwendeten Arbeitsspeichers. Mit anderen Worten: Der Speicher kann fragmentiert werden.

Garbage Collection reduziert möglicherweise den verwendeten Speicher, reduziert jedoch nur den zugewiesenen Speicher, wenn mindestens ein Superblock leer wird und freigegeben wird. Dies gilt sowohl für die automatische als auch die erzwungene Garbage Collection mithilfe der sys.sp_xtp_force_gc gespeicherten Systemprozedur.

Wenn die Fragmentierung des In-Memory OLTP-Enginespeichers und die Nutzung des zugewiesenen Speichers höher als erwartet werden, können Sie die Ablaufverfolgungsflagge 9898 aktivieren. Dies ändert das Partitionierungsschema von pro-CPU auf pro-NUMA-Knoten, wodurch die Gesamtanzahl der Superblöcke und das Potenzial für eine hohe Speicherfragmentierung reduziert wird.

Diese Optimierung ist für große Maschinen mit vielen logischen CPUs relevanter. Der Kompromiss dieser Optimierung ist ein potenzieller Anstieg der Speicherzuweisungsstreit, der sich aus weniger Superblocks ergibt, was den Gesamtdurchsatz der Arbeitslast verringern könnte. Abhängig von den Workloadmustern kann die Durchsatzreduzierung bei der Verwendung der Speicherpartitionierung pro NUMA-Node möglicherweise bemerkbar sein.

Verwandte Inhalte

• Migrieren zu In-Memory OLTP