Ottimizzare la manutenzione degli indici per migliorare le prestazioni delle query e ridurre il consumo di risorse

Si applica a: sìSQL Server (tutte le versioni supportate) Sìdatabase SQL di Azure SìIstanza gestita di SQL di Azure sìParallel Data Warehouse

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

Nota

Le informazioni contenute in questo articolo non si applicano 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 di pool SQL dedicate in Azure Synapse Analytics .

Concetti: frammentazione dell'indice e densità delle pagine

Che cos'è la frammentazione dell'indice e come influisce sulle prestazioni:

  • Negli indici albero B (rowstore), la frammentazione si verifica quando gli indici hanno pagine in cui l'ordinamento logico all'interno dell'indice, in base ai valori di chiave dell'indice, non corrisponde all'ordinamento fisico delle pagine di indice.
  • Tramite il Motore di database la modifica degli indici viene eseguita automaticamente dopo ogni operazione di modifica, inserimento o eliminazione dei dati sottostanti. Ad esempio, l'aggiunta di righe in una tabella può causare la divisione delle pagine esistenti negli indici rowstore, facendo spazio all'inserimento di nuove righe. Nel corso del tempo queste modifiche possono causare la dispersa dei dati nell'indice nel database (frammentati).
  • Per le query che leggono molte pagine usando analisi di indici completi o di intervallo, gli indici molto frammentati possono ridurre le prestazioni delle query perché potrebbe essere necessario un I/O aggiuntivo per leggere i dati richiesti dalla query. Invece di 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 completezza della pagina) e come influisce sulle prestazioni:

  • Ogni pagina del database può contenere un numero variabile di righe. Se le righe prendono tutto lo spazio in una pagina, la densità di pagina è 100%. Se una pagina è vuota, la densità della pagina è 0%. Se una pagina con una densità del 100% è suddivisa in due pagine per contenere una nuova riga, la densità delle due nuove pagine è di circa il 50%.
  • Quando la densità di pagina è bassa, sono necessarie più pagine per archiviare la stessa quantità di dati. Ciò significa che sono necessarie più operazioni di I/O per leggere e scrivere questi dati, oltre a una maggiore quantità di memoria per memorizzare i dati nella cache. Quando la memoria è limitata, un numero inferiore di pagine richieste da una query verrà memorizzato nella cache, causando un numero ancora maggiore di I/O su disco. Di conseguenza, una bassa densità di pagina influisce negativamente sulle prestazioni.
  • Quando aggiunge righe a una pagina, non riempirà completamente la pagina se il fattore di riempimento per l'indice è impostato su un valore diverso da Motore di database 100 (o 0, equivalente in questo contesto). In questo modo si riduce la densità di pagina e si aggiunge in modo analogo un sovraccarico di I/O e influisce negativamente sulle prestazioni.
  • Una bassa densità di pagina può aumentare il numero di livelli intermedi dell'albero B. Ciò aumenta moderatamente i costi di CPU e I/O per la ricerca di pagine a livello foglia nelle analisi e nelle attività di ricerca dell'indice.
  • Quando Query Optimizer compila un piano di query, considera il costo dell'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, quando la densità di pagina diminuisce nel tempo a causa delle divisioni di pagina, l'utilità di ottimizzazione può compilare un piano diverso per la stessa query, con un profilo di utilizzo delle risorse e delle prestazioni diverso.

Suggerimento

In molti carichi di lavoro l'aumento della densità di pagina comporta un impatto sulle prestazioni più positivo rispetto alla riduzione della frammentazione.

Per evitare di ridurre inutilmente la densità di pagina, 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 gli indici modificati di frequente con colonne iniziali contenenti valori GUID non sequenziali.

Misurazione della frammentazione dell'indice e della 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 utilizzare.

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 in 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 sys.dm_db_index_physical_stats restituito da 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 della pagina.

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

Il set di risultati sys.dm_db_column_store_row_group_physical_stats restituito da 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à delle pagine 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 query di esempio per determinare la frammentazione e la densità di pagina.

