Riorganizzazione e ricompilazione degli indici

Rilevamento del grado di frammentazione

Il primo passaggio nel decidere il metodo di deframmentazione da utilizzare è eseguire un'analisi dell'indice per determinare il grado di frammentazione. La funzione di sistema sys.dm_db_index_physical_stats consente di rilevare la frammentazione in un indice specifico, in tutti gli indici di una tabella o vista indicizzata, in tutti gli indici di un database o in tutti gli indici di tutti i database. Per gli indici partizionati, sys.dm_db_index_physical_stats fornisce inoltre informazioni sulla frammentazione per ogni partizione.

Il set di risultati restituito dalla funzione sys.dm_db_index_physical_stats include le colonne seguenti.

Una volta noto il grado di frammentazione, utilizzare la tabella seguente per determinare il metodo migliore per la correzione della frammentazione.

* È possibile eseguire la ricompilazione di un indice online oppure offline. La riorganizzazione di un indice viene sempre eseguita online. Per ottenere una disponibilità simile a quella offerta dall'opzione di riorganizzazione è necessario ricompilare gli indici in modalità online.

Questi valori costituiscono un'indicazione approssimativa per determinare il punto in cui passare da ALTER INDEX REORGANIZE a ALTER INDEX REBUILD. I valori effettivi, in realtà, variano da caso a caso. È importante riuscire a determinare la soglia migliore per l'ambiente in uso.

Non è consigliabile utilizzare questi comandi per livelli ridotti di frammentazione (inferiori al 5%) poiché i vantaggi offerti dalla rimozione di una frammentazione così limitata sono praticamente annullati dal costo della riorganizzazione o della ricompilazione dell'indice.

Esempio

USE AdventureWorks2008R2;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Riorganizzazione di un indice

Per riorganizzare uno o più indici, utilizzare l'istruzione ALTER INDEX con la clausola REORGANIZE. Questa istruzione sostituisce l'istruzione DBCC INDEXDEFRAG. Per riorganizzare una singola partizione di un indice partizionato, utilizzare la clausola PARTITION di ALTER INDEX.

La riorganizzazione di un indice deframmenta il livello foglia degli indici cluster e non cluster delle tabelle e delle viste riordinando fisicamente le pagine al livello foglia, in modo che l'ordine corrisponda a quello logico (da sinistra a destra) dei nodi foglia. Il fatto che le pagine siano in ordine migliora le prestazioni delle operazioni di analisi degli indici. La riorganizzazione viene eseguita nell'ambito delle pagine esistenti allocate all'indice. Non vengono allocate nuove pagine. Se un indice si espande su più file, i file vengono riorganizzati uno alla volta. Non viene eseguita la migrazione delle pagine da un file all'altro.

La riorganizzazione, inoltre, compatta le pagine dell'indice. Le eventuali pagine vuote create in seguito alla compattazione vengono eliminate, liberando spazio su disco. La compattazione viene eseguita in base al valore del fattore di riempimento della vista del catalogo sys.indexes.

Il processo di riorganizzazione utilizza una quantità minima di risorse di sistema e viene eseguito automaticamente in modalità online. Il processo non applica blocchi di lunga durata, pertanto non blocca le query o gli aggiornamenti in esecuzione.

La riorganizzazione è da preferire quando l'indice non è molto frammentato. Le linee guida sulla frammentazione sono riportate nella tabella precedente. Se l'indice è molto frammentato si otterranno risultati migliori eseguendo una ricompilazione.

Compattazione di dati LOB (Large Object)

Ricompilazione di un indice

Durante una ricompilazione, l'indice viene eliminato e ne viene creato uno nuovo. Così facendo, la frammentazione viene eliminata, viene recuperato spazio su disco compattando le pagine in base al fattore di riempimento specificato o esistente e le righe dell'indice vengono riordinate in pagine contigue (allocando nuove pagine se necessario). In questa situazione, le prestazioni del disco potrebbero migliorare grazie alla riduzione del numero di letture di pagina necessarie per ottenere i dati richiesti.

Per la ricompilazione degli indici cluster e non cluster è possibile utilizzare i metodi seguenti:

• ALTER INDEX con la clausola REBUILD. Questa istruzione sostituisce l'istruzione DBCC DBREINDEX.

• CREATE INDEX con la clausola DROP_EXISTING.

Ambedue i metodi eseguono la stessa funzione, ma è necessario considerarne vantaggi e svantaggi, in base a quanto illustrato nella tabella seguente.

* È possibile convertire un indice non cluster in un indice cluster specificando CLUSTERED nella definizione dell'indice. Questa operazione va eseguita con l'opzione ONLINE impostata su OFF. La conversione da indice cluster a indice non cluster non è supportata, indipendentemente dall'impostazione dell'opzione ONLINE.

** Se l'indice viene ricreato con lo stesso nome, le stesse colonne e lo stesso tipo di ordinamento, è possibile che l'operazione di ordinamento venga omessa. Durante l'operazione di ricompilazione viene verificato che le righe siano ordinate.

È inoltre possibile ricompilare un indice eliminandolo con l'istruzione DROP INDEX e quindi ricreandolo con un'istruzione CREATE INDEX separata. L'esecuzione di queste operazioni come istruzioni separate presenta numerosi svantaggi e non è consigliata.

Disabilitazione di indici non cluster per ottimizzare l'utilizzo dello spazio su disco durante le operazioni di ricompilazione

Quando un indice non cluster viene disabilitato, le relative righe di dati vengono eliminate, ma la definizione viene mantenuta nei metadati. Dopo la ricompilazione, l'indice viene abilitato. Quando l'indice non cluster non è disabilitato, l'operazione di ricompilazione richiede una quantità di spazio temporaneo su disco sufficiente a contenere il vecchio e il nuovo indice. Disabilitando e ricompilando un indice non cluster in transazioni separate, tuttavia, lo spazio su disco reso disponibile con la disattivazione dell'indice può essere riutilizzato dalla successiva operazione di ricompilazione o da qualunque altra operazione. Non è necessario ulteriore spazio, fatta eccezione per lo spazio su disco temporaneo richiesto dall'ordinamento, che in genere corrisponde al 20% della dimensione dell'indice. Se l'indice non cluster è sulla chiave primaria, qualsiasi vincolo attivo facente riferimento a FOREIGN KEY verrà automaticamente disabilitato. Sarà necessario riabilitare manualmente i vincoli dopo la ricompilazione dell'indice. Per ulteriori informazioni, vedere Disabilitazione di indici e Linee guida per l'abilitazione di indici e vincoli.

Ricompilazione di indici di grandi dimensioni

Gli indici con più di 128 extent vengono ricompilati in due fasi separate: logica e fisica. Nella fase logica, le unità di allocazione esistenti utilizzate dall'indice vengono contrassegnate per la deallocazione, le righe di dati vengono copiate e ordinate, quindi spostate nelle nuove unità di allocazione create per archiviare l'indice ricompilato. Nella fase fisica, le unità di allocazione precedentemente contrassegnate per la deallocazione vengono fisicamente eliminate nelle transazioni brevi eseguite in background e che non richiedono molti blocchi. Per ulteriori informazioni, vedere Eliminazione e ricostruzione di oggetti di grandi dimensioni.

Impostazione delle opzioni per gli indici

Non è possibile specificare le opzioni per un indice quando se ne esegue la riorganizzazione. Quando si ricompila un indice utilizzando ALTER INDEX REBUILD o CREATE INDEX WITH DROP_EXISTING tuttavia, è possibile impostare le opzioni seguenti:

Inoltre, la clausola SET nell'istruzione ALTER INDEX consente di impostare le opzioni seguenti senza ricompilare l'indice:

Per ulteriori informazioni, vedere Impostazione delle opzioni di un indice.

Per ricompilare o riorganizzare un indice

ALTER INDEX (Transact-SQL)

Per ricompilare un indice eliminandolo e ricreandolo in un solo passaggio

CREATE INDEX (Transact-SQL)

Esempi

A. Ricompilazione di un indice

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

B. Ricompilazione di tutti gli indici di una tabella e specifica delle opzioni

USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

C. Riorganizzazione di un indice con la compattazione dei dati LOB

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Vedere anche

Riferimento

Concetti

Altre risorse