Optimale Wartung von Indizes zum Verbessern der Leistung und Verringern der Ressourcenauslastung

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzAnalytics Platform System (PDW)SQL-Datenbank in Microsoft Fabric

Dieser Artikel hilft Ihnen bei der Entscheidung, wann und wie die Indexwartung durchgeführt werden sollte. Es werden Konzepte wie Indexfragmentierung und Seitendichte sowie deren Auswirkungen auf die Abfrageleistung und den Ressourcenverbrauch behandelt. Es beschreibt zwei Indexwartungsmethoden: Neuorganisieren eines Indexes und Neuaufbauen eines Indexes. Der Artikel schlägt auch eine Indexwartungsstrategie vor, die potenzielle Leistungsverbesserungen gegen den für die Wartung erforderlichen Ressourcenverbrauch ausgleicht.

Konzepte: Indexfragmentierung und Seitendichte

Was ist Indexfragmentierung, und wie wirkt sie sich auf die Leistung aus?

• In B-Tree (Rowstore)-Indizes ist eine Fragmentierung vorhanden, wenn Indizes Seiten aufweisen, in denen die logische Sortierung innerhalb des Indexes basierend auf den Schlüsselwerten des Indexes nicht mit der physischen Reihenfolge von Indexseiten übereinstimmt.

  Note

  Die Dokumentation verwendet den Begriff B-Baum im Allgemeinen in Bezug auf Indizes. In Zeilenstoreindizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder Indizes auf speicheroptimierten Tabellen. Weitere Informationen finden Sie im Leitfaden zur Architektur und zum Entwerfen von SQL Server- und Azure SQL-Indizes.

• Die Datenbank-Engine verwaltet Indizes automatisch, wenn Einfüge-, Update- oder Löschvorgänge an den zugrunde liegenden Daten vorgenommen werden. Zum Beispiel kann das Hinzufügen von Zeilen in einer Tabelle dazu führen, dass vorhandene Seiten in Rowstore-Indizes aufgeteilt werden, um Platz für das Einfügen neuer Zeilen zu schaffen. Im Laufe der Zeit können diese Änderungen dazu führen, dass die Daten im Index in der Datenbank verstreut (fragmentiert) werden.

• Bei Abfragen, die mithilfe von vollständigen Scans oder Bereichsindexscans viele Seiten lesen, können stark fragmentierte Indizes die Abfrageleistung beeinträchtigen, da möglicherweise zusätzliche E/A-Anforderungen erforderlich sind, um die für die Abfrage erforderlichen Daten zu lesen. Anstelle einiger großer E/A-Anforderungen benötigt die Abfrage viele kleine E/A-Anforderungen, um dieselbe Datenmenge zu lesen.

• Wenn das Speichersubsystem bei sequenzieller E/A eine bessere Leistung als bei zufälliger E/A bietet, kann die Indexfragmentierung die Leistung beeinträchtigen, da zum Lesen fragmentierter Indizes ein größerer Betrag an zufälliger E/A erforderlich ist.

Was ist Seitendichte (auch als „Seitenfüllgrad“ bezeichnet), und wie wirkt sie sich auf die Leistung aus?

• Jede Seite in der Datenbank kann eine variable Anzahl von Zeilen enthalten. Wenn Zeilen den gesamten Platz auf einer Seite nehmen, beträgt die Seitendichte 100 %. Wenn eine Seite leer ist, beträgt die Seitendichte 0 %. Wenn eine Seite mit einer Dichte von 100 % auf zwei Seiten aufgeteilt wird, um eine neue Zeile aufnehmen zu können, beträgt die Dichte der beiden neuen Seiten ungefähr 50 %.
• Bei einer geringen Seitendichte sind mehr Seiten erforderlich, um die gleiche Datenmenge zu speichern. Dies bedeutet, dass mehr E/A erforderlich ist, um diese Daten zu lesen und zu schreiben, und dass mehr Arbeitsspeicher erforderlich ist, um diese Daten zwischenspeichern zu können. Wenn der Arbeitsspeicher begrenzt ist, werden weniger für eine Abfrage erforderliche Seiten zwischengespeichert, sodass die Datenträger-E/A weiter zunimmt. Folglich wirkt sich eine niedrige Seitendichte negativ auf die Leistung aus.
• Wenn das Datenbankmodul während der Indexerstellung Zeilen zu einer Seite hinzufügt, neu erstellt oder neu organisiert wird, wird die Seite nicht vollständig ausgefüllt, wenn der Füllfaktor für den Index auf einen anderen Wert als 100 (oder 0, was in diesem Kontext gleichwertig ist) festgelegt ist. Dies führt zu einer geringeren Seitendichte, erhöht den E/A-Aufwand und beeinträchtigt die Leistung.
• Niedrige Seitendichte kann die Anzahl der Zwischenschichten im B-Baum erhöhen. Hierdurch werden die CPU- und E/A-Kosten für die Suche nach Seiten auf Blattebene bei Indexscans und Suchvorgängen moderat erhöht.
• Wenn der Abfrageoptimierer einen Abfrageplan kompiliert, werden die Kosten der E/A-Vorgänge berücksichtigt, die zum Lesen der für die Abfrage benötigten Daten erforderlich sind. Bei geringer Seitendichte müssen mehr Seiten gelesen werden, daher sind die E/A-Kosten höher. Dies kann sich auf die Auswahl des Abfrageplans auswirken. Zum Beispiel kann der Optimierer, wenn die Seitendichte im Laufe der Zeit aufgrund von Seitensplits abnimmt, einen anderen Plan für dieselbe Abfrage mit einem unterschiedlichen Leistungs- und Ressourcenverbrauchsprofil erstellen.

