Resolve index fragmentation by reorganizing or rebuilding indexes

• 03/19/2020
• 21 minutes to read
• • p
  • j
  • M
  • M
  • d
  • +11

Applies to: SQL Server (tutte le versioni supportate) Azure SQL Database Azure SQL Istanza Gestita Azure Sinapsi Analytics Parallel Data Warehouse

in Questo articolo viene descritto come indice di deframmentazione si verifica e discute il suo impatto sulle prestazioni delle query. Una volta determinata la quantità di frammentazione esistente per un indice, è possibile deframmentare un indice riorganizzando un indice o ricostruendo un indice eseguendo i comandi Transact-SQL nello strumento preferito o utilizzando SQL Server Management Studio.

Panoramica della frammentazione dell’indice

Cos’è la frammentazione dell’indice e perché dovrei preoccuparmene:

• La frammentazione esiste quando gli indici hanno pagine in cui l’ordinamento logico all’interno dell’indice, basato sul valore chiave dell’indice, non corrisponde all’ordinamento fisico all’interno delle pagine dell’indice.
• Il motore di database modifica automaticamente gli indici ogni volta che vengono effettuate operazioni di inserimento, aggiornamento o eliminazione dei dati sottostanti. Ad esempio, l’aggiunta di righe in una tabella può causare la divisione delle pagine esistenti negli indici rowstore per fare spazio all’inserimento di nuovi valori chiave. Nel tempo queste modifiche possono causare la dispersione delle informazioni nell’indice nel database (frammentato). La frammentazione esiste quando gli indici hanno pagine in cui l’ordinamento logico, basato sul valore della chiave, non corrisponde all’ordinamento fisico all’interno del file di dati.
• Gli indici fortemente frammentati possono degradare le prestazioni delle query perché è necessario un I / O aggiuntivo per individuare i dati a cui punta l’indice. Più I / O fa sì che l’applicazione risponda lentamente, specialmente quando sono coinvolte operazioni di scansione.

Rilevamento della quantità di frammentazione

Il primo passo per decidere quale metodo di deframmentazione dell’indice utilizzare è analizzare l’indice per determinare il grado di frammentazione. È possibile rilevare la frammentazione in modo diverso per gli indici rowstore e gli indici columnstore.

Rilevamento della frammentazione degli indici rowstore

utilizzando sys.dm_db_index_physical_stats, è possibile rilevare la frammentazione in un indice specifico, tutti gli indici su una tabella o una vista indicizzata, tutti gli indici in un database o tutti gli indici in tutti i database. Per gli indici partizionati, sys. dm_db_index_physical_stats fornisce anche informazioni sulla frammentazione per ogni partizione.

Il set di risultati restituito da sys. dm_db_index_physical_stats include le seguenti colonne:

Colonna	Descrizione
avg_fragmentation_in_percent	La percentuale di frammentazione logica (out-of-ordine pagine nell’indice).
fragment_count	Il numero di frammenti (pagine foglia fisicamente consecutive) nell’indice.
avg_fragment_size_in_pages	Numero medio di pagine in un frammento in un indice.

Dopo che il grado di frammentazione è noto, utilizzare la seguente tabella per determinare il metodo migliore per rimuovere la frammentazione: INDICE RIORGANIZZARE o INDICE.

avg_fragmentation_in_percent value	Corrective statement
> 5% and < = 30% 1	ALTER INDEX REORGANIZE
> 30% 1	ALTER INDEX REBUILD WITH (ONLINE = ON) 2

1 These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZEe ALTER INDEX REBUILD. Tuttavia, i valori effettivi possono variare da caso a caso. È importante sperimentare per determinare la soglia migliore per il proprio ambiente.

2 La ricostruzione di un indice può essere eseguita online o offline. La riorganizzazione di un indice viene sempre eseguita online. Per ottenere una disponibilità simile all’opzione riorganizza, è necessario ricostruire gli indici online. Per ulteriori informazioni, vedere INDICE ed Eseguire operazioni di indice online.