Metodi di manutenzione degli indici: 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 richiede meno risorse rispetto alla ricompilazione di un indice. Per questo motivo deve essere il metodo di manutenzione dell'indice preferito, a meno che non vi sia un motivo specifico per usare la ricompilazione dell'indice. Riorganizzare è 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 ALTER INDEX ... REORGANIZE l'operazione.

  • Per gli indici rowstore, 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 Motore di database destra). La riorganizzazione compatta anche le pagine di indice per rendere la densità della pagina uguale al fattore di riempimento dell'indice. Per visualizzare l'impostazione del fattore di riempimento, usare sys.indexes. Per esempi di sintassi, vedere Examples - Rowstore reorganize.
  • 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 dei dati nel tempo. La riorganizzazione di un indice columnstore forza i gruppi di righe dell'archivio differenziale 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 anche fisicamente le righe contrassegnate come eliminate nel columnstore. La riorganizzazione di un indice columnstore può richiedere risorse cpu aggiuntive per comprimere i dati, con un possibile rallentamento delle prestazioni complessive del sistema durante l'esecuzione dell'operazione. Tuttavia, dopo la compressione dei dati, le prestazioni delle query migliorano. Per esempi di sintassi, vedere Examples - Columnstore reorganize.

Nota

A partire da , e , il metodo SQL Server 2019 (15.x) database SQL di Azure tuple-mover viene utilizzato da un'attività di unione in background che comprime automaticamente rowgroup differenziali aperti più piccoli esistenti per un certo periodo di tempo come determinato da una soglia interna o unisce rowgroup compressi da cui è stato eliminato un numero elevato di Istanza gestita di SQL di Azure righe. In questo modo viene migliorata la qualità dell'indice columnstore nel tempo. Nella maggior parte dei casi questo elimina la necessità di eseguire ALTER INDEX ... REORGANIZE comandi.

Suggerimento

Se si annulla un'operazione di riorganizzazione o se viene interrotta in altro modo, lo stato di avanzamento fino a quel 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 Motore di database versione, un'operazione di ricompilazione può essere eseguita offline o online. Una ricompilazione dell'indice offline richiede in genere meno tempo rispetto a una ricompilazione online, ma mantiene blocchi a livello di oggetto per la durata dell'operazione di ricompilazione, bloccando l'accesso delle query alla tabella o alla vista.

Una ricompilazione dell'indice online non richiede blocchi a livello di oggetto fino alla fine dell'operazione, quando un blocco deve essere mantenuto per un breve periodo di tempo per completare la ricompilazione. A seconda della versione di Motore di database , una ricompilazione dell'indice online può essere avviata come operazione resumable. Una ricompilazione dell'indice resumibile può essere sospesa, mantenendo lo stato di avanzamento fino a quel punto. Un'operazione di ricompilazione resumable può essere ripresa dopo essere stata sospesa o interrotta oppure interrotta se il completamento della ricompilazione non è più necessario.

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

Nota

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

Se un'operazione sull'indice resumibile 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 riutilizzabile, interromperla invece di sospendere l'operazione.

Suggerimento

A seconda delle risorse disponibili e dei modelli di carico di lavoro, specificando un valore maggiore del valore predefinito nell'istruzione ALTER INDEX REBUILD è possibile ridurre la durata della ricompilazione a scapito di un utilizzo più elevato MAXDOP 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 nella tabella vengono eliminati e ricompilati in una singola operazione. Quando gli indici con 128 o più extent vengono ricompilati, la pagina rinvia le deallocazione e acquisisce i blocchi associati fino al completamento della Motore di database 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 SQL Server 2016 (13.x) da , la ricompilazione dell'indice columnstore non è in genere necessaria perché esegue le nozioni di base di una ricompilazione REORGANIZE come operazione online.

Uso della ricompilazione dell'indice per il ripristino da un danneggiamento dei dati

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

A partire da , è comunque possibile correggere tali incoerenze nell'indice non cluster ricompilando offline un indice SQL Server 2008 non cluster. Tuttavia, non è possibile correggere le incoerenze dell'indice 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 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 sia utilizzato come origine dei dati, eliminare e ricreare l'indice non cluster anziché ricompilarlo. Come con le versioni precedenti, è consigliabile eseguire il ripristino da incoerenze ripristinando i dati interessati da un backup. Tuttavia, potrebbe essere possibile correggere le incoerenze dell'indice non cluster ricompilarlo offline o ricrearlo. Per altre informazioni, vedere DBCC CHECKDB (Transact-SQL).

Gestione automatica dell'indice e delle statistiche

Sfruttare soluzioni come Adaptive Index Defrag 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

Negli scenari seguenti tutti gli indici rowstore non cluster in una tabella vengono ricompilati automaticamente:

  • Creazione di un indice cluster in una tabella, inclusa la ricreazione dell'indice cluster con una chiave diversa tramite 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 trova 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 con l'istruzione , gli indici cluster, non cluster e XML nella tabella ALL ALTER INDEX ... REORGANIZE vengono riorganizzati.

