SORT_IN_TEMPDB-Option für Indizes

Gilt für:SQL ServerAzure SQL-DatenbankVerwaltete Azure SQL-InstanzSQL-Datenbank in Microsoft Fabric

Wenn Sie einen Index erstellen oder neu aufbauen, können Sie durch Festlegen der SORT_IN_TEMPDB-Option auf ON bestimmen, dass die SQL Server-Datenbank-Engine tempdb zum Speichern der temporären Sortierergebnisse verwendet, die zum Erstellen des Indexes benötigt werden. Obwohl durch diese Option die Menge an Speicherplatz erhöht wird, die zur Indexerstellung verwendet wird, kann dadurch die Zeit verringert werden, die zum Erstellen eines Indexes erforderlich ist, wenn tempdb auf einer anderen Gruppe von Datenträgern gespeichert ist als die Benutzerdatenbank. Weitere Informationen zu tempdb finden Sie unter Konfigurieren der Serverkonfigurationsoption „Speicher für die Indexerstellung“.

Phasen der Indexerstellung

Wenn die Datenbank-Engine einen Index erstellt, werden die folgenden Phasen durchlaufen:

• Zunächst scannt die Datenbank-Engine die Datenseiten, um Schlüsselwerte abzurufen, und erstellt eine Indexzeile auf Blattebene für jede Datenzeile. Wenn die internen Sortierpuffer mit Blattindexeinträgen gefüllt wurden, werden die Einträge sortiert und als intermediärer Sortierlauf auf den Datenträger geschrieben. Dann setzt die Datenbank-Engine den Scanvorgang der Datenseiten fort, bis die Puffer der Sortierung erneut aufgefüllt sind. Dieses Muster des Scannens mehrerer Datenseiten, gefolgt vom Sortieren und Schreiben eines Sortierlaufs, wird so lange fortgesetzt, bis alle Zeilen der Basistabelle verarbeitet worden sind.

  Bei einem gruppierten Index handelt es sich bei den Blattzeilen des Indexes um die Datenzeilen der Tabelle, sodass die Zwischensortierläufe alle Datenzeilen enthalten. In einem nicht gruppierten Index können die Blattzeilen Nichtschlüsselspalten enthalten, sind aber in der Regel kleiner als die eines gruppierten Indexes. Ein nicht gruppierter Sortierlauf kann jedoch umfangreich sein, wenn die Indexschlüssel groß sind oder wenn mehrere Nichtschlüsselspalten in den Index einbezogen sind. Weitere Informationen zum Einbeziehen von Nichtschlüsselspalten finden Sie unter Create Indexes with Included Columns.

• Die Datenbank-Engine führt die sortierten Läufe der Indexzeilen auf Blattebene in einem einzelnen, sortierten Strom zusammen. Die Sortierzusammenführungskomponente des Datenbankmoduls beginnt mit der ersten Seite jedes Sortierlaufs, ermittelt den niedrigsten Schlüssel auf allen Seiten und übergibt diese Blattzeile an die Komponente zur Indexerstellung. Danach wird der nächste niedrigste Schlüssel verarbeitet, dann der darauf folgende usw. Wenn die letzte Blattindexzeile aus einer Seite des Sortierlaufs extrahiert wird, geht der Prozess zur nächsten Seite dieses Sortierlaufs über. Wenn alle Seiten in einem Extent eines Sortierlaufs verarbeitet worden sind, wird der Extent freigegeben. Bei der Übergabe jeder Blattindexzeile an die Indexerstellungskomponente wird diese Zeile in einer Blattindexseite im Puffer eingeschlossen. In jede Blattseite wird geschrieben, wenn sie aufgefüllt wird. Während des Beschreibens von Blattseiten erstellt die Datenbank-Engine außerdem die oberen Ebenen des Indexes. Jede Indexseite einer höheren Ebene wird geschrieben, wenn sie gefüllt ist.

Option SORT_IN_TEMPDB (Sortieren in TEMPDB)

