Condividi tramite

Ottimizzare la manutenzione dell'indice per migliorare le prestazioni delle query e ridurre il consumo di risorse

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure Piattaforma di strumenti analitici (PDW)

Questo articolo illustra come decidere quando e come eseguire la manutenzione dell'indice. Vengono illustrati concetti come la frammentazione dell'indice e la densità delle pagine e il relativo impatto sulle prestazioni delle query e sull'utilizzo delle risorse. Descrive i metodi di manutenzione degli indici, la riorganizzazione di un indice e la ricompilazione di un indice e suggerisce una strategia di manutenzione dell'indice che bilancia i potenziali miglioramenti delle prestazioni rispetto al consumo di risorse necessario per la manutenzione.

Nota

Questo articolo non si applica a un pool SQL dedicato in Azure Synapse Analytics. Per informazioni sulla manutenzione degli indici per un pool SQL dedicato di Azure Synapse Analytics, vedere Indici nelle tabelle dei pool SQL dedicati in Azure Synapse Analytics.

Concetti: Frammentazione degli indici e densità delle pagine

Cos'è la frammentazione dell'indice e il modo in cui influisce sulle prestazioni:

  • La frammentazione di indici ad albero B (rowstore) si verifica quando negli indici sono presenti 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.

    Nota

    Nella documentazione viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore, il motore di database implementa un albero B+. Ciò non si applica a indici columnstore o a indici in tabelle ottimizzate per la memoria. Per altre informazioni, vedere Architettura e guida per la progettazione degli indici di SQL Server e Azure SQL.

  • Il motore di database modifica gli indici automaticamente dopo ogni operazione di modifica, inserimento o eliminazione dei dati sottostanti. Ad esempio, l'aggiunta di righe in una tabella può comportare la suddivisione delle pagine esistenti negli indici rowstore per fare spazio per l'inserimento di nuove righe. Nel tempo, queste modifiche possono provocare la dispersione dei dati nell'indice nel database (frammentazione).

  • Per le query che leggono molte pagine che usano analisi di indici complete o di intervallo, gli indici fortemente frammentati possono ridurre le prestazioni delle query quando è necessario un I/O aggiuntivo per leggere i dati. Per leggere la stessa quantità di dati, anziché un numero ridotto di richieste di I/O di grandi dimensioni, la query richiederebbe un numero maggiore di richieste di I/O di piccole dimensioni.

  • Quando il sottosistema di archiviazione offre prestazioni di I/O sequenziali migliori rispetto alle prestazioni di I/O casuali, la frammentazione dell'indice può ridurre le prestazioni perché è necessario un I/O più casuale per leggere gli indici frammentati.

Cos'è la densità di pagina (nota anche come fullness di pagina) e il modo in cui influisce sulle prestazioni:

  • Ogni pagina del database può contenere un numero variabile di righe. Se le righe occupano tutto lo spazio in una pagina, la densità di pagina è il 100%. Se una pagina è vuota, la densità di pagina è lo 0%. Se una pagina con densità del 100% viene suddivisa in due pagine per contenere una nuova riga, la densità delle due nuove pagine sarà approssimativamente del 50%.
  • Quando la densità di pagina è bassa, per archiviare la stessa quantità di dati sono necessarie più pagine. Ciò significa che sono necessarie più operazioni di I/O per leggere e scrivere questi dati e per memorizzare nella cache i dati è necessaria una quantità maggiore di memoria. Quando la memoria è limitata, nella cache vengono memorizzate meno pagine richieste da una query, causando un numero ancora maggiore di operazioni di I/O su disco. Di conseguenza, la densità di pagina bassa influisce negativamente sulle prestazioni.
  • Quando motore di database aggiunge righe a una pagina durante la creazione, la ricompilazione o la riorganizzazione dell'indice, non riempirà completamente la pagina se il fattore di riempimento per l'indice è impostato su un valore diverso da 100 (o 0, equivalente in questo contesto). Ciò causa una riduzione della densità di pagina e aggiunge in modo analogo il sovraccarico di I/O, influendo negativamente sulle prestazioni.
  • Una bassa densità di pagina può aumentare il numero di livelli intermedi dell'albero B. Questo aumenta moderatamente il costo della CPU e dell'I/O di trovare pagine a livello foglia nelle analisi e nelle ricerche di indici.
  • Quando Query Optimizer compila un piano di query, considera il costo di I/O necessario per leggere i dati richiesti dalla query. Con una bassa densità di pagina, ci sono più pagine da leggere, quindi il costo di I/O è superiore. Ciò può influire sulla scelta del piano di query. Ad esempio, man mano che la densità di pagina diminuisce nel tempo a causa delle divisioni delle pagine, l'ottimizzatore può compilare un piano diverso per la stessa query, con un profilo di prestazioni e consumo di risorse diverso.

Suggerimento