Messen Sie die Indexfragmentierung und Seitendichte

Fragmentierung und Seitendichte gehören zu den Faktoren, die bei der Entscheidung zu berücksichtigen sind, ob eine Indexwartung durchgeführt werden soll und welche Wartungsmethode verwendet werden sollte.

Die Fragmentierung wird für Rowstore-Indizes und Columnstore-Indizes unterschiedlich definiert. Bei Rowstore-Indizes können Sie mit sys.dm_db_index_physical_stats() Fragmentierung und Seitendichte in einem bestimmten Index oder in mehreren Indizes bestimmen. Bei partitionierten Indizes stellt sys.dm_db_index_physical_stats() diese Informationen für jede Partition bereit.

Das von sys.dm_db_index_physical_stats zurückgegebene Resultset führt die folgenden Spalten auf:

Bei komprimierten Zeilengruppen in Columnstore-Indizes wird Fragmentierung als das Verhältnis zwischen gelöschten Zeilen und allen Zeilen definiert, ausgedrückt als Prozentsatz. Mit sys.dm_db_column_store_row_group_physical_stats können Sie die Anzahl aller Zeilen und der gelöschten Zeilen pro Zeilengruppe in einem bestimmten Index, in allen Indizes einer Tabelle oder in allen Indizes einer Datenbank bestimmen.

Das von sys.dm_db_column_store_row_group_physical_stats zurückgegebene Resultset führt die folgenden Spalten auf:

Die Fragmentierung komprimierter Zeilengruppen in einem Columnstore-Index kann mithilfe dieser Formel berechnet werden:

100.0 * (ISNULL(total_stored_deleted_rows, 0)) / NULLIF(total_rows, 0)

Um die Gesamtanzahl der physisch gespeicherten gelöschten Zeilen für einen nicht gruppierten Columnstore-Index zu ermitteln, fügen Sie die Werte in der deleted_rows Spalte in der Spalte sys.dm_db_column_store_row_group_physical_stats in rowssys.internal_partitions für den internen Objekttyp COLUMN_STORE_DELETE_BUFFER und dasselbe Objekt, denselben Index und dieselbe Partition hinzu. Ein Beispiel finden Sie unter Überprüfen der Fragmentierung eines Columnstore-Indexes.

Beispiele für Abfragen zum Bestimmen der Fragmentierung und Seitendichte finden Sie hier.

Indexwartungsmethoden: Neuorganisieren und Neuerstellen

Sie können die Indexfragmentierung reduzieren und die Seitendichte erhöhen, indem Sie eine der folgenden Methoden verwenden:

• Neuorganisieren eines Index
• Neuerstellen eines Indexes

Für partitionierte Indizes können beide der folgenden Methoden für alle Partitionen oder für eine einzelne Partition eines Index verwendet werden.

Neuorganisieren eines Index

Das Neuorganisieren eines Index ist weniger ressourcenintensiv als das Neuerstellen eines Index. Deshalb sollten Sie bevorzugt diese Indexwartungsmethode verwenden, es sei denn, es gibt einen bestimmten Grund für die Indexneuerstellung. Das Neuorganisieren wird immer online durchgeführt. Dies bedeutet, dass langfristige Sperren auf Objektebene nicht gehalten werden, und Abfragen oder Aktualisierungen der zugrunde liegenden Tabelle können während des ALTER INDEX ... REORGANIZE Vorgangs fortgesetzt werden.

• Bei Rowstore-Indizes defragiert das Datenbankmodul nur die Blattebene gruppierter und nicht gruppierter Indizes in Tabellen und Ansichten. Die Seiten auf Blattebene werden physisch neu angeordnet, um die logische Reihenfolge der Blattknoten von links nach rechts abzugleichen. Beim Neuorganisieren werden auch Indexseiten komprimiert, damit die Seitendichte dem Füllfaktor des Index entspricht. Verwenden Sie zum Anzeigen der Füllfaktoreinstellung sys.indexes. Syntaxbeispiele finden Sie unter Beispiele: Rowstore-Indizes.
• Beim Verwenden von Columnstore-Indizes kann der Delta-Speicher nach dem Einfügen, Aktualisieren und Löschen von Daten im Laufe der Zeit mit mehreren kleinen Zeilengruppen enden. Durch das Neuorganisieren eines Columnstore-Index werden aus Deltastore-Zeilengruppen komprimierte Zeilengruppen im Columnstore, und kleinere komprimierte Zeilengruppen werden zu größeren Zeilengruppen zusammengefasst. Durch den Neuorganisierungsvorgang werden auch zeilen entfernt, die im Columnstore als gelöscht markiert sind. Das Neuorganisieren eines Columnstore-Index kann zusätzliche CPU-Ressourcen erfordern, um Daten zu komprimieren. Während der Ausführung des Vorgangs kann die Leistung nachlassen. Sobald die Daten jedoch komprimiert sind, verbessert sich die Abfrageleistung. Syntaxbeispiele finden Sie unter Beispiele: ColumnStore-Indizes.

