Opzione SORT_IN_TEMPDB per gli indiciSORT_IN_TEMPDB Option For Indexes

QUESTO ARGOMENTO SI APPLICA A: sìSQL Server (a partire dalla versione 2016)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Quando si crea o si ricompila un indice, l'impostazione dell'opzione SORT_IN_TEMPDB su ON consente a Motore di database di SQL ServerSQL Server Database Engine di usare tempdb per l'archiviazione dei risultati intermedi dell'ordinamento usati per la compilazione dell'indice.When you create or rebuild an index, by setting the SORT_IN_TEMPDB option to ON you can direct the Motore di database di SQL ServerSQL Server Database Engine to use tempdb to store the intermediate sort results that are used to build the index. Questa opzione aumenta lo spazio su disco temporaneo necessario per creare un indice, ma può consentire di creare o ricompilare un indice in tempi più brevi se tempdb si trova in un set di dischi diverso rispetto al database utente.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. Per altre informazioni su tempdb, vedere Configurare l'opzione di configurazione del server index create memory.For more information about tempdb, see Configure the index create memory Server Configuration Option.

Fasi della compilazione di un indicePhases of Index Building

La compilazione di un indice in Motore di databaseDatabase Engine include le fasi seguenti:As the Motore di databaseDatabase Engine builds an index, it goes through the following phases:

  • Motore di databaseDatabase Engine esegue innanzitutto l'analisi delle pagine di dati della tabella di base per recuperare i valori di chiave e quindi compila una riga foglia dell'indice per ogni riga di dati.The Motore di databaseDatabase Engine first scans the data pages of the base table to retrieve key values and builds an index leaf row for each data row. Quando i buffer di ordinamento interni vengono riempiti completamente con le voci dell'indice di livello foglia, le voci vengono ordinate e scritte su disco come operazione di ordinamento intermedia.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. A questo punto Motore di databaseDatabase Engine riprende l'analisi delle pagine di dati fino a quando i buffer di ordinamento non vengono di nuovo riempiti completamente.The Motore di databaseDatabase Engine then resumes the data page scan until the sort buffers are again filled. Questo processo, che prevede l'analisi di più pagine di dati seguita dall'ordinamento e dalla registrazione di un'operazione di ordinamento, continua finché non vengono elaborate tutte le righe della tabella di base.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.

    Poiché in un indice cluster le righe foglia sono le righe dei dati della tabella, le operazioni di ordinamento intermedie includono tutte le righe di dati.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 un indice non cluster, le righe foglia possono contenere colonne non chiave, ma sono in genere di dimensioni inferiori rispetto a quelle di un indice cluster.In a nonclustered index, the leaf rows may contain nonkey columns, but are generally smaller than a clustered index. Se le chiavi dell'indice sono di grandi dimensioni o se l'indice contiene numerose colonne non chiave, un'operazione di ordinamento di un indice non cluster può essere estesa.If the index keys are large, or there are several nonkey columns included in the index, a nonclustered sort run can be large. Per altre informazioni sull'inclusione di colonne non chiave, vedere Creare indici con colonne incluse.For more information about including nonkey columns, see Create Indexes with Included Columns.

  • Motore di databaseDatabase Engine unisce i vari ordinamenti delle righe foglia dell'indice in un unico flusso ordinato.The Motore di databaseDatabase Engine merges the sorted runs of index leaf rows into a single, sorted stream. Il componente di Motore di databaseDatabase Engine che presiede all'unione delle operazioni di ordinamento inizia dalla prima pagina di ogni operazione, trova la chiave minore in tutte le pagine e quindi passa la riga foglia corrispondente al componente per la creazione dell'indice.The sort merge component of the Motore di databaseDatabase 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. Vengono quindi elaborate tutte le altre chiavi, dalla minore alla maggiore.The next lowest key is processed, and then the next, and so on. Dopo aver estratto l'ultima riga foglia dell'indice da una pagina di un'operazione di ordinamento, il processo passa alla pagina successiva della stessa operazione di ordinamento.When the last leaf index row is extracted from a sort run page, the process shifts to the next page from that sort run. Quando sono state elaborate tutte le pagine di un extent di un'operazione di ordinamento, l'extent viene liberato.When all the pages in a sort run extent have been processed, the extent is freed. Ogni riga foglia passata al componente per la creazione dell'indice viene inserita in una pagina foglia dell'indice all'interno del buffer.As each leaf index row is passed to the index create component, it is included in a leaf index page in the buffer. Ogni pagina foglia viene scritta non appena viene riempita completamente.Each leaf page is written as it is filled. Man mano che vengono scritte le pagine foglia, Motore di databaseDatabase Engine compila inoltre i livelli superiori dell'indice.As leaf pages are written, the Motore di databaseDatabase Engine also builds the upper levels of the index. Ogni pagina dell'indice di livello superiore viene scritta non appena viene riempita completamente.Each upper level index page is written when it is filled.

