Blog
Tuesday, 17. March 2020

Die richtige Verwendung der In-Memory Optimized TempDB in SQL Server 2019

Simon
IT-Consultant

Mit der Veröffentlichung von SQL Server 2019 führt Microsoft eine ganze Reihe neuer Features und Verbesserungen ein. Eine dieser Erneuerungen ist die In-Memory Optimized TempDB.

Die In-Memory-Datenbanktechnologie wurde erstmals mit SQL Server 2014 eingeführt. Sie erlaubt es, Daten direkt in den Arbeitsspeicher (RAM) abzulegen statt diese auf einer herkömmlichen Festplatte zu speichern. Dadurch lassen sich wesentlich höhere Zugriffsgeschwindigkeiten realisieren. Allerdings fallen hierdurch deutlich höhere Kosten an, da RAM als Festplattenspeicher erheblich teurer ist, was sich vor allem bei größeren Datenbanken bemerkbar macht.

Als Teil der In-Memory Database führt Microsoft in SQL Server 2019 die Memory-Optimized TempDB ein. Die TempDB selbst ist eine globale Systemdatenbank, die allen Benutzern zur Verfügung steht, die mit der zugehörigen SQL Server Instanz verbunden sind. Sie wird als Speicherort für interne Objekte, Zeilenversionen, Arbeitstabellen, temporäre Tabellen und Indizes verwendet.

Als zentrale Ressource stellt die TempDB eine der größten Latenzquellen und Flaschenhälse von SQL Servern dar. Die in SQL Server 2019 eingeführte Memory-Optimized TempDB erlaubt es nun, die in der TempDB enthaltenen Meta-Daten ebenfalls auf den Arbeitsspeicher zu verlagern, um so die allgemeine Performance von SQL Servern erheblich zu steigern.

Im folgenden Beitrag wollen wir uns die Funktionsweise der Optimized TempDB genauer anschauen und deren Verwendung an einem kleinen Beispiel vorstellen.

Was ist die TempDB genau?
Bevor wir uns nun eingehend mit der Verwendung und der Funktionsweise der Optimized TempDB beschäftigen, wollen wir uns erst einmal ein klareres Bild von der TempDB machen.

Bei der TempDB handelt es sich um eine globale Ressource, die von SQL Server für diverse Zwecke verwendet wird. Einige davon umfassen:

  • Das Speichern von temporären Tabellen und Tabellenvariablen
  • Das Speichern von Arbeitsdaten, die für Hash-Join/Aggregat-Vorgänge benötigt werden
  • Das Speichern von Daten, die durch Tabellenfunktionen zurückgegeben wurden
  • Das Speichern von Arbeitstabellen für Spulen und Sortiervorgänge
  • Das Wiederherstellen von Indizes, insbesondere bei der Aktivierung von SORT_IN_TEMPDB
  • Das Bereitstellen von Zeilenversionen, die von Snapshot-Isolation, der Neuerstellung von Online-Indizes, AFTER-TRIGGER und von mehreren Ergebnismengen verwendet werden

Die TempDB findet also, innerhalb einer SQL Server Umgebung, überall Verwendung, wobei ihre Funktion im Allgemeinen den Entwicklern und Technikern verborgen bleibt.

Bei allen TempDB-Vorgängen werden Objekte nur so lange gespeichert, bis sie wieder gelöscht werden, eine Sitzung getrennt, oder der Dienst neugestartet wird. Dementsprechend ist die TempDB bei einem neuen Start von SQL Server immer leer.

Da TempDB Vorgänge nur minimal protokolliert werden und ihre Verwendung immer vorübergehend ist, ist es nicht erforderlich, Objekte dauerhaft beizubehalten. Dies macht die Verwendung der In-Memory-Technologie in Verbindung mit der TempDB äußerst sinnvoll, da der Arbeitsspeicher selbst auch ein vorübergehendes Repository für Daten darstellt.

