SORT_IN_TEMPDB-Option für IndizesSORT_IN_TEMPDB Option For Indexes

Dieses Thema gilt für: JaSQL ServerJaAzure SQL-DatenbankkeineAzure SQL Data Warehouse keine Parallel DatawarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Wenn Sie einen Index erstellen oder neu erstellen, indem Sie die SORT_IN_TEMPDB-Option auf ON setzen, wird SQL Server-DatenbankmodulSQL Server Database Engine angewiesen, zum Speichern der Zwischenergebnisse der Sortierung für die Erstellung eines Indexes tempdb zu verwenden.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the SQL Server-DatenbankmodulSQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. 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.Although this option increases the amount of temporary disk space that is used to create an index, the option could reduce the time that is required to create or rebuild an index when tempdb is on a set of disks different from that of the user database. Weitere Informationen zu tempdbfinden Sie unter Configure the index create memory Server Configuration Option.For more information about tempdb, see Configure the index create memory Server Configuration Option.

Phasen der IndexerstellungPhases of Index Building

Wenn DatenbankmodulDatabase Engine einen Index erstellt, werden die folgenden Phasen durchlaufen:As the DatenbankmodulDatabase Engine builds an index, it goes through the following phases:

  • Zunächst scannt DatenbankmodulDatabase Engine die Datenseiten, um Schlüsselwerte abzurufen, und erstellt eine Indexzeile auf Blattebene für jede Datenzeile.The DatenbankmodulDatabase Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. 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.When the internal sort buffers have been filled with leaf index entries, the entries are sorted and written to disk as an intermediate sort run. Dann setzt DatenbankmodulDatabase Engine den Scanvorgang der Datenseiten fort, bis die Puffer der Sortierung erneut aufgefüllt sind.The DatenbankmodulDatabase Engine then resumes the data page scan until the sort buffers are again filled. 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.This pattern of scanning multiple data pages followed by sorting and writing a sort run continues until all the rows of the base table have been processed.

    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 a clustered index, the leaf rows of the index are the data rows of the table; therefore, the intermediate sort runs contain all the data rows. 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.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered 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.If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. Weitere Informationen zum Einbeziehen von Nichtschlüsselspalten finden Sie unter Create Indexes with Included Columns.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • DatenbankmodulDatabase Engine führt die sortierten Läufe der Indexzeilen auf Blattebene in einem einzelnen, sortierten Strom zusammen.The DatenbankmodulDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. Die Komponente von DatenbankmodulDatabase Engine für das Zusammenführen der Sortierung beginnt mit der ersten Seite jedes Sortierlaufs, sucht nach dem niedrigsten Schlüssel in allen Seiten und gibt die Blattzeile an die Indexerstellungskomponente weiter.The sort merge component of the DatenbankmodulDatabase Engine starts with the first page of each sort run, finds the lowest key in all the pages, and passes that leaf row to the index create component. Danach wird der nächste niedrigste Schlüssel verarbeitet, dann der darauf folgende usw.The next lowest key is processed, and then the next, and so on. Wenn die letzte Indexzeile auf Blattebene aus einer Sortierlaufseite extrahiert wurde, wechselt der Prozess zur nächsten Seite dieses Sortierlaufs.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. Wenn alle Seiten in einem Sortierlaufblock verarbeitet worden sind, wird der Block freigegeben.When all the pages in a sort run extent have been processed, the extent is freed. Bei der Übergabe jeder Blattindexzeile an die Indexerstellungskomponente wird diese Zeile in einer Blattindexseite im Puffer eingeschlossen.As each leaf index row is passed to the index create component, it is included in a leaf index page in the buffer. In jede Blattseite wird geschrieben, wenn sie aufgefüllt wird.Each leaf page is written as it is filled. Während des Beschreibens von Blattseiten erstellt DatenbankmodulDatabase Engine außerdem die oberen Ebenen des Indexes.As leaf pages are written, the DatenbankmodulDatabase Engine also builds the upper levels of the index. In jede Indexseite einer oberen Ebene wird geschrieben, wenn sie aufgefüllt wird.Each upper level index page is written when it is filled.

SORT_IN_TEMPDB-OptionSORT_IN_TEMPDB Option

Wenn die SORT_IN_TEMPDB-Option auf OFF gesetzt ist (Standardeinstellung), werden die Sortierläufe in der Zieldateigruppe gespeichert.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. 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.During the first phase of creating the index, the alternating reads of the base table pages and writes of the sort runs move the disk read/write heads from one area of the disk to another. Die Köpfe befinden sich in dem Bereich der Datenseiten, während die Datenseiten gescannt werden.The heads are in the data page area as the data pages are scanned. 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.They move to an area of free space when the sort buffers fill and the current sort run has to be written to disk, and then move back to the data page area as the table page scan is resumed. Das Verschieben der Schreib-/Leseköpfe nimmt in der zweiten Phase zu.The read/write head movement is greater in the second phase. Zu dieser Zeit wechselt der Sortierprozess in der Regel die Lesevorgänge in jedem Sortierlaufbereich.At that time the sort process is typically alternating reads from each sort run area. Sowohl die Sortierläufe als auch die neuen Indexseiten werden in der Zieldateigruppe erstellt.Both the sort runs and the new index pages are built in the destination filegroup. Dies bedeutet, dass zur gleichen Zeit, wenn das DatenbankmodulDatabase Engine Lesevorgänge auf die Sortierläufe verteilt, ein regelmäßiges Springen zu den Indexblöcken erforderlich ist, um neue Indexseiten zu schreiben, während sie aufgefüllt werden.This means that at the same time the DatenbankmodulDatabase Engine is spreading reads across the sort runs, it has to periodically jump to the index extents to write new index pages as they are filled.

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.If the SORT_IN_TEMPDB option is set to ON and tempdb is on a separate set of disks from the destination filegroup, during the first phase, the reads of the data pages occur on a different disk from the writes to the sort work area 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.This means the disk reads of the data keys generally continue more serially across the disk, and the writes to the tempdb disk also are generally serial, as do the writes to build the final index. 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.Even if other users are using the database and accessing separate disk addresses, the overall pattern of reads and writes are more efficient when SORT_IN_TEMPDB is specified than when it is not.

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.The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. Die Blöcke im Bereich der Sortierarbeit werden im Hinblick auf ihren Speicherort in der Datenbank eher nach dem Zufallsprinzip freigegeben.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. 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.If the sort work areas are contained in the destination filegroup, as the sort work extents are freed, they can be acquired by the requests for extents to hold the index structure as it is built. Dabei können die Speicherorte der Indexblöcke bis zu einem gewissen Grad zufällig ausgewählt werden.This can randomize the locations of the index extents to a degree. 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.If the sort extents are held separately in tempdb, the sequence in which they are freed has no effect on the location of the index extents. Wenn darüber hinaus die Zwischensortierläufe in tempdb anstelle der Zieldateigruppe gespeichert werden, steht mehr Speicherplatz in der Zieldateigruppe zur Verfügung,Also, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. Dadurch werden die Möglichkeiten verbessert, dass die Indexblöcke zusammenhängend sind.This increases the chances that index extents will be contiguous.

Die Option SORT_IN_TEMPDB wirkt sich nur auf die aktuelle Anweisung aus.The SORT_IN_TEMPDB option affects only the current statement. Ob der Index in tempdbsortiert wurde, wird nicht in Metadaten aufgezeichnet.No metadata records that the index was or was not sorted in tempdb. 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 DatenbankmodulDatabase Engine diese Option nicht, wenn es den nicht gruppierten Index neu erstellt.For example, if you create a nonclustered index using the SORT_IN_TEMPDB option, and at a later time create a clustered index without specifying the option, the DatenbankmodulDatabase Engine does not use the option when it re-creates the nonclustered index.

Hinweis

