SharePoint Performance Tuning - SQL AutoGrowth-Einstellungen

  • Article

SharePoint Adventskalender - 4. Türchen

Performance Tuning - Performance Monitoring - Best-Practices für SP-SQL-Konfigurationen - BLOB Management - Backup & Recovery

-------------------------------------------------------------

Auch bei den AutoGrowth-Einstellungen sind die SQL Standardwerte leider alles andere als optimal gewählt. Aufgrund einer kleinen Startgröße und kleiner automatischer Vergrößerungsschritte, können Daten nicht fortlaufend in die Blöcke (Extents) und Seiten (Pages) geschrieben werden. Dies geschieht bei allen INSERT-, UPDATE- und DELETE-Vorgängen. Es kommt zur Fragmentierung, die zu einer schlechteren Performance führt.

Weiterhin werden bei jedem AutoGrowth-Vorgang Server-Ressourcen verbraucht. Bei prozentualen Änderungen kommen noch Kalkulationsvorgänge hinzu. Dies sind alles zusätzliche Operationen, die der SQL nebenbei ausführen muss, bevor er die eigentlichen SharePoint-Anfragen abarbeiten kann. Wir erkennen also, dass wir mit den AutoGrowth-Einstellungen viel beeinflussen können.

Häufige AutoGrowth-Operationen fragmentieren außerdem in Hohem Maße das Transaction-Log. Wie können wir uns das vorstellen? Intern ist jedes Transaction-Log in kleinere virtuelle Logs unterteilt (Virtual Log File - VLF). Abhängig von der Größe, um die das Log erweitert werden soll, wird dieser "Chunk" in unterschiedlich viele virtuelle Logs aufgeteilt. Dies geschieht nach folgender Regel:

  • Chunks bis 64MB = 4 VLFs
  • Chunks mehr als 64MB und bis zu 1GB = 8 VLFs
  • Chunks mehr als 1GB = 16 VLFs

Aber: Ab SQL 2014 wurde diese Regel etwas angepasst. Ist die Chunk-Größe kleiner als 1/8 der aktuellen Log-Größe?

  • Ja: Erstelle eine neue VLF in der Größe des Chunks
  • Nein: Nutze die Formel oben

Bei vielen kleinen AutoGrowth Operationen mit noch kleinere virtuellen Logs (besonders bevor SQL 2014) fragmentiere ich also meine Datenbanken und Logfiles extrem - und dies beeinflusst die Performance.

Wir gehen noch weiter ins Detail: Angenommen, wir haben eine Datenbank von initial 80 MB und ein ebenso großes Log. Nach obiger Regel haben wir also 8 etwa gleich große virtuelle Log Dateien zu je 10 MB. Nun laden wir in SharePoint eine 12 MB Datei hoch. Was passiert?

 

SQL "schaut" in jedes virtuelle Log, ob die Datei da rein passt. Wenn nicht, versucht SQL das nächste usw. Wurden alle VLFs probiert, ohne die Datei schreiben zu können, so wird erst danach eine entsprechende AutoGrowth-Operation durchgeführt, um die Operation abschließen zu können. In der Ziwschenzeit sind aber bereits 8 I/Ops für die Versuche vergangen, die Datei in die kleineren VLFs zu schreiben. Und nun stellen Sie sich vor, sie haben extrem viele dieser kleineren VLFs. Die Summer der nötigen I/Ops dürfen Sie sich gern selber ausrechnen...

Mit der einfachen Query "DBCC LOGINFO" auf die jeweilige Datenbank können Sie herausfinden, wie viele virtuelle Log-Dateien Sie haben. Die ausgegebene Anzahl der Zeilen, entspricht der VLFs. Sollten es mehr als 50 sein, empfehle ich, diese zu bereinigen und die inkrementellen Zuwachsraten (AutoGrowth) anzupassen. (Weitere Details zur Log Datei Bereinigung finden Sie hier: https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/)

Die Zuwachsraten sollten idealerweise einen festen und keinen prozentualen Wert haben. Der Wert der Vergrößerung sollte etwa 10 % der Datenbankgröße betragen. Bei einer Inhaltsdatenbank von 10 GB wäre somit der Autozuwachs auf 1 GB einzustellen. (Soll die Datenbank 100 GB und größer werden, können auch größere Zuwachsschritte gewählt werden.) Für die Log-Datei sollte dies ähnlich konfiguriert werden.

Als Ergebnis werden wir weniger Vergrößerungsoperationen haben, welche die Serverleistung reduzieren. Weiterhin haben wir eine geringere Fragmentierung, sodass SharePoint-Anfragen schneller bearbeitet werden können.

--------------------------------------------

Danke auch an Paul Randal https://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/ 

Weitere Türchen: