Verwalten von Dateispeicherplatz für Datenbanken in Azure SQL Managed Instance

  • Artikel

Gilt für:Azure SQL Managed Instance

In diesem Artikel wird beschrieben, wie Sie Dateien in Datenbanken in Azure SQL Managed Instance überwachen und verwalten. Wir zeigen Ihnen, wie Sie die Größe der Datenbankdatei überwachen, das Transaktionsprotokoll verkleinern, eine Transaktionsprotokolldatei vergrößern und das Wachstum einer Transaktionsprotokolldatei kontrollieren können.

Dieser Artikel gilt für Azure SQL Managed Instance. Informationen zur Verwaltung der Größe von Transaktionsprotokolldateien in SQL Server finden Sie unter Verwalten der Größe der Transaktionsprotokolldatei, obwohl diese sehr ähnlich ist..

Grundlegendes zu den Arten von Speicherplatz für eine Datenbank

Es wichtig, dass Sie mit den folgenden Speicherplatzmengen vertraut sind, damit Sie den Dateispeicherplatz einer Datenbank verwalten können.

Datenbankmenge Definition Kommentare
Genutzter Speicherplatz Der Speicherplatz, der zum Speichern von Datenbankdaten verwendet wird. In der Regel erhöht (verringert) sich der Platzbedarf bei eingefügten (gelöschten) Daten. In manchen Fällen ändert sich der genutzte Speicherplatz beim Einfügen oder Löschen von Daten nicht, je nach Menge und Muster der an dem Vorgang beteiligten Daten und einer eventuellen Fragmentierung. Beispielsweise wird der genutzte Speicherplatz durch Löschen einer Zeile auf jeder Datenseite nicht zwangsläufig gesenkt.
Zugeordneter Datenspeicherplatz Die Menge an formatiertem Dateispeicherplatz zum Speichern von Daten. Die Menge des zugeordneten Speicherplatzes wächst automatisch an, wird aber nach dem Löschen nicht kleiner. Dieses Verhalten stellt sicher, dass Daten später schneller eingefügt werden, da der Platz nicht neu formatiert werden muss.
Zugeordneter Datenspeicherplatz (ungenutzt) Die Differenz zwischen der Menge des zugeordneten Datenspeicherplatzes und des genutzten Datenspeicherplatzes. Diese Menge ist die maximale Menge des freien Speicherplatzes, die freigegeben werden kann, indem Datendateien von Datenbanken verkleinert werden.
Maximale Datengröße Die maximale Speicherplatzmenge, die zum Speichern von Datenbankdaten verwendet werden kann. Die Menge des zugeordneten Datenspeicherplatzes kann die maximale Datengröße nicht überschreiten.

Das folgende Diagramm veranschaulicht die Beziehung zwischen den verschiedenen Arten von Speicherplatz für eine Datenbank.

Diagram that demonstrates the size of difference database space concepts in the database quantity table.

Abfragen eines Singletons nach Dateispeicherplatzinformationen

Verwenden Sie die folgende Abfrage auf sys.database_files, um die Menge des zugewiesenen und des ungenutzten Speicherplatzes in der Datenbankdatei zu ermitteln. Als Einheit für das Abfrageergebnis wird MB verwendet.

-- Connect to a user database
SELECT file_id, type_desc,
       CAST(FILEPROPERTY(name, 'SpaceUsed') AS decimal(19,4)) * 8 / 1024. AS space_used_mb,
       CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS decimal(19,4)) AS space_unused_mb,
       CAST(size AS decimal(19,4)) * 8 / 1024. AS space_allocated_mb,
       CAST(max_size AS decimal(19,4)) * 8 / 1024. AS max_size_mb
FROM sys.database_files;

Überwachen der Belegung des Protokollspeicherplatzes

Überwachen Sie mithilfe von sys.dm_db_log_space_usage die Belegung des Protokollspeicherplatzes. Diese DMV gibt Informationen zum derzeit belegten Protokollspeicherplatz zurück und zeigt an, wann das Transaktionsprotokoll abgeschnitten werden muss.

Informationen zur aktuellen Größe einer Protokolldatei, ihrer maximalen Größe sowie der für die Datei festgelegten automatischen Vergrößerungsoption können Sie auch den Spalten size, max_size, und growth in sys.database_files entnehmen.

Die in den Azure Resource Manager-basierten Metrik-APIs angezeigten Speicherplatzmetriken messen nur die Größe der verwendeten Datenseiten. Beispiele finden Sie unter PowerShell get-metrics.

Verkleinern der Protokolldateigröße

Um die physische Größe einer physischen Protokolldatei durch Entfernen von ungenutztem Platz zu reduzieren, verkleinern Sie die Protokolldatei. Eine Verkleinerung macht nur dann einen Unterschied, wenn eine Transaktionsprotokolldatei ungenutzten Platz enthält. Wenn die Protokolldatei voll ist, wahrscheinlich wegen offener Transaktionen, untersuchen Sie, was das Abschneiden des Transaktionsprotokolls verhindert.

Achtung

Die Verkleinerungsvorgänge sollten nicht als ein regulärer Wartungsvorgang betrachtet werden. Die Daten- und Protokolldateien, die aufgrund regelmäßiger, wiederkehrender Geschäftsvorgänge zunehmen, erfordern keine Verkleinerungsvorgänge. Verkleinerungsbefehle können während der Ausführung der Datenbank zu einer Beeinträchtigung der Leistung führen und sollten daher nur zu Zeiten mit geringer Auslastung ausgeführt werden. Es ist nicht empfehlenswert, Datendateien zu verkleinern, wenn die regelmäßige Arbeitslast der Anwendung dazu führt, dass die Dateien wieder auf die gleiche Größe anwachsen.

Beachten Sie die möglichen negativen Auswirkungen des Verkleinerns von Datenbankdateien auf die Leistung, siehe den Abschnitt Indexpflege nach dem Verkleinern. In seltenen Fällen können Verkleinerungsvorgänge durch automatische Datenbanksicherungen beeinträchtigt werden. Wiederholen Sie bei Bedarf den Verkleinerungsvorgang.

Berücksichtigen Sie vor dem Verkleinern des Transaktionsprotokolls die Faktoren, die die Protokollkürzung verzögern können. Wenn der Speicherplatz nach einer Protokollverkleinerung wieder benötigt wird, vergrößert sich das Transaktionsprotokoll wieder und führt bei der Protokollvergrößerung infolgedessen zu einem Leistungsoverhead. Weitere Informationen finden Sie unter Empfehlungen.

Sie können eine Protokolldatei nur dann verkleinern, wenn die Datenbank online und mindestens eine virtuelle Protokolldatei (Virtual Log File, VLF) verfügbar ist. In einigen Fällen ist eine Verkleinerung des Protokolls möglicherweise erst nach der nächsten Protokollkürzung möglich.

Faktoren, wie z.B. Transaktionen mit langer Laufzeit, können VLFs für einen längeren Zeitraum aktiv halten, die Protokollverkleinerung einschränken oder sogar gänzlich verhindern. Weitere Informationen finden Sie unter Faktoren, die die Protokollkürzung verzögern können.

Beim Verkleinern einer Protokolldatei werden VLFs entfernt, die keinen Teil des logischen Protokolls enthalten (d.h. inaktive VLFs). Beim Verkleinern einer Transaktionsprotokolldatei werden inaktive VLFs am Ende der Protokolldatei entfernt, um das Protokoll auf ungefähr die Zielgröße zu reduzieren.

Weitere Informationen zu Verkleinerungsvorgängen finden Sie in den folgenden Ressourcen:

Verkleinern einer Protokolldatei (ohne die Datenbankdateien zu verkleinern)

Überwachen der Protokollverkleinerungsereignisse

Überwachen von Protokollspeicherplatz

Indexpflege nach dem Schrumpfen

