Optimieren der tempdb

Eine Systemdatenbank, die es in sich hat und trotzdem oft genug ein Schattendasein führt.  Immerhin steckt hier ein sehr großes Potential für Performance Tuning.

Wenn mancher wüßte, was alles über die tempdb im Hintergrund verarbeitet wird oder werden kann, hätte man sicher schon sehr früh die Performance Schraube anziehen können.  Grob ausgedrückt wird die tempdb umso wichtiger je mehr Datenbanken auf dem Server betrieben werden., da sie eine zentrale Ressource für verschiedenste Aktivitäten darstellt. Aber wer verursacht Zugriffe auf die tempdb? Im Prinzip sind es zunächste zwei „Objekte“ die den Zugriff verursachen: Einerseits der Benutzer und andererseits der SQL Server selber.

Inhalte der tempdb

Folgende Objekte, die von einem Benutzer in der tempdb erstellt werden können:

• Benutzerdefinierte Tabllen und Indizes
• Globale und lokal temporäre Tabellen
• Tabellenvariablen
• Tabellen, die aus Funktionen heraus zurückgegeben werden
• Systemtabellen und –indizes

Der SQL Server selber verwendet die tempdb, um beispw. :

• Arbeitstabellen für Cursor oder LOB (large objects)
• Joins für Hashverknüpfungen
• Sortierarbeiten wie Group by, order by, union oder auch die Erstellung von Indizes mit der Angabe sort_in_tempdb

zu verwalten.

Ein weitere umfangreiche Funktionsweise der tempdb ist der Versionsspeicher.  Seit SQL Server 2005 gibt es zwei Versionsspeicher: ein allgemeiner Versionsspeicher und ein weiterer für Onlineindexerstellungsversionsspeicher.
Die Versionsspeicher organisieren unter anderem folgendes:

• CTEs
• MARS
• Zeilenversionsverwaltung
• Dialogverwaltung des Service Broker
• Trigger (deleted und inserted Tabellen)
• Große XML Werte (bis max. 2 GB)

Optimierung der tempdb

Anhand der oben genannten Funktionen, die über die tempdb entweder organisiert oder verwaltet werden, scheint es nun offensichtlich zu sein, der tempdb mehr Aufmerksamkeit zu schenken. Aber wie? Dazu habe ich ein paar Regeln zusammengefasst:

1. Die tempdb wird nach jedem Neustart neu aufgebaut: Geben Sie der tempdb eine ausreichend große Anfangsgröße.
2. Da auf die tempdb sehr häufig auch schreibend zugegriffen wird, ist das Auslagern der tempdb auf ein anderes (evtl. eigenes) Laufwerk  (RAID 0) eine gute Maßnahme zum beschleunigten Zugriff.
   Hier das Script zum Verschieben der tempdb – Dateien.
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = tempdev, FILENAME = ‚E:DATAtempdb.mdf‘);
   GO
   ALTER DATABASE tempdb
   MODIFY FILE (NAME = templog, FILENAME = ‚E:Datatemplog.ldf‘);
   GO
3. Geben sie der tempdb immer ausreichen Kapazität. Lassen Sie also das Wachstum auf automatisch. Eine Vergrößerung von 10% als pi*Daumen-Wert hat sich in der Praxis bewährt.
   Die tempdb kann übrigens maximal 2 TB groß werden.
4. Setzen Sie unter den Optionen den Wiederherstellungsmodus auf „Einfach“. Damit wird zunächst mal nicht alles in der Transaktionsprotokoll aufgezeichnet und andererseits werden beendete Transaktionen umgehend aus dem Transaktionsprotokoll entfernt. Somit wächst auch die .ldf Datei nicht unnötigerweise weiter an.
5. Das Anlegen weiterer Datendateien kann durchaus die den Zugriff auf den Datenträger optimieren, da dadurch Speicherkonflikte vermieden werden können. Es gibt eine pi*Daumen Regel: Anzahl der CPUs entspricht Anzahl der Dateien. Dadurch werden die Zugriffe auf den Datenträger über eine Skalierung auf Threadebene verbessert.
6. Falls Sie mehrere Dateien verwenden (s. Pkt. 5), dann geben Sie gleiche Dateigrößen an. Das erleichtert das proportionale füllen der Dateien.

Fazit

Wie man also sieht, sollte gerade unter SQL Server 2005 der tempdb deutlich mehr Aufmerksamkeit geschenkt werden. Aber auch wer SQL Server 2000 sein eigen nennt oder generell viele Datenbanken auf einem SQL Server hostet, sollte sich unbedingt die Last der tempdb mal genauer ansehen und dementsprechend konfigurieren. Wer übrigens jetzt auch mal an eine Sicherung der tempdb denkt, oder an ein Löschen…der kann es getrost sein lassen. das geht nicht. Ebenso wenig auch an einer Datenbankspiegelung teilnehmen oder per Dateigruppen zu optimieren. In diesem Sinne; fröhliches Schaffen mit der tempdb;-)