Die in der TempDB abgelegten Daten lassen sich grob in zwei Datentypen unterteilen:

  • Metadaten, die die in TempDB gespeicherten Datenstrukturen beschreiben
  • Daten, die in diesen Datenstrukturen enthalten sind
  • Im folgenden Anwendungsbeispiel wollen wir nun die in der TempDB gespeicherten Metadaten im Arbeitsspeicher ablegen. Diese Metadaten enthalten Systemtabellen wie sys.tables, sys.columns oder sys.procedures. Die Daten selbst, welche durch diese Metadaten beschrieben werden, bleiben jedoch weiterhin auf der Festplatte gespeichert und werden nicht auf dem Arbeitsspeicher abgelegt.

Verwendung der Optimized-TempDB

Da die Metadaten in der TempDB von Datenbanken und Prozessen innerhalb einer bestimmten SQL Server Instanz gemeinsam genutzt werden, kann es bei einer Datenbank, welche häufig temporäre Objekte manipuliert oder einer Instanz, die über eine große Anzahl von Datenbanken verfügt, die temporäre Objekte verwenden, zu Problemen mit Metatabellen kommen.

Dieses Problem führt zu einer Latenz, aus welcher beim Entfernen von TempDB-Seiten aus dem Cache oder dem Verwenden von DDL Vorgängen für temporäre Objekte, Wartezeiten resultieren können. Je mehr temporäre Objekte vorhanden sind und je mehr sie manipuliert werden, desto größer wird die Arbeitslast eines Servers.

Um zu verdeutlichen, welche Auswirkungen die Verwendung der TempDB auf die Performance einer SQL Server Instanz haben kann, führen wir eine einfache SELECT-Operation gegen diese aus:

SET STATISTICS IO ON;
GO

CREATE TABLE #Einkausliste ([id] INT NOT NULL, 
                            [Ware] VARCHAR(10));
INSERT INTO #Einkaufsliste ([id], 
                            [Ware])
VALUES
  (1, 'Eier'),
  (2, 'Mehl'),
  (3, 'Apfelsaft'),
  (4, 'Kartoffeln'),
  (5, 'Gurken'),
  (6, 'Milch'),
  (7, 'Käse');
  
SELECT * FROM tempdb.sys.tables;

Betrachten wir nun die IO Statistiken:

Mainzer Datenfabrik - Die richtige Verwendung der In-Memory Optimized TempDB in SQL Server 2019

Wie wir sehen können werden genau 65 logical reads für diese einfache SELECT Abfrage durchgeführt. Damit wird klar, dass Metadatenspeicher alles andere als billig ist und ähnliche Abfragen an einem ausgelasteten Server die Performance sehr stark beeinflussen können.

Durch das Verschieben der TempDB-Metadaten in den Arbeitsspeicher können wir diese hohen IO Kosten deutlich minimieren und so eine wesentlich bessere Performance erreichen.

Hierfür müssen wir zunächst den speicheroptimierten TempDB-Metadatenspeicher mit dem folgenden T-SQL Code aktivieren:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Dies erfordert allerdings einen Neustart der SQL Server Instanz.

Um sicherzustellen dass die neuen Einstellungen übernommen wurden, kann dies durch den SERVERPROPERTY Check überprüft werden:

SELECT SERVERPROPERTY('IsTempdbMetadataMemoryOptimized');

Ist die Optimized-TempDB aktiviert, sollte die Ausgabe wie folgt aussehen:

Mainzer Datenfabrik - Die richtige Verwendung der In-Memory Optimized TempDB in SQL Server 2019

Ist die Optimized-TempDB deaktiviert, würde in der Ausgabe eine 0 stehen.

Nun führen wir erneut die selbe SELECT-Anweisung durch, und betrachten erneut die IO Statistiken:

Mainzer Datenfabrik - Die richtige Verwendung der In-Memory Optimized TempDB in SQL Server 2019

Und tatsächlich ist eine deutliche Optimierung zu erkennen, da für die selbe Abfrage nur noch 6 statt 65 logical reads durchgeführt wurden.