Nachdem ein Verkleinerungsvorgang für Datendateien abgeschlossen wurde, können Indizes fragmentiert werden. Dies verringert die Wirksamkeit der Leistungsoptimierung für bestimmte Arbeitslasten, z. B. für Abfragen mit großen Suchvorgängen. Wenn nach dem Abschluss des Verkleinerungsvorgang eine Leistungsbeeinträchtigung auftritt, sollten Sie eine Indexwartung in Betracht ziehen, um die Indizes neu zu erstellen. Denken Sie daran, dass der Neuaufbau von Indizes freien Speicherplatz in der Datenbank erfordert und daher zu einer Vergrößerung des zugewiesenen Speicherplatzes führen kann, was dem Effekt der Verkleinerung entgegenwirkt.

Weitere Informationen zur Indexpflege finden Sie unter Optimierung der Indexpflege zur Verbesserung der Abfrageleistung und Reduzierung des Ressourcenverbrauchs.

Bewertung der Indexseitendichte

Wenn das Kürzen von Datendateien nicht zu einer ausreichenden Verringerung des zugewiesenen Speicherplatzes geführt hat, können Sie beschließen, die Datendateien der Datenbank zu verkleinern, um ungenutzten Speicherplatz aus diesen Dateien zurückzugewinnen. Als optionaler, aber empfohlener Schritt sollten Sie jedoch zunächst die durchschnittliche Seitendichte für die Indizes in der Datenbank ermitteln. Bei der gleichen Datenmenge ist das Schrumpfen schneller abgeschlossen, wenn die Seitendichte hoch ist, da weniger Seiten verschoben werden müssen. Wenn die Seitendichte für einige Indizes niedrig ist, sollten Sie eine Wartung dieser Indizes in Betracht ziehen, um die Seitendichte zu erhöhen, bevor Sie die Datendateien verkleinern. Auf diese Weise lässt sich auch der zugewiesene Speicherplatz stärker reduzieren.

Um die Seitendichte für alle Indizes in der Datenbank zu ermitteln, verwenden Sie die folgende Abfrage. Die Seitendichte wird in der Spalte avg_page_space_used_in_percent angegeben.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_page_space_used_in_percent,
       ips.avg_fragmentation_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc,
       ips.ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

Wenn es Indizes mit einer hohen Seitenzahl und einer Seitendichte von weniger als 60-70 % gibt, sollten Sie in Erwägung ziehen, diese Indizes neu zu erstellen oder zu reorganisieren, bevor Sie die Datendateien verkleinern.

Hinweis

Bei größeren Datenbanken kann die Abfrage zur Bestimmung der Seitendichte sehr lange dauern (Stunden). Darüber hinaus erfordert der Neuaufbau oder die Reorganisation großer Indizes ebenfalls einen erheblichen Zeit- und Ressourcenaufwand. Es besteht ein Kompromiss zwischen dem zusätzlichen Zeitaufwand für die Erhöhung der Seitendichte einerseits und der Verringerung der Schrumpfdauer und der Erzielung einer höheren Platzeinsparung andererseits.

Wenn es mehrere Indizes mit geringer Seitendichte gibt, können Sie diese möglicherweise parallel in mehreren Datenbanksitzungen neu aufbauen, um den Prozess zu beschleunigen. Achten Sie jedoch darauf, dass Sie dabei nicht an die Grenzen der Datenbankressourcen stoßen, und lassen Sie genügend Spielraum für eventuell laufende Anwendungsworkloads. Überwachen Sie den Ressourcenverbrauch (CPU, Data IO, Log IO) im Azure-Portal oder mithilfe der Ansicht sys.dm_db_resource_stats und starten Sie zusätzliche parallele Rebuilds nur, wenn die Ressourcenauslastung in jeder dieser Dimensionen deutlich unter 100 % liegt. Wenn die CPU-, Daten-IO- oder Protokoll-IO-Auslastung bei 100 % liegt, können Sie die Datenbank skalieren, um mehr CPU-Kerne zu haben und den IO-Durchsatz zu erhöhen. Dies kann zusätzliche parallele Rebuilds ermöglichen, um den Prozess schneller abzuschließen.

