Optimierung der TempDB für bessere Performance

Ich wurde gerade via E-Mail nach der optimalen Konfiguration der TempDB gefragt, daher möchte ich hier die Gelegenheit nutzen, meine persönliche Erfahrung und Best-Practice darstellen, denn im Grunde wiederholt sich alles und es gibt sicherlich technisch ausführlichere Darstellungen als meine folgende 😉

Ich möchte im folgenden meine Standard-Konfiguration für die TempDB auf allgemeinen SQL Servern (ab SQL Server 2016/2017) vorstellen und daraufhin weisen, dass es natürlich Abweichungen geben kann/sollte, die je nach Anwendung, Anforderungen oder Systemen davon abweichen können. Wenn mich ein Kunde nach einem „0815“ SQL Server fragt, auf dem er mehrere gemischte Applikationen betreiben kann, dann erhält er von mir diese Konfiguration als Empfehlung.

Und natürlich sollte man sich bewusst sein, dass die TempDB eine System-Datenbank und für den Betrieb des SQL Servers unerlässlich ist, also eine gewisse Kenntnis und Vorsicht sollte vorhanden sein!

Festplatten für die TempDB

Die TempDB besteht wie alle Datenbanken auf einem SQL Server mindestens aus einer Datendatei und einem Transaktionsprotokoll, mittlerweile sollte allgemeinhin bekannt sein, dass man Datendateien und Transaktionsprotokolle – aufgrund des unterschiedlichen Schreib-Lese-Verhaltens – voneinander trennt, dies ist natürlich auch bei der TempDB der Fall.

Idealerweise liegt die TempDB auf einer eigenen Platte (und ich rede hier von einer eigenständigen Platte und nicht einer Partition) und verfügt idealerweise über einen eigenen Storage-Controller, dies hat nicht nur Bedeutung für die Performance des SQL Servers sondern auch administrative Vorteile… in der TempDB kann theoretisch jeder User unzählige eigenen Objekte anlegen, Daten ablegen, Gruppieren oder Sortieren, also eine ganze Menge Daten dort „speichern“, heißt das Datenfile der TempDB kann enorm wachsen… wenn dies jetzt auf der selben Platte wie die User-Datenbanken liegt… könnte man sehr schnell in ein Problem laufen!

Da die TempDB eigentlich eher selten Transkationen protokollieren möchte/muss, kann das Transaktions-Protokoll der TempDB gerne auf dem Laufwerk der allgemeinen Laufwerk für die Transaktionsprotokolle abgelegt werden.

Fazit => Wir brauchen also eine sehr performante Platte – in einer Azure VM ist es immer die performanteste Platte, die lokale SSD.

Aber wie groß soll diese Platte werden? – Das ist immer schwierig zu sagen, sollte man die Platte so dimensionieren, dass die größte Datenbank (theoretisch) hineinpasst, oder der größte Index… das ist schwierig zu sagen, meine Empfehlung ist meistens => Schauen Sie sich die Gesamtgröße ihrer User-Datenbanken an und orientieren sich daran.

Beispiel:
Alle Datendateien der User-Datenbanken auf dem SQL Server haben in Summe eine Größe von 365GB, die Größe der Transaktionsprotokolle spielt hierfür erst einmal keine Rolle.

dann nehme ich diese Größe als 100% Data an und um nicht in ein Problem zu laufen, addiere ich einen Puffer von 25%, dann habe ich die Größe der Daten-Platte => 365GB + 25% = 456GB (ich rechne noch gerne in den „alten“ Plattenkapazitäten, die es zu kaufen gab), also würde ich meinem Kunden empfehlen dem SQL Server eine Festplatte von 450-480GB für die Datendateien der User-Datenbanken zur Verfügung zu stellen.