Ab SQL Server 2019 (15.x), Azure SQL Database und Azure SQL Managed Instance wird der Tupelverschiebungsvorgang von einem Mergetask im Hintergrund unterstützt, der automatisch kleinere offene Deltazeilengruppen komprimiert, die für einen bestimmten Zeitraum vorhanden waren (wie durch einen internen Schwellenwert festgelegt), oder komprimierten Zeilengruppen zusammenführt, aus denen eine große Anzahl von Zeilen gelöscht wurde. Dies verbessert die Qualität des Columnstore-Index im Lauf der Zeit. In den meisten Fällen entfällt dadurch die Notwendigkeit der Ausgabe von ALTER INDEX ... REORGANIZE-Befehlen.

Neuerstellen eines Indexes

Beim Neuerstellen eines Indexes wird der Index gelöscht und neu erstellt. Je nach Indextyp und Version der Datenbank-Engine kann ein Neuerstellungsvorgang online oder offline erfolgen. Die offline durchgeführte Neuerstellung eines Index erfordert in der Regel weniger Zeit als eine Onlineneuerstellung. Dabei werden jedoch für die Dauer des Neuerstellungsvorgangs Sperren auf Objektebene gehalten, sodass Abfragen nicht auf die Tabelle oder Sicht zugreifen können.

Eine Onlineindexneuerstellung erfordert keine Sperren auf Objektebene bis zum Ende des Vorgangs, wenn eine Sperre für eine kurze Dauer gehalten werden muss, um die Neuerstellung abzuschließen. Je nach Version der Datenbank-Engine kann eine Onlineneuerstellung des Index als fortsetzbarer Vorgang gestartet werden. Eine fortsetzbare Indexneuerstellung kann angehalten werden, sodass der bis zu diesem Punkt erzielte Fortschritt erhalten bleibt. Ein fortsetzbarer Neuerstellungsvorgang kann fortgesetzt werden, nachdem er angehalten oder unterbrochen wurde, oder abgebrochen werden, wenn es nicht mehr erforderlich ist, die Neuerstellung abzuschließen.

Informationen zur Transact-SQL-Syntax finden Sie unter ALTER INDEX REBUILD. Weitere Informationen zur Onlineindexneuerstellung finden Sie unter Ausführen von Onlineindexvorgängen.

• Bei Rowstore-Indizes wird durch die Neuerstellung die Fragmentierung auf allen Indexebenen entfernt, und die Seiten werden basierend auf dem angegebenen oder aktuellen Füllfaktor komprimiert. Wenn ALL angegeben ist, werden alle Indizes der Tabelle in einem einzelnen Vorgang gelöscht und neu erstellt. Wenn Indizes mit mindestens 128 Blöcken neu erstellt werden, verzögert die Datenbank-Engine das Aufheben der Seitenzuordnungen und das Erwerben der zugehörigen Sperren, bis der Neuerstellungsvorgang abgeschlossen ist. Syntaxbeispiele finden Sie unter Beispiele: Rowstore-Indizes.

• Für Columnstore-Indizes beseitigt das Neuaufbauen die Fragmentierung, verschiebt alle Delta-Store-Zeilen in den Columnstore und löscht physisch die Zeilen, die zur Löschung markiert sind. Syntaxbeispiele finden Sie unter Beispiele: Columnstore-Indizes.

  Tip

  Ab SQL Server 2016 (13.x) ist die Neuerstellung des Columnstore-Index in der Regel nicht erforderlich, weil REORGANIZE die Grundlagen der Neuerstellung in einem Onlinevorgang ausführt.

Verwenden der Indexneuerstellung zur Wiederherstellung nach Datenbeschädigung

Vor SQL Server 2008 (10.0.x) konnten Sie manchmal einen Rowstore-nicht gruppierten Index neu erstellen, um Inkonsistenzen aufgrund von Datenkorruption im Index zu beheben.

Sie können solche Inkonsistenzen im nicht gruppierten Index weiterhin beheben, indem Sie einen nicht gruppierten Index offline neu erstellen. Sie können jedoch nicht gruppierte Indexinkonsistenzen reparieren, indem Sie den Index online neu erstellen, da der Online-Neuerstellungsmechanismus den vorhandenen nicht gruppierten Index als Grundlage für die Neuerstellung verwendet und somit die Inkonsistenz überträgt. Bei der Offlineneuerstellung des Index kann manchmal eine Überprüfung des gruppierten Index (oder Heaps) erzwungen werden, sodass die inkonsistenten Daten im nicht gruppierten Index durch die Daten aus dem gruppierten Index oder Heap ersetzt werden.

Um sicherzustellen, dass der gruppierte Index oder Heap als Datenquelle verwendet wird, löschen Sie den nicht gruppierten Index, bevor Sie ihn neu erstellen. Wie bei früheren Versionen können Sie Inkonsistenzen beheben, indem Sie die betroffenen Daten aus einem Backup wiederherstellen. Möglicherweise können Sie jedoch nicht gruppierte Indexinkonsistenzen reparieren, indem Sie sie offline neu erstellen oder neu erstellen. Weitere Informationen finden Sie unter DBCC CHECKDB (Transact-SQL).

Automatische Verwaltung von Index und Statistiken

Verwenden Sie Lösungen wie Adaptive Index Defrag, um die Indexfragmentierung und Statistikaktualisierungen für eine oder mehrere Datenbanken automatisch zu verwalten. Diese Vorgehensweise entscheidet unter anderem anhand des Fragmentierungsgrads automatisch, ob ein Index neu erstellt oder neu organisiert wird, und aktualisiert Statistiken mit einem linearen Schwellenwert.

