Opzione SORT_IN_TEMPDB per gli indici

Si applica a:SQL Serverdatabase SQL di AzureAzure SQL Istanza gestitaDatabase SQL in Microsoft Fabric

Quando si crea o si ricompila un indice, l'impostazione dell'opzione SORT_IN_TEMPDB su ON consente al motore di database di SQL Server di usare tempdb per l'archiviazione dei risultati intermedi dell'ordinamento usati per la compilazione dell'indice. 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. Per altre informazioni su tempdb, vedere Configurare l'opzione di configurazione del server index create memory.

Fasi della compilazione di un indice

La compilazione di un indice nel motore di database include le fasi seguenti:

• Il motore di database 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. 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. A questo punto il motore di database riprende l'analisi delle pagine di dati fino a quando i buffer di ordinamento non vengono di nuovo riempiti completamente. 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.

  In un indice clusterizzato, le righe foglia dell'indice sono le righe di dati della tabella; pertanto, le esecuzioni di ordinamento intermedie contengono tutte le righe di dati. In un indice non clusterizzato, le righe di foglia possono contenere colonne non chiave, ma in genere sono più piccole di quelle di un indice clusterizzato. Se le chiavi dell'indice sono di grandi dimensioni o nell'indice sono incluse diverse colonne non chiave, un'esecuzione di ordinamento non clusterizzato può essere di grandi dimensioni. Per altre informazioni sull'inclusione di colonne non chiave, vedere Creare indici con colonne incluse.

• Il motore di database unisce i vari ordinamenti delle righe foglia dell'indice in un unico flusso ordinato. Il componente di merge dell'ordinamento del Motore di database inizia con la prima pagina di ogni esecuzione di ordinamento, individua la chiave più bassa tra tutte le pagine e passa quindi quella riga foglia al componente di creazione dell'indice. Viene quindi elaborata la chiave immediatamente successiva più bassa, poi quella seguente, e così via. Quando l'ultima riga indice foglia viene estratta da una pagina di una sequenza di ordinamento, il processo passa alla pagina successiva di quella sequenza di ordinamento. Quando tutte le pagine di un extent di sort run sono state elaborate, l'extent viene liberato. Ogni riga foglia passata al componente per la creazione dell'indice viene inserita in una pagina foglia dell'indice all'interno del buffer. Ogni pagina foglia viene scritta man mano che viene riempita. Man mano che vengono scritte le pagine foglia, il motore di database costruisce anche i livelli superiori dell'indice. Ogni pagina dell'indice di livello superiore viene scritta non appena viene riempita completamente.

Opzione SORT_IN_TEMPDB

Se l'opzione SORT_IN_TEMPDB è impostata su OFF (impostazione predefinita), le operazioni di ordinamento vengono archiviate nel filegroup di destinazione. 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. Le testine si trovano nell'area della pagina dati mentre le pagine dati vengono scansionate. 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. Il movimento delle testine di lettura/scrittura è maggiore nella seconda fase. In quel momento il processo di ordinamento legge in genere in modo alternato da ciascuna area di sort run. Sia le operazioni di ordinamento che le pagine del nuovo indice vengono compilate nel filegroup di destinazione, Ciò significa che, mentre il Motore di database distribuisce le letture tra i run di ordinamento, deve passare periodicamente agli extent di indice per scrivere nuove pagine di indice man mano che vengono riempite.

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

L'opzione SORT_IN_TEMPDB può migliorare la contiguità delle estensioni dell'indice, soprattutto se l'operazione CREATE INDEX non viene eseguita in parallelo. Gli extent dell'area delle operazioni di ordinamento vengono liberati in modo relativamente casuale rispetto alla relativa posizione nel 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. In una certa misura, ciò può comportare la distribuzione casuale degli extent dell'indice. Se gli extent di ordinamento sono archiviati separatamente in tempdb, la sequenza con cui vengono liberati non ha alcun effetto sulla posizione degli extent dell'indice. 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 spazio Ciò aumenta la probabilità che gli extent degli indici siano contigui.

L'opzione SORT_IN_TEMPDB ha effetto solo sull'istruzione corrente. Non sono presenti metadati che indicano se l'indice è stato ordinato o meno 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, il motore di database non la utilizza per ricreare l'indice non cluster.

Requisiti relativi allo spazio su disco

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. L'istruzione CREATE INDEX ha esito negativo se lo spazio libero disponibile è insufficiente e, per qualche motivo, i database non possono aumentare automaticamente le proprie dimensioni per ottenere più spazio, ad esempio perché non c'è spazio sul disco oppure l'aumento automatico è disattivato.

Se l'opzione SORT_IN_TEMPDB è impostata su OFF, lo spazio libero nel filegroup di destinazione deve corrispondere approssimativamente alle dimensioni dell'indice finale. Durante la prima fase vengono compilate le operazioni di ordinamento, che richiedono uno spazio quasi equivalente a quello necessario per l'indice finale. Durante la seconda fase, ciascun extent di run di ordinamento viene liberato dopo essere stato elaborato. 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. Uno degli effetti collaterali è rappresentato dal fatto che se la quantità di spazio libero equivale approssimativamente alle dimensioni dell'indice finale, il motore di database tenderà a riutilizzare gli extent delle operazioni di ordinamento non appena vengono liberati. Il fatto che gli extent delle operazioni di ordinamento vengano liberati in modo relativamente casuale rende meno continui gli extent dell'indice. 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.

Quando si crea un indice non clusterizzato, deve essere disponibile come spazio libero quanto segue:

• 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. Le operazioni di ordinamento includono le righe foglia dell'indice.

• 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. È possibile che gli extent dell'indice abbiano una maggiore continuità se è disponibile una maggiore quantità di spazio libero.

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:

• Se l'opzione SORT_IN_TEMPDB è impostata su ON, in tempdb deve essere disponibile spazio sufficiente per archiviare le operazioni di ordinamento, Queste includono le righe di dati della tabella. Nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la struttura finale dell'indice, Questo include le righe di dati della tabella e il B-tree dell'indice. Può essere necessario adeguare tale stima tenendo conto di fattori quali chiavi di grandi dimensioni oppure un fattore di riempimento con un valore basso.

• Se l'opzione SORT_IN_TEMPDB è impostata su OFF, nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la tabella finale, Questo include la struttura dell'indice. È possibile che gli extent della tabella e dell'indice abbiano una maggiore continuità se è disponibile una maggiore quantità di spazio libero.

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:

• 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, incluso l'indice cluster che contiene le righe di dati della tabella.

• Se l'opzione SORT_IN_TEMPDB è impostata su OFF, nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la tabella finale, incluse le strutture di tutti gli indici. È possibile che gli extent della tabella e dell'indice abbiano una maggiore continuità se è disponibile una maggiore quantità di spazio libero.

Attività correlate

CREATE INDEX (Transact-SQL)

Riorganizzare e ricompilare gli indici

Contenuto correlato

ALTER INDEX (Transact-SQL)

Impostare l'opzione di configurazione del server index create memory

Requisiti di spazio su disco per operazioni DDL sugli indici