In molti carichi di lavoro, l'aumento della densità delle pagine comporta un maggiore impatto positivo sulle prestazioni rispetto alla riduzione della frammentazione.

Per evitare di ridurre la densità di pagina inutilmente, Microsoft consiglia non di impostare il fattore di riempimento su valori diversi da 100 o 0, tranne in alcuni casi per gli indici che riscontrano un numero elevato di divisioni di pagina, ad esempio indici modificati frequentemente con colonne iniziali contenenti valori GUID non sequenziali.

Misurare la frammentazione degli indici e la densità delle pagine

La frammentazione e la densità di pagina sono tra i fattori da considerare quando si decide se eseguire la manutenzione dell'indice e quale metodo di manutenzione usare.

La frammentazione viene rilevata in modo diverso per gli indici rowstore e columnstore. Usare sys.dm_db_index_physical_stats() per determinare la frammentazione e la densità di pagina 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 anche informazioni sulla frammentazione per ogni partizione.

La serie di risultati restituiti da sys.dm_db_index_physical_stats include le seguenti colonne:

Colonna Descrizione
avg_fragmentation_in_percent Frammentazione logica (pagine non ordinate nell'indice).
avg_page_space_used_in_percent Densità media delle pagine.

Per i gruppi di righe compressi negli indici columnstore, la frammentazione viene definita come il rapporto tra righe eliminate e righe totali, espresso sotto forma di percentuale. sys.dm_db_column_store_row_group_physical_stats consente di determinare il numero di righe totali ed eliminate per ogni gruppo di righe in un indice specifico, tutti gli indici in una tabella o tutti gli indici in un database.

La serie di risultati restituiti da sys.dm_db_column_store_row_group_physical_stats include le seguenti colonne:

Colonna Descrizione
total_rows Righe di totali archiviate fisicamente nell'apposito gruppo. Per i gruppi di righe compressi, sono incluse le righe contrassegnate come eliminate.
deleted_rows Numero di righe archiviate fisicamente in un gruppo di righe compresso contrassegnate per l'eliminazione. 0 per i gruppi di righe che si trovano nell'archivio differenziale.

La frammentazione dei gruppi di righe compressi in un indice columnstore può essere calcolata usando questa formula:

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

Suggerimento

Per gli indici rowstore e columnstore, esaminare la frammentazione dell'indice o dell'heap e la densità di pagina dopo l'eliminazione o l'aggiornamento di un numero elevato di righe. Per gli heap, se sono presenti aggiornamenti frequenti, esaminare periodicamente la frammentazione per evitare la proliferazione di record di inoltro. Per altre informazioni sugli heap, vedere Heap (tabelle senza indici cluster).

Per determinare la frammentazione e la densità delle pagine, vedere Esempi per le query di esempio.

Metodi di manutenzione dell'indice: riorganizzazione e ricompilazione

È possibile ridurre la frammentazione dell'indice e aumentare la densità di pagina usando uno dei metodi seguenti:

  • Riorganizzare un indice
  • Ricompilare un indice

Nota

Per gli indici partizionati compilati in base a uno schema di partizione, è possibile usare uno dei metodi seguenti su tutte le partizioni o su una singola partizione di un indice.

Riorganizzare un indice

La riorganizzazione di un indice è un processo meno intensivo rispetto alla ricompilazione. Per questo motivo questo deve essere il metodo di manutenzione dell'indice preferito, a meno che non esista un motivo specifico per usare la ricompilazione. La riorganizzazione viene sempre eseguita online. Ciò significa che i blocchi a livello di oggetto a lungo termine non vengono mantenuti attivi e le query o gli aggiornamenti inerenti la tabella sottostante possono continuare durante l'operazione ALTER INDEX ... REORGANIZE.

  • Per gli indici rowstore, il motore di database deframmenta il livello foglia di indici cluster e non cluster di tabelle e viste tramite il riordinamento fisico delle pagine al livello foglia in base all'ordine logico dei nodi foglia (da sinistra verso destra). La riorganizzazione compatta anche le pagine di indice in modo da rendere la densità di pagina uguale al fattore di riempimento dell'indice. Per visualizzare l'impostazione del fattore di riempimento, usare sys.indexes. Per esempi di sintassi, vedere Esempi - Riorganizzazione di rowstore.
  • Quando si usano gli indici columnstore, l'archivio differenziale potrebbe ritrovarsi con più rowgroup di piccole dimensioni dopo l'inserimento, l'aggiornamento e l'eliminazione dei dati nel tempo. Riorganizzare un indice columnstore forza i gruppi di righe dell'archivio delta in gruppi di righe compressi in columnstore e combina gruppi di righe compressi più piccoli in gruppi di righe più grandi. L'operazione di riorganizzazione rimuove fisicamente anche le righe che sono state contrassegnate come eliminate dal columnstore. La riorganizzazione di un indice columnstore può richiedere risorse CPU aggiuntive per comprimere i dati. Mentre l'operazione è in esecuzione, le prestazioni possono rallentare. Tuttavia, dopo la compressione dei dati, le prestazioni delle query migliorano. Per esempi di sintassi, vedere Esempi - Riorganizzazione di columnstore.

Nota

A partire da SQL Server 2019 (15.x), database SQL di Azure e Istanza gestita di SQL di Azure, il motore di tuple viene aiutato da un'attività di unione in background, che comprime automaticamente i rowgroup differenziali aperti più piccoli che sono esistiti per un dato periodo di tempo (come determinato da una soglia interna) oppure unisce i rowgroup compressi da cui è stato eliminato un numero elevato di righe. In questo modo viene migliorata la qualità dell'indice columnstore nel tempo. Per la maggior parte dei casi, questa operazione evita di dover eseguire comandi ALTER INDEX ... REORGANIZE.

Suggerimento

Se si annulla un'operazione di riorganizzazione o se viene interrotta, lo stato di avanzamento apportato a tale punto viene salvato in modo permanente nel database. Per riorganizzare gli indici di grandi dimensioni, l'operazione può essere avviata e arrestata più volte fino al completamento.

Ricompilare un indice

La ricompilazione di un indice consiste nell'eliminazione e nella ricreazione dell'indice. A seconda del tipo di indice e della versione del motore di database, un'operazione di ricompilazione può essere eseguita offline oppure online. Una ricompilazione dell'indice offline richiede in genere meno tempo rispetto a quella online, ma comporta blocchi a livello di oggetto per l'intera durata dell'operazione, impedendo alle query di accedere alla tabella o alla vista.

Una ricompilazione dell'indice online non richiede blocchi a livello di oggetto fino al termine dell'operazione, quando un blocco deve essere applicato per un breve periodo di tempo per completare la ricompilazione. A seconda della versione del motore di database, una ricompilazione dell'indice online può essere avviata come operazione ripristinabile. Una ricompilazione dell'indice ripristinabile può essere sospesa, senza perdere lo stato di avanzamento raggiunto. Un'operazione di ricompilazione ripristinabile può essere ripresa dopo una sospensione o un'interruzione oppure può essere terminata se non è più necessario completarla.

Per la sintassi Transact-SQL, vedere ALTER INDEX REBUILD. Per altre informazioni sulle ricompilazioni degli indici online, vedere Eseguire operazioni online sugli indici.

Nota

Durante la ricompilazione di un indice online, ogni modifica dei dati nelle colonne indicizzate deve aggiornare una copia aggiuntiva dell'indice. Ciò può comportare una riduzione del livello delle prestazioni delle istruzioni di modifica dei dati durante la ricompilazione online.

Se un'operazione di indicizzazione ripristinabile online viene sospesa, l'impatto sulle prestazioni persiste fino al completamento o all'interruzione dell'operazione. Se non si intende completare un'operazione di indicizzazione ripristinabile, interromperla invece di sospenderla.

Suggerimento

A seconda delle risorse disponibili e dei modelli di carico di lavoro, la specifica di un valore superiore a quello predefinito MAXDOP nell'istruzione ALTER INDEX REBUILD può ridurre la durata della ricompilazione a scapito di un maggiore utilizzo della CPU.

  • Per gli indici rowstore, la ricompilazione rimuove la frammentazione in tutti i livelli dell'indice e compatta le pagine in base al fattore di riempimento specificato o corrente. Quando viene specificato ALL, tutti gli indici della tabella vengono eliminati e ricompilati in una singola operazione. Quando vengono ricompilati indici con 128 o più extent, il motore di database posticipa le effettive deallocazioni delle pagine e l'acquisizione dei blocchi associati fino al termine della ricompilazione. Per esempi di sintassi, vedere Esempi - Ricompilazione di rowstore.

  • Per gli indici columnstore, la ricompilazione rimuove la frammentazione, sposta le righe dell'archivio differenziale in columnstore ed elimina fisicamente le righe contrassegnate per l'eliminazione. Per esempi di sintassi, vedere Esempi - Ricompilazione di columnstore.

    Suggerimento

    A partire da SQL Server 2016 (13.x), la ricompilazione dell'indice columnstore in genere non è necessaria perché REORGANIZE esegue gli aspetti principali di una ricompilazione come operazione online.

Usare la ricompilazione dell'indice per eseguire il ripristino in seguito a un danneggiamento dei dati

Prima di SQL Server 2008 (10.0.x), è talvolta possibile ricompilare un indice non cluster rowstore per risolvere le incoerenze causate dal danneggiamento dei dati nell'indice.

È ancora possibile correggere tali incoerenze nell'indice non cluster tramite la ricompilazione di un indice non cluster offline. Non è possibile, tuttavia, correggere le incoerenze di indici non cluster tramite la ricompilazione dell'indice online, in quanto il meccanismo di ricompilazione online usa l'indice non cluster esistente come base per la ricompilazione e di conseguenza l'incoerenza persiste. La ricompilazione dell'indice offline può talvolta forzare un'analisi dell'indice cluster (o dell'heap) e quindi sostituire i dati incoerenti nell'indice non cluster con i dati dell'indice cluster o dell'heap.