Beispielbefehl zum Neuerstellen des Index

Es folgt ein Beispielbefehl zum Neuerstellen eines Index und zum Erhöhen seiner Seitendichte mithilfe der ALTER INDEX-Anweisung:

ALTER INDEX [index_name] ON [schema_name].[table_name]
REBUILD WITH (FILLFACTOR = 100, MAXDOP = 8, 
ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5 MINUTES, ABORT_AFTER_WAIT = NONE)), 
RESUMABLE = ON);

Dieser Befehl initiiert einen Online-Index-Neuaufbau mit Wiederaufnahmefunktion. Dadurch können gleichzeitige Arbeitslasten die Tabelle weiter verwenden, während der Neuaufbau läuft, und Sie können den Neuaufbau wieder aufnehmen, wenn er aus irgendeinem Grund unterbrochen wird. Diese Art des Rebuilds ist jedoch langsamer als ein Offline-Rebuild, bei dem der Zugriff auf die Tabelle blockiert wird. Wenn keine anderen Workloads während des Wiederaufbaus auf die Tabelle zugreifen müssen, setzen Sie die Optionen ONLINE und RESUMABLE auf OFF und entfernen Sie die Klausel WAIT_AT_LOW_PRIORITY.

Weitere Informationen zur Indexpflege finden Sie unter Optimierung der Indexpflege zur Verbesserung der Abfrageleistung und Reduzierung des Ressourcenverbrauchs.

Mehrere Datendateien schrumpfen

Wie bereits erwähnt, ist das Schrumpfen mit Datenverschiebung ein langwieriger Prozess. Wenn die Datenbank mehrere Datendateien enthält, können Sie den Vorgang beschleunigen, indem Sie mehrere Datendateien parallel verkleinern. Dazu öffnen Sie mehrere Datenbanksitzungen und verwenden DBCC SHRINKFILE in jeder Sitzung mit einem anderen file_id-Wert. Ähnlich wie bei der Wiederherstellung von Indizes sollten Sie vor jedem neuen parallelen Verkleinerungsbefehl sicherstellen, dass Sie über genügend Ressourcen verfügen (CPU, Data IO, Log IO).

Der folgende Beispielbefehl verkleinert die Datendatei mit file_id 4 und versucht, die ihr zugewiesene Größe durch Verschieben von Seiten innerhalb der Datei auf 52000 MB zu reduzieren:

DBCC SHRINKFILE (4, 52000);

Wenn Sie den zugewiesenen Speicherplatz für die Datei so weit wie möglich reduzieren wollen, führen Sie die Anweisung ohne Angabe der Zielgröße aus:

DBCC SHRINKFILE (4);

Wenn ein Workload gleichzeitig mit Shrink läuft, kann er beginnen, den von Shrink freigegebenen Speicherplatz zu verwenden, bevor Shrink abgeschlossen ist und die Datei abgeschnitten wird. In diesem Fall ist es nicht möglich, den zugewiesenen Speicherplatz auf das angegebene Ziel zu verkleinern.

Sie können dies abmildern, indem Sie jede Datei in kleineren Schritten verkleinern. Das bedeutet, dass Sie im DBCC SHRINKFILE-Befehl ein Ziel festlegen, das etwas kleiner als der aktuell zugewiesene Speicherplatz für die Datei ist. Wenn der zugewiesene Speicherplatz für die Datei mit der Dateinummer 4 beispielsweise 200.000 MB beträgt und Sie auf 100.000 MB verkleinern möchten, können Sie das Ziel zunächst auf 170.000 MB setzen:

DBCC SHRINKFILE (4, 170000);

