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

Artikel
06/20/2023

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

Zusammenfassung

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

Im Folgenden finden Sie einige Punkte, die Sie berücksichtigen sollten, wenn Sie die Parameter für die automatische Vergrößerung und die automatische Verkleinerung optimieren möchten.

Gewusst wie Konfigurieren der Einstellungen

Sie können die Einstellungen für die automatische Vergrößerung und die automatische Verkleinerung mit einer der folgenden Optionen konfigurieren oder ändern:
- SQL Server Management Studio
- Eine ALTER DATABASE -Anweisung
  - Verwenden der Optionen "Datei" und "Dateigruppe" , um die Einstellungen für die automatische Vergrößerung zu ändern
  - Verwenden Sie SET-Optionen , um Einstellungen zu konfigurieren AUTO_SHRINK .
Hinweis

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

Sie können auch die Option für die automatische Vergrößerung konfigurieren, wenn Sie eine Datenbank erstellen.

Führen Sie den folgenden Transact-SQL-Befehl aus, um die aktuellen Einstellungen anzuzeigen:
```
sp_helpdb [ [ @dbname= ] 'name' ]
```
Beachten Sie, dass die Einstellungen für die automatische Vergrößerung pro Datei gelten. 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 Umgebung können Sie mit unterschiedlichen Einstellungen für jede Datenbankdatei enden.

Überlegungen zu AUTO_SHRINK

AUTO_SHRINKist 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 für das Verkleinern und Verkleinern der Daten- oder Protokolldateien erfüllen.

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

Wenn mehrere Datenbanken häufig verkleinern und vergrößern, führt dies leicht zur Fragmentierung auf Dateisystemebene. Dies kann schwerwiegende Auswirkungen auf die Leistung haben. Dies gilt unabhängig davon, ob Sie die automatischen Einstellungen verwenden oder 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 in Anspruch nehmen, 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. Behalten Sie den nicht verwendeten Speicherplatz in den Datenbankdateien bei, wenn Sie der Meinung sind, dass die Anwendungsnutzungsmuster sie wieder benötigen. Dies kann eine häufige Verkleinerung und Vergrößerung der Datenbankdateien verhindern.

Überlegungen zur AUTOMATISCHEN VERGRÖßERUNG

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, umfasst die Zeit, die die Transaktion bis zum Abschluss benötigt, die Zeit, die das Transaktionsprotokoll benötigt, um den konfigurierten Betrag zu vergrößern. Wenn das Vergrößerungsinkrement groß ist oder ein anderer Faktor dafür sorgt, dass es lange dauert, kann die Abfrage, in der Sie die Transaktion öffnen, aufgrund eines Timeoutfehlers fehlschlagen. Die gleiche Art von Problem kann sich aus einer automatischen Vergrößerung des Datenteils Ihrer Datenbank ergeben.
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, ebenfalls warten, bis der Vergrößerungsvorgang abgeschlossen ist.
Wenn Ihre Protokolldateien viele Dateivergrößerungen aufweisen, verfügen Sie möglicherweise über eine übermäßig große Anzahl virtueller Protokolldateien (VLF). Dies kann zu Leistungsproblemen bei Datenbankstart-/Onlinevorgängen, Replikation, Spiegelung und Change Data Capture (CDC) führen. Darüber hinaus kann dies manchmal zu Leistungsproblemen bei Datenänderungen führen.

Hinweis

Wenn Sie die Optionen für die automatische Vergrößerung und die automatische Verkleinerung kombinieren, können Sie unnötigen Mehraufwand verursachen. Stellen Sie sicher, dass die Schwellenwerte, die die Verkleinerungs- 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 bis zum Commit um 100 MB vergrößert wird. Einige Zeit danach wird die automatische Verkleinerung gestartet und verkleinert das Transaktionsprotokoll um 100 MB. Anschließend führen Sie dieselbe Transaktion aus, wodurch das Transaktionsprotokoll erneut um 100 MB vergrößert wird. In diesem Beispiel erzeugen Sie unnötigen Mehraufwand und möglicherweise eine Fragmentierung der Protokolldatei, von denen sich beide negativ auf die Leistung auswirken können.

Wenn Sie Ihre Datenbank in kleinen Schritten vergrößern oder wenn Sie sie vergrößern und dann verkleinern, kann die Datenträgerfragmentierung enden. Die Datenträgerfragmentierung kann unter bestimmten Umständen zu Leistungsproblemen führen. Ein Szenario mit kleinen Wachstumsschritten kann auch die Leistung auf Ihrem System beeinträchtigen.

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

