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 Analytics Platform System (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 in Azure Synapse Analytics, vedere Indicizzazione di tabelle del pool SQL dedicato in Azure Synapse Analytics.

Concetti: frammentazione dell'indice e densità di pagina

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

  • Negli indici dell'albero B (rowstore) la frammentazione esiste quando gli indici hanno pagine in cui l'ordinamento logico all'interno dell'indice, in base ai valori chiave dell'indice, non corrisponde all'ordinamento fisico delle pagine di indice.

    Nota

    Nella documentazione di SQL Server viene usato in modo generico il termine albero B in riferimento agli indici. Negli indici rowstore SQL Server implementa un albero B+. Questo non si applica agli indici columnstore o agli archivi dati in memoria. Per altre informazioni, vedere la guida all'architettura e alla progettazione degli indici SQL di SQL di Azure.

  • Il motore di database modifica automaticamente gli indici ogni volta che vengono eseguite operazioni di inserimento, aggiornamento o eliminazione sui dati sottostanti. Ad esempio, l'aggiunta di righe in una tabella può causare la divisione delle pagine esistenti negli indici rowstore, rendendo spazio per l'inserimento di nuove righe. Nel corso del tempo queste modifiche possono causare la dispersione dei dati nell'indice nel database (frammentati).

  • Per le query che leggono molte pagine che usano analisi di indici completi o di intervallo, gli indici fortemente frammentati possono ridurre le prestazioni delle query perché potrebbe essere necessario un I/O aggiuntivo per leggere i dati richiesti dalla query. 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 per leggere la stessa quantità di dati.

  • 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.

Che 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, densità di pagina è 100%. Se una pagina è vuota, la densità di pagina è 0%. Se una pagina con densità del 100% viene suddivisa in due pagine per contenere una nuova riga, la densità delle due nuove pagine è approssimativamente del 50%.
  • Quando la densità di pagina è bassa, sono necessarie più pagine per archiviare la stessa quantità di dati. Ciò significa che è necessario più operazioni di I/O per leggere e scrivere questi dati e per memorizzare nella cache questi dati è necessaria una quantità maggiore di memoria. Quando la memoria è limitata, vengono memorizzate nella cache 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, 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 e influisce negativamente sulle prestazioni.
  • 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 ricerca 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 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 di pagina, 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 non consiglia 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 dell'indice e la densità delle pagine

Sia la frammentazione che 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 definita in modo diverso per gli indici rowstore e columnstore . Per gli indici rowstore, sys.dm_db_index_physical_stats() consente di determinare la frammentazione e la densità di pagina in un indice specifico, tutti gli indici di una tabella o 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 queste informazioni per ogni partizione.

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

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 come 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.

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

Colonna Descrizione
total_rows Numero di righe archiviate fisicamente nel gruppo di righe. 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 presenti 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, è particolarmente importante 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, potrebbe anche essere necessario esaminare periodicamente la frammentazione per evitare la proliferazione dei record di inoltro. Per altre informazioni sugli heap, vedere Heap (tabelle senza indici cluster).

Vedere Esempi per le query di esempio per determinare la frammentazione e la densità delle pagine.

Metodi di manutenzione dell'indice: riorganizzare e ricompilare

È 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 , è possibile usare uno dei metodi seguenti in tutte le partizioni o in una singola partizione di un indice.

Riorganizzare un indice

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

  • Per gli indici rowstore, il motore di database deframmenta solo il livello foglia degli indici cluster e non cluster in tabelle e viste riordinando fisicamente le pagine a livello foglia in modo che corrispondano all'ordine logico dei nodi foglia (da sinistra a destra). La riorganizzazione compatta anche le pagine di indice per 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 indici columnstore, l'archivio differenziale può terminare con più gruppi di righe di piccole dimensioni dopo l'inserimento, l'aggiornamento e l'eliminazione di 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, che potrebbero rallentare le prestazioni complessive del sistema durante l'esecuzione dell'operazione. Tuttavia, una volta compressi i dati, le prestazioni delle query migliorano. Per esempi di sintassi, vedere Esempi - Riorganizzazione columnstore.

Nota

A partire da SQL Server 2019 (15.x), database SQL di Azure e Istanza gestita di SQL di Azure, lo spostamento delle tuple è aiutato da un'attività di unione in background che comprime automaticamente i rowgroup differenziali aperti più piccoli esistenti per un certo periodo di tempo, come determinato da una soglia interna o 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 ignora la necessità di eseguire ALTER INDEX ... REORGANIZE comandi.

Suggerimento

Se si annulla un'operazione di riorganizzazione o se in caso contrario viene interrotta, lo stato di avanzamento apportato a tale punto viene salvato in modo permanente nel database. Per riorganizzare 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 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 indice ripristinabile, interromperla invece di sospendere l'operazione.

Suggerimento

A seconda delle risorse e dei modelli di carico di lavoro disponibili, la specifica di un valore superiore al valore predefinito MAXDOP nell'istruzione ALTER INDEX REBUILD può ridurre la durata della ricompilazione a scapito di un utilizzo più elevato 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 ALL viene specificato, tutti gli indici della tabella vengono eliminati e ricompilati in una singola operazione. Quando gli indici con 128 o più extent vengono ricompilati, la motore di database rinvia le deallozioni di pagina e acquisisce i blocchi associati fino al completamento della ricompilazione. Per esempi di sintassi, vedere Esempi - Ricompilazione 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 columnstore.

    Suggerimento

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

Usare la ricompilazione dell'indice per eseguire il ripristino dal danneggiamento dei dati

Nelle versioni precedenti di SQL Server, talvolta è possibile ricompilare un indice rowstore non cluster per correggere le incoerenze a causa del danneggiamento dei dati nell'indice.

A partire da SQL Server 2008 (10.0.x), è comunque possibile ripristinare tali incoerenze nell'indice non cluster ricompilando un indice non cluster offline. Tuttavia, non è possibile ripristinare le incoerenze degli indici non cluster ricompilando l'indice online, perché il meccanismo di ricompilazione online usa l'indice non cluster esistente come base per la ricompilazione e quindi comporta l'incoerenza. 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, eliminare e ricreare l'indice non cluster anziché ricompilarlo. Come per le versioni precedenti, è consigliabile eseguire il ripristino da incoerenze ripristinando i dati interessati da un backup; Tuttavia, è possibile ripristinare 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

Sfruttare soluzioni come la deframmentazione degli indici adattivi per gestire automaticamente la frammentazione 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 causano 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)
  • Eliminazione di un indice cluster, che causa l'archiviazione della tabella come heap