Per assicurarsi che l'indice cluster o l'heap vengano usati come origine dei dati, è preferibile eliminare e ricreare l'indice non cluster anziché ricompilarlo. Come per le versioni precedenti, è possibile eseguire il ripristino da incoerenze ripristinando i dati interessati da un backup. Tuttavia, potrebbe essere possibile riparare le incoerenze dell'indice non cluster ricompilandolo offline o ricreandolo. Per altre informazioni, vedere DBCC CHECKDB (Transact-SQL).

Gestione automatica dell'indice e delle statistiche

Usare soluzioni, ad esempio la deframmentazione dell'indice adattivo, per gestire automaticamente la deframmentazione dell'indice e gli aggiornamenti delle statistiche per uno o più database. Questa procedura sceglie automaticamente se ricompilare o riorganizzare un indice in base al relativo livello di frammentazione, tra gli altri parametri, e aggiornare le statistiche con una soglia lineare.

Considerazioni specifiche per la ricompilazione e la riorganizzazione degli indici rowstore

Gli scenari seguenti forzano la ricompilazione automatica di tutti gli indici rowstore non cluster in una tabella:

  • Creazione di un indice cluster in una tabella, inclusa la ricreazione dell'indice cluster con una chiave diversa usando CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON)
  • Rimozione di un indice cluster, che causa l'archiviazione della tabella come heap

