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

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Analytics Platform System (PDW)

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 werden Indexwartungsmethoden, das Neuorganisieren eines Index und das Neuerstellen eines Index beschrieben. Außerdem wird eine Indexwartungsstrategie empfohlen, die potenzielle Leistungsverbesserungen gegen den für die Wartung erforderlichen Ressourcenverbrauch abwägt.

Konzepte: Indexfragmentierung und Seitendichte

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

• Für Indizes mit B-Struktur (Rowtree) liegt Fragmentierung vor, wenn Indizes über Seiten verfügen, bei denen die logische Reihenfolge innerhalb des Index basierend auf dem Schlüsselwert des Index nicht der physischen Reihenfolge der Indexseiten entspricht.

  Hinweis

  In der Dokumentation wird der Begriff „B-Struktur“ im Allgemeinen in Bezug auf Indizes verwendet. In Zeilenspeicherindizes implementiert die Datenbank-Engine eine B+-Struktur. Dies gilt nicht für Columnstore-Indizes oder In-Memory-Datenspeicher. 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. Das Hinzufügen von Zeilen in einer Tabelle kann beispielsweise dazu führen, dass im Rowstore-Index vorhandene Seiten 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, wenn zusätzliche E/A-Anforderungen erforderlich sind, um die Daten zu lesen. Die Abfrage erfordert dann zum Lesen der gleichen Datenmenge anstelle einer geringen Anzahl großer E/A-Anforderungen eine größere Anzahl kleiner E/A-Anforderungen.

• 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 Datenbank-Engine eine Seite während der Indexerstellung, Neuerstellung oder Neuorganisation Zeilen hinzufügt, 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.
• Eine geringe Seitendichte kann die Anzahl der mittleren Ebenen der B-Struktur 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. Wenn beispielsweise die Seitendichte aufgrund von Seitenteilungen im Laufe der Zeit abnimmt, kann der Optimierer für die Abfrage einen anderen Plan mit einem anderen Leistungs- und Ressourcenverbrauchsprofil kompilieren.

Messindexfragmentierung 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. Für Rowstore-Indizes können Sie mithilfe von sys.dm_db_index_physical_stats() die Fragmentierung und Seitendichte in einem bestimmten Index, in allen Indizes einer Tabelle oder indizierten Sicht, in allen Indizes einer Datenbank oder in allen Indizes sämtlicher Datenbanken 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(deleted_rows,0))/NULLIF(total_rows,0)

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

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 keine Langzeitsperren auf Objektebene aufrechterhalten werden und dass während des ALTER INDEX ... REORGANIZE-Vorgangs Abfragen oder Aktualisierungen der zugrunde liegenden Tabelle fortgesetzt werden können.

• Für Rowstore-Indizes defragmentiert die Datenbank-Engine nur die Blattebene von gruppierten und nicht gruppierten Indizes in Tabellen und Sichten, indem die Blattebenenseiten physisch neu geordnet werden, damit sie mit der logischen Reihenfolge der Blattknoten von links nach rechts übereinstimmen. 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.
• Bei der Verwendung von Columnstore-Indizes ist es möglich, dass der Deltastore nach dem Einfügen, Aktualisieren und Löschen von Daten im Laufe der Zeit nur noch aus mehreren kleinen Zeilengruppen besteht. 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 die Neuorganisation werden zudem Zeilen physisch entfernt, die im Columnstore als gelöscht markiert wurden. Die Neuorganisation eines Columnstore-Index kann zusätzliche CPU-Ressourcen zum Komprimieren von Daten erfordern. Während der Vorgang ausgeführt wird, kann die Leistung verlangsamt werden. Sobald die Daten jedoch komprimiert sind, verbessert sich die Abfrageleistung. Syntaxbeispiele finden Sie unter Beispiele: ColumnStore-Indizes.

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. Das neu organisieren von einem Offline-Index benötigt in der Regel weniger Zeit als das neu organisieren online aber es hält Sperren auf Objektebene für die Dauer des Rebuild-Vorgangs, wodurch Abfragen vom Zugriff auf die Tabelle oder Ansicht blockiert werden.

Die online durchgeführte Neuerstellung eines Index erfordert erst am Ende des Vorgangs Sperren auf Objektebene, die für eine kurze Zeitspanne gehalten werden müssen, um die Neuerstellung abschließen zu können. 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. Eine fortsetzbare Neuerstellung kann fortgesetzt werden, nachdem sie angehalten oder unterbrochen wurde. Oder sie kann abgebrochen werden, wenn die Neuerstellung nicht mehr erforderlich ist.

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 Erwerben der zugeordneten Sperren, bis die Neuerstellung abgeschlossen wurde. Syntaxbeispiele finden Sie unter Beispiele: Rowstore-Indizes.