La ricompilazione o la riorganizzazione di indici rowstore di piccole dimensioni potrebbero non ridurre la frammentazione. Fino a SQL Server 2014 (12.x), incluso, Motore di database di SQL Server alloca spazio usando extent misti. Di conseguenza, le pagine di indici di piccole dimensioni vengono a volte archiviate in extent misti, il 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

Durante la ricompilazione di un indice columnstore, il Motore di database legge tutti i dati dell'indice columnstore originale, incluso l'archivio differenziale. Combina i dati in nuovi gruppi di righe e comprime tutti i gruppi di righe in columnstore. Motore di databasedeframmenta il columnstore eliminando fisicamente le righe contrassegnate come eliminate.

Nota

A partire da , lo strumento di spostamento della tupla è consentito da un'attività di unione in background che comprime automaticamente i gruppi di righe dell'archivio differenziale aperto più piccoli esistenti per un certo periodo di tempo, come determinato da una soglia interna, oppure unisce gruppi di righe compressi in cui è stato eliminato un numero elevato di SQL Server 2019 (15.x) righe. Ciò migliora 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 dell'intera tabella richiede molto tempo se l'indice è di grandi dimensioni 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 le istruzioni , o hanno interessato un numero elevato di UPDATE DELETE MERGE righe.

La ricompilazione di una partizione dopo il caricamento o la modifica dei dati garantisce che tutti i dati siano archiviati in gruppi di righe compressi in columnstore. Quando il processo di caricamento dei dati inserisce dati in una partizione usando batch inferiori a 102.400 righe, la partizione può terminare con più gruppi di righe aperti nell'archivio differenziale. La ricompilazione sposta tutte le righe dell'archivio differenziale in gruppi di righe compressi in columnstore.

Considerazioni specifiche per la riorganizzazione di un indice columnstore

Quando si riorganizza un indice columnstore, comprime ogni gruppo di righe chiuso nell'archivio differenziale in Motore di database columnstore come gruppo di righe 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 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, rimuoverà le righe eliminate e comprimerà nuovamente il gruppo di righe con Motore di database 900.000 righe, riducendo il footprint di archiviazione.
  • Combina uno o più gruppi di righe compressi per aumentare le righe per ogni rowgroup, fino a un massimo di 1.048.576 righe. Ad esempio, se si inseriscono in blocco cinque batch di 102.400 righe ognuna, si otterrà 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.
  • Tenta di combinare gruppi di righe in cui il 10% o più righe sono state contrassegnate come Motore di database 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, lasciando 409.830 righe. il privilegio di combinare questi due gruppi di righe per comprimere un nuovo gruppo di righe Motore di database 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 per forzare questi gruppi di righe in columnstore e quindi combinare gruppi di righe compressi più piccoli in gruppi di righe ALTER INDEX REORGANIZE compressi più grandi. L'operazione di riorganizzazione rimuoverà anche le righe contrassegnate come eliminate dal columnstore.

Nota

La riorganizzazione di un indice columnstore tramite combina i gruppi di righe compressi, ma non forza la compressione di tutti i gruppi di righe Management Studio 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 Transact-SQL l'esempio che include COMPRESS_ALL_ROW_GROUPS = ON .

Cosa considerare prima di eseguire la manutenzione dell'indice

La manutenzione degli indici, eseguita riorganizzando o ricompilando un indice, richiede un utilizzo intensivo delle risorse. Causa un aumento significativo dell'utilizzo della CPU, della memoria usata e dell'I/O di archiviazione. Tuttavia, a seconda del carico di lavoro del database e di altri fattori, i vantaggi che offre variano da estremamente importanti a meno significativi.

Per evitare un utilizzo non necessario delle risorse che può essere dannoso per i carichi di lavoro di query, Microsoft non consiglia di eseguire in modo indiscriminato la manutenzione degli indici. I vantaggi in termini di prestazioni della manutenzione degli indici devono invece essere determinati empiricamente per ogni carico di lavoro usando la strategia consigliata e ponderati in base ai costi delle risorse e all'impatto del carico di lavoro necessari per ottenere questi vantaggi.

