• Articolo

tempdb e creazione dell'indice

Quando si crea o si ricostruisce un indice, l'impostazione dell'opzione SORT_IN_TEMPDB su ON consente a Motore di database di SQL Server di utilizzare tempdb per l'archiviazione dei risultati intermedi dell'ordinamento utilizzati per la creazione dell'indice. Questa opzione aumenta lo spazio su disco temporaneo necessario per creare un indice, ma può consentire di creare o ricostruire un indice in tempi più brevi se tempdb si trova in un set di dischi diverso da quello in cui si trova il database utente. Per ulteriori informazioni su tempdb, vedere Database tempdb.

Fasi della creazione di un indice

La creazione di un indice in Motore di database include le fasi seguenti:

  • Motore di database esegue innanzitutto la scansione delle pagine di dati della tabella di base per recuperare i valori di chiave e quindi crea 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 Motore di database riprende la scansione delle pagine di dati fino a quando i buffer di ordinamento non vengono di nuovo riempiti completamente. Questo processo, che prevede la scansione 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 ulteriori informazioni sull'inserimento di colonne non chiave, vedere Creazione di indici con colonne incluse.

  • Motore di database unisce i vari ordinamenti delle righe foglia dell'indice in un unico flusso ordinato. Il componente di Motore di database 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. 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 le pagine foglia, Motore di database crea inoltre 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 delle pagine di dati delle quali viene eseguita la scansione, 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 la scansione 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 create nel filegroup di destinazione, a indicare che mentre Motore di database 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.

Se l'opzione SORT_IN_TEMPDB è impostata su ON e tempdb si trova in un set di dischi diverso da quello del 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. Ciò significa che le letture da disco delle chiavi di dati tendono a procedere in modo più seriale nel disco e che anche le operazioni di scrittura nel disco di tempdb e quelle necessarie per creare 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 creata. In una certa misura, ciò può comportare la distribuzione casuale degli extent dell'indice. Se gli extent di ordinamento vengono inclusi separatamente in tempdb, la sequenza con cui vengono liberati non influisce sulla posizione degli extent dell'indice. Inoltre, se le operazioni di ordinamento vengono archiviate in tempdb anziché nel filegroup di destinazione, 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 utilizzando l'opzione SORT_IN_TEMPDB e successivamente si crea un indice cluster senza specificare tale opzione, Motore di database non la utilizza per ricreare 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 di spazio su disco

Se si imposta l'opzione SORT_IN_TEMPDB su ON, è necessario che lo spazio libero in tempdb sia sufficiente per includere le operazioni di ordinamento intermedie e che lo spazio libero nel filegroup di destinazione sia sufficiente per includere 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 create 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. Uno degli effetti collaterali è rappresentato dal fatto che se la quantità di spazio libero equivale approssimativamente alle dimensioni dell'indice finale, 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 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 le 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.