Überlegungen zu den Einstellungen für automatische Vergrößerung und Verkleinerung in SQL Server

Artikel
12/14/2024

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 315512

Zusammenfassung

Die Standardeinstellungen für automatische Vergrößerung und AutomatischesHrinken sind für viele SQL Server-Systeme geeignet. Es gibt jedoch Umgebungen, in denen Sie die Parameter für automatisches Wachstum und automatische Verkleinerung möglicherweise anpassen müssen. Dieser Artikel enthält einige Hintergrundinformationen, die Sie bei der Auswahl dieser Einstellungen für Ihre Umgebung unterstützen.

Hier sind einige Punkte, die Sie berücksichtigen sollten, wenn Sie sich entscheiden, die Parameter für automatische Vergrößerung und Autohrink zu optimieren.

Gewusst wie die Einstellungen konfigurieren

Sie können die Einstellungen für automatische Vergrößerung und AutomatischesHrinken mithilfe einer der folgenden Einstellungen konfigurieren oder ändern:
- SQL Server Management Studio
- Eine ALTER DATABASE-Anweisung
  - Verwenden der Optionen " Datei" und "Dateigruppe" zum Ändern der Einstellungen für automatisches Wachstum
  - Verwenden Sie SET-Optionen zum Konfigurieren von AUTO_SHRINK Einstellungen.
Notiz

Weitere Informationen zum Festlegen dieser Einstellungen auf Datenbankdateiebene finden Sie unter Hinzufügen von Daten oder Protokolldateien zu einer Datenbank.

Sie können die Option zum automatischen Vergrößerung auch konfigurieren, wenn Sie eine Datenbank erstellen.

Führen Sie zum Anzeigen der aktuellen Einstellungen den folgenden Transact-SQL-Befehl aus:
```
sp_helpdb [ [ @dbname= ] 'name' ]
```
Denken Sie daran, dass die Einstellungen für die automatische Vergrößerung pro Datei vorliegen. Daher müssen Sie sie an mindestens zwei Stellen für jede Datenbank festlegen (eine für die primäre Datendatei und eine für die primäre Protokolldatei). Wenn Sie über mehrere Daten- und/oder Protokolldateien verfügen, müssen Sie die Optionen für jede Datei festlegen. Je nach Ihrer Umgebung können Sie mit unterschiedlichen Einstellungen für jede Datenbankdatei enden.

Überlegungen zu AUTO_SHRINK

AUTO_SHRINK ist eine Datenbankoption in SQL Server. Wenn Sie diese Option für eine Datenbank aktivieren, kann diese Datenbank durch eine Hintergrundaufgabe verkleinern. Diese Hintergrundaufgabe wertet alle Datenbanken aus, die die Kriterien zum Verkleinern und Verkleinern der Daten- oder Protokolldateien erfüllen.

Sie müssen die Einstellung dieser Option für die Datenbanken in einer SQL Server-Instanz sorgfältig auswerten. Häufige Vergrößerungs- und Verkleinerungsvorgänge können zu verschiedenen Leistungsproblemen führen.

Wenn mehrere Datenbanken häufig verkleinern und wachsen, führt dies leicht zur Fragmentierung auf Dateisystemebene. Dies kann erhebliche Auswirkungen auf die Leistung haben. Dies gilt unabhängig davon, ob Sie die automatischen Einstellungen verwenden oder ob Sie die Dateien häufig manuell vergrößern und verkleinern.
Nach AUTO_SHRINK dem erfolgreichen Verkleinern der Daten oder Protokolldatei kann ein nachfolgender DML- oder DDL-Vorgang erheblich verlangsamt werden, wenn Speicherplatz erforderlich ist und die Dateien vergrößert werden müssen.
Die AUTO_SHRINK Hintergrundaufgabe kann Ressourcen aufnehmen, wenn viele Datenbanken verkleinern müssen.
Die AUTO_SHRINK Hintergrundaufgabe muss Sperren und andere Synchronisierungen abrufen, die mit anderen regulären Anwendungsaktivitäten in Konflikt stehen können.