Nach Beendigung dieses Befehls wird die Datei abgeschnitten und ihre Größe auf 170.000 MB reduziert. Sie können dann diesen Befehl wiederholen, indem Sie das Ziel zuerst auf 140.000 MB, dann auf 110.000 MB usw. setzen, bis die Datei auf die gewünschte Größe geschrumpft ist. Wenn der Befehl ausgeführt wird, die Datei aber nicht abgeschnitten wird, verwenden Sie kleinere Schritte, z. B. 15.000 MB statt 30.000 MB.

Um den Fortschritt der Verkleinerung für alle gleichzeitig laufenden Verkleinerungssitzungen zu überwachen, können Sie die folgende Abfrage verwenden:

SELECT command,
       percent_complete,
       status,
       wait_resource,
       session_id,
       wait_type,
       blocking_session_id,
       cpu_time,
       reads,
       CAST(((DATEDIFF(s,start_time, GETDATE()))/3600) AS varchar) + ' hour(s), '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%3600)/60 AS varchar) + 'min, '
                     + CAST((DATEDIFF(s,start_time, GETDATE())%60) AS varchar) + ' sec' AS running_time
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.databases AS d
ON r.database_id = d.database_id
WHERE r.command IN ('DbccSpaceReclaim','DbccFilesCompact','DbccLOBCompact','DBCC');

Hinweis

Der Schrumpfungsprozess kann nicht linear verlaufen, und der Wert in der Spalte percent_complete kann über lange Zeiträume praktisch unverändert bleiben, obwohl die Schrumpfung noch im Gange ist.

Sobald die Verkleinerung für alle Datendateien abgeschlossen ist, führen Sie die Speicherplatzverwendungsabfrage aus, um die daraus resultierende Verringerung der zugewiesenen Speichergröße zu ermitteln. Wenn der Unterschied zwischen dem belegten und dem zugewiesenen Speicherplatz immer noch groß ist, können Sie die Indizes neu erstellen. Dadurch kann sich der zugewiesene Speicherplatz vorübergehend weiter erhöhen, aber ein erneutes Verkleinern der Datendateien nach dem Wiederaufbau der Indizes sollte zu einer stärkeren Reduzierung des zugewiesenen Speicherplatzes führen.

Vergrößern einer Protokolldatei

Fügen Sie in Azure SQL Managed Instance einer Protokolldatei Platz hinzu, indem Sie die vorhandene Protokolldatei vergrößern (sofern der Speicherplatz dies zulässt). Das Hinzufügen einer Protokolldatei zur Datenbank wird nicht unterstützt. Eine Transaktionsprotokolldatei ist ausreichend, es sei denn, der Protokollspeicherplatz und der Speicherplatz auf dem Volume, auf dem die Protokolldatei gespeichert ist, sind ausgeschöpft.

Um die Protokolldatei zu vergrößern, verwenden Sie die MODIFY FILE-Klausel der ALTER DATABASE-Anweisung unter Angabe der Syntax SIZE und MAXSIZE. Weitere Informationen finden Sie unter ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL).

Weitere Informationen finden Sie unter Empfehlungen.

Steuern einer Transaktionsprotokolldatei

Sie können die Anweisung ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL) verwenden, um die Vergrößerung einer Transaktionsprotokolldatei zu steuern. Beachten Sie Folgendes:

  • Verwenden Sie die Option SIZE, um die aktuelle Dateigröße in KB-, MB-, GB- und TB-Einheiten zu ändern.
  • Verwenden Sie die Option FILEGROWTH, um das Vergrößerungsinkrement zu ändern. Der Wert 0 gibt an, dass die automatische Vergrößerung deaktiviert und kein zusätzlicher Speicherplatz zulässig ist.
  • Verwenden Sie die Option MAXSIZE, um die maximale Größe einer Protokolldatei in KB-, MB-, GB- und TB-Einheiten zu steuern oder die Vergrößerung auf UNLIMITED festzulegen.

Empfehlungen