Überlegungen zur Neuerstellung und Neuorganisation eines Zeilenstore-Index

Die folgenden Szenarien bewirken, dass alle nicht gruppierten Rowstore-Indizes für eine Tabelle automatisch neu erstellt werden:

• Erstellen eines gruppierten Index für eine Tabelle, einschließlich der Neuerstellung des gruppierten Index mit einem anderen Schlüssel, mithilfe von CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
• Löschen eines gruppierten Index, was zur Folge hat, dass die Tabelle als Heap gespeichert wird

In den folgenden Szenarien werden nicht automatisch alle nicht gruppierten Rowstore-Indizes in derselben Tabelle neu erstellt:

• Neuerstellen eines gruppierten Index
• Ändern des Speichers des gruppierten Index, beispielsweise Anwenden eines Partitionierungsschemas oder Verschieben des gruppierten Index in eine andere Dateigruppe

Wenn ALL mit der ALTER INDEX ... REORGANIZE-Anweisung angegeben wird, werden gruppierte Indizes, nicht gruppierte Indizes und XML-Indizes der Tabelle neu organisiert.

Durch das Neuerstellen oder Neuorganisieren kleiner Rowstoreindizes wird in der Regel die Fragmentierung nicht reduziert. Bis zu und einschließlich SQL Server 2014 (12.x) weist das SQL Server-Datenbankmodul Speicherplatz mithilfe gemischter Erweiterungen zu. Daher werden Seiten kleiner Indizes manchmal in gemischten Blöcken gespeichert, wodurch diese Indizes implizit fragmentiert werden. Da gemischte Blöcke von bis zu acht Objekten gemeinsam genutzt werden, lässt sich die Fragmentierung in einem kleinen Index durch die erneute Erstellung oder Organisation des Indexes möglicherweise nicht verringern.

Überlegungen zur Neuerstellung eines Spaltenstore-Indexes

Bei der Neuerstellung eines Columnstore-Index liest die Datenbank-Engine alle Daten aus dem ursprünglichen Columnstore-Index, einschließlich des Deltastore. Dabei werden Daten in neuen Zeilengruppen kombiniert und alle Zeilengruppen in einem Columnstore komprimiert. Das Datenbankmodul defragiert den Columnstore, indem zeilen physisch gelöscht werden, die als gelöscht markiert sind.

Neuerstellen einer Partition anstatt der gesamten Tabelle

Das Neuerstellen der gesamten Tabelle dauert lange, wenn der Index groß ist und genügend Speicherplatz benötigt, um eine Kopie des gesamten Indexes während der Neuerstellung zu speichern.

Bei partitionierten Tabellen müssen Sie den gesamten Columnstore-Index nicht neu erstellen, wenn die Fragmentierung nur in einigen Partitionen vorhanden ist, z. B. in Partitionen, in denen UPDATE, DELETEoder MERGE Anweisungen eine große Anzahl von Zeilen geändert haben.

Durch das Neuerstellen einer Partition nach dem Laden oder Ändern von Daten wird sichergestellt, dass alle Daten in komprimierten Zeilengruppen im Columnstore gespeichert werden. Wenn der Datenladeprozess Daten in eine Partition einfügt, die aus Batches mit weniger als 102.400 Zeilen besteht, kann die Partition mit mehreren offenen Zeilengruppen im Delta-Store enden. Beim Neuerstellen werden alle Deltastore-Zeilen in komprimierte Zeilengruppen im Spaltenstore verschoben.

Überlegungen zur Neuorganisation eines Spaltenstore-Indexes

Bei der Neuorganisation eines Columnstore-Index komprimiert die Datenbank-Engine jede geschlossene Zeilengruppe im Deltastore als komprimierte Zeilengruppe im Columnstore. Ab SQL Server 2016 (13.x) und in Azure SQL-Datenbank führt der REORGANIZE-Befehl die folgenden Optimierungen für eine zusätzliche Defragmentierung online aus:

• Entfernt Zeilen physisch aus einer Zeilengruppe, wenn 10% oder mehr der Zeilen logisch gelöscht werden. Wenn z. B. in einer komprimierten Zeilengruppe von 1 Million Zeilen 100.000 Zeilen gelöscht wurden, entfernt die Datenbank-Engine die gelöschten Zeilen und komprimiert die Zeilengruppe mit 900.000 Zeilen neu, sodass sich der Speicherbedarf verringert.
• Eine oder mehrere komprimierte Zeilengruppen werden kombiniert, um die Anzahl der Zeilen pro Zeilengruppe auf maximal 1.048.576 Zeilen zu erhöhen. Wenn Sie beispielsweise fünf Batches mit jeweils 102.400 Zeilen massenweise einfügen, erhalten Sie fünf komprimierte Zeilengruppen. Wenn Sie REORGANIZE ausführen, werden diese Zeilengruppen mit einer komprimierten Zeilengruppe mit 512.000 Zeilen zusammengeführt. Dies setzt voraus, dass keine Wörterbuchumfangsbegrenzungen oder Arbeitsspeichereinschränkungen vorhanden sind.
• Das Datenbankmodul versucht, Zeilengruppen zu kombinieren, in denen 10% oder mehr zeilen mit anderen Zeilengruppen als gelöscht markiert werden. Beispiel: Zeilengruppe 1 ist komprimiert und hat 500.000 Zeilen, und Zeilengruppe 21 ist komprimiert und hat 1.048.576 Zeilen. In Zeilengruppe 21 sind 60 % der Zeilen als gelöscht markiert, wodurch noch 409.830 Zeilen vorhanden sind. Die Datenbank-Engine kombiniert diese beiden Zeilengruppen vorzugsweise, um eine neue Zeilengruppe mit 909.830 Zeilen zu komprimieren.