SORT_IN_TEMPDB - opzioneSORT_IN_TEMPDB Option

Se l'opzione SORT_IN_TEMPDB è impostata su OFF (impostazione predefinita), le operazioni di ordinamento vengono archiviate nel filegroup di destinazione.When SORT_IN_TEMPDB is set to OFF, the default, the sort runs are stored in the destination filegroup. Durante la prima fase della creazione dell'indice, l'alternarsi delle operazioni di lettura delle pagine della tabella di base e delle operazioni di scrittura delle operazioni di ordinamento comporta lo spostamento delle testine di lettura/scrittura tra le diverse aree del disco.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. Le testine si trovano nell'area delle pagine di dati delle quali viene eseguita l'analisi,The heads are in the data page area as the data pages are scanned. passano a un'area di spazio libero quando vengono riempiti completamente i buffer di ordinamento ed è necessario scrivere su disco l'operazione di ordinamento corrente, quindi tornano all'area delle pagine di dati non appena riprende l'analisi delle pagine della tabella.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. Il movimento delle testine di lettura/scrittura è più intenso nella seconda fase,The read/write head movement is greater in the second phase. durante la quale il processo di ordinamento in genere legge alternativamente le varie aree relative alle operazioni di ordinamento.At that time the sort process is typically alternating reads from each sort run area. Sia le operazioni di ordinamento che le pagine del nuovo indice vengono compilate nel filegroup di destinazione,Both the sort runs and the new index pages are built in the destination filegroup. a indicare che mentre Motore di databaseDatabase Engine distribuisce le letture tra le operazioni di ordinamento, deve passare periodicamente agli extent dell'indice per scrivere le nuove pagine dell'indice non appena vengono riempite completamente.This means that at the same time the Motore di databaseDatabase 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.

Se l'opzione SORT_IN_TEMPDB è impostata su ON e tempdb si trova in un set di dischi diverso rispetto al filegroup di destinazione, durante la prima fase le operazioni di lettura delle pagine di dati vengono eseguite in un disco diverso da quello in cui vengono eseguite le operazioni di scrittura nell'area delle operazioni di ordinamento 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. Di conseguenza, le letture da disco delle chiavi di dati tendono a procedere in modo più seriale nel disco e anche le operazioni di scrittura nel disco di tempdb e quelle necessarie per compilare l'indice finale sono seriali.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. Anche se altri utenti utilizzano il database e accedono a diverse aree dei dischi, lo schema generale di lettura e scrittura risulta più efficiente se viene specificata l'opzione SORT_IN_TEMPDB.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.