Gli indici con frammentazione inferiore al 5% non hanno bisogno di essere deframmentati perché il beneficio derivante dalla rimozione di una così piccola quantità di frammentazione è quasi sempre ampiamente superato dal costo della CPU sostenuto per riorganizzare o ricostruire l’indice. Inoltre, la ricostruzione o la riorganizzazione di piccoli indici rowstore in genere non riduce la frammentazione effettiva.Fino a, e compreso, SQL Server 2014 (12.x), il motore di database SQL Server alloca lo spazio utilizzando estensioni miste. Pertanto, le pagine di piccoli indici sono talvolta memorizzate su estensioni miste. Le estensioni miste sono condivise da un massimo di otto oggetti, quindi la frammentazione in un piccolo indice potrebbe non essere ridotta dopo la riorganizzazione o la ricostruzione. Vedere anche Considerazioni specifiche per la ricostruzione degli indici rowstore. Per ulteriori informazioni sulle estensioni, consultare la Guida all’architettura delle pagine e delle estensioni.

Rilevamento della frammentazione degli indici columnstore

Utilizzando sys.dm_db_column_store_row_group_physical_stats, è possibile determinare la percentuale di righe eliminate in un indice, che è una misura ragionevole per la frammentazione in un rowgroup di un indice columnstore. Utilizzare queste informazioni per calcolare la frammentazione in un indice specifico, tutti gli indici in una tabella, tutti gli indici in un database o tutti gli indici in tutti i database.

Il set di risultati restituito da sys.dm_db_column_store_row_group_physical_stats include le seguenti colonne:

Colonna	Descrizione
total_rows	Numero di righe fisico memorizzati nel gruppo di righe. Per i gruppi di righe compressi, ciò include le righe contrassegnate come eliminate.
deleted_rows	Numero di righe fisicamente memorizzate in un gruppo di righe compresse contrassegnate per l’eliminazione. 0 per i gruppi di righe presenti nell’archivio delta.

Utilizzare queste informazioni restituito per calcolare l’indice di frammentazione utilizzando questa formula:

100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Dopo che il grado di frammentazione dell’indice è noto, utilizzare la seguente tabella per determinare il metodo migliore per rimuovere la frammentazione: INDICE di RIORGANIZZAZIONE o di un INDICE.

computed fragmentation in percent value	Applies to version	Corrective statement
> = 20%	SQL Server 2012 (11.x) and SQL Server 2014 (12.x)	ALTER INDEX REBUILD
> = 20%	Starting with SQL Server 2016 (13.x)	ALTER INDEX RIORGANIZZARE

Per controllare la frammentazione di un rowstore indice di utilizzo di Transact-SQL

L’esempio seguente viene calcolata la media percentuale di frammentazione di tutti gli indici nel HumanResources.Employee tabella AdventureWorks2016 database.

SELECT a.object_id, object_name(a.object_id) AS TableName, a.index_id, name AS IndedxName, avg_fragmentation_in_percentFROM sys.dm_db_index_physical_stats (DB_ID (N'AdventureWorks2016_EXT') , OBJECT_ID(N'HumanResources.Employee') , NULL , NULL , NULL) AS aINNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;GO

L’istruzione precedente restituisce un set di risultati simile al seguente.

object_id TableName index_id IndexName avg_fragmentation_in_percent----------- ------------ ----------- ----------------------------------------------------- ------------------------------1557580587 Employee 1 PK_Employee_BusinessEntityID 01557580587 Employee 2 IX_Employee_OrganizationalNode 01557580587 Employee 3 IX_Employee_OrganizationalLevel_OrganizationalNode 01557580587 Employee 5 AK_Employee_LoginID 66.66666666666671557580587 Employee 6 AK_Employee_NationalIDNumber 501557580587 Employee 7 AK_Employee_rowguid 0(6 row(s) affected)

Per ulteriori informazioni, vedere sys.dm_db_index_physical_stats.

Per verificare la frammentazione di un indice columnstore utilizzando Transact-SQL

L’esempio seguente trova la percentuale media di frammentazione di tutti gli indici nella tabelladbo.FactResellerSalesXL_CCInel databaseAdventureWorksDW2016.

SELECT i.object_id, object_name(i.object_id) AS TableName, i.index_id, i.name AS IndexName, 100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'FROM sys.indexes AS i INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups ON i.object_id = CSRowGroups.object_id AND i.index_id = CSRowGroups.index_idWHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'GROUP BY i.object_id, i.index_id, i.nameORDER BY object_name(i.object_id), i.name;