Gli scenari seguenti non richiedono la ricompilazione automatica di tutti gli indici rowstore non cluster nella stessa tabella:

  • Ricompilazione di un indice cluster
  • Modifica dell'archiviazione dell'indice cluster, ad esempio tramite l'applicazione di uno schema di partizione o lo spostamento dell'indice cluster in un filegroup diverso

Importante

Non è possibile riorganizzare o ricompilare un indice contenuto in un filegroup offline o di 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 ha esito negativo.

Quando viene eseguita la ricompilazione di un indice, il supporto fisico deve disporre di spazio sufficiente per archiviare due copie dell'indice. Al termine della ricompilazione, il motore di database elimina l'indice originale.

Quando viene specificato ALL con l'istruzione ALTER INDEX ... REORGANIZE, vengono riorganizzati gli indici cluster, non cluster e XML.

La ricompilazione o la riorganizzazione degli indici rowstore di dimensioni ridotte generalmente non riduce la frammentazione. Fino a SQL Server 2014 (12.x) incluso, il motore di database di SQL Server alloca spazio usando extent misti. Per questo motivo, le pagine di indici di dimensioni ridotte vengono talvolta archiviate in extent misti che rendono implicitamente tali indici frammentati. Poiché gli extent misti possono essere condivisi al massimo da otto oggetti, la frammentazione in un indice di dimensioni ridotte potrebbe non ridursi dopo la riorganizzazione o la ricompilazione dell'indice.

Considerazioni specifiche per la ricompilazione di un indice columnstore

Durante la ricompilazione di un indice columnstore, il motore di database legge tutti i dati dall'indice columnstore originale, incluso l'archivio differenziale. Combina i dati in nuovi gruppi di righe e comprime tutti i gruppi di righe nel columnstore. Il motore di database deframmenta il columnstore eliminando fisicamente le righe che sono state eliminate logicamente dalla tabella.

Nota

A partire da SQL Server 2019 (15.x), il motore di tuple viene aiutato da un'attività di unione in background, che comprime automaticamente i rowgroup di archivio differenziale aperti più piccoli che sono esistiti per un dato periodo di tempo (come determinato da una soglia interna) oppure unisce i rowgroup compressi da cui è stato eliminato un numero elevato di righe. In questo modo viene migliorata la qualità dell'indice columnstore nel tempo. Per altre informazioni sui termini e sui concetti dei columnstore, vedere Indici columnstore: Panoramica

Ricompilare una partizione in alternativa all'intera tabella

La ricompilazione di un'intera tabella richiede molto tempo se l'indice è esteso ed è necessario sufficiente spazio su disco per archiviare una copia aggiuntiva dell'indice durante la ricompilazione.

Per le tabelle partizionate, non è necessario ricompilare l'intero indice columnstore se la frammentazione è presente solo in alcune partizioni, ad esempio nelle partizioni in cui le istruzioni UPDATE, DELETE o MERGE hanno interessato un numero elevato di righe.

Ricompilare una partizione dopo il caricamento dei dati assicura che tutti i dati vengano archiviati in rowgroup compressi nel columnstore. Quando il processo di caricamento dei dati inserisce dati in una partizione usando batch inferiori a 102.400 righe, la partizione può finire con più rowgroup aperti nell'archivio differenziale. La ricompilazione sposta tutte le righe dell'archivio differenziale in rowgroup compressi nel columnstore.