• Bei Columnstore-Indizes wird durch die Neuerstellung die Fragmentierung entfernt, alle Deltastore-Zeilen werden in den Columnstore verschoben, und zum Löschen markierte Zeilen werden physisch gelöscht. Syntaxbeispiele finden Sie unter Beispiele: Columnstore-Indizes.

  Tipp

  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 in einigen Fällen ein nicht gruppierter Rowstore-Index neu erstellt werden, um durch Datenbeschädigung im Index verursachte Inkonsistenzen zu korrigieren.

Sie können weiterhin solche Inkonsistenzen im nicht gruppierten Index reparieren, indem Sie einen nicht gruppierten Index offline neu erstellen. Sie können die Inkonsistenzen eines nicht gruppierten Index jedoch nicht beheben, indem Sie den Index online neu erstellen, da der Onlineneuerstellungsmechanismus den vorhandenen nicht gruppierten Index als Grundlage für die Neuerstellung verwendet und somit die Inkonsistenzen bestehen bleiben. 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 einer Sicherung wiederherstellen. Möglicherweise können Sie 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 das Aktualisieren der Statistiken 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 Rowstore-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 erfolgt keine automatische Neuerstellung aller nicht gruppierten Rowstore-Indizes für dieselbe Tabelle:

• 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 erneute Erstellen oder Organisieren kleiner Rowstore-Indizes lässt sich die Fragmentierung normalerweise nicht verringern. Bis zu und einschließlich SQL Server 2014 (12.x) weist die SQL Server-Datenbank-Engine 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 Columnstore-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. Die Datenbank-Engine defragmentiert den Columnstore, indem Zeilen physisch gelöscht werden, die als gelöscht markiert wurden.

Neuerstellen einer Partition anstatt der gesamten Tabelle

Wenn der Index groß ist, nimmt das Neuerstellen der gesamten Tabelle viel Zeit in Anspruch, und es muss ausreichend Speicherplatz verfügbar sein, um während der Neuerstellung eine zusätzliche Kopie des gesamten Index speichern zu können.

Für partitionierte Tabellen müssen Sie nicht den gesamten Columnstore-Index neu erstellen, wenn die Fragmentierung nur in einigen Partitionen vorhanden ist, z. B. in Partitionen, bei denen sich die UPDATE-, DELETE- oder MERGE-Anweisung auf eine große Anzahl von Zeilen ausgewirkt hat.

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 beim Laden der Daten diese als Batches, die kleiner als 102.400 Zeilen sind, in eine Partition eingefügt werden, kann die Partition schließlich mehrere offene Zeilengruppen im Deltaspeicher enthalten. Beim Neuerstellen werden alle Deltastore-Zeilen in komprimierte Zeilengruppen im Columnstore verschoben.

Überlegungen zur Neuorganisation eines Columnstore-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:

• Es werden Zeilen physisch aus einer Zeilengruppe entfernt, wenn mindestens 10 % der Zeilen logisch gelöscht wurden. 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 eine Masseneinfügung von fünf Batches mit jeweils 102.400 Zeilen durchführen, erhalten Sie fünf komprimierte Zeilengruppen. Wenn Sie REORGANIZE ausführen, werden diese Zeilengruppen in einer komprimierten Zeilengruppe mit 512.000 Zeilen zusammengeführt. Dies setzt voraus, dass keine Wörterbuchumfangsbegrenzungen oder Arbeitsspeichereinschränkungen vorhanden sind.
• Die Datenbank-Engine versucht, Zeilengruppen, in denen mindestens 10 % der Zeilen als gelöscht markiert wurden, mit anderen Zeilengruppen zu kombinieren. 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 Neuorganisationsvorgang entfernt auch Zeilen, die im Columnstore als gelöscht markiert wurden.

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 eine unnötige Ressourcenverwendung zu vermeiden, vermeiden Sie die wahllose Durchführung von Indexwartung en. 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 berücksichtigt werden 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 Stichprobenquote für Statistiken oder aus anderen Gründen nicht optimal war, bietet der neu kompilierte Plan häufig eine bessere Qualität.

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. Tatsächlich lässt sich der gleiche Vorteil häufig zu wesentlich geringeren Ressourcenkosten erzielen, 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 die Workload immer merklich 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 brauchen Sie Indizes möglicherweise weniger häufig oder überhaupt nicht neu zu erstellen und können stattdessen regelmäßige Statistikaktualisierungen durchführen. Für einige Statistiken müssen Sie möglicherweise mithilfe der WITH SAMPLE ... PERCENT-Klausel oder WITH FULLSCAN-Klausel (nicht üblich) die Stichprobenquote erhöhen.
• Ü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 nach einem festen Zeitplan ausgefü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 eine Indexwartung erforderlich ist und die entsprechenden Ressourcenkosten akzeptabel sind, führen Sie die Wartung ggf. zu Zeiten mit geringer Ressourcennutzung durch.
• Testen Sie regelmäßig, da sich Ressourceneinsatzmuster 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 sich der kumulative Ressourcenverbrauch aller Workloads den Ressourcengrenzen nähert, kann das Neuerstellen oder Neuorganisieren die Leistung anderer Workloads aufgrund von Ressourcenkonflikten beeinträchtigen. Beispielsweise kann das Massenladen von Daten langsamer erfolgen, da die Transaktionsprotokoll-E/A aufgrund einer gleichzeitigen Indexneuerstellung bei 100 % liegt. In Azure SQL Managed Instance kann diese Auswirkung verringert werden, indem die Indexwartung in einer separaten Resource Governor-Workloadgruppe mit eingeschränkter Ressourcenzuordnung ausgeführt wird. Dies ist jedoch mit einer längeren Dauer der Indexwartung verbunden.
• 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 einer erheblichen Zunahme der Ressourcennutzung aufgrund der Indexwartung gerecht zu werden, ohne die Anwendungsleistung zu beeinträchtigen, müssen Kunden möglicherweise mehr Ressourcen bereitstellen und die Kosten erhöhen, ohne dass sich die Anwendungsleistung zwingend verbessert.
• In Pools für elastische Datenbanken werden Ressourcen von allen Datenbanken in einem Pool gemeinsam genutzt. Selbst wenn sich eine bestimmte Datenbank im Leerlauf befindet, kann sich das Ausführen der Indexwartung für diese Datenbank auf Anwendungsworkloads auswirken, die gleichzeitig in anderen Datenbanken in demselben Pool ausgeführt werden. 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 Verwendung von Replikaten mit Leseskalierung oder Georeplikation erhöht sich häufig die Datenlatenz für Replikate, während die Indexwartung für das primäre Replikat ausgeführt wird. Wenn ein Georeplikat mit unzureichenden Ressourcen bereitgestellt wird, um eine Zunahme der Transaktionsprotokollgenerierung aufgrund der Indexwartung zu unterstützen, kann es weit hinter dem primären Replikat zurückbleiben, sodass ein erneutes Seeding für es erfolgt. Dadurch ist das Replikat erst verfügbar, wenn das erneute Seeding abgeschlossen ist. Darüber hinaus können Replikate, die für Hochverfügbarkeit verwendet werden, auf den Dienstebenen „Premium“ und „Unternehmenskritisch“ während der Indexwartung ebenfalls weit hinter dem primären Replikat zurückbleiben. Wenn ein Failover während oder kurz nach der Indexwartung erforderlich ist, kann dies länger als erwartet dauern.
• Wenn auf dem primären Replikat eine Indexneuerstellung ausgeführt wird und gleichzeitig auf einem Lesereplikat eine Abfrage mit langer Ausführungszeit erfolgt, wird die Abfrage möglicherweise automatisch beendet, damit der Wiederholungsthread auf dem Replikat nicht 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 belegten Speicherplatz in der Datenbank zu reduzieren, damit das Größenlimit des Tarifs nicht überschritten wird. Dadurch lässt sich der Wechsel zu einem höheren Tarif mit einem höheren Größenlimit vermeiden.
• Wenn Datendateien verkleinert werden müssen, erhöht sich durch das Neuerstellen oder Neuorganisieren von Indizes vor dem Verkleinern von Dateien die Seitendichte. Dies beschleunigt den Verkleinerungsvorgang, 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

Rowstore-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 für die Aufhebung der Zuordnung markierten Zuordnungseinheiten in kurzen Transaktionen physisch gelöscht, die im Hintergrund ausgeführt werden und nicht viele Sperren benötigen. 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 möglicherweise 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 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.

Statistikeinschrä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. Ab SQL Server 2012 (11.x) werden Statistiken jedoch nicht durch das Scannen aller Zeilen in der Tabelle erstellt oder aktualisiert, wenn ein partitionierter Index erstellt oder neu erstellt wird. 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 die Statistiken nicht aktualisiert.

Beispiele

Überprüfen der Fragmentierung und Seitendichte eines Rowstore-Index mit Transact-SQL

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 sehr lange dauern.

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 der Fragmentierung eines Columnstore-Indexes mit Transact-SQL

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

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,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, 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 AdventureWorks2022-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 AdventureWorksDW2022-Datenbank neu organisiert. Dieser Befehl erzwingt die Übernahme aller geschlossenen und offenen Zeilengruppen in den Columnstore.

-- 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 AdventureWorks2022-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 AdventureWorks2022-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 AdventureWorks2022-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.

Zugehöriger Inhalt

• Leitfaden zur Architektur und zum Design von SQL Server-Indizes
• Ausführen von Onlineindexvorgängen
• ALTER INDEX (Transact-SQL)
• Adaptive Indexdefragmentierung
• CREATE STATISTICS (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• Columnstore-Indizes: Abfrageleistung
• Erste Schritte mit Columnstore für die operative Echtzeitanalyse
• Columnstore-Indizes: Data Warehouse
• Columnstore-Indizes und Zusammenführungsrichtlinien für Zeilengruppen