L’istruzione precedente restituisce un set di risultati simile al seguente.

object_id TableName index_id IndexName Fragmentation----------- --------------------------- ----------- ------------------------------- ---------------114099447 FactResellerSalesXL_CCI 1 IndFactResellerSalesXL_CCI 0(1 row(s) affected)

Controllare la frammentazione dell’indice di utilizzo di SQL Server Management Studio

1. In Esplora oggetti, espandere il database che contiene la tabella in cui si desidera controllare la frammentazione di un indice.
2. Espandere la cartella Tabelle.
3. Espandere la tabella in cui si desidera controllare la frammentazione di un indice.
4. Espandere la cartella Indici.
5. Fare clic con il pulsante destro del mouse sull’indice di cui si desidera verificare la frammentazione e selezionare Proprietà.
6. In Seleziona una pagina, seleziona Frammentazione.

sono disponibili Le seguenti informazioni su la Frammentazione pagina:

Valore	Descrizione
Pagina pienezza	Indica il livello medio di riempimento delle pagine di indice, come una percentuale. 100% significa che le pagine dell’indice sono completamente piene. 50% significa che, in media, ogni pagina indice è piena per metà.
Frammentazione totale	La percentuale di frammentazione logica. Questo indica il numero di pagine in un indice che non sono memorizzate in ordine.
Dimensione media riga	La dimensione media di una riga a livello di foglia.
Profondità	Il numero di livelli nell’indice, incluso il livello foglia.
Record inoltrati	Il numero di record in un heap che hanno puntatori in avanti a un’altra posizione di dati. (Questo stato si verifica durante un aggiornamento, quando non c’è abbastanza spazio per memorizzare la nuova riga nella posizione originale.)
Ghost rows	Il numero di righe contrassegnate come eliminate ma non ancora rimosse. Queste righe verranno rimosse da un thread di pulizia, quando il server non è occupato. Questo valore non include le righe che vengono mantenute a causa di una transazione di isolamento dello snapshot in sospeso.
Tipo di indice	Il tipo di indice. I valori possibili sono indice cluster, indice non cluster e XML primario. Le tabelle possono anche essere memorizzate come heap (senza indici), ma questa pagina delle proprietà dell’indice non può essere aperta.
Righe a livello di foglia	Il numero di righe a livello di foglia.
Dimensione massima della riga	La dimensione massima della riga a livello di foglia.
Dimensione minima della riga	La dimensione minima della riga a livello di foglia.
Pagine	Il numero totale di pagine di dati.
Partition ID	L’ID della partizione del b-tree contenente l’indice.
Versione ghost rows	Il numero di record ghost che vengono mantenuti a causa di una transazione di isolamento snapshot in sospeso.

Deframmentazione degli indici ricostruendo o riorganizzando l’indice

Si deframmenta un indice frammentato utilizzando uno dei seguenti metodi:

• Riorganizzazione dell’indice
• Ricostruzione dell’indice

Riorganizza un indice

La riorganizzazione di un indice utilizza risorse di sistema minime ed è un’operazione online. Ciò significa che i blocchi della tabella di blocco a lungo termine non vengono mantenuti e le query o gli aggiornamenti alla tabella sottostante possono continuare durante la transazione ALTER INDEX REORGANIZE.

• Per gli indici rowstore, il Motore di database deframmenta il livello foglia di indici in cluster e non cluster su tabelle e viste riordinando fisicamente le pagine a livello foglia per abbinare l’ordine logico dei nodi foglia (da sinistra a destra). La riorganizzazione compatta anche le pagine dell’indice in base al valore del fattore di riempimento dell’indice. Per visualizzare l’impostazione del fattore di riempimento, utilizzare sys.indice. Per esempi di sintassi, vedere Esempi: Rowstore riorganizzare.

