Riorganizzare e ricompilare gli indici
In questo argomento viene descritto come riorganizzare o ricompilare un indice frammentato in SQL Server 2012 utilizzando SQL Server Management Studio o Transact-SQL. Tramite il Motore di database di SQL Server la manutenzione degli indici viene automaticamente eseguita dopo ogni operazione di modifica, inserimento o eliminazione dei dati sottostanti. Nel tempo, queste modifiche possono provocare la frammentazione dell'indice nel database. La frammentazione si verifica quando negli indici sono presenti pagine in cui l'ordinamento logico, basato sul valore chiave, non corrisponde all'ordinamento fisico all'interno del file di dati. Gli indici con un alto grado di frammentazione possono essere causa del calo delle prestazioni delle query e rallentare l'applicazione.
È possibile porre rimedio alla frammentazione eseguendo la riorganizzazione o la ricompilazione dell'indice. Per gli indici partizionati compilati in base a uno schema di partizione è possibile procedere in uno dei metodi seguenti sull'intero indice o su una singola partizione. La ricompilazione di un indice consiste nell'eliminazione e nella ricompilazione dell'indice. In questo modo si rimuove la frammentazione, si rende disponibile ulteriore spazio su disco grazie alla compattazione delle pagine in base all'impostazione del fattore di riempimento esistente o specificata e si riordinano le righe dell'indice in pagine contigue. Quando si specifica la parola chiave ALL, tutti gli indici della tabella vengono eliminati e ricompilati in una singola transazione. La riorganizzazione di un indice richiede una quantità minima di risorse di sistema. Tramite questa operazione si deframmenta il livello foglia di indici cluster e non cluster in tabelle e viste riordinando fisicamente le pagine al livello foglia in base all'ordine logico, da sinistra verso destra, dei nodi foglia. Tramite la riorganizzazione vengono inoltre compattate le pagine di indice in base al valore del fattore di riempimento esistente.
Contenuto dell'argomento
Prima di iniziare:
Rilevamento della frammentazione
Limitazioni e restrizioni
Sicurezza
Per controllare la frammentazione di un indice utilizzando:
SQL Server Management Studio
Transact-SQL
Per riorganizzare o ricompilare un indice utilizzando:
SQL Server Management Studio
Transact-SQL
Prima di iniziare
Rilevamento della frammentazione
Il primo passaggio per decidere il metodo di deframmentazione da utilizzare consiste nell'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.
Colonna |
Descrizione |
|---|---|
avg_fragmentation_in_percent |
Percentuale di frammentazione logica (pagine non ordinate nell'indice). |
fragment_count |
Numero di frammenti (pagine foglia fisicamente consecutive) nell'indice. |
avg_fragment_size_in_pages |
Numero medio di pagine in un frammento di un indice. |
Una volta noto il grado di frammentazione, utilizzare la tabella seguente per determinare il metodo migliore per la correzione della frammentazione.
Valore di avg_fragmentation_in_percent |
Istruzione correttiva |
|---|---|
> 5% and < = 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* È 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.
[!NOTA]
In generale, non è possibile controllare la frammentazione sugli indici di dimensioni ridotte. Le pagine di indici di dimensioni ridotte vengono archiviate in extent misti. Poiché gli extent misti possono essere condivisi al massimo da otto oggetti, la frammentazione di un indice di dimensioni ridotte potrebbe non ridursi dopo la riorganizzazione o la ricompilazione dello stesso.
Limitazioni e restrizioni
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 non richiedono molti blocchi.
Durante la riorganizzazione, non è possibile specificare le opzioni relative a un indice.
Sicurezza
Autorizzazioni
È richiesta l'autorizzazione ALTER per la tabella o la vista. L'utente deve essere un membro del ruolo predefinito del server sysadmin o dei ruoli predefiniti del database db_ddladmin e db_owner.
.gif "Icona freccia utilizzata con il collegamento Torna all'inizio")
[Inizio pagina]
Utilizzo di SQL Server Management Studio
Per controllare la frammentazione di un indice
In Esplora oggetti espandere il database contenente la tabella in cui si desidera controllare la frammentazione di un indice.
Espandere la cartella Tabelle.
Espandere la tabella in cui si desidera controllare la frammentazione di un indice.
Espandere la cartella Indici.
Fare clic con il pulsante destro del mouse sull'indice di cui si desidera controllare la frammentazione e scegliere Proprietà.
In Selezione pagina selezionare Frammentazione.
Le informazioni seguenti sono disponibili nella pagina Frammentazione:
Livello di riempimento pagina
Indica il livello medio di riempimento delle pagine di indice, espresso come percentuale. Il valore 100% indica che le pagine di indice sono completamente piene. Il valore 50% indica che ogni pagina di indice è piena all'incirca per metà.Frammentazione totale
Percentuale di frammentazione logica. Indica il numero di pagine di un indice che non sono archiviate in ordine.Dimensioni medie delle righe
Dimensioni medie di una riga al livello foglia.Livello nidificazione
Numero di livelli dell'indice, compreso il livello foglia.Record inoltrati
Numero di record in un heap che hanno inoltrato puntatori a un altro percorso dei dati. Questo stato si verifica durante un aggiornamento nel caso in cui non vi sia spazio sufficiente per archiviare la riga nel percorso originale.Righe fantasma
Numero di righe contrassegnate come eliminate ma non ancora rimosse. Queste righe verranno rimosse da un thread di pulitura nel momento in cui il server non è occupato. Questo valore non comprende le righe mantenute a causa di una transazione di isolamento dello snapshot in attesa.Tipo di indice
Tipo di indice. I valori possibili sono Indice cluster, Indice non cluster e XML primario. È inoltre possibile archiviare le tabelle come heap (senza indici), ma in questo caso non sarà possibile aprire la pagina Proprietà indice.Righe al livello foglia
Numero di righe al livello foglia.Dimensioni massime righe
Dimensioni massime delle righe al livello foglia.Dimensioni minime righe
Dimensioni minime delle righe al livello foglia.Pagine
Numero totale di pagine di dati.ID partizione
ID partizione dell'albero B contenente l'indice.Righe fantasma versione
Numero di record fantasma mantenuti a causa di una transazione di isolamento dello snapshot in attesa.
[Inizio pagina]
Utilizzo di Transact-SQL
Per controllare la frammentazione di un indice
In Esplora oggetti connettersi a un'istanza del Motore di database.
Sulla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi scegliere Esegui.
USE AdventureWorks2012; GO -- Find the average fragmentation percentage of all indexes -- in the HumanResources.Employee table. SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GOL'istruzione potrebbe restituire un set di risultati simile al seguente.
index_id name avg_fragmentation_in_percent ----------- ----------------------------------------------------- ---------------------------- 1 PK_Employee_BusinessEntityID 0 2 IX_Employee_OrganizationalNode 0 3 IX_Employee_OrganizationalLevel_OrganizationalNode 0 5 AK_Employee_LoginID 66.6666666666667 6 AK_Employee_NationalIDNumber 50 7 AK_Employee_rowguid 0 (6 row(s) affected)
Per ulteriori informazioni, vedere sys.dm_db_index_physical_stats (Transact-SQL).
[Inizio pagina]
Utilizzo di SQL Server Management Studio
Per riorganizzare o ricompilare un indice
In Esplora oggetti espandere il database che contiene la tabella in cui si desidera riorganizzare un indice.
Espandere la cartella Tabelle.
Espandere la tabella in cui si desidera riorganizzare un indice.
Espandere la cartella Indici.
Fare clic con il pulsante destro del mouse sull'indice che si desidera riorganizzare e scegliere Riorganizza.
Nella finestra di dialogo Riorganizza indici verificare che nella griglia Indici da riorganizzare sia presente l'indice corretto, quindi scegliere OK.
Selezionare la casella di controllo Compatta dati di colonne LOB per specificare che tutte le pagine che contengono dati LOB vengano compattate.
Fare clic su OK.
Per riorganizzare tutti gli indici in una tabella
In Esplora oggetti espandere il database che contiene la tabella in cui si desidera riorganizzare gli indici.
Espandere la cartella Tabelle.
Espandere la tabella in cui si desidera riorganizzare gli indici.
Fare clic con il pulsante destro del mouse sulla cartella Indici e scegliere Riorganizza tutto.
Nella finestra di dialogo Riorganizza indici verificare che nella griglia Indici da riorganizzare siano presenti gli indici corretti. Per rimuovere un indice dalla griglia Indici da riorganizzare, selezionare l'indice desiderato e premere CANC.
Selezionare la casella di controllo Compatta dati di colonne LOB per specificare che tutte le pagine che contengono dati LOB vengano compattate.
Fare clic su OK.
Per ricompilare un indice
In Esplora oggetti espandere il database che contiene la tabella in cui si desidera riorganizzare un indice.
Espandere la cartella Tabelle.
Espandere la tabella in cui si desidera riorganizzare un indice.
Espandere la cartella Indici.
Fare clic con il pulsante destro del mouse sull'indice che si desidera riorganizzare e scegliere Riorganizza.
Nella finestra di dialogo Ricompila indici verificare che nella griglia Indici da ricompilare sia presente l'indice corretto, quindi scegliere OK.
Selezionare la casella di controllo Compatta dati di colonne LOB per specificare che tutte le pagine che contengono dati LOB vengano compattate.
Fare clic su OK.
[Inizio pagina]
Utilizzo di Transact-SQL
Per riorganizzare un indice deframmentato
In Esplora oggetti connettersi a un'istanza del Motore di database.
Sulla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi scegliere Esegui.
USE AdventureWorks2012; GO -- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE ; GO
Per riorganizzare tutti gli indici in una tabella
In Esplora oggetti connettersi a un'istanza del Motore di database.
Sulla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi scegliere Esegui.
USE AdventureWorks2012; GO -- Reorganize all indexes on the HumanResources.Employee table. ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ; GO
Per ricompilare un indice deframmentato
In Esplora oggetti connettersi a un'istanza del Motore di database.
Sulla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query, quindi scegliere Esegui. Nell'esempio viene ricompilato un solo indice nella tabella Employee.
USE AdventureWorks2012; GO ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee REBUILD; GO
Per ricompilare tutti gli indici in una tabella
In Esplora oggetti connettersi a un'istanza del Motore di database.
Sulla barra Standard fare clic su Nuova query.
Copiare e incollare l'esempio seguente nella finestra Query. Nell'esempio viene specificata la parola chiave ALL. per ricompilare tutti gli indici associati alla tabella. Vengono inoltre impostate tre opzioni.
USE AdventureWorks2012; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON); GO
Per ulteriori informazioni, vedere ALTER INDEX (Transact-SQL).
[Inizio pagina]
Vedere anche
Altre risorse
Procedure consigliate relative alla deframmentazione degli indici in Microsoft SQL Server 2000