Considerazioni specifiche per la riorganizzazione di un indice columnstore

Durante la riorganizzazione di un indice columnstore, il motore di database comprime tutti i rowgroup chiusi nell'archivio delta nel columnstore come rowgroup compresso. A partire da SQL Server 2016 (13.x) e nel database SQL di Azure, il comando REORGANIZE esegue online le ottimizzazioni di deframmentazione aggiuntive seguenti:

  • Rimuove fisicamente le righe da un rowgroup quando più del 10% delle righe è stato eliminato in modo logico. Ad esempio, se in un rowgroup compresso di 1 milione di righe sono state eliminate 100.000 righe, il motore di database rimuoverà le righe eliminate e ricomprimerà il gruppo con 900.000 righe, riducendo in questo modo il footprint di archiviazione.
  • Combina uno o più rowgroup compressi per aumentare le righe in ogni gruppo, fino a un massimo di 1.048.576 righe. Ad esempio, se si inseriscono globalmente cinque batch di 102.400 righe ognuno, si otterranno cinque rowgroup compressi. Se si esegue l'operazione REORGANIZE, questi rowgroup verranno uniti in un rowgroup compresso di 512.000 righe. Si presuppone che non vi siano limiti di memoria o di dimensioni del dizionario.
  • Il motore di database prova a combinare rowgroup in cui il 10% o più righe sono state contrassegnate come eliminate con altri rowgroup. Ad esempio, il rowgroup 1 viene compresso con 500.000 righe e il rowgroup 21 viene compresso con 1.048.576 righe. Nel rowgroup 21 il 60% delle righe è stato contrassegnato come eliminato, il che lascia 409.830 righe. Il motore di database consente di combinare questi due rowgroup per comprimere un nuovo rowgroup con 909.830 righe.

Dopo l'esecuzione dei caricamenti di dati, è possibile che nell'archivio differenziale rimangano più rowgroup di piccole dimensioni. È possibile usare ALTER INDEX REORGANIZE per forzare questi rowgroup in columnstore e quindi combinare rowgroup compressi più piccoli in rowgroup compressi più grandi. L'operazione di riorganizzazione rimuoverà anche le righe che sono state eliminate dal columnstore.

Nota

La riorganizzazione di un indice columnstore con Management Studio combina i rowgroup compressi, ma non forza la compressione di tutti i rowgroup nel columnstore. I rowgroup chiusi verranno compressi, mentre quelli aperti non verranno compressi nel columnstore. Per forzare la compressione di tutti i rowgroup, usare l'esempio Transact-SQL che include COMPRESS_ALL_ROW_GROUPS = ON.

Cosa considerare prima di eseguire la manutenzione dell'indice

La manutenzione degli indici, eseguita tramite la riorganizzazione o la ricompilazione di un indice, è un'operazione a elevato utilizzo di risorse. Determina un aumento significativo dell'utilizzo della CPU, della memoria usata e delle operazioni di I/O di archiviazione. In base al carico di lavoro del database e ad altri fattori, tuttavia, i vantaggi offerti possono essere estremamente importanti.

Per evitare l'utilizzo non necessario delle risorse, non eseguire la manutenzione dell'indice in modo indiscriminato. Invece, i vantaggi delle prestazioni derivanti dalla manutenzione dell'indice dovrebbero essere determinati empiricamente per ogni carico di lavoro tramite la strategia consigliata e valutati rispetto ai costi delle risorse e all'impatto del carico di lavoro necessari per ottenerli.

La probabilità di visualizzare i vantaggi per le prestazioni della riorganizzazione o la ricompilazione di un indice è maggiore quando l'indice è molto frammentato o quando la densità di pagina è bassa. Tuttavia, queste non sono le uniche cose da considerare. Fattori quali modelli di query (elaborazione delle transazioni rispetto ad analisi e creazione di report), comportamento del sottosistema di archiviazione, memoria disponibile e miglioramenti del motore di database nel corso del tempo svolgono tutti un ruolo.

Importante

Le decisioni relative alla manutenzione degli indici devono essere prese dopo aver preso in considerazione più fattori nel contesto specifico di ogni carico di lavoro, incluso il costo della manutenzione delle risorse. Non devono essere basate solo su soglie di frammentazione fissa o densità di pagina.

Effetto collaterale positivo della ricompilazione dell'indice

Dopo la ricompilazione degli indici, i clienti spesso osservano un miglioramento delle prestazioni. Tuttavia, in molti casi questi miglioramenti non sono correlati alla riduzione della frammentazione o all'aumento della densità di pagina.

Una ricompilazione dell'indice offre un vantaggio importante: aggiorna le statistiche sulle colonne chiave dell'indice analizzando tutte le righe nell'indice. Equivale all'esecuzione di UPDATE STATISTICS ... WITH FULLSCAN, che rende aggiornate le statistiche e talvolta migliora la qualità rispetto all'aggiornamento predefinito delle statistiche campionate. Quando vengono aggiornate le statistiche, i piani di query che vi fanno riferimento vengono ricompilati. Se il piano precedente per una query non è ottimale a causa di statistiche non aggiornate, rapporto di campionamento delle statistiche insufficienti o per altri motivi, il piano ricompilato spesso offre prestazioni migliori.