Es folgen einige allgemeine Empfehlungen für die Arbeit mit Transaktionsprotokolldateien:

  • Das mit der Option FILEGROWTH festgelegte automatische Vergrößerungsinkrement (autogrow) des Transaktionsprotokolls muss groß genug sein, um den Anforderungen der Workloadtransaktionen immer einen Schritt voraus sein. Die Schrittweite für die Dateivergrößerung sollte für eine Protokolldatei stets groß genug sein, um häufige Erweiterungen zu vermeiden. Ein guter Anhaltspunkt für die korrekte Anpassung der Größe eines Transaktionsprotokolls ist die Überwachung der Menge der Protokolldaten, die in folgenden Zeiträumen belegt werden:

    • Die Zeit, die erforderlich ist, um eine vollständige Sicherung durchzuführen, da Protokollsicherungen erst nach Beendigung der vollständigen Sicherung durchgeführt werden können
    • Die Zeit, die für die umfangreichsten Vorgänge zur Indexwartung erforderlich ist
    • Die Zeit, die für die Ausführung des größten Batches in einer Datenbank erforderlich ist

  • Wenn Sie die automatische Vergrößerung für Daten- und Protokolldateien mit dieser FILEGROWTH-Option festlegen, ist es möglicherweise besser, sie auf size statt auf percentage festzulegen, um eine bessere Steuerung der Wachstumsrate zu ermöglichen, da der Prozentsatz eine stetig steigende Zahl darstellt.

    • In Azure SQL Managed Instance kann die sofortige Dateiinitialisierung das Wachstum von Transaktionsprotokollen auf bis zu 64 MB begünstigen. Der Standardschritt bei der automatischen Vergrößerung für neue Datenbanken beträgt 64 MB. Ereignisse bei einer automatischen Zunahme bei Transaktionsprotokolldateien von mehr als 64 MB profitieren nicht von der sofortigen Dateiinitialisierung.
    • Als bewährte Methode sollten Sie für Transaktionsprotokolle den Wert der Option FILEGROWTH nicht auf über 1.024 MB festlegen.

  • Ein kleines automatisches Vergrößerungsinkrement (autogrow) könnte dazu führen, dass zu viele kleine VLFs generiert werden und darüber hinaus die Leistung beeinträchtigt. Informationen darüber, wie Sie die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls aller Datenbanken in einer bestimmten Instanz sowie die benötigten Wachstumsinkremente zum Erreichen der erforderlichen Größe ermitteln, finden Sie in diesem Skript zur Analyse und Korrektur von VLFs, das vom SQL Tiger Team bereitgestellt wird..

  • Ein großes automatisches Vergrößerungsinkrement kann zwei Probleme verursachen:

    • Ein großes automatisches Vergrößerungsinkrement kann dazu führen, dass die Datenbank pausiert, während der neue Speicherplatz zugewiesen wird, was möglicherweise zu Zeitüberschreitungen bei Abfragen führt.
    • Ein großes automatisches Vergrößerungsinkrement könnte dazu führen, dass wenige große VLFs generiert werden und darüber hinaus die Leistung beeinträchtigt. Informationen darüber, wie Sie die optimale VLF-Verteilung für die aktuelle Größe des Transaktionsprotokolls aller Datenbanken in einer bestimmten Instanz sowie die benötigten Wachstumsinkremente zum Erreichen der erforderlichen Größe ermitteln, finden Sie in diesem Skript zur Analyse und Korrektur von VLFs, das vom SQL Tiger Team bereitgestellt wird..

  • Auch bei aktiviertem autogrow-Inkrement 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. Weitere Informationen zum Ändern des Vergrößerungsinkrements finden Sie unter ALTER DATABASE-Optionen für Dateien und Dateigruppen (Transact-SQL).

  • Für Protokolldateien kann eine automatische Verkleinerung durchgeführt werden. Dies wird jedoch nicht empfohlen, und die Datenbankeigenschaft auto_shrink ist standardmäßig auf FALSE festgelegt. Wenn auto_shrink auf TRUE festgelegt ist, wird die Größe einer Datei nur dann automatisch verkleinert, wenn mehr als 25 Prozent des Speicherplatzes ungenutzt sind.

Nächste Schritte