• Quando si utilizzano gli indici columnstore, l’archivio delta può finire con più piccoli gruppi di righe dopo l’inserimento, l’aggiornamento e l’eliminazione dei dati nel tempo. La riorganizzazione di un indice columnstore forza tutti i gruppi di righe nel columnstore e quindi combina i gruppi di righe in meno gruppi di righe con più righe. L’operazione riorganizza rimuove anche le righe che sono state eliminate dal columnstore. La riorganizzazione richiede inizialmente risorse aggiuntive della CPU per comprimere i dati, il che potrebbe rallentare le prestazioni complessive del sistema. Tuttavia, non appena i dati vengono compressi, le prestazioni della query migliorano. Per esempi di sintassi, vedere Esempi: ColumnStore riorganizza.

Ricostruisci un indice

La ricostruzione di un indice diminuisce e ricrea l’indice. A seconda del tipo di versione del motore di indice e database, un’operazione di ricostruzione può essere eseguita online o offline. Per la sintassi T-SQL, vedere ALTER INDEX REBUILD

• Per gli indici rowstore, rebuilding rimuove la frammentazione, recupera spazio su disco compattando le pagine in base all’impostazione del fattore di riempimento specificato o esistente e riordina le righe dell’indice in pagine contigue. Quando viene specificatoALL, tutti gli indici sulla tabella vengono eliminati e ricostruiti in una singola transazione. I vincoli delle chiavi esterne non devono essere eliminati in anticipo. Quando gli indici con 128 estensioni o più vengono ricostruiti, il Motore di database rinvia le deallocazioni effettive della pagina e i relativi blocchi associati fino a dopo il commit della transazione. Per esempi di sintassi, vedere Esempi: Rowstore riorganizzare.

• Per gli indici columnstore, la ricostruzione rimuove la frammentazione, sposta tutte le righe nel columnstore e recupera spazio su disco eliminando fisicamente le righe che sono state cancellate logicamente dalla tabella.

  Suggerimento

  A partire da SQL Server 2016 (13.x), la ricostruzione dell’indice columnstore di solito non è necessaria poiché REORGANIZE esegue gli elementi essenziali di una ricostruzione in background come operazione online.

  Per esempi di sintassi, vedere Esempi: ColumnStore rebuild.

Autorizzazioni

Richiede ALTER autorizzazione sul tavolo o vista. L’utente deve essere membro di almeno uno dei seguenti ruoli:

• db_ddladmin database role 1
• db_owner database role
• sysadmin server role

1db_ddladmin database role è il meno privilegiato.

Rimuovere la frammentazione utilizzando SQL Server Management Studio

Per riorganizzare o ricostruire un indice

1. In Esplora oggetti, espandere il database contenente la tabella in cui si desidera riorganizzare un indice.
2. Espandere la cartella Tabelle.
3. Espandere la tabella in cui si desidera riorganizzare un indice.
4. Espandere la cartella Indici.
5. Fare clic con il pulsante destro del mouse sull’indice che si desidera riorganizzare e selezionare Riorganizza.
6. Nella finestra di dialogo Riorganizza indici, verificare che l’indice corretto si trovi nella griglia Indici da riorganizzare e fare clic su OK.
7. Selezionare la casella di controllo Dati colonna oggetto grande compatto per specificare che tutte le pagine che contengono dati oggetto grande (LOB) vengono compattate.
8. Fare clic su OK.

Per riorganizzare tutti gli indici in una tabella

1. In Esplora oggetti, espandere il database che contiene la tabella in cui si desidera riorganizzare gli indici.
2. Espandere la cartella Tabelle.
3. Espandere la tabella in cui si desidera riorganizzare gli indici.
4. Fare clic con il pulsante destro del mouse sulla cartella Indici e selezionare Riorganizza tutto.
5. Nella finestra di dialogo Riorganizza indici, verificare che gli indici corretti si trovino negli indici da riorganizzare. Per rimuovere un indice dalla griglia Indici da riorganizzare, selezionare l’indice e premere il tasto Canc.
6. Selezionare la casella di controllo Dati colonna oggetto grande compatto per specificare che tutte le pagine che contengono dati oggetto grande (LOB) vengono compattate.
7. Fare clic su OK.

Per ricostruire un indice