I clienti spesso attribuivano in modo errato questo miglioramento alla ricompilazione dell'indice stesso, considerandolo la causa di una riduzione della frammentazione e di una maggiore densità di pagina. In realtà, lo stesso vantaggio può essere spesso ottenuto a un costo di risorse molto più economico aggiornando le statistiche anziché ricompilando gli indici.

Suggerimento

Il costo delle risorse per l'aggiornamento delle statistiche è minore rispetto alla ricompilazione dell'indice e l'operazione viene spesso completata in pochi minuti. Le ricompilazione degli indici possono richiedere ore.

Strategia di manutenzione degli indici

Microsoft consiglia ai clienti di prendere in considerazione e adottare la seguente strategia di manutenzione degli indici:

  • Non presupporre che la manutenzione degli indici migliorerà sempre notevolmente il carico di lavoro.
  • Misurare l'impatto specifico della riorganizzazione o della ricompilazione degli indici sulle prestazioni delle query nel carico di lavoro. Query Store è un ottimo strumento per misurare le prestazioni "prima della manutenzione" e "dopo la manutenzione" usando la tecnica di test A/B.
  • Se si osserva che la ricompilazione degli indici migliora le prestazioni, provare a sostituirla con l'aggiornamento delle statistiche. Ciò può comportare un miglioramento simile. In tal caso, potrebbe non essere necessario ricompilare gli indici con una certa frequenza ed eseguire invece aggiornamenti periodici delle statistiche. Per alcune statistiche, potrebbe essere necessario aumentare il rapporto di campionamento usando le clausole WITH SAMPLE ... PERCENT o WITH FULLSCAN (questa operazione non è comune).
  • Monitorare la frammentazione dell'indice e la densità di pagina nel tempo per verificare se è presente una correlazione tra questi valori che tendono verso l'alto o verso il basso e le prestazioni delle query. Se una maggiore frammentazione o una densità di pagina inferiore riduce in modo inaccettabile le prestazioni, riorganizzare o ricompilare gli indici. Spesso è sufficiente riorganizzare o ricompilare indici specifici usati dalle query con prestazioni ridotte. In questo modo si evita un costo di risorse superiore per la gestione di ogni indice nel database.
  • La definizione di una correlazione tra la densità di frammentazione/pagina e le prestazioni consente anche di determinare la frequenza di manutenzione dell'indice. Non presupporre che la manutenzione debba essere eseguita in base a una pianificazione fissa. Una strategia migliore consiste nel monitorare la frammentazione e la densità delle pagine ed eseguire la manutenzione degli indici in base alle esigenze prima che le prestazioni diminuiscano in modo inaccettabile.
  • Se si è determinato che la manutenzione dell'indice è necessaria e il relativo costo delle risorse è accettabile, eseguire la manutenzione durante i tempi di utilizzo delle risorse ridotti, se possibile.
  • Testare periodicamente, perché i modelli di uso delle risorse possono cambiare nel tempo.

Manutenzione dell'indice nel database SQL di Azure e nell'istanza gestita di SQL di Azure

