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.

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… 😉