1. In Esplora oggetti, espandere il database contenente la tabella in cui si desidera riorganizzare un indice.
2. Espandere la cartella Tabelle.
3. Espandere la tabella in cui si desidera riorganizzare un indice.
4. Espandere la cartella Indici.
5. Fare clic con il pulsante destro del mouse sull’indice che si desidera riorganizzare e selezionare Ricostruisci.
6. Nella finestra di dialogo Ricostruisci indici, verificare che l’indice corretto si trovi nella griglia Indici da ricostruire e fare clic su OK.
7. Selezionare la casella di controllo Dati colonna oggetto grande compatto per specificare che tutte le pagine che contengono dati oggetto grande (LOB) vengono compattate.
8. Fare clic su OK.

Rimuovi la frammentazione usando Transact-SQL

Per riorganizzare un indice frammentato

L’esempio seguente riorganizza l’indiceIX_Employee_OrganizationalLevel_OrganizationalNodesulla tabellaHumanResources.Employeenel databaseAdventureWorks2016.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee REORGANIZE;

Il seguente esempio riorganizza l’indice columnstoreIndFactResellerSalesXL_CCIsulla tabelladbo.FactResellerSalesXL_CCInel databaseAdventureWorksDW2016.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.ALTER INDEX IndFactResellerSalesXL_CCI ON FactResellerSalesXL_CCI REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Per riorganizzare tutti gli indici in una tabella

Il seguente esempio riorganizza tutti gli indici nella tabellaHumanResources.Employeenel databaseAdventureWorks2016.

ALTER INDEX ALL ON HumanResources.Employee REORGANIZE;

Per ricostruire un indice frammentato

L’esempio seguente ricostruisce un singolo indice sulla tabellaEmployeenel databaseAdventureWorks2016.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.EmployeeREBUILD;

Per ricostruire tutti gli indici in una tabella

Il seguente esempio ricostruisce tutti gli indici associati alla tabella nel databaseAdventureWorks2016utilizzando la parola chiaveALL. Sono specificate tre opzioni.

ALTER INDEX ALL ON Production.ProductREBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

Per ulteriori informazioni, vedere ALTER INDEX (Transact-SQL).

Gestione automatica di indici e statistiche

Sfrutta soluzioni come Adaptive Index Defrag per gestire automaticamente la deframmentazione degli indici e gli aggiornamenti delle statistiche per uno o più database. Questa procedura sceglie automaticamente se ricostruire o riorganizzare un indice in base al suo livello di frammentazione, tra gli altri parametri, e aggiornare le statistiche con una soglia lineare.

Considerazioni specifiche per la ricostruzione degli indici rowstore

La ricostruzione di un indice cluster ricostruisce automaticamente qualsiasi indice non cluster che fa riferimento alla chiave di clustering, se è necessario modificare gli identificatori fisici o logici contenuti nei record dell’indice non cluster.

I seguenti scenari costringono tutti gli indici non cluster di rowstore su una tabella a essere ricostruiti automaticamente:

• Creazione di un indice cluster su una tabella
• Rimozione di un indice cluster, che fa sì che la tabella venga memorizzata come heap
• Modifica della chiave di clustering per includere o escludere colonne

Gli scenari seguenti non richiedono che tutti gli indici rowstore non cluster vengano ricostruiti automaticamente su una tabella:

• la Ricostruzione di un indice cluster univoco
• la Ricostruzione di un indice cluster univoco non
• Modifica dell’indice schema, ad esempio l’applicazione di uno schema di partizionamento di un indice cluster o muovendo l’indice cluster in un filegroup diverso

QuandoALLviene specificato con l’istruzioneALTER INDEX, gli indici relazionali, sia cluster che non cluster, e gli indici XML sulla tabella vengono riorganizzati.

Considerazioni specifiche per la ricostruzione di un indice columnstore

Quando si ricostruisce un indice columnstore, il motore di database legge tutti i dati dall’indice columnstore originale, incluso l’archivio delta. Combina i dati in nuovi gruppi di righe e comprime i gruppi di righe in columnstore. Il motore di database deframmenta il columnstore eliminando fisicamente le righe che sono state cancellate logicamente dalla tabella. I byte eliminati vengono recuperati sul disco.

Ricostruire una partizione invece dell’intera tabella

