Die verzögerte Dauerhaftigkeit macht viele, kleine SQL-Transaktionen schneller!
Performance Tuning, wenn Transaktionssicherheit nicht so wichtig ist
Wenn man sehr viele Datensätze in eine SQL Server-Datenbank importieren will oder viele Änderungen daran vornehmen muss, aber das verwendete Werkzeug macht dies als viele, viele kleine Transaktionen, Zeile für Zeile, dann gibt es einen einfachen Weg, diesen Vorgang zu beschleunigen: die „delayed durability“, auf deutsch etwas sperrig „verzögerte Transaktionsdauerhaftigkeit“ genannt. Diese Datenbank-Einstellung hilft bei den Fällen, wo es mehr auf Geschwindigkeit ankommt als darauf, dass man sich bei jeder abgeschlossenen Transaktion, sofort nach „COMMIT TRAN“ zu 100% sicher sein kann, dass die Änderung jeden Stromausfall überlebt! Dass jede festgeschriebene Transaktion immer wiederhergestellt werden kann (das passiert automatisch beim Neustart des Servers), ist ein super-Feature des SQL Server, aber es ist auch aufwändig. Deshalb ist es sehr gut, dass man seit SQL 2014 schon (und auch in SQL Azure) dieses Feature einfach mal abschalten kann. Das macht man natürlich nur, wenn es ausschließlich um Geschwindigkeit geht, und wenn dann z.B. ein Import abbricht, leert man einfach die Zieltabelle wieder und beginnt von vorn!
So schaltet man (auf Datenbankebene) dieses Feature ein:
Der Standartwert ist dabei natürlich „Disabled„, denn jede SQL-Datenbank soll natürlich per Grundeinstellung immer komplett transaktionssicher sein!
Aber warum ist es eigentlich so aufwändig, dass jede Transaktion immer gleich festgeschrieben wird, und wo geschieht das eigentlich? Nun, jeder, der den SQL Server näher kennt, weiß, dass jede Datenbank mindestens zwei Dateien hat: eine Datenbankdatei (.mdf) und eine Logdatei (.ldf). So sieht das im einfachsten Fall aus:
Und die Datei, die bei vielen Transaktionen am meisten beansprucht wird, ist die Log-Datei, also die, wo alle Änderungen mitprotokolliert werden, nicht die Daten-Datei, in der die eigentlichen Daten stehen! Genau genommen ist es so, dass jede Transaktion, die beendet werden soll, so lange mit dem COMMIT wartet, bis der Festplattenspeicher eindeutig zurückmeldet, dass die Änderungen dieser Transaktion in die Log-Datei geschrieben wurden.
Um diesen Effekt mal nachvollziehen zu können, habe ich eine Stored Procedure geschrieben, die 500.000 generierte Datensätze in eine Tabelle schreibt, jeweils als eigene Transaktion. So sieht sie aus:
Jedes INSERT ist dabei eine eigene Transaktion, die dann mit delayed_durability = OFF sofort festgeschrieben wird (das ist die Standardeinstellung). In der Praxis würde man das natürlich vermeiden und z.B. alle INSERT Statements als eine einzige Transaktion abbilden, aber oft schreibt man ja sein SQL nicht selbst, sondern Tools generieren es, und überraschend viele Lade-Werkzeuge sind nicht sehr clever und wählen den Weg der einzelnen Mini-Transaktionen!
Wenn ich diese Prozedur dann aufrufe, dauert das natürlich elend lange. Während ich darauf warte, kann man die Ursache sehr schön mit der Leistungsüberwachung (perfmon) und einigen passenden Performance Monitor Countern darin beobachten, und das sieht dann etwa so aus:
Hier sieht man klar, dass das System nur knapp 7000 Transaktionen in der Sekunde schafft, weil es etwa 7000 Wartevorgänge für Protokolleerungen/Sekunde abwarten muss. Das kann also gar nicht schneller gehen!
Und während man darauf wartet, kann man sich noch den Beweis dafür beschaffen, dass es wirklich die Log-Datei ist, auf die die Verzögerung zurückzuführen ist. Dafür genügt ein Blick in den Ressourcenmonitor:
Da sieht mal also ganz klar, dass wesentlich mehr Daten in die .ldf-Datei geschrieben werden als in die .mdf-Datei (das wird dann beim CHECKPOINT nachgeholt, aber davon ein andermal).
Wenn man dann die Stored Procedure von oben noch einmal mit delayed_durability = ON erzeugt und aufruft, wird man je nach Hardware überraschende Geschwindigkeitsgewinne bemerken. Natürlich werden die Transaktionen trotzdem noch festgeschrieben, aber der Server wartet halt nicht darauf, dass die Log-Datei den Erfolg dieser Operation auch zurückmeldet! Es geschieht dann aber sofort, sobald die Ressourcen dafür vorhanden sind, und dann „auf einen Rutsch“, was es auch noch schneller macht.
Soll man die verzögerte Dauerhaftigkeit nun immer verwenden (siehe oben die Einstellung forced)? Keinesfalls, denn meistens bringt es gar nicht so viel. Wenn man vernünftig ist und viele, viele Operationen sinnvoll zu einer Transaktion zusammenfasst, dann gibt es auch nur ein COMMIT, auf das sich das Warten dann lohnt! Aber wer das nicht kann, warum auch immer, der hat hier den Schlüssel für einen INSERT- und UPDATE-Turbo in der Hand.