SQL Server: Datenverzeichnisse und I/O Zugriffe

Um eine optimale Performance zu erreichen, sollte es eine Trennung zwischen der Datendatei einer Datenbank und dem Transaktionsprotokoll geben. Sicherlich stellen sich gerade Einsteiger hier oft die Frage: „Warum ist das so?“. Der Grund liegt in der unterschiedlichen I/O – Charakteristik der Zugriffe auf diese Dateien.

Wer als Dienst das Datenbankmodul und die Analysis Services installiert, der kann zur optimalen Ressourcenverteilung im I/O-Subsystem für beide Dienste den Speicherort der Dateien separat festlegen. Es folgen einige Ausführungen zu den Dateitypen.

Das Transaktionsprotokoll einer Datenbank

In einem Transaktionsprotokoll werden die Transaktionen aufgezeichnet. Dazu gehört z.B. ein Update, ein Delete, ein Insert. Alle diese Transaktionen werden in das Transaktionsprotokoll sequenziell geschrieben. Das ist ein Zugriffsverfahren, ähnlich einer Schallplatte. Der Schreib-Lesekopf der Festplatte kann in der Spur bleiben und es wird lediglich geschrieben. Es sind also keine Sprünge zwischen den Spuren notwendig. Gelesen werden muss aus dem Transaktionsprotokoll lediglich für den Fall, das eine Wiederherstellung notwendig ist.

Die Datendatei einer Datenbank

Anders verhält es sich bei der Datendatei. In dieser wird sowohl gelesen, als auch geschrieben. Das bedeutet natürlich, dass sich die Position der Schreib-Leseköpfe ständig ändert. Das wiederum führt dazu, dass wir es hier mit einer schlechteren Performance zu tun haben, wenn wir die Datendateien und das Transaktionsprotokoll auf einen gemeinsamen physischen Datenträger legen. Hier liegt ein wesentlicher Grund der Trennung von Datendatei und Transaktionsprotokoll. Das ganze sollte pro Datenbank betrachtet werden.

Ich möchte allerdings an dieser Stelle noch den Hinweis geben, dass es schon einen Unterschied macht, ob in einer Datenbank ein Benutzer alle zwei Wochen eine Abfrage ausführt oder ein Online-Bestellsystem vorliegt, mit mehreren hunderten Transaktionen in der Minute. Insofern ist die Planung für jedes Datenbanksystem immer eine individuelle Planung. Es muss das jeweilige System mit seinen Anforderungen betrachtet werden.

Die Datenbank tempdb

Der Datenbank tempdb kommt eine besondere Bedeutung innerhalb einer Instanz von SQL Server zu. Alle Datenbanken einer Instanz teilen sich die Zugriffe auf diese eine Datenbank. In der tempdb werden nicht nur Objekte, wie temporäre Tabellen oder Variablen vorgehalten, sondern auch Result-Sets von Abfragen zwischengespeichert. Weiterhin wird die Datenbank tempdb zur Zeilenversionsverwaltung von SQL Server, für die Sortierung von Indizes oder andere Vorgänge verwendet.

Auch hier gilt: ein eigener Laufwerkspfad mit gesondertem Zugriff kann die Leistung von SQL Server erheblich verbessern.

Das Backupverzeichnis

Das Backupverzeichnis wird benötigt um die Sicherungen abzulegen. Auch hier gilt, wenn die I/O-Performance verbessert werden soll, legen Sie nicht alles auf eine physische Platte, sondern verteilen Sie die I/O-Zugriffe auf unterschiedliche physische Laufwerke. Die folgende Abbildung zeigt die Verteilung der Datendatei und des Transaktionsprotokolls. Das sind die grundsätzlichen Pfade bei der Installation.

Zu einem späteren Zeitpunkt können Sie für jede Benutzerdatenbank selbst noch einmal die Pfade für die Datendatei und das Transaktionsprotokoll bestimmen. Ein Minimum bei der Planung sollte die grundsätzliche Trennung von allen Datendateien und den Transaktionsprotokollen sein. Auch tempdb sollte unbedingt einem separaten physischen Laufwerk zugewiesen werden. In der nächsten Grafik sehen Sie die Verteilung der Datenbanken für die Analysis Services.

Ich habe hier die Originalpfade gelassen, um Ihnen zu zeigen, dass es wenig Sinn macht, diese so zu übernehmen.

Wie Sie in der Grafik gut sehen können, würde in diesem Fall das gleiche physische Laufwerk für die Datendatei, das Protokoll verwendet. Es kann somit keine Parallelisierung der I/O erfolgen.

Planung der Anzahl und Größe der Partitionen

Zunächst ist es wichtig, sich Gedanken um das Wachstum seiner Datenbanken zu machen. Hier helfen Erfahrungswerte, Baseline-Daten aus der Vergangenheit oder Schätzungen. Beachten Sie bitte, dass auch Indizes Speicherplatz belegen.

Wenn es absehbar ist, dass die Datenbank innerhalb weniger Wochen auf eine Größe von 150 GB anwächst, dann macht es wenig Sinn, diese beim Anlegen mit einer Größe von 100 MB festzulegen. Vor allem, weil die Fragmentierung beachtet werden muss, die bei jeder Vergrößerung entsteht. Der Standardwert für die automatische Vergrößerung beträgt z.B. bei einer Datendatei 1024 KB.

Stellen Sie sich vor, wie viele Stücke auf der physischen Platte existieren, die nicht zusammen in einem Bereich liegen. Das bedeutet einen erheblichen Mehraufwand für die Schreib-Leseköpfe der Festplatte.

Alles was bisher aufgezeigt wurde, macht deutlich, dass es keine allgemein gültige Formel gibt. Es spielen viele Faktoren, wie die Anzahl der Datenbanken innerhalb der Instanz, die Nutzung der jeweiligen Datenbank, das Wachstum und die Art der Verwendung (Lese-Schreibzugriffe) eine Rolle. Sollten Sie bereits ein Datenbanksystem haben, dann sehen Sie sich diese Faktoren genau an. Sie geben wichtige Hinweise auf das zu erwartende Verhalten.

Lesen Sie auch: Funktionsauswahl bei der Installation von SQL Server 2012