Gli scenari seguenti non ricompilano automaticamente tutti gli indici rowstore non cluster nella stessa tabella:

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

Importante

Non è possibile riorganizzare o ricompilare un indice se il filegroup in cui si trova è offline o di sola lettura. Quando si specifica 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 ALL viene specificato con l'istruzione ALTER INDEX ... REORGANIZE , gli indici cluster, non cluster e XML nella tabella vengono riorganizzati.

La ricompilazione o la riorganizzazione di indici rowstore di piccole dimensioni potrebbero non ridurre la frammentazione. Fino a e include SQL Server 2014 (12.x), SQL Server motore di database alloca spazio usando extent misti. Di conseguenza, le pagine di indici di piccole dimensioni vengono talvolta archiviate in extent misti, che rende implicitamente frammentati tali indici. 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

Quando si ricompila 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 in columnstore. Il motore di database deframmenta il columnstore eliminando fisicamente le righe contrassegnate come eliminate.

Nota

A partire da SQL Server 2019 (15.x), lo spostamento delle tuple è aiutato da un'attività di unione in background che comprime automaticamente i gruppi di righe dell'archivio delta aperti più piccoli esistenti per un certo periodo di tempo, come determinato da una soglia interna o unisce gruppi di righe compressi in cui è stato eliminato un numero elevato di righe. Ciò migliora la qualità dell'indice columnstore nel tempo. Per altre informazioni sui termini e sui concetti columnstore, vedere Indici columnstore: Panoramica.