Oltre alle considerazioni e alla strategia precedenti, in database SQL di Azure e Istanza gestita di SQL di Azure è particolarmente importante considerare i costi e i vantaggi della manutenzione degli indici. I clienti devono eseguire questi processi solo quando è presente una reale necessità e tenendo conto dei punti seguenti.

  • per impostare i limiti relativi all'utilizzo di CPU, memoria e I/O in base al piano tariffario con provisioning, il database SQL di Azure e l'istanza gestita di SQL di Azure implementano la governance delle risorse. Questi limiti si applicano a tutti i carichi di lavoro utente, inclusa la manutenzione degli indici. Se l'utilizzo cumulativo delle risorse da parte di tutti i carichi di lavoro si avvicina ai limiti delle risorse, l'operazione di ricompilazione o riorganizzazione potrebbe compromettere le prestazioni di altri carichi di lavoro a causa di conflitti di risorse. Ad esempio, i caricamenti di dati in blocco possono risultare più lenti perché l'I/O del log delle transazioni è al 100% a causa di una ricompilazione dell'indice simultanea. In Istanza gestita di SQL di Azure questo impatto può essere ridotto eseguendo la manutenzione degli indici in un gruppo di carico di lavoro di Resource Governor separato con allocazione limitata delle risorse, a scapito dell'estensione della durata della manutenzione dell'indice.
  • Per risparmiare sui costi, i clienti spesso effettuano il provisioning di database, pool elastici e istanze gestite con una capacità aggiuntiva minima delle risorse. Il piano tariffario viene scelto in modo che sia sufficiente per i carichi di lavoro dell'applicazione. Per supportare un aumento significativo dell'utilizzo delle risorse a causa della manutenzione degli indici senza compromettere le prestazioni dell'applicazione, i clienti potrebbero dover effettuare il provisioning di più risorse e aumentare i costi, senza necessariamente migliorare le prestazioni dell'applicazione.
  • Nei pool elastici, le risorse sono condivise tra tutti i database del pool. Anche se un database specifico è inattivo, l'esecuzione della manutenzione dell'indice su tale database può influire sui carichi di lavoro dell'applicazione in esecuzione simultaneamente in altri database nello stesso pool. Per altre informazioni, vedere Gestione delle risorse in pool elastici densi.
  • Per la maggior parte dei tipi di archiviazione usati in database SQL di Azure e Istanza gestita di SQL di Azure, non esiste alcuna differenza nelle prestazioni tra operazioni di I/O sequenziali e casuali. In questo modo si riduce l'impatto della frammentazione dell'indice sulle prestazioni delle query.
  • Quando si usano repliche con scalabilità in lettura o con replica geografica, la latenza dei dati nelle repliche aumenta spesso durante l'esecuzione della manutenzione dell'indice nella replica primaria. Se viene effettuato il provisioning di una replica geografica con risorse insufficienti per sostenere un aumento della generazione del log delle transazioni causato dalla manutenzione dell'indice, potrebbe essere in ritardo rispetto al database primario, causando la reinizializzazione del sistema. In questo modo la replica non sarà disponibile fino al completamento della riproduzione. Inoltre, nei livelli di servizio Premium e Business Critical, durante la manutenzione dell'indice le repliche usate per la disponibilità elevata possono essere molto indietro rispetto a quella primaria. Se un failover è necessario durante o poco dopo la manutenzione dell'indice, può richiedere più tempo del previsto.
  • Se viene eseguita una ricompilazione dell'indice nella replica primaria e una query con esecuzione prolungata viene eseguita contemporaneamente su una replica leggibile, la query può essere terminata automaticamente per impedire il blocco del thread di rollforward nella replica.

Esistono scenari specifici ma non comuni in cui potrebbe essere necessaria una manutenzione dell'indice una tantum o periodica in database SQL di Azure e Istanza gestita di SQL di Azure:

  • er aumentare la densità della pagina e ridurre lo spazPio usato nel database e quindi rimanere entro il limite di dimensioni del piano tariffario. In questo modo si evita di dover aumentare le prestazioni fino a un piano tariffario superiore con un limite di dimensioni superiore.
  • Se diventa necessario compattare i file, la ricompilazione o la riorganizzazione degli indici prima di compattare i file aumenterà la densità di pagina. In questo modo l'operazione di compattazione risulterà più veloce perché sarà necessario spostare meno pagine. Per altre informazioni, vedere

Suggerimento

Se si è determinato che la manutenzione degli indici è necessaria per i carichi di lavoro database SQL di Azure e Istanza gestita di SQL di Azure, è necessario riorganizzare gli indici o usare la ricompilazione dell'indice online. In questo modo, i carichi di lavoro di query accederanno alle tabelle durante la ricompilazione degli indici.

Inoltre, rendendo ripristinabile l'operazione, sarà possibile evitare di riavviarla dall'inizio se viene interrotta da un failover pianificato o non pianificato del database. L'uso di operazioni sugli indici ripristinabili è particolarmente importante quando gli indici sono di grandi dimensioni.

Suggerimento

Le operazioni sugli indici offline vengono in genere completate più velocemente delle operazioni online Devono essere usate quando le tabelle non saranno accessibili dalle query durante l'operazione, ad esempio dopo il caricamento dei dati nelle tabelle di staging come parte di un processo ETL sequenziale.

Limitazioni e restrizioni

Gli indici rowstore con più di 128 extent vengono ricompilati in due fasi separate, logica e fisica. Nella fase logica, le unità di allocazione esistenti usate 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. Per altre informazioni sulle unità di allocazione, vedere Guida sull'architettura di pagina ed extent.

L'istruzione ALTER INDEX REORGANIZE richiede che nel file di dati che include l'indice sia disponibile spazio, perché l'operazione può allocare solo pagine di lavoro temporanee nello stesso file, non in un altro file nel filegroup. Anche se il filegroup ha spazio disponibile, se un file di dati non è disponibile l'utente potrebbe 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 durante l'operazione di riorganizzazione.

Quando l'opzione ALLOW_PAGE_LOCKS è impostata su OFF, non è possibile eseguire operazioni di riorganizzazione degli indici.

Fino a SQL Server 2017 (14.x), la ricompilazione di un indice columnstore cluster è un'operazione offline. Il motore di database deve acquisire un blocco esclusivo sulla tabella o sulla partizione durante la ricompilazione. I dati sono offline e non sono disponibili durante la ricompilazione, anche quando si usa NOLOCK, l'isolamento dello snapshot Read Committed (RCSI) o l'isolamento dello snapshot. A partire da SQL Server 2019 (15.x), un indice columnstore cluster può essere ricompilato tramite l'opzione ONLINE = ON.