Nach dem Ausführen von Datenladevorgängen weist der Deltastore möglicherweise mehrere kleine Zeilengruppen auf. Sie können mit ALTER INDEX REORGANIZE das Verschieben dieser Zeilengruppen in den Columnstore erzwingen und dann kleinere komprimierte Zeilengruppen zu größeren komprimierten Zeilengruppen kombinieren. Der Vorgang zum Neuorganisieren entfernt auch Zeilen, die im Columnstore als gelöscht markiert sind.

Vor der Indexwartung zu berücksichtigende Überlegungen

Die Indexwartung durch Neuorganisieren oder Neuerstellen eines Index ist ressourcenintensiv. Sie verursacht einen erheblichen Anstieg der CPU-Auslastung, des belegten Arbeitsspeichers und der Speicher-E/A. Abhängig von der Datenbankworkload und anderen Faktoren können die Vorteile der Indexwartung erheblich oder minimal sein.

Um unnötige Ressourcenauslastungen zu vermeiden, vermeiden Sie die Indizierungswartung indiskriminiert. Stattdessen sollten die Leistungsvorteile der Indexwartung anhand der empfohlenen Strategie empirisch für jede Workload ermittelt und gegen die Ressourcenkosten und Auswirkungen auf die Workload abgewogen werden, die erforderlich sind, um diese Vorteile zu erzielen.

Die Wahrscheinlichkeit von Leistungsvorteilen durch das Neuorganisieren oder Neuerstellen eines Index ist höher, wenn der Index stark fragmentiert oder die Seitendichte gering ist. Dies sind jedoch nicht die einzigen Punkte, die sie berücksichtigen sollten. Faktoren wie Abfragemuster (Transaktionsverarbeitung oder Analyse und Berichterstellung), Verhalten des Speichersubsystems, verfügbarer Arbeitsspeicher und Verbesserungen der Datenbank-Engine spielen eine Rolle.

Eine positive Nebenwirkung der Indexneuerstellung

Kunden beobachten nach dem Neuerstellen von Indizes häufig Leistungsverbesserungen. In vielen Fällen stehen diese Verbesserungen jedoch in keinem Zusammenhang mit der Verringerung der Fragmentierung oder der Erhöhung der Seitendichte.

Eine Indexneuerstellung hat einen wichtigen Vorteil: Die Statistiken für Schlüsselspalten des Index werden aktualisiert, indem alle Zeilen im Index gescannt werden. Dies entspricht der Ausführung von UPDATE STATISTICS ... WITH FULLSCAN, wodurch Statistiken aktualisiert werden und manchmal deren Qualität im Vergleich zur Aktualisierung der standardmäßig aus Stichproben gewonnenen Statistiken verbessert wird. Beim Aktualisieren von Statistiken werden Abfragepläne, die auf sie verweisen, neu kompiliert. Wenn der vorherige Plan für eine Abfrage aufgrund veralteter Statistiken, unzureichender Statistisches Sampling-Verhältnis oder aus anderen Gründen nicht optimal war, führt der neu kompilierte Plan häufig besser aus.

Kunden führen diese Verbesserung häufig fälschlicherweise auf die Neuerstellung des Index sowie einer damit einhergehenden reduzierten Fragmentierung und erhöhten Seitendichte zurück. In Wirklichkeit kann derselbe Vorteil oft zu einem viel niedrigeren Ressourcenkosten erzielt werden, indem Statistiken aktualisiert werden, anstatt Indizes neu zu erstellen.

Strategie für die Indexwartung

Microsoft empfiehlt Kunden die folgende Indexwartungsstrategie:

• Gehen Sie nicht davon aus, dass die Indexwartung Ihre Arbeitslast immer spürbar verbessert.
• Messen Sie die spezifischen Auswirkungen der Neuorganisation oder Neuerstellung von Indizes auf die Abfrageleistung in der Workload. Der Abfragespeicher ist eine gute Möglichkeit, mit A/B-Tests die Leistung vor der Wartung und nach der Wartung zu messen.
• Wenn Sie feststellen, dass das Neuerstellen von Indizes die Leistung verbessert, versuchen Sie, stattdessen Statistiken zu aktualisieren. Dies kann zu einer ähnlichen Verbesserung führen. In diesem Fall müssen Sie möglicherweise die Indizes nicht so häufig oder überhaupt nicht neu erstellen und stattdessen regelmäßige Statistikaktualisierungen durchführen. Für einige Statistiken müssen Sie möglicherweise das Stichprobenverhältnis mithilfe der WITH SAMPLE ... PERCENT- oder WITH FULLSCAN-Klauseln erhöhen (dies ist nicht üblich).
• Überwachen Sie die Indexfragmentierung und Seitendichte im Zeitverlauf, um festzustellen, ob eine Korrelation zwischen diesen Werten mit dem Trend nach oben oder unten und der Abfrageleistung besteht. Wenn eine höhere Fragmentierung oder eine geringere Seitendichte die Leistung unzumutbar beeinträchtigt, können Sie Indizes neu organisieren oder neu erstellen. Häufig reicht es aus, nur bestimmte Indizes neu zu organisieren oder neu zu erstellen, die von Abfragen mit beeinträchtigter Leistung verwendet werden. Dadurch werden höhere Ressourcenkosten für die Wartung jedes Index in der Datenbank vermieden.
• Durch das Korrelieren der Fragmentierung/Seitendichte mit der Leistung können Sie auch die Häufigkeit der Indexwartung bestimmen. gehen Sie nicht davon aus, dass die Wartung in einem festen Zeitplan durchgeführt werden muss. Stattdessen empfiehlt es sich, die Fragmentierung und Seitendichte zu überwachen und die Indexwartung nach Bedarf auszuführen, bevor die Leistung unzumutbar beeinträchtigt wird.
• Wenn Sie festgestellt haben, dass die Indexwartung erforderlich ist und die Ressourcenkosten akzeptabel sind, führen Sie die Wartung während geringer Ressourcennutzungszeiten aus, falls möglich.
• Testen Sie regelmäßig, da sich die Ressourcennutzungsmuster im Laufe der Zeit ändern können.

