AUTOGROW_ALL_FILES in SQL Server 2016

Die Option AUTOGROW_ALL_FILES ist neu beim SQL Server 2016 und wird gerne mal übersehen. Im Folgenden wird genauer erläutert wie man diese Funktion einsetzt und wann sie Sinn bringt.

Bisher war es so, dass wenn es zu einem Wachstum der Datendateien (*.mdf, *.ndf) kommt, die Dateien innerhalb einer Filegroup nicht alle gleichzeitig wachsen, sondern jede Datei für sich. Dabei verwendet der SQL Server ein Round Robin Verfahren. Das heißt bei 3 Dateien wächst zuerst die erste Datei, wenn alle Dateien voll sind, dann die zweite Datei und schließlich die dritte. Danach wächst wieder die erste Datei. Solange noch Platz in mehreren Datendateien ist, verwendet der SQL Server einen Proportional Fill Algorithmus. Dabei füllt er am schnellsten jene Datei, die noch am meisten Platz bietet, so dass am Ende möglichst alle Dateien gleichzeitig voll werden.

Hat man Wachstumsvorgänge verteilen sich die Daten also nicht gleichmäßig auf die Dateien. Dies kann aus mehreren Gründen allerdings Performance Vorteile bringen. Einerseits können die Datendateien einer Filegroup auf verschiedenen Disks liegen und man möchte den Schreib-I/O möglichst parallelisieren. Andererseits sorgt eine möglichst gute Aufteilung auf mehrere Dateien dafür, dass es weniger LatchContention Probleme auf den PFS Datapages gibt. Vereinfacht gesagt, stehen dem SQL Server in jeder Datei eigene PFS Datapages zur Verfügung, die jeweils für ein 64 MB Intervall angeben, wie stark die einzelnen Datapages innerhalb der Extents gefüllt sind und wo es noch Platz für neue Daten gibt.

Alte Methode: Trace Flag 1117

Die alte Möglichkeit das Standardverhalten des SQL Servers zu ändern, ist das Traceflag 1117 zu verwenden. Dies ändert für alle Datenbanken (inklusive der TempDB Datenbank) das Standardverhalten so, dass immer wenn eine Datei einen Autogrowth Vorgang durchführt, alle Dateien der gleichen Filegroup auf einmal den Autogrowth Vorgang durchführen.

Das Trace Flag 1117 lässt sich global in den Eigenschaften der Instanz (im SQL Server Configuration Manager) unter Startup Parameters setzen.

Neue Methode: AUTOGROW_ALL_FILES

Seit SQL Server 2016 gibt es allerdings eine bessere Methode, die volle Kontrolle über das Wachstum der Dateien auf Basis einzelner Filegroups ermöglicht. Mit dem Folgenden T-SQL Code ist es möglich für eine Filegroup einzustellen, dass alle Dateien der Filegroup gleichzeitig wachsen sollen, falls es bei einer Datei zu einem Autogrow-Vorgang kommt:

In diesem Fall wird AUTOGROW_ALL_FILES für die Filegroup PRIMARY aktiviert. Diese Einstellung ist für die TempDB mit SQL Server 2016 oder höher übrigens standardmäßig bereits aktiv. Die ganze Einstellung hat natürlich nur einen Effekt für Dateigruppen, die mindestens 2 Dateien oder mehr enthalten.

In der Praxis stellt sich oft das Problem, dass die Einstellung für die Filegroup nur gesetzt werden kann, wenn keine anderen User mit Objekten der Filegroup verbunden sind. Man sollte die Umstellung also zu einer Wartungszeit durchführen und evt. andere Transaktionen vorher beenden, die Datenbank in den SINGLE_USER Mode schalten und danach wieder in den MULTI_USER Mode:

Weiterhin ist es möglich die Einstellung auch wieder rückgängig zu machen, dies geht mit:

In diesem Fall wird dann wieder jede Datei der Filegroup nacheinander per Round Robin vergrößert.

Am Ende lässt sich für jede Filegroup leicht prüfen ob AUTOGROW_ALL_FILES aktiviert ist oder nicht. Dafür gibt es eine neue Spalte in sys.filegroups mit dem Namen is_autogrow_all_files.

In diesem Fall sieht man, dass für die PRIMARY Filegroup AUTOGROW_ALL_FILES aktiviert wurde.