Erwägen Sie, Datenbanken auf eine erforderliche Größe festzulegen und sie vorab zu vergrößern. Lassen Sie den nicht verwendeten Speicherplatz in den Datenbankdateien, wenn Sie denken, dass die Anwendungsnutzungsmuster sie erneut benötigen. Dies kann häufiges Verkleinern und Wachstum der Datenbankdateien verhindern.

Überlegungen für AUTOGROW

Wenn Sie eine Transaktion ausführen, die mehr Protokollspeicher benötigt, als verfügbar ist, und Sie die Option für die automatische Vergrößerung für das Transaktionsprotokoll dieser Datenbank aktiviert haben, enthält die Zeit, zu der die Transaktion abgeschlossen ist, die Zeit, die das Transaktionsprotokoll benötigt, um den konfigurierten Betrag zu vergrößern. Wenn der Wachstumsschritt groß ist oder ein anderer Faktor vorhanden ist, der dazu führt, dass dies eine lange Zeit dauert, kann die Abfrage, in der Sie die Transaktion öffnen, aufgrund eines Timeoutfehlers fehlschlagen. Die gleiche Art von Problem kann aus einer automatischen Vergrößerung des Datenteils Ihrer Datenbank resultieren.
Wenn Sie eine große Transaktion ausführen, für die das Protokoll vergrößert werden muss, müssen andere Transaktionen, die einen Schreibvorgang in das Transaktionsprotokoll erfordern, auch warten, bis der Erweiterungsvorgang abgeschlossen ist.
Wenn Sie viele Dateiwachstums in Ihren Protokolldateien haben, haben Sie möglicherweise eine übermäßig große Anzahl virtueller Protokolldateien (VLF). Dies kann zu Leistungsproblemen bei Datenbankstart-/Onlinevorgängen, Replikation, Spiegelung und Änderung der Datenerfassung (CDC) führen. Darüber hinaus kann dies manchmal zu Leistungsproblemen bei Datenänderungen führen.

Notiz

Wenn Sie die Optionen für automatisches Anwachsen und AutomatischesHrinken kombinieren, können Sie unnötigen Aufwand verursachen. Stellen Sie sicher, dass die Schwellenwerte, die die An- und Verkleinerungsvorgänge auslösen, keine häufigen Größenänderungen nach oben und unten verursachen. Sie können beispielsweise eine Transaktion ausführen, die bewirkt, dass das Transaktionsprotokoll um 100 MB um 100 MB vergrößert wird, wenn ein Commit ausgeführt wird. Einige Zeit danach startet und verkleinert das Transaktionsprotokoll um 100 MB. Anschließend führen Sie dieselbe Transaktion aus und bewirkt, dass das Transaktionsprotokoll erneut um 100 MB vergrößert wird. In diesem Beispiel erstellen Sie unnötigen Mehraufwand und möglicherweise eine Fragmentierung der Protokolldatei, die sich negativ auf die Leistung auswirken kann.

Wenn Sie Ihre Datenbank um kleine Schritten vergrößern oder wenn Sie sie vergrößern und dann verkleinern, können Sie die Datenträgerfragmentierung erreichen. Die Datenträgerfragmentierung kann unter bestimmten Umständen zu Leistungsproblemen führen. Ein Szenario kleiner Wachstumsschritte kann auch die Leistung ihres Systems verringern.

In SQL Server können Sie die Sofortige Dateiinitialisierung aktivieren. Die sofortige Dateiinitialisierung beschleunigt dateizuordnungen nur für Datendateien. Die Sofortige Dateiinitialisierung gilt nicht für Protokolldateien. Weitere Informationen finden Sie unter Initialisierung der Datenbankdatei.

Bewährte Methoden für autogrow und autohrink

