Allgemeine Informationen zur TempDB
Die Datenbank TempDB ist der Dreh- und Angelpunkt eines MS SQL-Servers.
Hierbei handelt es sich um eine globale Ressource, die jedem Benutzer zur Verfügung steht.
In der TempDB Datenbank sind folgende Elemente enthalten:
- Temporäre vom Benutzer erzeugte Objekte (z. B. globale oder lokale temporäre Tabellen, Tabellenvariablen, temporäre Prozeduren usw.)
- Interne Objekte der Database Engine (Datenbankmodul) (z. B. Arbeitstabellen zum Speichern von Zwischenergebnissen für Spool- und Sortiervorgänge)
Bei einem Neustart des SQL-Servers wird die TempDB neu erstellt. Operationen innerhalb der TempDB werden nur minimal protokolliert, somit kann ein ROLLBACK für Transaktionen durchgeführt werden. Die Datenbank kann nicht Gesichert und daher auch nicht durch ein Backup wieder hergestellt werden.
Konfiguration
Die Datenbank besteht wie jede andere Datenbank aus 2 Dateien (*.MDF/*.NDF und *.LDF).
Es wird empfohlen, je CORE eine MDF-Datei zu erstellen. Diese Regelung gilt allerdings nur bis zu 7 CORES.
- 1 bis 7 CORES je CORE eine MDF / NDF-Datei
- 8 bis 32 => Anzahl CORES / 2 = Anzahl MDF / NDF-Dateien
- mehr als 32 CORES => Anzahl CORES / 4 = Anzahl MDF / NDF-Dateien
Die Dateien werden alle mit der gleichen Größe Konfiguriert.
WICHTIG: Betreibt man die TempDB mit mehreren Datenfiles muss der SQL-Server mit dem Trace Flag 1117 gestartet werden. Dieses Trace Flag bewirkt, dass die Datenfiles gleichmäßig befüllt werden.
Bei der automatischen Vergrößerung der Dateien gibt es ebenfalls ein Richtwert.
- Dateigröße von 0 – 100 MB => 10 MB Vergrößerung
- Dateigröße von 100 – 200 MB => 20 MB Vergrößerung
- Dateigröße größer 200 MB => 100 MB Vergrößerung
Es sollte nie eine prozentuale Vergrößerung gewählt werden!
Hier muss man allerdings die Anwendungen sowie die Leistung des Systems berücksichtigen.
Die Größe der LDF-Datei würde ich persönlich an die Größe der MDF / NDF-Dateien anpassen.
Um noch etwas mehr Leistung aus der TempDB zu ziehen, sollte man die Dateien auf eine eigene schnelle Festplatte legen. Durch diese Maßnahme erhöht sich der I/O Durchsatz. Zusätzlich kann das LOG-File ebenfalls auf eine eigene Festplatte gelegt werden.
Scripte
Anlegen neuer Dateien:
USE [master] GO ALTER DATABASE
[tempdb]
ADD FILE ( NAME = N’tempdev1′, FILENAME = N’D:\Program Files\Microsoft SQL Server\MSSQL12.2016\MSSQL\DATA\tempdev1.ndf‘ , SIZE = 5120KB , FILEGROWTH = 1024KB ) GO ALTER DATABASE
[tempdb]
ADD FILE ( NAME = N’tempdev2′, FILENAME = N’D:\Program Files\Microsoft SQL Server\MSSQL12.2016\MSSQL\DATA\tempdev2.ndf‘ , SIZE = 5120KB , FILEGROWTH = 1024KB ) GO ALTER DATABASE
[tempdb]
ADD FILE ( NAME = N’tempdev3′, FILENAME = N’D:\Program Files\Microsoft SQL Server\MSSQL12.2016\MSSQL\DATA\tempdev3.ndf‘ , SIZE = 5120KB , FILEGROWTH = 1024KB ) GO
Verschieben der MDF- / NDF / LDF-Dateien auf eine andere Festplatte:
--verschieben der TempDB auf eine andere Festplatte ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev , FILENAME = 'T:\SQLData\tempdb.mdf' ); ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev1 , FILENAME = 'T:\SQLData\tempdb1.ndf' ); ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev2 , FILENAME = 'T:\SQLData\tempdb2.ndf' ); ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev3 , FILENAME = 'T:\SQLData\tempdb3.ndf' ); ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = 'T:\SQLData\tempdb.ldf' );
Größer der MDF- / NDF-Dateien festlegen (Größe 5 GB und 100MB Vergrößerung)
USE [master] GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 5242880KB, MAXSIZE = 78643200KB, FILEGROWTH = 100MB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev1', SIZE = 5242880KB, MAXSIZE = 78643200KB, FILEGROWTH = 100MB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev2', SIZE = 5242880KB, MAXSIZE = 5242880KB, FILEGROWTH = 100MB ) GO ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev3', SIZE = 5242880KB, MAXSIZE = 5242880KB, FILEGROWTH = 100MB ) GO
In der SQL-Server Version 2016 kann direkt während der Installation die Konfiguration eingestellt werden.