Ricompilare una partizione in alternativa all'intera tabella

La ricompilazione dell'intera tabella richiede molto tempo se l'indice è grande e richiede spazio su disco sufficiente per archiviare una copia aggiuntiva dell'intero 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 UPDATEle istruzioni , DELETEo MERGE hanno interessato un numero elevato di righe.

La ricompilazione di una partizione dopo il caricamento o la modifica dei dati garantisce che tutti i dati vengano archiviati in gruppi di righe 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ù gruppi di righe aperti nell'archivio differenziale. La ricompilazione sposta tutte le righe dell'archivio differenziale in gruppi di righe compressi 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 chiuso nell'archivio delta in columnstore come gruppo di righe compresso. A partire da SQL Server 2016 (13.x) e in database SQL di Azure, il REORGANIZE comando esegue le ottimizzazioni di deframmentazione aggiuntive seguenti online:

  • Rimuove fisicamente le righe da un gruppo di righe quando il 10% o più righe sono state eliminate logicamente. Ad esempio, se un gruppo di righe compresso di 1 milione di righe ha 100.000 righe eliminate, il motore di database rimuove le righe eliminate e ricomprime il gruppo di righe con 900.000 righe, riducendo il footprint di archiviazione.
  • Combina uno o più gruppi di righe compressi per aumentare le righe per ogni rowgroup, fino al massimo di 1.048.576 righe. Ad esempio, se si inseriscono in blocco cinque batch di 102.400 righe ciascuno, si otterranno cinque gruppi di righe compressi. Se si esegue REORGANIZE, questi gruppi di righe verranno uniti in un unico rowgroup compresso con 512.000 righe. Si presuppone che non vi siano limiti di memoria o di dimensioni del dizionario.
  • Il motore di database tenta di combinare gruppi di righe in cui il 10% o più righe sono state contrassegnate come eliminate con altri gruppi di righe. Ad esempio, il gruppo di righe 1 è compresso e contiene 500.000 righe, mentre il rowgroup 21 è compresso e contiene 1.048.576 righe. Il rowgroup 21 ha il 60% delle righe contrassegnate come 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ù gruppi di righe di piccole dimensioni nell'archivio differenziale. È possibile usare ALTER INDEX REORGANIZE per forzare questi gruppi di righe in columnstore e quindi combinare gruppi di righe compressi più piccoli in gruppi di righe compressi più grandi. L'operazione di riorganizzazione rimuoverà anche le righe contrassegnate come eliminate dal columnstore.

Nota

La riorganizzazione di un indice columnstore tramite Management Studio combina i gruppi di righe compressi, ma non impone la compressione di tutti i gruppi di righe nel columnstore. I gruppi di righe chiusi verranno compressi, ma i gruppi di righe aperti non verranno compressi in columnstore. Per comprimere forzatamente tutti i gruppi di righe, 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, richiede un utilizzo intensivo delle risorse. Determina un aumento significativo dell'utilizzo della CPU, della memoria usata e delle operazioni di I/O di archiviazione. Tuttavia, a seconda del carico di lavoro del database e di altri fattori, i vantaggi offerti vanno dall'importanza vitale a quella meno importante.

Per evitare un utilizzo non necessario delle risorse che potrebbero essere dannose per i carichi di lavoro di query, Microsoft non consiglia di eseguire la manutenzione degli indici in modo indiscriminato. Al contrario, i vantaggi delle prestazioni derivanti dalla manutenzione degli indici devono essere determinati in modo empirico per ogni carico di lavoro usando la strategia consigliata e pesati in base ai costi delle risorse e all'impatto del carico di lavoro necessari per ottenere questi vantaggi.

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

Importante

Le decisioni di 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 basati solo su soglie di frammentazione fissa o densità di pagina.

Effetto collaterale positivo della ricompilazione dell'indice