La probabilità di visualizzare i vantaggi in termini di prestazioni derivanti dalla riorganizzazione o dalla ricompilazione di un indice è maggiore quando l'indice è frammentato in modo elevato o quando la densità della pagina è bassa. Tuttavia, questi non sono gli unici aspetti 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 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 delle risorse di manutenzione. Non devono essere basate solo su soglie fisse di frammentazione 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 presenta un vantaggio importante: aggiorna le statistiche sulle colonne chiave dell'indice eseguendo l'analisi di tutte le righe nell'indice. Equivale all'esecuzione di , che rende aggiornate le statistiche e talvolta ne migliora la qualità rispetto UPDATE STATISTICS ... WITH FULLSCAN all'aggiornamento predefinito delle statistiche campionate. Quando le statistiche vengono aggiornate, i piani di query che vi fanno riferimento vengono ricompilati. Se il piano precedente per una query non era ottimale a causa di statistiche non aggiornate, di un rapporto di campionamento delle statistiche insufficiente o per altri motivi, il piano ricompilato avrà spesso prestazioni migliori.

I clienti spesso attribuite erroneamente questo miglioramento alla ricompilazione dell'indice stesso, considerandolo come risultato di una frammentazione ridotta e di una maggiore densità di pagina. In realtà, lo stesso vantaggio può spesso essere ottenuto a un costo delle 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 potrebbero essere necessarie per le ricompilazioni 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 migliori sempre in modo 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 modo efficace 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 sostituirli con l'aggiornamento delle statistiche. Ciò può comportare un miglioramento simile. In tal caso, potrebbe non essere necessario ricompilare gli indici con la frequenza o con cui eseguire aggiornamenti periodici delle statistiche. Per alcune statistiche, potrebbe essere necessario aumentare il rapporto di campionamento usando le WITH SAMPLE ... PERCENT clausole WITH FULLSCAN o (questo non è comune).
  • Monitorare la frammentazione dell'indice e la densità delle pagine nel tempo per verificare se esiste una correlazione tra questi valori di tendenza verso l'alto o verso il basso e le prestazioni delle query. Se una maggiore frammentazione o una densità di pagina inferiore riducono le prestazioni in modo inaccettabile, 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 delle risorse più elevato per la gestione di ogni indice nel database.
  • La definizione di una correlazione tra frammentazione/densità di pagina e prestazioni consente anche di determinare la frequenza di manutenzione dell'indice. Non presupporre che la manutenzione deve 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 dell'indice in base alle esigenze prima che le prestazioni si peggiorino in modo inaccettabile.
  • Se si è determinato che è necessaria la manutenzione dell'indice e il relativo costo delle risorse è accettabile, eseguire la manutenzione durante i periodi di utilizzo ridotto delle risorse, se presenti, tenendo presente che i modelli di utilizzo delle risorse possono cambiare nel tempo.

Manutenzione dell'indice in database SQL di Azure e Istanza gestita di SQL di Azure

Oltre alle considerazioni e alla strategia precedenti, in ed è particolarmente importante considerare i costi e database SQL di Azure i vantaggi della manutenzione degli Istanza gestita di SQL di Azure indici. I clienti devono eseguire questa operazione solo in caso di esigenze dimostrate e tenendo conto dei punti seguenti.

  • database SQL di Azure e Istanza gestita di SQL di Azure implementano la governance delle risorse per impostare i limiti di CPU, memoria e consumo di I/O in base al piano tariffario di cui è stato effettuato il 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 può ridurre le prestazioni di altri carichi di lavoro a causa di un'incoerzione delle risorse. Ad esempio, i caricamenti bulk dei dati possono diventare più lenti perché l'I/O del log delle transazioni è al 100% a causa di una ricompilazione simultanea dell'indice. In , questo impatto può essere ridotto eseguendo la manutenzione degli indici in un gruppo di carico di lavoro Resource Governor separato con allocazione di risorse limitate, a scapito dell'estensione della durata della Istanza gestita di SQL di Azure manutenzione degli indici.
  • Per risparmiare sui costi, i clienti spesso effettuano il provisioning di database, pool elastici e istanze gestite con una capacità di gestione delle risorse minima. Il piano tariffario viene scelto come sufficiente per i carichi di lavoro delle applicazioni. Per consentire un aumento significativo dell'utilizzo delle risorse dovuto alla manutenzione degli indici senza ridurre le prestazioni dell'applicazione, i clienti possono 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 tra tutti i database di un pool. Anche se un database specifico è inattivo, l'esecuzione della manutenzione degli indici su tale database può influire sui carichi di lavoro delle applicazioni in esecuzione contemporaneamente in altri database nello stesso pool. Per altre informazioni, vedere Gestione delle risorse in pool elastici ad alta densità.
  • Per la maggior parte dei tipi di archiviazione usati in e , non esiste alcuna differenza di prestazioni tra database SQL di Azure Istanza gestita di SQL di Azure I/O sequenziale e I/O casuale. 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 replica geografica, la latenza dei dati nelle repliche aumenta spesso durante l'esecuzione della manutenzione degli indici nella replica primaria. Se viene effettuato il provisioning di una replica geografica con risorse insufficienti per sostenere un aumento nella generazione del log delle transazioni causato dalla manutenzione dell'indice, potrebbe essere molto indietro rispetto alla replica primaria, causando il reinizializzazione da parte del sistema. In questo modo la replica non sarà disponibile fino al completamento del reinieding. Inoltre, nei livelli di servizio Premium e business critical, le repliche usate per la disponibilità elevata possono essere molto indietro rispetto alla replica primaria durante la manutenzione dell'indice. Se è necessario un failover durante o subito dopo la manutenzione dell'indice, l'operazione può richiedere più tempo del previsto.
  • Se nella replica primaria viene eseguita una ricompilazione dell'indice e viene eseguita contemporaneamente una query con esecuzione di lunga durata su una replica leggibile, la query potrebbe essere terminata automaticamente per impedire il blocco del thread di risincronizzazione nella replica.