Davon ausgehend errechne ich die Größen für die TLog-Platte und die TempDB => je nach Vorgespräch und Nutzungsverhalten der Applikationen empfehle ich eine Größe der Platte für diese Systemdatenbank zwischen 25 und 33 Prozent von Data, in unserem Beispiel dann 120-160GB.

Konfiguration der TempDB

Nun kann man mittlerweile im Rahmen des SQL Server Setups zweierlei Dinge tun

• Berechtigungen des SQL Server Service-Users für „Instant File Initialization“ vergeben
• TempDB Konfiguration anpassen

Hier ist es eine gute Empfehlung einfach mit den Vorgaben von Microsoft weiterzumachen, oder den Best-Practices zu folgen. 😉

Zum einen ist „Instant File Initialization“ für das Handling der Datendateien relevant, sollte man also auf jeden Fall die Berechtigungen für den SQL Server Service-User setzen! Zum anderen ist die Default-Konfiguration der TempDB durch das Setup schon einmal ein sehr guter Einstieg.

Also der SQL Server sollte zwischen 1 und 8 Datendateien für die TempDB erhalten, je nach Anzahl der zur Verfügung stehenden Kerne. Wer es ganz genau wissen möchte, und ob 8 für sein System der maximale Wert ist, sollte sich den Artikel zum KB2154548 durchlesen, hier werden die genauen Details beschrieben. Für meinen „0815“-SQL Server gilt daher die grobe Formel:

Anzahl Datendateien der TempDB = wenn Anzahl Kerne kleiner 8, dann Anzahl Kerne sonst 8

Jetzt kommt ein Teil in der Konfiguration über den man sich streiten kann/könnte oder in eine Richtung „Glauben“ gehört… sollte man die Datendateien initial klein lassen und mit der Nutzung wachsen oder sollte man die Dateien gleich auf das Platten-Maximum aufblasen?
Ich halte es hier wie Brent Ozar, wenn ich schon eine ganze Festplatte nur für die TempDB habe, dann kann ich diese auch gleich voll nutzen 😉 Also berechne ich wie folgt die Größe der einzelnen Datendatei (aus unserem obigen Beispiel):

160 GB Platten-Kapazität für die TempDB
die meisten Monitoring-Systeme schlagen bei 90% Füllgrad an, daher wird ein Maximum von 89% der Plattenkapazität angenommen => ~142GB
mein SQL Server verfügt über 8 Kerne (siehe Screenshot) = 142GB / 8 Datendateien = 18.176 MB pro Datendatei

Diese Konfiguration lässt sich auch sehr schön und einfach mit dbatools realisieren, denn übernimmt das PowerShell für mich die komplette Konfiguration und Kalkulation.

Set-DbaTempDbConfig -SqlInstance ServerName\InstanceName -DataFileCount 8 -DataFileSize 145408 -DataFileGrowth 0

TempDB diverse Contention Probleme

Nur um die Vollständigkeit zu waren, es kann auch Probleme mit der TempDB geben, diese sind – bei 0815-Systemen – eher selten, können aber vorkommen:

• Object allocation contention
• Metadata contention

Lassen sich aber auch mit den entsprechenden Anleitungen verhindern oder beseitigen. Mittels dem neuen Feature „Memory Optimized MetaData for TempDB“ kann in gewissen Szenarien die Performance der TempDB noch weiter erhöht werden, dazu mehr in diesem Artikel bzw in dem dazugehörigen Video. Ansonsten folgen im Anhang noch relevante Links zur Konfiguration der TempDB.

Zusammenfassung

Der SQL Server Service User sollte die Rechte zum Ausführen der „Instant File Initialization“ erhalten, die Datendateien der TempDB auf einer eigenständigen Platte abgelegt werden, es sollten 2-8 Datenfiles angelegt werden und die Datenbank initial voll aufgeblasen werden ohne AutoGrowth.

Sehr guter technischer Artikel zur TempDB

Microsoft Dokumentation zur TempDB

Beitragsfoto von Mike Newbry auf Unsplash