• Ricostruire l’intera tabella richiede molto tempo se l’indice è grande e richiede abbastanza spazio su disco per memorizzare una copia aggiuntiva dell’indice durante la ricostruzione. Di solito è solo necessario ricostruire la partizione utilizzata più di recente.
• Per le tabelle partizionate, non è necessario ricostruire l’intero indice columnstore perché è probabile che la frammentazione si verifichi solo nelle partizioni che sono state modificate di recente. Le tabelle dei fatti e le tabelle di grandi dimensioni sono solitamente partizionate per eseguire operazioni di backup e gestione su blocchi della tabella.

Rebuild a partition after heavy DML operations

Rebuilding a partition deframmenta la partizione e riduce l’archiviazione su disco. La ricostruzione elimina tutte le righe dal columnstore contrassegnate per l’eliminazione e sposta tutti i gruppi di righe dall’archivio delta nel columnstore. Nell’archivio delta possono essere presenti più gruppi di righe con meno di un milione di righe.

Ricostruire una partizione dopo il caricamento dei dati

La ricostruzione di una partizione dopo la data di caricamento assicura che tutti i dati siano memorizzati nel columnstore. Quando processi simultanei ogni carico meno di 100.000 righe nella stessa partizione allo stesso tempo, la partizione può finire con più negozi delta. La ricostruzione sposta tutte le righe del negozio delta nel columnstore.

Considerazioni specifiche per la riorganizzazione di un indice columnstore

Quando si riorganizza un indice columnstore, il motore di database comprime ogni gruppo di righe delta CHIUSO nel columnstore come gruppo di righe compresso. A partire da SQL Server 2016 (13.x) e nel database SQL di Azure, il comandoREORGANIZE esegue le seguenti ottimizzazioni di deframmentazione aggiuntive online:

• Rimuove fisicamente le righe da un rowgroup quando il 10% o più delle righe sono state cancellate logicamente. I byte eliminati vengono recuperati sul supporto fisico. Ad esempio, se un gruppo di righe compresso di 1 milione di righe ha 100K righe eliminate, SQL Server rimuoverà le righe eliminate e ricomprimerà il gruppo di righe con 900k righe. Salva sulla memoria rimuovendo le righe eliminate.

• Combina uno o più gruppi di righe compressi per aumentare le righe per gruppo di righe fino al massimo di 1.048.576 righe. Ad esempio, se importate in blocco 5 batch di 102.400 righe otterrete 5 gruppi di righe compressi. Se si esegue RIORGANIZZA, questi gruppi di righe verranno uniti in 1 gruppo di righe compresso di dimensioni 512.000 righe. Ciò presuppone che non vi fossero limitazioni di dimensioni o memoria del dizionario.

• Per i gruppi di righe in cui il 10% o più delle righe sono state cancellate logicamente, il Motore di database tenta di combinare questo gruppo di righe con uno o più gruppi di righe. Ad esempio, rowgroup 1 viene compresso con 500.000 righe e rowgroup 21 viene compresso con il massimo di 1.048.576 righe. Rowgroup 21 ha il 60% delle righe eliminate che lascia 409.830 righe. Il motore di database favorisce la combinazione di questi due gruppi di righe per comprimere un nuovo gruppo di righe con 909.830 righe.

Dopo aver eseguito il caricamento dei dati, è possibile avere più piccoli gruppi di righe nell’archivio delta. È possibile utilizzare ALTER INDEX REORGANIZE per forzare tutti i gruppi di righe nel columnstore e quindi combinare i gruppi di righe in meno gruppi di righe con più righe. L’operazione riorganizza rimuoverà anche le righe che sono state eliminate dal columnstore.

Limitazioni e restrizioni

Gli indici Rowstore con più di 128 estensioni vengono ricostruiti 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 in nuove unità di allocazione create per memorizzare l’indice ricostruito. Nella fase fisica, le unità di allocazione precedentemente contrassegnate per la deallocazione vengono eliminate fisicamente in transazioni brevi che si verificano in background e non richiedono molti blocchi. Per ulteriori informazioni sulle estensioni, vedere Pagine e Guida all’architettura delle estensioni.

