SORT_IN_TEMPDB-Option für Indizes

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

Wenn Sie einen Index erstellen oder neu erstellen, können Sie die Option SORT_IN_TEMPDB auf ON festlegen, das SQL Server-Datenbankmodul an die Verwendung von tempdb weiterleiten, um die Zwischensortierungsergebnisse zu speichern, die zum Erstellen des Indexes verwendet 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 tempdbfinden Sie unter Konfigurieren der Serverkonfigurationsoption Speicher für Indexerstellung.

Phasen der Indexerstellung

Wenn das Datenbankmodul einen Index erstellt, durchläuft es die folgenden Phasen:

  • Das Datenbankmodul überprüft zunächst die Datenseiten der Basistabelle, um Schlüsselwerte abzurufen und erstellt eine Indexblattzeile für jede Datenzeile. Wenn die internen Puffer der Sortierung mit Indexeinträgen auf Blattebene aufgefüllt wurden, werden die Einträge sortiert und als Zwischensortierlauf auf den Datenträger geschrieben. Das Datenbankmodul setzt dann den Scan der Datenseite fort, bis die Sortierpuffer erneut ausgefü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, daher sind sie jedoch in der Regel kleiner als bei einem gruppierten Index. 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.

  • Das Datenbankmodul führt die sortierten Läufe von Indexblattzeilen in einem einzigen sortierten Datenstrom zusammen. Die Sortierzusammenführungskomponente des Datenbankmoduls beginnt mit der ersten Seite jeder Sortierausführung, findet den niedrigsten Schlüssel auf allen Seiten und übergibt diese Blattzeile an die Indexerstellungskomponente. Danach wird der nächste niedrigste Schlüssel verarbeitet, dann der darauf folgende usw. Wenn die letzte Indexzeile auf Blattebene aus einer Sortierlaufseite extrahiert wurde, wechselt der Prozess zur nächsten Seite dieses Sortierlaufs. Wenn alle Seiten in einem Sortierlaufblock verarbeitet worden sind, wird der Block 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. Als Blattseiten geschrieben werden, erstellt das Datenbankmodul auch die oberen Ebenen des Indexes. In jede Indexseite einer oberen Ebene wird geschrieben, wenn sie aufgefüllt wird.

SORT_IN_TEMPDB-Option

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. Das Verschieben der Schreib-/Leseköpfe nimmt in der zweiten Phase zu. 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 das Datenbankmodul gleichzeitig Lesevorgänge über die Sortiervorgänge verteilt, muss es regelmäßig zu den Index-Indizes springen, um neue Indexseiten zu schreiben, sobald sie 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.

Mithilfe der Option SORT_IN_TEMPDB stehen die Indexblöcke möglicherweise näher zusammen, besonders 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. Dabei können die Speicherorte der Indexblöcke bis zu einem gewissen Grad zufällig ausgewählt werden. 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 werden die Möglichkeiten verbessert, dass die Indexblöcke zusammenhängend sind.

Die Option SORT_IN_TEMPDB wirkt sich nur auf die aktuelle Anweisung aus. Ob der Index in tempdbsortiert wurde, wird nicht in Metadaten aufgezeichnet. Wenn Sie beispielsweise einen nicht gruppierten Index mithilfe der Option SORT_IN_TEMPDB erstellen und zu einem späteren Zeitpunkt einen gruppierten Index erstellen, ohne die Option anzugeben, verwendet das Datenbankmodul die Option nicht, wenn er den nicht gruppierten Index erneut erstellt.

Hinweis

Wenn kein Sortiervorgang erforderlich ist oder die Sortierung im Arbeitsspeicher erfolgen kann, wird die Option SORT_IN_TEMPDB ignoriert.

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 erzeugt einen Fehler, wenn nicht genügend freier Speicherplatz zur Verfügung steht und es eine Ursache dafür gibt, dass die Datenbanken keine automatische Vergrößerung durchführen können, um mehr Speicherplatz zu reservieren (wenn z. B. kein Datenträgerspeicher verfügbar ist oder die automatische Vergrößerung ausgeschaltet ist).

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 Block mit Sortierläufen freigegeben, nachdem er verarbeitet worden ist. 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 ist, dass das Datenbankmodul die Sortierausführungsausdehnungen in der Regel sehr schnell wiederverwendet, wenn der freie Speicherplatz sehr nah an der Größe des endgültigen Indexes liegt. Da die Blöcke mit Sortierläufen eher nach dem Zufallsprinzip freigegeben werden, wird dadurch die Kontinuität der Indexblöcke in dieser Szenario verringert. 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 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 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 Kontinuität der Tabelle und 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 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.

CREATE INDEX (Transact-SQL)

Neuorganisieren und Neuerstellen von Indizes

ALTER INDEX (Transact-SQL)

Konfigurieren der Serverkonfigurationsoption Speicher für Indexerstellung

Speicherplatzanforderungen für Index-DDL-Vorgänge