Wenn die SORT_IN_TEMPDB-Option auf OFF gesetzt ist (Standardeinstellung), werden die Sortierläufe in der Zieldateigruppe gespeichert. Während der ersten Phase der Indexerstellung werden durch die sich abwechselnden Lesevorgänge in den Basistabellenseiten und den Schreibvorgängen der Sortierläufe die Schreib-/Leseköpfe des Datenträgers von einem Bereich des Datenträgers in einen anderen Bereich verschoben. Die Köpfe befinden sich in dem Bereich der Datenseiten, während die Datenseiten gescannt werden. Sie werden in einen Bereich mit freiem Speicherplatz verschoben, wenn die Sortierpuffer aufgefüllt werden und der aktuelle Sortierlauf auf den Datenträger geschrieben werden muss. Anschließend werden sie wieder in den Bereich der Datenseiten verschoben, wenn der Seitenscanvorgang in der Tabelle fortgesetzt wird. Die Bewegung der Lese-/Schreibköpfe ist in der zweiten Phase größer. Zu dieser Zeit wechselt der Sortierprozess in der Regel die Lesevorgänge in jedem Sortierlaufbereich. Sowohl die Sortierläufe als auch die neuen Indexseiten werden in der Zieldateigruppe erstellt. Dies bedeutet, dass die Datenbank-Engine, während sie Lesevorgänge auf die Sortierläufe verteilt, gleichzeitig regelmäßig zu den Index-Extents springen muss, um neue Indexseiten zu schreiben, sobald diese gefüllt sind.

Falls die Option SORT_IN_TEMPDB auf ON festgelegt ist und tempdb auf einer anderen Datenträgergruppe als der Zieldateigruppe gespeichert ist, finden die Lesevorgänge der Datenseiten während der ersten Phase auf einem anderen Datenträger statt als die Schreibvorgänge in den Bereich der Sortierarbeit in tempdb. Dies bedeutet, dass die Lesevorgänge der Datenschlüssel auf dem Datenträger eher seriell auf dem Datenträger verlaufen und dass die Schreibvorgänge auf dem Datenträger mit tempdb ebenfalls seriell sind, genauso wie die Schreibvorgänge zum Erstellen des endgültigen Indexes. Auch wenn andere Benutzer die Datenbank verwenden und auf unterschiedliche Datenträgeradressen zugreifen, ist die Gesamtstruktur der Lese- und Schreibvorgänge viel effizienter, wenn die Option SORT_IN_TEMPDB angegeben ist.

Die Option SORT_IN_TEMPDB kann die Zusammenhängigkeit der Index-Extents verbessern, insbesondere wenn der CREATE INDEX-Vorgang nicht parallel ausgeführt wird. Die Blöcke im Bereich der Sortierarbeit werden im Hinblick auf ihren Speicherort in der Datenbank eher nach dem Zufallsprinzip freigegeben. Wenn die Bereiche der Sortierarbeit in der Zieldateigruppe enthalten sind, können sie bei der Freigabe der Blöcke der Sortierarbeit durch Anforderungen von Blöcken reserviert werden, in denen die Indexstruktur während ihrer Erstellung gespeichert werden soll. Dies kann die Positionen der Index-Extents bis zu einem gewissen Grad zufällig verteilen. Wenn die Sortierblöcke separat in tempdbgespeichert werden, steht die Abfolge, in der sie freigegeben werden, in keinem Zusammenhang mit dem Speicherort der Indexblöcke. Wenn darüber hinaus die Zwischensortierläufe in tempdb anstelle der Zieldateigruppe gespeichert werden, steht mehr Speicherplatz in der Zieldateigruppe zur Verfügung, Dadurch erhöht sich die Wahrscheinlichkeit, dass die Index-Extents zusammenhängend sind.

Die Option SORT_IN_TEMPDB wirkt sich nur auf die aktuelle Anweisung aus. In den Metadaten ist nicht verzeichnet, ob der Index in tempdb sortiert wurde oder nicht. Wenn Sie z. B. einen nicht gruppierten Index mithilfe der Option SORT_IN_TEMPDB und später einen gruppierten Index ohne Angabe dieser Option erstellen, verwendet die Datenbank-Engine diese Option nicht, wenn es den nicht gruppierten Index neu erstellt.

Anforderungen an den Datenträgerspeicher

Wenn Sie die Option SORT_IN_TEMPDB auf ON setzen, muss in tempdb genügend freier Speicherplatz zum Speichern der Zwischensortierläufe zur Verfügung stehen, und es muss in der Zieldateigruppe genügend Speicherplatz verfügbar sein, damit der neue Index gespeichert werden kann. Die CREATE INDEX Anweisung schlägt fehl, wenn nicht genügend freier Speicherplatz vorhanden ist, und es gibt einen Grund, warum die Datenbanken nicht automatisch wachsen können, um mehr Speicherplatz zu erhalten, z. B. kein Speicherplatz auf dem Datenträger oder die automatische Vergrößerung wird deaktiviert.