Wenn kein Sortiervorgang erforderlich ist oder die Sortierung im Arbeitsspeicher erfolgen kann, wird die Option SORT_IN_TEMPDB ignoriert.If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Anforderungen an den DatenträgerspeicherDisk Space Requirements

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.When you set the SORT_IN_TEMPDB option to ON, you must have sufficient free disk space available in tempdb to hold the intermediate sort runs, and enough free disk space in the destination filegroup to hold the new index. 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).The CREATE INDEX statement fails if there is insufficient free space and there is some reason the databases cannot autogrow to acquire more space, such as no space on the disk or autogrow is set to off.

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.If SORT_IN_TEMPDB is set to OFF, the available free disk space in the destination filegroup must be roughly the size of the final index. Während der ersten Phase werden die Sortierläufe erstellt, sie benötigen ungefähr gleich viel Speicherplatz wie der endgültige Index.During the first phase, the sort runs are built and require about the same amount of space as the final index. Während der zweiten Phase wird jeder Block mit Sortierläufen freigegeben, nachdem er verarbeitet worden ist.During the second phase, each sort run extent is freed after it has been processed. 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.This means that sort run extents are freed at about the same rate at which extents are acquired to hold the final index pages; therefore, the overall space requirements do not greatly exceed the size of the final index. Als Nebeneffekt hiervon tendiert DatenbankmodulDatabase Engine dazu, die Blöcke mit Sortierläufen sehr schnell nach ihrer Freigabe wieder zu verwenden, wenn die Menge an verfügbarem Speicherplatz ungefähr der Größe des endgültigen Indexes entspricht.One side effect of this is that if the amount of free space is very close to the size of the final index, the DatenbankmodulDatabase Engine will generally reuse the sort run extents very quickly after they are freed. 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.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. 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.If SORT_IN_TEMPDB is set to OFF, the continuity of the index extents is improved if there is sufficient free space available in the destination filegroup that the index extents can be allocated from a contiguous pool instead of from the freshly deallocated sort run extents.

Wenn Sie einen nicht gruppierten Index erstellen, muss die folgende Menge an Speicherplatz zur Verfügung stehen:When you create a nonclustered index, you must have available as free space:

  • 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.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs, and sufficient free space in the destination filegroup to store the final index structure. Die Sortierläufe enthalten die Blattzeilen des Indexes.The sort runs contain the leaf rows of the index.

  • 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.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final index structure. Die Kontinuität der Indexblöcke kann verbessert werden, wenn mehr freier Speicherplatz zur Verfügung steht.The continuity of the index extends may be improved if more free space is available.

    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:When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • Wenn SORT_IN_TEMPDB auf ON gesetzt ist, muss in tempdb ausreichend freier Speicherplatz zur Verfügung stehen, um die Sortierläufe zu speichern.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. Diese schließen die Datenzeilen der Tabelle ein.These include the data rows of the table. Es muss ausreichend freier Speicherplatz in der Zieldateigruppe vorhanden sein, um die endgültige Indexstruktur zu speichern.There must be sufficient free space in the destination filegroup to store the final index structure. Dies schließt die Datenzeilen der Tabelle und des B-Baumes des Indexes ein.This includes the data rows of the table and the index B-tree. 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.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • 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.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Dies schließt die Indexstruktur ein.This includes the index structure. Die Kontinuität der Tabelle und Indexblöcke kann verbessert werden, wenn mehr freier Speicherplatz zur Verfügung steht.The continuity of the table and index extents may be improved if more free space is available.

    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:When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • 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.If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the collection of sort runs for the largest index, typically the clustered index, and sufficient free space in the destination filegroup to store the final structures of all the indexes. Das schließt den gruppierten Index ein, der die Datenzeilen der Tabelle enthält.This includes the clustered index that contains the data rows of the table.

  • 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.If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. Das schließt die Strukturen aller Indizes ein.This includes the structures of all the indexes. Die Kontinuität der Tabelle und Indexblöcke kann verbessert werden, wenn mehr freier Speicherplatz zur Verfügung steht.The continuity of the table and index extents may be improved if more free space is available.

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)

Neuorganisieren und Neuerstellen von IndizesReorganize and Rebuild Indexes

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

Configure the index create memory Server Configuration OptionConfigure the index create memory Server Configuration Option

Speicherplatzanforderungen für Index-DDL-VorgängeDisk Space Requirements for Index DDL Operations