Index-Wartung in Azure SQL-Datenbank und Azure SQL Managed Instance

Zusätzlich zu den oben genannten Überlegungen und der beschriebenen Strategie ist es in Azure SQL-Datenbank und Azure SQL Managed Instance besonders wichtig, die Kosten und Vorteile der Indexwartung zu berücksichtigen. Kunden sollten dies nur durchführen, wenn nachweislich ein Bedarf besteht, und die folgenden Punkte berücksichtigen.

• Azure SQL-Datenbank und Azure SQL Managed Instance implementieren Ressourcenkontrolle, um Begrenzungen für CPU-, Arbeitsspeicher- und E/A-Verbrauch gemäß dem bereitgestellten Tarif festzulegen. Diese Begrenzungen gelten für alle Benutzerworkloads, einschließlich der Indexwartung. Wenn der kumulative Ressourcenverbrauch aller Workloads die Ressourcenbeschränkungen erreicht, kann der Wiederherstellungs- oder Neuorganisationsvorgang die Leistung anderer Workloads aufgrund von Ressourcenengpässen beeinträchtigen. Beispielsweise können Massendatenlasten langsamer werden, da Transaktionsprotokoll-E/A aufgrund einer gleichzeitigen Indexerstellung bei 100 % liegt. In Azure SQL Managed Instance kann diese Auswirkung reduziert werden, indem die Indexwartung in einer separaten resource governor-Workloadgruppe mit eingeschränkter Ressourcenzuordnung auf Kosten der Verlängerung der Indexwartungsdauer ausgeführt wird.
• Um Kosten zu sparen, stellen Kunden häufig Datenbanken, Pools für elastische Datenbanken und verwaltete Instanzen mit einem minimalen Ressourcentoleranzbereich bereit. Es wird ein für Anwendungsworkloads ausreichender Tarif gewählt. Um einen signifikanten Anstieg der Ressourcennutzung aufgrund der Indexwartung zu bewältigen, ohne die Anwendungsleistung zu beeinträchtigen, müssen Kunden möglicherweise mehr Ressourcen bereitstellen und die Kosten erhöhen, ohne die Anwendungsleistung unbedingt zu verbessern.
• In Pools für elastische Datenbanken werden Ressourcen von allen Datenbanken in einem Pool gemeinsam genutzt. Selbst wenn eine bestimmte Datenbank im Leerlauf ist, kann die Durchführung von Indexwartungen an dieser Datenbank die gleichzeitig in anderen Datenbanken im selben Pool laufenden Anwendungs-Workloads beeinträchtigen. Weitere Informationen finden Sie unter Ressourcenverwaltung in dichten Pools für elastische Datenbanken.
• Bei den meisten in Azure SQL-Datenbank und Azure SQL Managed Instance verwendeten Speichertypen besteht kein Leistungsunterschied zwischen sequenzieller E/A und zufälliger E/A. Dies reduziert die Auswirkungen der Indexfragmentierung auf die Abfrageleistung.
• Bei der Verwendung von entweder Read Scale-out oder Geo-Replikation erhöht sich die Datenlatenz auf den Replikaten häufig, während die Indexwartung auf der primären Replik durchgeführt wird. Wenn ein Georeplikat mit unzureichenden Ressourcen bereitgestellt wird, um eine Zunahme der durch die Indexwartung verursachten Transaktionsprotokollgenerierung aufrechtzuerhalten, kann es weit hinter dem primären Liegen liegen, was dazu führt, dass das System es erneut erhält. Dadurch ist das Replikat erst verfügbar, wenn das erneute Seeding abgeschlossen ist. Darüber hinaus können Replikate, die für hohe Verfügbarkeit verwendet werden, in Premium- und Business Critical Service Tiers weit hinter der primären während der Indexwartung zurückkommen. Wenn ein Failover während oder kurz nach der Indexwartung erforderlich ist, kann dies länger als erwartet dauern.
• Wenn eine Indexneuerstellung im primären Replikat ausgeführt wird und eine lange ausgeführte Abfrage gleichzeitig für ein lesbares Replikat ausgeführt wird, kann die Abfrage automatisch beendet werden, um zu verhindern, dass der Redo-Thread für das Replikat blockiert wird.

Es gibt spezielle ungewöhnliche Szenarien, in denen in der Azure SQL-Datenbank und Azure SQL Managed Instance eine einmalige oder regelmäßige Indexwartung erforderlich sein kann:

