Hinter der Abkürzung VLF verbergen sich Virtual Log Files. Diese virtuellen Protokolldateien sind nicht besonders gut dokumentiert und man kann sie sich mit dokumentierten Methoden auch nicht anschauen. Vernachlässigen sollte man das Thema aber auch nicht unbedingt.

Zumindest sollte man ein paar Gedanken an die Auswirkungen verschwenden, wobei Server mit wenigen Datenbanken eigentlich nicht besonders betroffen sind. Schauen wir uns zuerst einmal an, worüber wir reden und danach, welche Auswirkungen diese VLFs auf die Performance haben können. Wer sich inhaltlich mit dem Transaktionslog auseinandersetzen möchte, wird vielleicht bei diesem älteren Artikel fündig.

Wie entstehen VLFs?

Bereits bei der Anlage des Transaktionslogs wird ein Speicherbereich auf der Platte reserviert. Anders als viele annehmen, ist dies aber kein durchgängiger Bereich, sondern intern noch einmal in virtuelle Protokolldateien (VLFs) aufgeteilt. Wieviele VLFs nun erzeugt werden, hängt von der Größe des Bereichs ab, der in diesem Schritt allokiert werden soll.

Möchte man also sein Transaktionslog mit 80 MB initial anlegen, werden 8 VLFs erzeugt, also mit 10 MB pro VLF. Möchte man es mit 16 GB anlegen, werden 16 VLFs erzeugt, jedes mit 1 GB!

Das gleiche Verfahren wird auch beim Wachstum angewendet. Jedes Mal wird geschaut, um welchen Wert das Transaktionslog wachsen soll, dann wird die Anzahl der VLFs errechnet (4, 8 oder 16) und dann wird der gewünsche Wert in entsprechend vielen VLFs angelegt.

Es gibt also einen signifikanten Unterschied zwischen einem Transaktionslog mit 16 GB, welches in einem Schritt angelegt wurde und einem Transaktionslog mit 16 GB, welches initial 80 MB hatte und dann in Schritten von 80 MB gewachsen ist. Einmal reden wir von 16 VLFs und im anderen Fall von ca. 1.640 VLFs.

Der neue Algorithmus ab SQL Server 2014

• Ist das Wachstum kleiner als 1/8 der aktuellen Log-File Größe?
• Ja: lege nur noch 1 neues VLF an, welches die komplette Größe umfasst, die als Wachstum angegeben wurde.
• Nein: Dann verwende weiterhin die Formel, die vorher angewendet wurde.

Damit wird also sichergestellt, dass neue VLFs ab einer gewissen Größe des Log-Files nur noch in großen Stücken angelegt werden. Das reduziert also die Anzahl der VLFs pro Log-File und den Overhead, der in den Messungen u. a. für Backup und den Serverneustart gemessen wurde.

Ab SQL Server 2014 läuft es also folgendermaßen ab, wenn man ein Log-File mit 1 GB angelegt hat (8 VLFs) und das Wachstum auf Stücke von 512 MB eingestellt hat:

• Die ersten 8 VLFs werden bei der Erzeugung des Log-Files angelegt.
• Die weiteren Schritte beim Wachstum für die Größen 1.5, 2.0, 2.5, 3.0, 3.5, 4.0, 4.5 GB würden alle nach der alten Formel erfolgen und jeweils 8 VLFs erzeugen.
  Am Ende kommen hier also 7 * 8 VLFs = 56 VLFs zusammen.
• Jedes weitere Wachstum über 4.5 GB erzeugt jetzt nur noch 1 VLF pro Vergrößerung. Falls das Ziel 200 GB sind, berechnen sich die hier erzeugten VLFs also wie folgt: (200 GB – 4.5 GB) * 2 VLFs pro GB = 391 VLFs
• Insgesamt kommen wir auf 391 VLFs + 56 VLFs + 8 VLFs= 455 VLFs

Gegenüber der nach bisheriger Formel erzeugten 3.192 VLFs ist dies eine gewaltige Verbesserung. Diese neue Formel wird ab SQL Server 2014 unabhängig vom Datenbankkompatibilitätslevel angewendet.

Wie macht man es denn richtig?

Das ist leider nicht so einfach zu beantworten. Auf jeden Fall sollte man es möglichst nicht über ein automatisches Wachstum mit 10% versuchen. Das führt zu sehr vielen VLFs, wobei die Größen auch sehr unterschiedlich sind.

Der SQL Server meldet beim Start die Datenbanken, welche (seiner Meinung nach) zu viele VLFs haben. Im Errorolog findet man den folgenden Hinweis:

Database MyDB has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

Anscheinend haben die Leute von Microsoft 1000 als magische Grenze ausgemacht. Im SAP blog hat man 10.000 als Grenze ausgemacht. Andere empfehlen Werte unter 100.

Warum kosten VLFs Performance?

Jede Transaktion wird im Transaktionslog protokolliert. Das Transaktionslog wird gesichert und auch beim recovern gelesen. Also sollten wir Auswirkungen erwarten bei:

• Insert, Update, Delete
• Serverneustart
• Attachen einer Datenbank
• Backup

Darf ich mir das erst mal anschauen, bevor ich mich entscheide?

Aber sicher! Aber nur mit einer undokumentierten DBCC Option:

DBCC LOGINFO;

Als Ergebnis erhält man für jedes VLF eine Zeile zurück. Der Status 0 zeigt an, dass es nicht verwendet wird, der Status 2 zeigt an, dass dieses VLF derzeit belegt ist. die FSeqNo zeigt an, in welcher Reihenfolge diese aktiven VLFs geschrieben wurden. Das VLF mit der höchsten Nummer und dem Status 2 ist also der Punkt im Transaktionslog, wohin neue Transaktionen geschrieben werden. Wenn dieses VLF voll ist, wird das nächste freie VLF verwendet. Gibt es kein freies VLF mehr, wird das Transaktionslog (um 4, 8 oder 16 VLFs) erweitert. Kann das Log nicht erweitert werden, erhält man eine Fehlermeldung.

Die FileSize zeigt die Größe des VLF in Byte an. Hier gibt es schon mal Rundungsdifferenzen, so dass nicht alle VLFs gleich groß sind und auch zusammen ergeben die Werte, die in einem Schritt allokiert wurden nicht immer genau den gewünschten Wert.

Mein nicht repräsentativer Test

Im Netz gibt es einige wenige Skripte und Tests, die sich mit den Performance-Auswirkungen beschäftigen. Ich habe mir als Basis das Skript vom MVP Tony Rogerson genommen, welches ursprünglich mit SQL Server 2000 getestet wurde. Für meine Tests habe ich die Anzahl der Datensätze verzehnfacht, um überhaupt Unterschiede sehen zu können. Dann habe ich noch die Zeiten für das Backup mit erfasst und auch für das DELETE scheint die Anzahl der VLFs von Bedeutung zu sein. Am Ende habe ich noch für ausgesuchte Fälle einen Service-Neustart durchgeführt, wie es auch das Microsoft Support Team durchgeführt hat. Ich habe den Test aber so variiert, dass das LDF vor dem Neustart gesichert wurde und der Service normal beendet wurde.

Dann habe ich die Tests so durchgeführt, dass beim ersten Durchlauf das Transaktionslog noch wachsen muss, um den maximalen Wert zu erreichen. Im zweiten Test wurde die Datenbank nicht neu angelegt, so dass auch kein Wachstumszeiten mehr angefallen sein können.

Aus den unten stehenden Zahlen wird auch deutlich, dass nicht alle Unterschiede wirklich durch VLFs erklärbar sind. Weder wurden dieses Tests mehrfach durchgeführt und gemittelt, noch möchte ich behaupten, dass mein Testserver (ein virtuelles System) ohne Beeinflussung von außen existieren würde. Aber Tendenzen sollten erkennbar sein.

Test mit Wachstum

Die Variante mit den meisten VLFs ist erwartungsgemäß am langsamsten, wobei hier die Zeiten für das Backup signifikant größer sind, als bei den anderen Beispielen. Das Wachstum um fast 40.000 VLFs für das Delete schlägt auch hier deutlich zu Buche.

Die anderen Varianten liegen dicht beieinander, wobei anscheinend die besten Zeiten beim Delete mit dem ausbleiben des Wachstums des Transaktionslogs einhergehen. Ein weiteres Argument für eine vorrausschauende Ressourcen-Planung, damit keine automatischen Wachstumsprozesse störend in den Produktionsbetrieb einwirken können.

Der größte Fehler wäre natürlich ein Shrinken des Transaktionslogs als Teil eines Wartungsplans, da man dies bei nächster Gelegenheit wieder mit schlechter Performance bezahlen muss.

Test ohne Wachstum des Transaktionslogs

Wenn wir auf das Wachstum des Transaktionslogs verzichten können, sehen alle Zeiten erst einmal besser aus. Auch die Unterschiede zwischen den Varianten schrumpfen. Gewinner ist die Variante mit 176 VLFs, wobei auch der nächstplazierte mit 472 VLFs nicht so schlecht aussieht.