Avviso

La creazione e la ricompilazione di indici non allineati per una tabella con oltre 1.000 partizioni sono possibili, ma non supportate. Questo può causare riduzioni delle prestazioni e un eccessivo consumo della memoria. Quando il numero di partizioni supera 1.000, Microsoft consiglia di usare solo indici allineati.

Limitazioni delle statistiche

  • Quando un indice viene creato o ricompilato, le statistiche vengono create o aggiornate analizzando tutte le righe nella tabella, il che equivale a utilizzare la clausola FULLSCAN in CREATE STATISTICS o UPDATE STATISTICS. Tuttavia a partire da SQL Server 2012 (11.x), le statistiche non vengono create o aggiornate analizzando tutte le righe nella tabella quando viene creato o ricompilato un indice partizionato. Viene invece usato il rapporto di campionamento predefinito. Per creare o aggiornare statistiche sugli indici partizionati analizzando tutte le righe nella tabella, usare CREATE STATISTICS o UPDATE STATISTICS con la clausola FULLSCAN.
  • Allo stesso modo, quando l'operazione di creazione o ricompilazione dell'indice è ripristinabile, le statistiche vengono create o aggiornate con il rapporto di campionamento predefinito. Se le statistiche sono state create o aggiornate con la clausola PERSIST_SAMPLE_PERCENT impostata su ON, le operazioni di indice ripristinabile usano il rapporto di campionamento persistente per creare o aggiornare le statistiche.
  • Quando un indice viene riorganizzato, le statistiche non vengono aggiornate.

Esempi

Controllare la frammentazione e la densità di pagina di un indice rowstore usando Transact-SQL

Nell'esempio seguente viene determinata la frammentazione media e la densità di pagina per tutti gli indici rowstore nel database corrente. Usa la modalità SAMPLED per restituire rapidamente risultati interattivi. Per ottenere risultati più accurati, usare la modalità DETAILED. Ciò richiede l'analisi di tutte le pagine di indice e può richiedere molto tempo.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

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

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Per altre informazioni, vedere sys.dm_db_index_physical_stats.

Controllare la frammentazione di un indice columnstore usando Transact-SQL

Nell'esempio seguente viene determinata la frammentazione media per tutti gli indici columnstore con rowgroup compressi nel database corrente.

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

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

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Gestire gli indici con SQL Server Management Studio

Riorganizzare o ricompilare un indice

  1. In Esplora oggetti espandere il database che contiene 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 vuole riorganizzare e scegliere Riorganizza.
  6. Nella finestra di dialogo Riorganizza indici verificare che nella griglia Indici da riorganizzare sia presente l'indice corretto, quindi scegliere OK.
  7. Selezionare la casella di controllo Compatta dati di colonne LOB per specificare che tutte le pagine che contengono dati LOB vengano compattate.
  8. Seleziona OK.

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 scegliere Riorganizza tutto.
  5. Nella finestra di dialogo Riorganizza indici verificare che nella griglia Indici da riorganizzaresiano presenti gli indici corretti. Per rimuovere un indice dalla griglia Indici da riorganizzare , selezionare l'indice desiderato e premere CANC.
  6. Selezionare la casella di controllo Compatta dati di colonne LOB per specificare che tutte le pagine che contengono dati LOB vengano compattate.
  7. Seleziona OK.

Gestire gli indici con Transact-SQL

Nota

Per altri esempi sull'uso di Transact-SQL per ricompilare o riorganizzare gli indici, vedere Esempi ALTER INDEX - Indici rowstore ed Esempi ALTER INDEX - Indici columnstore.

Riorganizzare un indice

L'esempio seguente riorganizza l'indice IX_Employee_OrganizationalLevel_OrganizationalNode nella tabella HumanResources.Employee del database AdventureWorks2022.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

L'esempio seguente riorganizza l'indice columnstore IndFactResellerSalesXL_CCI nella tabella dbo.FactResellerSalesXL_CCI del database AdventureWorksDW2022. Eseguire questo comando per forzare tutti i rowgroup chiusi e aperti nel columnstore.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Riorganizzare tutti gli indici in una tabella

L'esempio seguente riorganizza tutti gli indici della tabella HumanResources.Employee del database AdventureWorks2022.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Ricompilare un indice

Nell'esempio seguente viene ricompilato un singolo indice della tabella Employee nel database AdventureWorks2022.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Ricompilare tutti gli indici in una tabella

L'esempio seguente ricompila tutti gli indici associati alla tabella nel database AdventureWorks2022 tramite la parola chiave ALL. Vengono inoltre specificate tre opzioni.

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

Per altre informazioni, vedere ALTER INDEX.

Contenuto correlato