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
TempDB-Konfiguration - SQL Server 2019 Setup - Screenshot

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:

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

TempDB-Performance-Tuning out of the box – T-SQL Tuesday #87

TempDB-Performance-Tuning out of the box - T-SQL Tuesday #87

T-SQL Tuesday ist eine wiederkehrende Blog-Serie, die von Adam Machanic (b | t) gestartet wurde, jeden Monat ist ein Blogger Gastgeber für ein Thema rund um den SQL Server und jeder kann einen Blogbeitrag zu diesem bestimmten Thema schreiben.

Diesen Monat ist Matt Gordon (b | t) unser Host und das Thema geht um neue Features im SQL Server (ab der Version 2014) welche alte Problemstellungen beheben, bekannt gegeben in seinem Announcement-Post.

Mein Thema für diesen TSQL2sDay ist die neue Möglickeit zur Konfiguration der TempDB im Rahmen der SQL Server Installation.

Umsetzung der Best-Practise für die TempDB vereinfacht

Bisher musste man nach erfolgter Installation des SQL Server die Best-Practise Empfehlungen von Microsoft mehr oder weniger aufwändig konfigurieren. Entweder man ist auf herkömmliche Art und Weise an das Thema heran gegangen und hat die Werte manuell über das SQL Servermanagement Studio eingestellt oder man hatte es sich einfacher gemacht, dieses entweder per T-SQL oder Powershell Skript automatisiert. Aber dennoch musste man jedesmal diese Konfigurationsanpassungen nachträglich hinzufügen, um eine optimierte Performance der TempDB zu erreichen.

Die grundsätzliche Performance erhält die TempDB im Grunde zwar über die Hardware-Ausstattung, hier die physikalische Trennung zwischen User-Datenbanken und TempDB und die Verlagerung der TempDB auf hochperformanten Storage. Microsoft empfiehlt die strikte Trennung von UserDB und TempDB nicht nur durch getrennte Ordner-Strukturen oder getrennte Partitionen, es sollen eigene (schnelle) Platten sein. Hier muss auch keine großartige RAID-Konfiguration vorliegen, ein RAID 1 oder RAID 10 wären toll, aber keine wirkliche Notwendigkeit, denn die TempDB wird beim Neustarten des SQL Servers immer wieder “neu” erstellt, somit wäre es kein “Drama” wenn sie korrput wäre oder die drunterliegenden Platten crashen würden. (Ok, mal von dem Verlust von Daten und Zeit abgesehen).

Überblick über die Best-Practise Änderungen

Soviele Anpassungen sind gar nicht notwendig um eine bessere Performance der TempDB zu erreichen, diese möchte ich hier kurz zusammenfassen:

  • physikalische Trennung von User-Datenbanken und TempDB
  • Aufsplittung der TempDB in einzelne Datenfiles gem. Prozessor-Anzahl
  • Autogrowth-Parameter Anpassungen
  • Trace-Flag je nach Verwendung/Bedarf

Wenn man nun diese physikalische Optimierung im Storage-Bereich realsiert hat, kann man sich an die logischen Anpassungen in der Konfiguration machen. Je nach Prozessor-/Kernzahl empfiehlt Microsoft anstatt eines großen Datenfiles die Aufsplittung in mehrere getrennte Datenfiles, um eine Optimierung der IO-Streams umzusetzen (Parallelisierung). Ebenso kann man – je nach Anwendungsgebiet bzw Bedarfs – nun die Datenfiles initial auf eine feste Größe festlegen, damit der interne Aufbau der Datendateien optimiert wird und alle Dateien gleich groß sind. (ähnl. Traceflag 1117/1118). Anderweitig sollte man den Autogrowth-Parameter auf 10% setzen, so dass die Datendateien in Verbindung mit den Traceflag 1117 und 1118 zu einem optimierten, gleichmäßigen Aufbau der TempDB beitragen.

SQLServer2016 - Configure TempDB during Installation

SQL Server 2016 bringt integrierte Vereinfachungen

Der SQL Server 2016 bringt nun diese ganzen logischen Optimierung gleich im Rahmen der Installation mit… man kann also ohne Umschweife dem SQL Server mitteilen, wie man die Anzahl und Aufteilung der TempDB-Datenfiles sowie deren Autogrowth-Parameter setzen möchte. Microsoft hat endlich die lange publizierten Best-Practise Empfehlungen in den Installationsprozess integriert. Somit muss man eigentlich nur noch die Hardware-Vorraussetzungen für die TempDB schaffen und kann anfangen zu installieren.
Ok, auch hier muss man sich natürlich Gedanken machen, welche Werte man wie setzt, aber zumindest muss man sich über diese Konfigurationen hinterher keine Gedanken machen!

VIELEN DANK an Microsoft für diese Verbesserung.

Man kann man während des Installationsprozesses einstellen, wieviele Datenfiles man anlegen möchte, dazu die initiale Größe und auch den Autogrowth-Parameter festlegen. Theoretisch könnte man sogar für jedes einzelne Datenfile (Aufteilung anhand der Anzahl der Kerne) einen eigenen Datenpfad oder sogar eine eigene (SSD-)Platte spendieren. Alles in einem übersichtlichen Screen dargestellt und auf für den unerfahrenen DBA einsetzbar.

Durch dieses neue “Toy” konnten einige “Problems” reduziert werden… 😉