Bewährte Methoden für automatische Vergrößerung und automatische Verkleinerung

Für ein verwaltetes Produktionssystem müssen Sie die automatische Vergrößerung nur als Notfall für unerwartetes Wachstum betrachten. Verwalten Sie Ihre Daten und protokollieren Sie das Wachstum nicht täglich mit automatischer Vergrößerung.
Sie können Warnungen oder Überwachungsprogramme verwenden, um Dateigrößen zu überwachen und Dateien proaktiv zu vergrößern. Dies hilft Ihnen, eine Fragmentierung zu vermeiden, und ermöglicht es Ihnen, diese Wartungsaktivitäten auf Nicht-Spitzenzeiten zu verlagern.
Autohrink und automatische Vergrößerung müssen von einem geschulten Datenbankadministrator (DBA) sorgfältig bewertet werden. Sie dürfen nicht unbemannt bleiben.
Ihr Inkrement für die automatische Vergrößerung muss groß genug sein, um die im vorherigen Abschnitt aufgeführten Leistungseinbußen zu vermeiden. Der genaue Wert, der in Ihrer Konfigurationseinstellung verwendet werden soll, und die Wahl zwischen einem prozentualen Wachstum und einer bestimmten MB-Größenvergrößerung hängt von vielen Faktoren in Ihrer Umgebung ab. Eine allgemeine Faustregel, die Sie für Tests verwenden können, ist das Festlegen der Einstellung für die automatische Vergrößerung auf etwa 1 bis acht der Größe der Datei.
Aktivieren Sie die \<MAXSIZE> Einstellung für jede Datei, um zu verhindern, dass eine Datei zu einem Punkt wächst, an dem sie den gesamten verfügbaren Speicherplatz belegt.
Halten Sie die Größe Ihrer Transaktionen so klein wie möglich, um eine ungeplante Dateivergrößerung zu verhindern.

Warum muss ich mich um den Speicherplatz kümmern, wenn die 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 hinaus vergrößern, für die Dateien definiert sind. Wenn Sie sich daher auf die Funktion zur automatischen Vergrößerung für die Größe Ihrer Datenbanken verlassen, müssen Sie den verfügbaren Festplattenspeicher weiterhin unabhängig überprüfen. Die Einstellung für die automatische Vergrößerung wird auch durch den Parameter eingeschränkt, den MAXSIZE Sie für jede Datei auswählen. Um die Möglichkeit zu verringern, dass der Speicherplatz knapp wird, können Sie den Leistungsmonitor Leistungsindikator SQL Server: Datenbanken Objekt: Größe der Datendateien (KB) überwachen und eine Warnung einrichten, wenn die Datenbank eine bestimmte Größe erreicht.
Ungeplantes Wachstum von Daten- oder Protokolldateien kann Speicherplatz in Anspruch nehmen, der von anderen Anwendungen erwartet wird, und kann zu Problemen bei diesen anderen Anwendungen führen.
Das Inkrement ihres Transaktionsprotokolls muss groß genug sein, um den Anforderungen Ihrer Transaktionseinheiten einen Schritt voraus zu sein. Selbst wenn die automatische Vergrößerung aktiviert ist, können Sie eine Meldung erhalten, dass das Transaktionsprotokoll voll ist, wenn es nicht schnell genug wächst, um die Anforderungen Ihrer Abfrage zu erfüllen.
SQL Server wird nicht ständig auf Datenbanken getestet, die den konfigurierten Schwellenwert für die automatische Verkleinerung erreicht haben. Stattdessen werden die verfügbaren Datenbanken untersucht und die erste Datenbank gefunden, die für die automatische Verkleinerung konfiguriert ist. Es überprüft diese Datenbank und verkleinert diese Datenbank bei Bedarf. Anschließend wird einige Minuten gewartet, bevor die nächste Datenbank überprüft wird, die für die automatische Verkleinerung konfiguriert ist. Anders ausgedrückt: SQL Server überprüft nicht alle Datenbanken gleichzeitig und verkleinert sie alle gleichzeitig. Sie durchläuft die Datenbanken auf Roundrobin-Weise, um die Last über einen bestimmten Zeitraum zu staffeln. Je nachdem, wie viele Datenbanken Sie für die automatische Verkleinerung auf einem bestimmten SQL Server instance konfiguriert haben, kann es daher mehrere Stunden dauern, nachdem die Datenbank den Schwellenwert erreicht hat, bis sie tatsächlich schrumpft.