Die Backup-Zeiten sind vergleichbar geblieben, da diese ja auch nicht vom Wachstum abhängig sind. Den Einfluss der VLFs auf die Backups sieht man am besten, wenn man in den jeweiligen Varianten das erste Backup des ersten Laufs mit dem ersten Backup des zweiten Laufs vergleicht. Beim zweiten Lauf haben wir ja bereits mit der maximalen Anzahl VLFs gestartet.

Die Variante mit 32 VLFs verliert deutlich beim Insert, Update und Delete gegenüber der schnellsten Variante. Die Backupzeiten sind hier nur geringfügig schlechter.
Wenige VLFs bedeuten also nicht unbedingt beste Backup-Zeiten!

Die schnellste Variante mit 176 VLFs ist immerhin noch 110 Sekunden besser, als die langsamste mit 52.436 VLFs.

Serverneustart

Oh je! Hier dachte ich erst, es käme evtl. keine Meldung mehr und die Vergleiche sind nicht so einfach möglich. Aber nach über 2 Minuten kam dann doch noch die erlösende Meldung, dass die Datenbank mit den 52.436 VLFs nun endlich bereit wäre.

Hier zeigt sich ein deutlicher Einfluss der Anzahl VLFs auf die Performance des Serverneustarts. Selbst wenn es bei jeder Datenbank nur 10 Sekunden wären, die man gewinnen könnte, so würde dies bei einem Server mit 50 oder mehr Datenbanken einen gewaltigen Unterschied für die Verfügbarkeit machen.

Es ist auch nicht die Größe des LDF, die hier den Unterschied macht, denn die schnelle Variante hat sogar 16 GB auf 32 VLFs aufgeteilt.

Wenn man sich das SQL Server Errorlog nach einem Neustart anschaut und dann mal die Zeit zwischen "Starting up database '...'." und der nächsten Meldung für diese Datenbank ausrechnet, wird die Verzögerung deutlich. Man kann sich nun einfach mal eine der Datenbanken nehmen, die einem direkt ins Auge fallen und mit DBCC LOGINFO kontrollieren, wieviele VLFs hier vorhanden sind. Ab 1000 VLFs wird ja zudem eine Meldung im Errorolog angezeigt. Aber wer kontrolliert das schon? Immer? Gründlich?

Was kann man sonst noch falsch machen?

Es gibt leider einen Bug, den Paul Randal in seinem blog beschreibt.

Wenn man also (in einer Version vor SQL Server 2012) das Wachstum des LDF auf ein Vielfaches von 4 GB stellt, bekommt man bei jedem zweiten Wachstum nur eine Vergrößerung von 31 KB. Ursache ist wohl ein Rundungsfehler im Code des SQL Servers.

Falls man so große Schritte benötigt, macht man am besten einen Bogen um die 4GB und verwendet lieber 4000 MB oder ein Vielfaches davon. Daher kommen auch die 8000 MB in meinem Beispiel oben!

Transaktionslog wieder korrekt ausrichten

Jetzt kommen wir also zu dem Teil, den man ausführen sollte, wenn man festgestellt hat, dass das Transaktionsprotokoll über zu viele oder zu wenige VLFs verfügt. Diese Schritte sollte man möglichst nicht in den Stoßzeiten der Produktion durchführen, da sie von der Produktion behindert werden und anders herum auch!

Verkleinern des Transaktionsprotokolls

Dies ist jetzt mal eine Stelle, an der die Verkleinerung einer Datei Sinn macht. Möglichst nach einem Backup des Transaktionslogs führt man ein

für das Transaktionslog durch. Ggf. muss eine kleine Dummy-Transaktion gestartet werden, die den aktiven Teil des Logs an den Anfang bewegt, ein erneutes Backup des Logs durchgeführt werden und abschließend ein erneutes Shrinken ausgeführt werden. Beobachten lässt sich das alles mit DBCC LOGINFO.

Konfigurieren des Transaktionslogs

Wenn man ein möglichst kleines Transaktionslog hat, kann man nun die Werte für das Log neu setzen. Z.B.:

Setzt man in diesem Schritt bereits den Wert für SIZE auf den Endwert, erhält man ja nur wenige VLFs im Log. Besser ist es den Wert mehrfach zu erhöhen, bis der Zielwert erreicht ist, oder darauf zu warten, bis die User durch ihre Aktionen das Log wieder auf den gewünschten Wert vergrößert haben. Die Wartezeiten liegen dann allerdings bei den Usern.

  VLFs_Test_01.sql
  VLFs_Test_02.sql