Für ein verwaltetes Produktionssystem müssen Sie das automatische Wachstum nur als Notfall für unerwartetes Wachstum betrachten. Führen Sie die tägliche Verwaltung Ihres Daten- und Protokolldateiwachstums nicht durch automatische Vergrößerung.
Sie können Warnungen oder Überwachungsprogramme verwenden, um Dateigrößen zu überwachen und Dateien proaktiv zu vergrößern. Dies hilft Ihnen, Fragmentierung zu vermeiden und ermöglicht es Ihnen, diese Wartungsaktivitäten auf Nicht-Spitzenzeiten zu verschieben.
Autohrink und AutoGrow müssen von einem geschulten Datenbankadministrator (DBA) sorgfältig ausgewertet werden; Sie dürfen nicht nicht verwaltet werden.
Ihr automatisches Inkrement muss groß genug sein, um die im vorherigen Abschnitt aufgeführten Leistungsstrafen zu vermeiden. Der genaue Wert, der in Ihrer Konfigurationseinstellung verwendet werden soll, und die Wahl zwischen einem Prozentualen Wachstum und einem bestimmten MB-Größenwachstum hängt von vielen Faktoren in Ihrer Umgebung ab. Eine allgemeine Faustregel, die Sie zum Testen verwenden können, besteht darin, die Einstellung für die automatische Vergrößerung auf etwa acht der Größe der Datei festzulegen.
Aktivieren Sie die \<MAXSIZE> Einstellung für jede Datei, um zu verhindern, dass eine Datei an einem Punkt wächst, an dem sie den gesamten verfügbaren Speicherplatz verwendet.
Halten Sie die Größe Ihrer Transaktionen so klein wie möglich, um ein nicht geplantes Dateiwachstum zu verhindern.

Warum muss ich mich um Speicherplatz kümmern, wenn Größeneinstellungen automatisch gesteuert werden

Die Einstellung für die automatische Vergrößerung kann die Datenbankgröße nicht über die Grenzen des verfügbaren Speicherplatzes auf den Laufwerken erweitern, für die Dateien definiert sind. Wenn Sie sich daher auf die Automatische Vergrößerungsfunktion verlassen, um die Größe Ihrer Datenbanken zu vergrößern, müssen Sie den verfügbaren Festplattenspeicher trotzdem unabhängig überprüfen. Die Einstellung für die automatische Vergrößerung ist auch durch den Parameter beschränkt, den MAXSIZE Sie für jede Datei auswählen. Um den Speicherplatz zu reduzieren, können Sie den Leistungsmonitor Leistungsindikator SQL Server: Databases Object: Data File(s) Size (KB) überwachen und eine Warnung einrichten, wenn die Datenbank eine bestimmte Größe erreicht.
Das ungeplante Wachstum von Daten- oder Protokolldateien kann Platz belegen, den andere Anwendungen erwarten, dass sie verfügbar sind, und diese anderen Anwendungen können zu Problemen führen.
Das Wachstum Ihres Transaktionsprotokolls muss groß genug sein, um den Anforderungen Ihrer Transaktionseinheiten voraus zu bleiben. Selbst wenn die automatische Vergrößerung aktiviert ist, können Sie eine Meldung erhalten, dass das Transaktionsprotokoll voll ist, wenn es nicht schnell genug wachsen kann, um die Anforderungen Ihrer Abfrage zu erfüllen.
SQL Server testt nicht ständig auf Datenbanken, die den konfigurierten Schwellenwert für autohrink erreicht haben. Stattdessen werden die verfügbaren Datenbanken untersucht und der erste gefunden, der für das automatischeHrinken konfiguriert ist. Sie überprüft diese Datenbank und verkleinert diese Datenbank bei Bedarf. Anschließend wartet es mehrere Minuten, bevor die nächste Datenbank überprüft wird, die für das automatischeHrinken konfiguriert ist. Mit anderen Worten, SQL Server überprüft nicht alle Datenbanken gleichzeitig und verkleinern sie alle gleichzeitig. Es wird die Datenbanken in einer Roundrobin-Art durchlaufen, um die Last über einen bestimmten Zeitraum auszustaffieren. Je nachdem, wie viele Datenbanken Sie für eine bestimmte SQL Server-Instanz konfiguriert haben, kann es daher mehrere Stunden dauern, bis die Datenbank den Schwellenwert erreicht, bis sie tatsächlich verkleinern wird.

Freigeben über