• Um die Seitendichte zu erhöhen und den genutzten Speicherplatz in der Datenbank zu reduzieren, und somit innerhalb der Größenbeschränkung der Preiskategorie zu bleiben. Dadurch lässt sich der Wechsel zu einem höheren Tarif mit einem höheren Größenlimit vermeiden.
• Wenn es erforderlich wird, Dateien zu verkleinern, erwägen Sie, Indizes neu zu erstellen oder neu zu organisieren, bevor sie verkleinern, um die Seitendichte zu erhöhen. Dadurch wird der Verkleinerungsvorgang schneller, da weniger Seiten verschoben werden müssen. Weitere Informationen finden Sie unter:
  • Verwalten von Dateispeicherplatz für Datenbanken in Azure SQL-Datenbank
  • Verwalten von Dateispeicherplatz für Datenbanken in Azure SQL Managed Instance

Einschränkungen

Zeilenstore-Indizes mit mehr als 128 Blöcken werden in zwei getrennten Phasen neu erstellt: der logischen und der physischen Phase. In der logischen Phase werden die vorhandenen Zuordnungseinheiten, die vom Index verwendet werden, für die Aufhebung der Zuordnung markiert, die Datenzeilen werden kopiert und sortiert und dann in neue Zuordnungseinheiten verschoben, die erstellt werden, um den neu erstellten Index zu speichern. In der physischen Phase werden die zuvor zur Deallokation markierten Zuordnungseinheiten physisch in kurzen Transaktionen gelöscht, die im Hintergrund ablaufen und nicht viele Sperren erfordern. Weitere Informationen zu Zuordnungseinheiten finden Sie im Handbuch zur Architektur von Seiten und Blöcken.

Die ALTER INDEX REORGANIZE-Anweisung erfordert, dass die Datendatei mit dem Index über Platz verfügt, da der Vorgang temporäre Arbeitsseiten nur in derselben Datei zuordnen kann, nicht in einer anderen Datei derselben Dateigruppe. Auch wenn für die Dateigruppe freier Speicherplatz verfügbar ist, kann für den Benutzer trotzdem während des Neuorganisationsvorgangs der Fehler 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup auftreten, falls für eine Datendatei nicht genügend Speicherplatz verfügbar ist.

Ein Index kann nicht neu organisiert werden, wenn ALLOW_PAGE_LOCKS er auf OFF festgelegt ist.

Bis SQL Server 2017 (14.x) wird die Neuerstellung eines gruppierten Columnstore-Indexes als Offlinevorgang durchgeführt. Die Datenbank-Engine muss eine exklusive Sperre für die Tabelle oder Partition abrufen, während die Neuerstellung ausgeführt wird. Die Daten sind während der Neuerstellung offline und nicht verfügbar, selbst bei Verwendung von NOLOCK, Read Commited-Momentaufnahmeisolation (RCSI) oder Momentaufnahmeisolation. Ab SQL Server 2019 (15.x) kann ein gruppierter Columnstore-Index mit der Option ONLINE = ON neu erstellt werden.

Statistische Einschränkungen

• Wenn ein Index erstellt oder neu erstellt wird, werden Statistiken erstellt oder aktualisiert, indem alle Zeilen in der Tabelle gescannt werden. Dies entspricht der Verwendung der FULLSCAN-Klausel in CREATE STATISTICS oder UPDATE STATISTICS. Mit SQL Server 2012 (11.x) werden bei der Erstellung oder dem Neuaufbau eines partitionierten Indexes die Statistiken nicht durch das Scannen aller Zeilen in der Tabelle erstellt oder aktualisiert. Stattdessen wird die Standardstrichprobenquote verwendet. Verwenden Sie CREATE STATISTICS oder UPDATE STATISTICS mit der FULLSCAN-Klausel, um Statistiken zu partitionierten Indizes durch das Scannen aller Zeilen in der Tabelle zu erstellen oder zu aktualisieren.
• Wenn der Vorgang der Indexerstellung oder Indexneuerstellung fortsetzbar ist, werden Statistiken ebenfalls mit der Standardstichprobenquote erstellt oder aktualisiert. Bei fortsetzbaren Indexvorgängen wird zum Erstellen oder Aktualisieren von Statistiken die beibehaltene Stichprobenquote verwendet, wenn bei der Erstellung oder letzten Aktualisierung von Statistiken die PERSIST_SAMPLE_PERCENT-Klausel auf ON festgelegt war.
• Wenn ein Index neu organisiert wird, werden Statistiken nicht aktualisiert.

Examples

Überprüfen der Fragmentierung und Seitendichte eines Rowstore-Indexes

Im folgenden Beispiel werden die durchschnittliche Fragmentierung und Seitendichte für alle Rowstore-Indizes in der aktuellen Datenbank bestimmt. Dabei wird der SAMPLED-Modus verwendet, um schnell verwertbare Ergebnisse zurückzugeben. Verwenden Sie den DETAILED-Modus, um genauere Ergebnisse zu erzielen. Dies erfordert das Scannen aller Indexseiten und kann viel Zeit in Anspruch nehmen.

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_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
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;

Das von der vorherigen Anweisung zurückgegebene Resultset kann in etwa wie folgt aussehen:

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Weitere Informationen finden Sie unter sys.dm_db_index_physical_stats.

Überprüfen Sie die Fragmentierung eines Columnstore-Indexes

Im folgenden Beispiel wird die durchschnittliche Fragmentierung für alle Columnstore-Indizes mit komprimierten Zeilengruppen in der aktuellen Datenbank bestimmt.