I clienti spesso osservano miglioramenti delle prestazioni dopo la ricompilazione degli indici. 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. Si tratta dell'equivalente dell'esecuzione UPDATE STATISTICS ... WITH FULLSCANdi , che rende le statistiche correnti 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, portandolo a 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 minuti anziché in ore che possono essere necessarie per le ricompilazione dell'indice.

Strategia di manutenzione degli indici

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

  • 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 frequenza o affatto ed eseguire invece aggiornamenti periodici delle statistiche. Per alcune statistiche, potrebbe essere necessario aumentare il rapporto di campionamento usando le WITH SAMPLE ... PERCENT clausole 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 tendenza 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 presenti, tenendo presente che i modelli di utilizzo delle risorse possono cambiare nel tempo.

Manutenzione degli indici in database SQL di Azure e 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 eseguirlo solo quando è presente una necessità dimostrata e tenendo conto dei punti seguenti.

  • database SQL di Azure e Istanza gestita di SQL di Azure implementare la governance delle risorse per impostare i limiti relativi all'utilizzo di CPU, memoria e I/O in base al piano tariffario con provisioning. 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 un headroom minimo delle risorse. Il piano tariffario viene scelto per essere 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 vengono condivise in tutti i database in un pool. Anche se un database specifico è inattiva, 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 I/O casuali. In questo modo si riduce l'impatto della frammentazione dell'indice sulle prestazioni delle query.
  • Quando si usano repliche con scalabilità orizzontale 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 è disponibile fino al completamento della riproduzione. Inoltre, nei livelli di servizio Premium e Business Critical, le repliche usate per la disponibilità elevata possono essere molto indietro rispetto a quella primaria durante la manutenzione dell'indice. 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.

In database SQL di Azure e Istanza gestita di SQL di Azure possono essere necessari scenari specifici ma non comuni:

  • La manutenzione degli indici può essere necessaria per aumentare la densità della pagina e ridurre lo spazio 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, ricompilare o riorganizzare gli indici prima di compattare i file aumenterà la densità di pagina. In questo modo l'operazione di compattazione risulta più veloce, perché sarà necessario spostare meno pagine. Per altre informazioni, visitare:

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 accedono alle tabelle durante la ricompilazione degli indici.

Inoltre, rendendo ripristinabile l'operazione, è possibile evitare di riavviarlo dall'inizio se viene interrotto 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 rispetto alle operazioni online. Devono essere usati 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 all'architettura di pagine ed extent.

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

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 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), è possibile ricompilare un indice columnstore cluster usando 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 tipo di operazioni può causare riduzioni delle prestazioni e un eccessivo consumo della memoria. Microsoft consiglia di usare solo indici allineati quando il numero di partizioni supera 1.000.

Limitazioni delle statistiche

  • Quando viene creato o ricompilato un indice, le statistiche vengono create o aggiornate analizzando tutte le righe della tabella, che equivale a usare la FULLSCAN clausola in CREATE STATISTICS o UPDATE STATISTICS. Tuttavia, a partire da SQL Server 2012 (11.x), quando viene creato o ricompilato un indice partizionato, le statistiche non vengono create o aggiornate analizzando tutte le righe della tabella. Viene invece usato il rapporto di campionamento predefinito. Per creare o aggiornare statistiche sugli indici partizionati analizzando tutte le righe della tabella, usare CREATE STATISTICS o UPDATE STATISTICS con la FULLSCAN clausola .
  • Analogamente, 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 per l'ultimo aggiornamento con la PERSIST_SAMPLE_PERCENT clausola impostata su ON, le operazioni di indice ripristinabili 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 SAMPLED modalità per restituire rapidamente risultati interattivi. Per ottenere risultati più accurati, usare la DETAILED modalità . 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 gruppi di righe 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 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 vuole riorganizzare e scegliere Riorganizza.
  6. Nella finestra di dialogo Riorganizza indici verificare che l'indice corretto si trova nella griglia Indici da riorganizzare e selezionare 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 contenente 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 tramite 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. Questo comando forza tutti i gruppi di righe 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.

Passaggi successivi