L'opzione SORT_IN_TEMPDB può determinare una maggiore contiguità degli extent degli indici, in particolare se l'operazione CREATE INDEX non viene elaborata in parallelo.The SORT_IN_TEMPDB option may improve the contiguity of index extents, especially if the CREATE INDEX operation is not being processed in parallel. Gli extent dell'area delle operazioni di ordinamento vengono liberati in modo relativamente casuale rispetto alla relativa posizione nel database.The sort work area extents are freed on a somewhat random basis with regard to their location in the database. Se le aree delle operazioni di ordinamento sono incluse nel filegroup di destinazione, man mano che vengono liberati gli extent di ordinamento possono essere acquisite dalle richieste di inclusione della struttura dell'indice negli extent mentre la struttura viene compilata.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. In una certa misura, ciò può comportare la distribuzione casuale degli extent dell'indice.This can randomize the locations of the index extents to a degree. Se gli extent di ordinamento sono separati in tempdb, la sequenza con cui vengono liberati non influisce sulla posizione degli extent dell'indice.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. Se le operazioni di ordinamento intermedie vengono archiviate in tempdb anziché nel filegroup di destinazione, inoltre, in quest'ultimo sarà disponibile una maggiore quantità di spazioAlso, when the intermediate sort runs are stored in tempdb instead of the destination filegroup, there is more space available in the destination filegroup. e pertanto aumenteranno le probabilità che gli extent degli indici siano contigui.This increases the chances that index extents will be contiguous.

L'opzione SORT_IN_TEMPDB ha effetto solo sull'istruzione corrente.The SORT_IN_TEMPDB option affects only the current statement. Non sono presenti metadati che indicano se l'indice è stato ordinato o meno in tempdb.No metadata records that the index was or was not sorted in tempdb. Ad esempio, se si crea un indice non cluster utilizzando l'opzione SORT_IN_TEMPDB e successivamente si crea un indice cluster senza specificare tale opzione, Motore di databaseDatabase Engine non la utilizza per ricreare l'indice non cluster.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 Motore di databaseDatabase Engine does not use the option when it re-creates the nonclustered index.

Nota

Se un'operazione di ordinamento non è necessaria o può essere eseguita in memoria, l'opzione SORT_IN_TEMPDB viene ignorata.If a sort operation is not required or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored.

Requisiti relativi allo spazio su discoDisk Space Requirements

Se si imposta l'opzione SORT_IN_TEMPDB su ON, è necessario che lo spazio disponibile su disco di tempdb sia sufficiente per contenere le operazioni di ordinamento intermedie e che lo spazio disponibile su disco del filegroup di destinazione sia sufficiente per contenere il nuovo indice.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. L'istruzione CREATE INDEX non viene eseguita correttamente se lo spazio libero non è sufficiente e se per qualche motivo non è possibile aumentare automaticamente le dimensioni del database in modo da acquisire lo spazio necessario. Ciò si verifica, ad esempio, se lo spazio su disco è insufficiente oppure se la funzione di aumento automatico delle dimensioni è disattivata.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.

Se l'opzione SORT_IN_TEMPDB è impostata su OFF, lo spazio libero nel filegroup di destinazione deve corrispondere approssimativamente alle dimensioni dell'indice finale.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. Durante la prima fase vengono compilate le operazioni di ordinamento, che richiedono uno spazio quasi equivalente a quello necessario per l'indice finale.During the first phase, the sort runs are built and require about the same amount of space as the final index. Durante la seconda fase vengono elaborati e quindi liberati gli extent delle operazioni di ordinamento.During the second phase, each sort run extent is freed after it has been processed. Poiché tali extent vengono liberati a una velocità analoga a quella con cui vengono acquisiti altri extent destinati alle pagine dell'indice finale, i requisiti di spazio complessivi non superano di molto le dimensioni dell'indice finale.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. Uno degli effetti collaterali è rappresentato dal fatto che se la quantità di spazio libero equivale approssimativamente alle dimensioni dell'indice finale, Motore di databaseDatabase Engine tenderà a riutilizzare gli extent delle operazioni di ordinamento non appena vengono liberati.One side effect of this is that if the amount of free space is very close to the size of the final index, the Motore di databaseDatabase Engine will generally reuse the sort run extents very quickly after they are freed. Il fatto che gli extent delle operazioni di ordinamento vengano liberati in modo relativamente casuale rende meno continui gli extent dell'indice.Because the sort run extents are freed in a somewhat random manner, this reduces the continuity of the index extents in this scenario. Se l'opzione SORT_IN_TEMPDB è impostata su OFF, la continuità degli extent dell'indice è maggiore se lo spazio libero nel filegroup di destinazione è sufficiente per consentire di allocare gli extent dell'indice da un pool contiguo anziché dagli extent delle operazioni di ordinamento appena deallocati.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.