Während einige Lesevorgänge dennoch erforderlich sind, um unsere Abfrage zu bearbeiten, wurden alle TempDB exklusiven Metadatentabellen aus dieser Liste entfernt. Auch wurde das Latching für diese Daten beseitigt, wodurch Konflikte erheblich reduziert werden können, da bei der Verwendung keine Daten mehr in den Cache verschoben werden müssen.

Diese Änderungen können die Leistung eines Systems, welches die TempDB stark nutzt, erheblich verbessern und für eine deutlich bessere Performance sorgen.

Die richtige Konfiguration

Mainzer Datenfabrik - Die richtige Verwendung der In-Memory Optimized TempDB in SQL Server 2019

Da die Optimized-TempDB Arbeitsspeicher verbraucht, ist es wichtig vor ihrer Verwendung sicher zu stellen, dass der zugehörigen SQL Server Instanz auch genügend Speicher zugewiesen ist, damit die TempDB-Metadaten vernünftig gespeichert werden können. Hierfür sollte die minimale Speichereinstellung erhöht werden, oder bereits hoch genug sein, damit eine akzeptable Basislinie für die Unterstützung von TempDB-Vorgängen bereitgestellt werden kann. Für eine optimale Nutzung der Optimized-TempDB sollte eine minimale Speichereinstellung von 1000MB festgelegt sein:

Aufgrund ihrer vorübergehenden Natur bietet die TempDB eine weitaus größere Flexibilität bei der Optimierung als andere Teile von SQL Server. Gerade bei Instanzen, auf denen die TempDB häufig verwendet wird und so ein Bottleneck darstellt, kann die Leistung durch das Verschieben von Daten auf den Arbeitsspeicher deutlich gesteigert werden.

Da die Größe der TempDB nicht annähernd an die Größe der von ihr unterstützten Datenbanken heranreicht, ist die Verlagerung auf einen schnelleren Speicher vergleichsweise günstig. Da die TempDB auch immer von Datenbank- und Protokolldateien getrennt sein sollte, kann eine Verschiebung auf den Arbeitsspeicher sowohl diese Trennung ermöglichen, als auch den größtmöglichen physischen Geschwindigkeitsschub erreichen.

Letztendlich kann jeder Speicher, auf dem sich die TempDB befindet, auf Geschwindigkeit und Haltbarkeit optimiert werden. Hierfür muss die TempDB nicht gesichert werden, weshalb die Konfiguration einer Sicherung nicht erforderlich ist. Anders ist es mit der Hochverfügbarkeit. Da SQL Server ohne die TempDB nicht funktionieren kann sollte ein System gegenüber dem Ausfall eines TempDB-Laufwerks stabil sein, unabhängig davon, ob diese Ausfallsicherheit durch Software- oder Hardwarekonventionen gewährleistet ist.

Fazit

Die TempDB ist eine wichtige Komponente des SQL Servers, die viele verschiedene Funktionen unterstützt und leicht zu einem Bottleneck für Anwendungen, die stark von ihr Gebrauch nehmen, werden kann.

Die Möglichkeit, TempDB-Metadaten in den Speicher zu verschieben, bietet eine hervorragende Möglichkeit, die Leistung auf Servern mit hoher TempDB-Auslastung zu steigern. Da es sich hierbei immer um vorübergehende Daten handelt, die im Falle eines Neustarts verschwinden, besteht hier nur ein minimales Risiko.

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

Kontaktieren Sie uns gerne über das Kontaktformular und vereinbaren ein unverbindliches Beratungsgespräch mit unseren Berater:innen zur Bedarfsevaluierung. Gemeinsam optimieren wir Ihre Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!

Taunusstraße 72
55118 Mainz
info@madafa.de
+49 6131 3331612
Bürozeiten
Montag bis Donnerstag:
9:00 - 17:00 Uhr MEZ

Freitags:
9:30 - 14:00 Uhr MEZ