Falls SORT_IN_TEMPDB auf OFF gesetzt ist, muss der verfügbare Speicherplatz in der Zieldateigruppe ungefähr der Größe des endgültigen Indexes entsprechen. Während der ersten Phase werden die Sortierläufe erstellt, sie benötigen ungefähr gleich viel Speicherplatz wie der endgültige Index. Während der zweiten Phase wird jeder Extent eines Sortierlaufs freigegeben, nachdem er verarbeitet wurde. Die Blöcke mit Sortierläufen werden demnach ungefähr genauso häufig freigegeben, wie Blöcke zum Speichern der Seiten des endgültigen Indexes reserviert werden, sodass die gesamten Speicherplatzanforderungen nicht bedeutend über der Größe des endgültigen Indexes liegen. Ein Nebeneffekt davon ist, dass die Datenbank-Engine die Extents der Sortierläufe in der Regel sehr schnell nach ihrer Freigabe wiederverwendet, wenn die Menge des freien Speicherplatzes der Größe des endgültigen Indexes sehr nahe kommt. Da die Sortierlauf-Extents in eher zufälliger Weise freigegeben werden, verringert sich in diesem Szenario die Kontinuität der Index-Extents. Wenn SORT_IN_TEMPDB auf OFF gesetzt ist, wird die Kontinuität der Indexblöcke verbessert, wenn ausreichend freier Speicherplatz in der Zieldateigruppe verfügbar ist, sodass für die Indexblöcke ein zusammenhängender Pool anstatt der Blöcke, deren Zuordnung soeben aufgehoben wurde, mit Sortierläufen zugeordnet werden können.

Wenn Sie einen nicht gruppierten Index erstellen, muss folgender freier Speicherplatz verfügbar sein:

• Falls SORT_IN_TEMPDB auf ON gesetzt ist, muss in tempdb ausreichend freier Speicherplatz zur Verfügung stehen, um die Sortierläufe zu speichern, und es muss ausreichend freier Speicherplatz in der Zieldateigruppe vorhanden sein, um die endgültige Indexstruktur zu speichern. Die Sortierläufe enthalten die Blattzeilen des Indexes.

• Falls SORT_IN_TEMPDB auf OFF gesetzt ist, muss genügend freier Speicherplatz für das Speichern der endgültigen Indexstruktur in der Zieldateigruppe verfügbar sein. Die Kontinuität der Indexblöcke kann verbessert werden, wenn mehr freier Speicherplatz zur Verfügung steht.

Wenn Sie einen gruppierten Index für eine Tabelle erstellen, die über keine nicht gruppierten Indizes verfügen, muss die folgende Menge an Speicherplatz zur Verfügung stehen:

• Wenn SORT_IN_TEMPDB auf ON gesetzt ist, muss in tempdb ausreichend freier Speicherplatz zur Verfügung stehen, um die Sortierläufe zu speichern. Diese schließen die Datenzeilen der Tabelle ein. Es muss ausreichend freier Speicherplatz in der Zieldateigruppe vorhanden sein, um die endgültige Indexstruktur zu speichern. Dies schließt die Datenzeilen der Tabelle und des B-Baumes des Indexes ein. Sie müssen evtl. die Schätzung für Faktoren wie eine große Schlüsselgröße oder ein Füllfaktor mit einem niedrigen Wert entsprechend anpassen.

• Falls SORT_IN_TEMPDB auf OFF gesetzt ist, muss genügend freier Speicherplatz für das Speichern der endgültigen Tabelle in der Zieldateigruppe verfügbar sein. Dies schließt die Indexstruktur ein. Die Zusammenhängigkeit der Tabellen- und Index-Extents kann verbessert werden, wenn mehr freier Speicherplatz zur Verfügung steht.

Wenn Sie einen gruppierten Index für eine Tabelle erstellen, die über nicht gruppierten Indizes verfügen, muss die folgende Menge an Speicherplatz zur Verfügung stehen:

• Falls SORT_IN_TEMPDB auf ON gesetzt ist, muss in tempdb ausreichend freier Speicherplatz zur Verfügung stehen, um die Auflistung von Sortierläufen für den größten Index (üblicherweise der gruppierte Index) zu speichern, und es muss ausreichend freier Speicherplatz in der Zieldateigruppe vorhanden sein, um die endgültigen Strukturen aller Indizes zu speichern. Das schließt den gruppierten Index ein, der die Datenzeilen der Tabelle enthält.

• Falls SORT_IN_TEMPDB auf OFF gesetzt ist, muss genügend freier Speicherplatz für das Speichern der endgültigen Tabelle in der Zieldateigruppe verfügbar sein. Das schließt die Strukturen aller Indizes ein. Die Kontinuität der Tabelle und Indexblöcke kann verbessert werden, wenn mehr freier Speicherplatz zur Verfügung steht.

Verwandte Aufgaben

CREATE INDEX (Transact-SQL)

Neuorganisieren und Neuerstellen von Indizes

Verwandte Inhalte

ALTER INDEX (Transact-SQL)

Konfigurieren der Serverkonfigurationsoption Speicher für Indexerstellung

Speicherplatzanforderungen für Index-DDL-Vorgänge