Opzione SORT_IN_TEMPDB per gli indici
Quando si crea o si ricompila un indice, impostando l'opzione SORT_IN_TEMPDB su ON, è possibile indirizzare il motore di database SQL Server a usare tempdb per archiviare i risultati di ordinamento intermedi usati per compilare l'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
Man mano che il motore di database compila un indice, esegue le fasi seguenti:
Il motore di database analizza innanzitutto le pagine di dati della tabella di base per recuperare i valori chiave e 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. Il motore di database riprende quindi l'analisi della pagina dei dati fino a quando i buffer di ordinamento non vengono nuovamente compilati. 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.
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 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. 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. Per altre informazioni sull'inclusione di colonne non chiave, vedere Creare indici con colonne incluse.
Il motore di database unisce le esecuzioni ordinate delle righe foglia dell'indice in un unico flusso ordinato. Il componente di ordinamento merge del motore di database inizia con la prima pagina di ogni esecuzione di ordinamento, trova la chiave più bassa in tutte le pagine e passa tale riga foglia al componente di creazione dell'indice. Vengono quindi elaborate tutte le altre chiavi, dalla minore alla maggiore. 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. Quando sono state elaborate tutte le pagine di un extent di un'operazione di ordinamento, 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 non appena viene riempita completamente. Man mano che vengono scritte pagine foglia, il motore di database compila anche i livelli superiori dell'indice. Ogni pagina dell'indice di livello superiore viene scritta non appena viene riempita completamente.
SORT_IN_TEMPDB - opzione
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 delle pagine di dati delle quali viene eseguita l'analisi, 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 è più intenso nella seconda fase, durante la quale il processo di ordinamento in genere legge alternativamente le varie aree relative alle operazioni di ordinamento. Sia le operazioni di ordinamento che le pagine del nuovo indice vengono compilate nel filegroup di destinazione, Ciò significa che contemporaneamente il motore di database distribuisce le letture tra le esecuzioni di ordinamento, è necessario 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ò determinare una maggiore contiguità degli extent degli indici, in particolare se l'operazione CREATE INDEX non viene elaborata 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 separati in tempdb, la sequenza con cui vengono liberati non influisce 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 e pertanto aumenteranno le 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 usando l'opzione SORT_IN_TEMPDB e in un secondo momento si crea un indice cluster senza specificare l'opzione , il motore di database non usa l'opzione quando crea nuovamente l'indice non cluster.
Nota
Se un'operazione di ordinamento non è necessaria o può essere eseguita in memoria, l'opzione SORT_IN_TEMPDB viene ignorata.
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 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.
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 vengono elaborati e quindi liberati gli extent delle operazioni di ordinamento. 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. Un effetto collaterale di questo è che se la quantità di spazio disponibile è molto vicina alle dimensioni dell'indice finale, il motore di database riutilizzerà in genere gli extent di esecuzione dell'ordinamento molto rapidamente dopo che sono stati 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 cluster, deve essere disponibile una quantità di spazio libero corrispondete 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 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, incluse le righe di dati della tabella. Nel filegroup di destinazione deve essere disponibile spazio sufficiente per archiviare la struttura finale dell'indice, incluse le righe di dati della tabella e l'albero B 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, inclusa 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
Riorganizzare e ricompilare gli indici
Contenuto correlato
Configurare l'opzione di configurazione del server index create memory
Requisiti di spazio su disco per operazioni DDL sugli indici