Esistono scenari specifici, ma non comuni, in cui può essere necessaria una manutenzione periodica o una sola volta degli indici in database SQL di Azure e Istanza gestita di SQL di Azure :

  • La manutenzione dell'indice può essere necessaria per aumentare la densità delle pagine 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 di dati,la ricompilazione o la riorganizzazione degli indici prima della compattazione dei file aumenterà la densità di pagina. In questo modo l'operazione di compattazione è più veloce, perché sarà necessario spostare un minor numero di pagine.

Suggerimento

Se si è determinato che la manutenzione degli indici è necessaria per i carichi di lavoro e , è necessario riorganizzare gli indici o usare la ricompilazione database SQL di Azure Istanza gestita di SQL di Azure dell'indice online. Ciò consente ai carichi di lavoro di query di accedere alle tabelle durante la ricompilazioni degli indici.

Inoltre, rendere l'operazione recuperabile consente di evitare di riavviarla dall'inizio se viene interrotta da un failover del database pianificato o non pianificato. L'utilizzo di operazioni di indice resumable è particolarmente importante quando gli indici sono di grandi dimensioni.

Suggerimento

Le operazioni degli indici offline vengono in genere completate più rapidamente rispetto alle operazioni online. Devono essere usate quando le query non accederanno alle tabelle 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 dipagine ed extent.

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

  • Quando un indice viene creato o ricompilato, le statistiche vengono create o aggiornate tramite l'analisi di tutte le righe della tabella, che equivale all'uso della clausola in o FULLSCAN CREATE STATISTICS UPDATE STATISTICS . Tuttavia, a partire da , quando viene creato o ricompilato un indice partizionato, le statistiche non vengono create o aggiornate tramite l'analisi di tutte SQL Server 2012 (11.x) le righe della tabella. Viene invece usato il rapporto di campionamento predefinito. Per creare o aggiornare statistiche sugli indici partizionati eseguendo l'analisi di 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 è riutilizzabile, le statistiche vengono create o aggiornate con il rapporto di campionamento predefinito. Se le statistiche sono state create o aggiornate per l'ultima volta con la clausola impostata su , le operazioni di indice riutilizzabili usano il rapporto di campionamento persistente PERSIST_SAMPLE_PERCENT per creare o aggiornare le ON statistiche.
  • Quando un indice viene riorganizzato, le statistiche non vengono aggiornate.

Esempio

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

L'esempio seguente determina la frammentazione media e la densità di pagina per tutti gli indici rowstore nel database corrente. Usa la modalità SAMPLED per restituire rapidamente risultati utilizzabili. Per risultati più accurati, usare la DETAILED modalità . Questa operazione 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.

Per 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

Gestione degli indici tramite SQL Server Management Studio

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

Gestione degli indici tramite Transact-SQL

Nota

Per altri esempi sull'uso di per ricompilare o riorganizzare gli indici, vedere Alter INDEX Examples - Rowstore Indexes (Esempi di Transact-SQL ALTER INDEX - Indici rowstore) e ALTER INDEX Examples - Columnstore Indexes (Esempi di ALTER INDEX - Indici columnstore).

Per riorganizzare un indice

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

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

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

Per riorganizzare tutti gli indici in una tabella

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

Per ricompilare un indice

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Per ricompilare tutti gli indici in una tabella

L'esempio seguente ricompila tutti gli indici associati alla tabella nel database AdventureWorks2016 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.

Vedere anche