WITH columnstore_row_group_partition
AS (SELECT object_id,
           index_id,
           partition_number,
           SUM(deleted_rows) AS partition_deleted_rows,
           SUM(total_rows) AS partition_total_rows
    FROM sys.dm_db_column_store_row_group_physical_stats
    WHERE state_desc = 'COMPRESSED'
    GROUP BY object_id, index_id, partition_number),
/* For nonclustered columnstore, include rows in the delete buffer */
 columnstore_internal_partition
AS (SELECT object_id,
           index_id,
           partition_number,
           SUM(rows) AS delete_buffer_rows
    FROM sys.internal_partitions
    WHERE internal_object_type_desc = 'COLUMN_STORE_DELETE_BUFFER'
    GROUP BY object_id, index_id, partition_number)
SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       crgp.partition_number,
       100.0 * (ISNULL(crgp.partition_deleted_rows + ISNULL(cip.delete_buffer_rows, 0), 0)) / NULLIF (crgp.partition_total_rows, 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
     INNER JOIN columnstore_row_group_partition AS crgp
         ON i.object_id = crgp.object_id
        AND i.index_id = crgp.index_id
     LEFT OUTER JOIN columnstore_internal_partition AS cip
         ON i.object_id = cip.object_id
        AND i.index_id = cip.index_id
        AND crgp.partition_number = cip.partition_number
ORDER BY schema_name, object_name, index_name, partition_number, index_type;

Das von der vorherigen Anweisung zurückgegebene Resultset kann in etwa wie folgt aussehen:

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Verwalten von Indizes mit SQL Server Management Studio

So organisieren oder erstellen Sie einen Index neu

1. Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie einen Index neu organisieren möchten.
2. Erweitern Sie den Ordner Tabellen .
3. Erweitern Sie die Tabelle, in der Sie einen Index neu organisieren möchten.
4. Erweitern Sie den Ordner Indizes .
5. Klicken Sie mit der rechten Maustaste auf den Index, den Sie neu organisieren möchten, und wählen Sie Neu organisierenaus.
6. Vergewissern Sie sich im Dialogfeld Indizes neu organisieren, dass der richtige Index im Raster Neu zu organisierende Indizes ausgewählt ist, und klicken Sie auf OK.
7. Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren , um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (Large Object, LOB) komprimiert werden sollen.
8. Wählen Sie "OK" aus.

Neuorganisieren aller Indizes in einer Tabelle

1. Erweitern Sie im Objekt-Explorer die Datenbank mit der Tabelle, in der Sie die Indizes neu organisieren möchten.
2. Erweitern Sie den Ordner Tabellen .
3. Erweitern Sie die Tabelle, in der Sie die Indizes neu organisieren möchten.
4. Klicken Sie mit der rechten Maustaste auf den Ordner Indizes , und wählen Sie Alle neu organisierenaus.
5. Vergewissern Sie sich im Dialogfeld Index neu organisieren , dass die richtigen Indizes im Raster Neu zu organisierende Indizesausgewählt sind. Um einen Index aus dem Raster Neu zu organisierende Indizes zu entfernen, wählen Sie den Index aus, und drücken Sie die ENTF-Taste.
6. Aktivieren Sie das Kontrollkästchen Spaltendaten großer Objekte komprimieren , um anzugeben, dass alle Seiten mit umfangreichen Objektdaten (Large Object, LOB) komprimiert werden sollen.
7. Wählen Sie "OK" aus.

Verwalten von Indizes mithilfe von Transact-SQL

Neuorganisieren eines Index

Im folgenden Beispiel wird der IX_Employee_OrganizationalLevel_OrganizationalNode-Index für die HumanResources.Employee-Tabelle in der AdventureWorks2025-Datenbank neu organisiert.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

Im folgenden Beispiel wird der IndFactResellerSalesXL_CCI-Columnstore-Index für die dbo.FactResellerSalesXL_CCI-Tabelle in der AdventureWorksDW2025-Datenbank neu organisiert. Dieser Befehl erzwingt alle geschlossenen und geöffneten Zeilengruppen in Spaltenstore.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Neuorganisieren aller Indizes in einer Tabelle

Im folgenden Beispiel werden alle Indizes für die HumanResources.Employee-Tabelle in der AdventureWorks2025-Datenbank neu organisiert.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Neuerstellen eines Indexes

Im folgenden Beispiel wird ein einzelner Index für die Employee-Tabelle der AdventureWorks2025-Datenbank neu erstellt.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

So erstellen Sie alle Indizes in einer Tabelle neu

Im folgende Beispiel werden alle Indizes, die der Tabelle in der AdventureWorks2025-Datenbank zugeordnet sind, mithilfe des Schlüsselworts ALL neu erstellt. Es werden drei Optionen angegeben.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

Weitere Informationen finden Sie unter ALTER INDEX.

Verwandte Inhalte

• Leitfaden zur Architektur und zum Design von SQL Server-Indizes
• Ausführen von Onlineindexvorgängen
• ALTER INDEX (Transact-SQL)
• Adaptive Indexdefragmentierung
• ERSTELLEN SIE STATISTIKEN (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• Columnstore-Indizes – Abfrageleistung
• Erste Schritte mit Columnstore-Indizes für Echtzeit-Betriebsanalysen
• Columnstore-Indizes im Datenbank-Management
• Spaltenstore-Indizes und Zusammenführungsrichtlinien für Zeilengruppen