L’istruzione ALTER INDEX REORGANIZE richiede che il file di dati contenente l’indice disponga di spazio disponibile, poiché l’operazione può allocare solo pagine di lavoro temporanee sullo stesso file, non in un altro file all’interno del filegroup. Quindi, sebbene il filegroup possa avere pagine libere disponibili, l’utente può comunque riscontrare l’errore 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Un indice non può essere riorganizzato o ricostruito se il filegroup in cui si trova è offline o impostato su sola lettura. Quando viene specificata la parola chiave ALL e uno o più indici si trovano in un filegroup offline o di sola lettura, l’istruzione non riesce.

Statistiche:

• Quando un indice viene creato o ricostruito, le statistiche vengono create o aggiornate scansionando tutte le righe della tabella. Tuttavia, a partire da SQL Server 2012 (11.x), le statistiche non vengono create o aggiornate scansionando tutte le righe della tabella quando viene creato o ricostruito un indice partizionato. Invece, l’ottimizzatore query utilizza l’algoritmo di campionamento predefinito per generare queste statistiche. Per ottenere statistiche sugli indici partizionati scansionando tutte le righe della tabella, utilizzare CREATE STATISTICS o UPDATE STATISTICS con la clausolaFULLSCAN.

• Quando un indice viene riorganizzato, le statistiche non vengono aggiornate.

Un indice non può essere riorganizzato quandoALLOW_PAGE_LOCKS è impostato su OFF.

Fino a SQL Server 2017 (14.x), la ricostruzione di un indice columnstore cluster è un’operazione offline. Il motore di database deve acquisire un blocco esclusivo sul tavolo o sulla partizione mentre si verifica la ricostruzione. I dati non sono in linea e non sono disponibili durante la ricostruzione anche quando si utilizza NOLOCK, Read-Committed Snapshot Isolation (RCSI) o Snapshot Isolation.A partire da SQL Server 2019 (15.x), un indice columnstore cluster può essere ricostruito utilizzando l’opzioneONLINE = ON.

Per una tabella di Azure Synapse Analytics (precedentemente Azure Synapse Analytics) con un indice columnstore cluster ordinato,ALTER INDEX REBUILD riordinerà i dati utilizzando TempDB. Monitorare TempDB durante le operazioni di ricostruzione. Se avete bisogno di più spazio TempDB, scalare il data warehouse. Ridimensiona una volta completata la ricostruzione dell’indice.

Per una tabella di Azure Synapse Analytics (precedentemente Azure Synapse Analytics) con un indice columnstore cluster ordinato,ALTER INDEX REORGANIZE non riordina i dati. Per ricorrere ai dati utilizzare ALTER INDEX REBUILD.

Utilizzo di INDEX REBUILD per recuperare da errori hardware

Nelle versioni precedenti di SQL Server, a volte è possibile ricostruire un indice non cluster rowstore per correggere le incongruenze causate da errori hardware.A partire da SQL Server 2008, è ancora possibile riparare tali incongruenze tra l’indice e l’indice cluster ricostruendo un indice non cluster offline. Tuttavia, non è possibile riparare le incongruenze dell’indice non cluster ricostruendo l’indice online, poiché il meccanismo di ricostruzione online utilizza l’indice non cluster esistente come base per la ricostruzione e quindi mantiene l’incoerenza. La ricostruzione dell’indice offline può talvolta forzare una scansione dell’indice cluster (o heap) e quindi rimuovere l’incoerenza. Per assicurare una ricostruzione dall’indice cluster, eliminare e ricreare l’indice non cluster. Come per le versioni precedenti, si consiglia di ripristinare le incongruenze ripristinando i dati interessati da un backup; tuttavia, è possibile ripristinare le incongruenze dell’indice ricostruendo l’indice non cluster offline. Per ulteriori informazioni, vedere DBCC CHECKDB (Transact-SQL).

Vedi anche

• Indice di SQL Server l’Architettura e il Design Guida
• Eseguire le Operazioni di Indice Online
• ALTER INDEX (Transact-SQL)
• Adaptive Indice Defrag
• CREARE STATISTICHE (Transact-SQL)
• AGGIORNARE le STATISTICHE (Transact-SQL)
• Indici Columnstore le Prestazioni delle Query
• iniziare con Columnstore per operativo real-time analytics
• Indici Columnstore per il Data Warehousing
• indici Columnstore e l’unione politica per rowgroups