Quando si crea un indice non cluster, deve essere disponibile una quantità di spazio libero corrispondete alle indicazioni seguenti:When you create a nonclustered index, you must have available as free space:

  • Se l'opzione SORT_IN_TEMPDB è impostata su ON, in tempdb deve essere disponibile spazio sufficiente per archiviare le operazioni di ordinamento e nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la struttura finale dell'indice.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. Le operazioni di ordinamento includono le righe foglia dell'indice.The sort runs contain the leaf rows of the index.

  • Se l'opzione SORT_IN_TEMPDB è impostata su OFF, nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la struttura finale dell'indice.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. È possibile che gli extent dell'indice abbiano una maggiore continuità se è disponibile una maggiore quantità di spazio libero.The continuity of the index extends may be improved if more free space is available.

    Quando si crea un indice cluster in una tabella che non contiene indici non cluster, deve essere disponibile una quantità di spazio libero corrispondente alle indicazioni seguenti:When you create a clustered index on a table that does not have nonclustered indexes, you must have available as free space:

  • Se l'opzione SORT_IN_TEMPDB è impostata su ON, in tempdb deve essere disponibile spazio sufficiente per archiviare le operazioni di ordinamento,If SORT_IN_TEMPDB is set to ON, there must be sufficient free space in tempdb to store the sort runs. incluse le righe di dati della tabella.These include the data rows of the table. Nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la struttura finale dell'indice,There must be sufficient free space in the destination filegroup to store the final index structure. incluse le righe di dati della tabella e l'albero B dell'indice.This includes the data rows of the table and the index B-tree. Può essere necessario adeguare tale stima tenendo conto di fattori quali chiavi di grandi dimensioni oppure un fattore di riempimento con un valore basso.You may have to adjust the estimate for factors such as having a large key size or a fill factor with a low value.

  • Se l'opzione SORT_IN_TEMPDB è impostata su OFF, nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la tabella finale,If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. inclusa la struttura dell'indice.This includes the index structure. È possibile che gli extent della tabella e dell'indice abbiano una maggiore continuità se è disponibile una maggiore quantità di spazio libero.The continuity of the table and index extents may be improved if more free space is available.

    Quando si crea un indice cluster in una tabella che contiene indici non cluster, deve essere disponibile una quantità di spazio libero corrispondente alle indicazioni seguenti:When you create a clustered index on a table that has nonclustered indexes, you must have available as free space:

  • Se l'opzione SORT_IN_TEMPDB è impostata su ON, in tempdb deve essere disponibile spazio sufficiente per archiviare la raccolta delle operazioni di ordinamento relative all'indice di dimensioni maggiori (in genere l'indice cluster) e nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare le strutture finali di tutti gli indici,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. incluso l'indice cluster che contiene le righe di dati della tabella.This includes the clustered index that contains the data rows of the table.

  • Se l'opzione SORT_IN_TEMPDB è impostata su OFF, nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la tabella finale,If SORT_IN_TEMPDB is set to OFF, the free space in the destination filegroup must be large enough to store the final table. incluse le strutture di tutti gli indici.This includes the structures of all the indexes. È possibile che gli extent della tabella e dell'indice abbiano una maggiore continuità se è disponibile una maggiore quantità di spazio libero.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)

Riorganizzare e ricompilare gli indiciReorganize and Rebuild Indexes

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

Configurare l'opzione di configurazione del server index create memoryConfigure the index create memory Server Configuration Option

Requisiti di spazio su disco per operazioni DLL sugli indiciDisk Space Requirements for Index DDL Operations