Condividi tramite

Linee guida per le operazioni sugli indici online

  • Articolo

Si applica a:SQL ServerDatabase SQL di AzureIstanza gestita di SQL di AzureDatabase SQL in Microsoft Fabric

Quando si eseguono operazioni sugli indici online sono da ritenersi valide le linee guida seguenti:

  • Gli indici cluster devono essere creati, ricompilati o eliminati offline se la tabella sottostante contiene i seguenti tipi di dati di grandi dimensioni (LOB): image, ntext, e text.
  • Gli indici non cluster non univoci possono essere creati online quando la tabella contiene colonne che usano i tipi di dati LOB, ma nessuna di queste colonne viene usata nella definizione dell'indice come colonna chiave o inclusa.
  • Non è possibile creare, ricompilare o rimuovere online indici su tabelle temporanee locali. Questa limitazione non è valida per gli indici su tabelle temporanee globali.
  • È possibile avviare un'operazione sull'indice online come operazione ripristinabile usando la clausola RESUMABLE di CREATE INDEX o ALTER INDEX. Un'operazione di indice ripristinabile può essere riavviata dopo un errore imprevisto, un failover del database o un comando ALTER INDEX PAUSE e continuare da dove è stato interrotto.

Nota

Le operazioni online sugli indici non sono disponibili in tutte le edizioni di Microsoft SQL Server. Per un elenco delle funzionalità supportate dalle varie edizioni di SQL Server, vedere Edizioni e funzionalità supportate di SQL Server 2022.

Nella tabella seguente sono riportate le operazioni sugli indici che è possibile eseguire online, gli indici che sono esclusi da queste operazioni online e le restrizioni per gli indici ripristinabili. Sono inoltre incluse ulteriori limitazioni.

Operazione su indice online Indici esclusi Altre limitazioni
ALTER INDEX REBUILD Indice clusterizzato disabilitato o vista indicizzata disabilitata

Indice XML

Indice di una tabella temporanea locale		 L'operazione può avere esito negativo se la tabella contiene un indice escluso e si utilizza la parola chiave ALL.

Si applicano ulteriori restrizioni alla ricostruzione degli indici disabilitati. Per altre informazioni, vedere Disabilitazione di indici e vincoli.
CREATE INDEX Indice XML

Indice univoco iniziale raggruppato su una vista

Indice di una tabella temporanea locale
CREATE INDEX WITH DROP_EXISTING Indice raggruppato disabilitato o vista indicizzata disabilitata

Indice di una tabella temporanea locale

Indice XML
DROP INDEX Indice disabilitato

Indice XML

Indice non clusterizzato

Indice di una tabella temporanea locale		 Non è possibile specificare più indici in una singola istruzione.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY o UNIQUE) Indice di una tabella temporanea locale

Indice clusterizzato		 È consentito l'utilizzo di una sola clausola secondaria alla volta. Ad esempio, non è possibile aggiungere e eliminare i vincoli PRIMARY KEY o UNIQUE nella stessa istruzione ALTER TABLE.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY o UNIQUE) Indice clustering

La tabella sottostante non può essere modificata, troncata o eliminata mentre è in corso un'operazione sull'indice online.

L'impostazione di un'opzione online (ON oppure OFF) specificata durante la creazione o l'eliminazione di un indice cluster viene applicata a tutti gli indici non cluster che devono essere ricompilati. Ad esempio, se l'indice cluster viene compilato online usando CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, vengono ricreati online anche tutti gli indici non cluster associati.

Quando si crea o si ricompila un indice UNIQUE online, il generatore dell'indice e una transazione utente simultanea potrebbero provare a inserire la stessa chiave, causando una violazione di univocità. Se una riga immessa da un utente viene inserita nel nuovo indice (destinazione) prima che la riga originale della tabella di origine venga spostata nel nuovo indice, l'operazione sull'indice online avrà esito negativo.

Un'operazione su un indice online può in rari casi provocare un deadlock quando interagisce con aggiornamenti al database a causa di attività dell'utente o dell'applicazione. In questi rari casi, l'attività dell'utente o dell'applicazione viene selezionata come vittima del deadlock.

È possibile eseguire operazioni DDL sugli indici online simultanee sulla stessa tabella o vista solo quando si stanno creando più indici nuovi non cluster oppure si stanno riorganizzando indici non cluster. Qualsiasi altra operazione sugli indici online eseguita nello stesso istante avrà esito negativo. Ad esempio, non è possibile creare un nuovo indice online durante la ricompilazione di un indice online esistente sulla stessa tabella.

Non è possibile eseguire un'operazione online quando un indice contiene una colonna del tipo di oggetto di grandi dimensioni e la stessa transazione apporta modifiche ai dati prima dell'avvio dell'operazione sull'indice online. Per risolvere questo problema, spostare l'operazione sull'indice online all'esterno della transazione o spostarla prima di qualsiasi modifica dei dati nella stessa transazione.

Considerazioni sullo spazio su disco

Le operazioni sugli indici online richiedono più spazio su disco rispetto alle operazioni sugli indici offline.

  • Durante le operazioni di creazione dell'indice e di ricompilazione dell'indice, è necessario spazio aggiuntivo per l'indice in corso di ricompilazione o ricompilato. In genere, questo spazio aggiuntivo corrisponde allo spazio corrente occupato dall'indice, ma potrebbe essere maggiore o minore a seconda della compressione utilizzata nell'indice corrente o ricompilato.
  • È richiesto anche spazio su disco aggiuntivo per l'indice di mapping temporaneo. Questo indice temporaneo è utilizzato nelle operazioni sugli indici online che creano, ricompilano o eliminano un indice cluster.
  • L'eliminazione di un indice cluster online richiede lo stesso spazio che è necessario per la creazione o la ricompilazione di un indice cluster online.

Per altre informazioni, vedere requisiti di spazio su disco per le operazioni DDL sull'indice.

Considerazioni sulle prestazioni

Anche se le operazioni sugli indici online consentono l'attività di aggiornamento utente simultaneo, le operazioni sugli indici possono richiedere più tempo se l'attività di aggiornamento è pesante. Le operazioni sugli indici online sono generalmente più lente delle operazioni sugli indici offline equivalenti, indipendentemente dal livello di attività di aggiornamento simultanee.

Poiché entrambe le strutture di origine e di destinazione vengono mantenute durante l'operazione sull'indice online, l'utilizzo delle risorse per le transazioni di inserimento, aggiornamento ed eliminazione viene aumentato, potenzialmente raddoppiato. Ciò può causare una riduzione delle prestazioni e un maggior utilizzo di risorse durante l'operazione sugli indici, specialmente del tempo CPU. Le operazioni sugli indici online vengono registrate completamente.

Sebbene le operazioni online siano consigliabili, è opportuno valutare l'ambiente e i requisiti specifici, Potrebbe essere ottimale eseguire le operazioni sugli indici offline. In quest'ultimo caso gli utenti avrebbero un accesso limitato ai dati durante l'operazione, la quale però terminerebbe più rapidamente e utilizzerebbe meno risorse.

Nei computer multiprocessore che eseguono SQL Server 2016 (13.x) e versioni successive, le operazioni sugli indici possono usare il parallelismo per eseguire le operazioni di analisi e ordinamento associate all'istruzione index. È possibile utilizzare l'opzione di indice MAXDOP per controllare il grado di parallelismo dell'operazione sull'indice online. In questo modo è possibile bilanciare le risorse utilizzate dall'operazione sugli indici con quelle occupate dagli utenti simultanei. Per altre informazioni, vedere Configurare le operazioni parallele sugli indici. Per altre informazioni sulle edizioni di SQL Server che supportano le operazioni indicizzate parallele, vedere Edizioni e funzionalità supportate di SQL Server 2022.

Poiché un blocco condiviso (S) o un blocco di modifica dello schema (Sch-M) viene mantenuto nella fase finale dell'operazione sull'indice, fare attenzione eseguendo un'operazione sull'indice online all'interno, ad esempio, del blocco BEGIN TRANSACTION ... COMMIT di una transazione utente esplicita. In questo modo i blocchi vengono mantenuti fino alla fine della transazione, bloccando potenzialmente altri carichi di lavoro.

Se i blocchi di pagina dell'indice sono disabilitati tramite ALLOW_PAGE_LOCKS=OFF, la ricostruzione online dell'indice può aumentare la frammentazione dell'indice quando viene eseguita con MAXDOP maggiore di 1. Per altre informazioni, vedere Funzionamento: Ricompilazione di indici online - Possibilità di aumento della frammentazione.

Considerazioni sul log delle transazioni

Le operazioni sugli indici su larga scala eseguite offline o online possono generare grandi quantità di log delle transazioni. Ciò è dovuto al fatto che le operazioni di ricompilazione degli indici offline e online vengono registrate completamente. Per garantire la possibilità di eseguire il rollback dell'operazione sugli indici, non è possibile troncare il log delle transazioni fino al completamento dell'operazione. È tuttavia possibile eseguire il backup del log durante l'operazione sugli indici.

È pertanto necessario che il log delle transazioni abbia spazio sufficiente per archiviare sia le transazioni dell'operazione sugli indici sia tutte le transazioni utente simultanee per l'intera durata dell'operazione sugli indici. Per altre informazioni, vedere spazio su disco del log delle transazioni per le operazioni sugli indici.

Le operazioni sugli indici online non causano un aumento elevato del log delle transazioni se è abilitato il ripristino accelerato del database (ADR).

Considerazioni sull'archiviazione delle versioni persistenti

Se ADR è abilitato, la creazione o la ricompilazione di un grande indice online può aumentare notevolmente le dimensioni dell'archivio delle versioni permanenti (PVS) mentre l'operazione sull'indice è in corso. Assicurarsi che il database disponga di spazio libero sufficiente per permettere a PVS di crescere. Per altre informazioni, vedere Monitorare e risolvere i problemi relativi al ripristino accelerato del database.

Considerazioni sugli indici ripristinabili

L'opzione di indice RESUMABLE per CREATE INDEX e ALTER INDEX si applica a SQL Server (ALTER INDEX a partire da SQL Server 2017 (14.x) e CREATE INDEX a partire da SQL Server 2019 (15.x),database SQL di Azure e Istanza gestita di SQL di Azure. Per altre informazioni, vedere CREATE INDEX e ALTER INDEX.

Per usare l'opzione RESUMABLE, è necessario usare anche l'opzione ONLINE. Quando si esegue la creazione o la ricompilazione dell'indice ripristinabile, si applicano le linee guida seguenti:

  • È possibile controllare meglio la gestione, la pianificazione e l'estensione delle finestre di manutenzione degli indici. È possibile sospendere e riavviare un'operazione di creazione o ricompilazione dell'indice più volte in base alle finestre di manutenzione.
  • È possibile eseguire il ripristino da errori di creazione o ricompilazione dell'indice, ad esempio failover del database o esaurimento dello spazio su disco, senza dover riavviare l'operazione sull'indice dall'inizio.
  • Quando un'operazione sull'indice è sospesa, sia l'indice originale sia quello appena creato richiedono spazio su disco e devono essere aggiornati durante le operazioni DML.
  • L'opzione SORT_IN_TEMPDB=ON non è supportata.
  • Gli indici disabilitati non sono supportati.

Suggerimento

Le operazioni sugli indici ripristinabili non richiedono una transazione di grandi dimensioni, consentendo un troncamento frequente del log durante questa operazione ed evitando un aumento elevato del log. I dati necessari per riprendere e completare un'operazione sull'indice vengono archiviati nei file di dati di un database.

In genere, non esiste alcuna differenza di prestazioni tra le operazioni di indice online ripristinabili e non ripristinabili. Per i CREATE INDEXripristinabili, esiste un sovraccarico costante che potrebbe causare operazioni notevolmente più lente per le tabelle più piccole.

Quando viene sospesa un'operazione di indice ripristinabile:

  • Per la maggior parte dei carichi di lavoro di lettura, la riduzione delle prestazioni è irrilevante.
  • Per i carichi di lavoro con un numero elevato di aggiornamenti, è possibile che si verifichi una riduzione della velocità effettiva a seconda delle specifiche del carico di lavoro.

In generale non vi sono differenze di qualità della deframmentazione nella creazione o ricompilazione degli indici online ripristinabili rispetto agli indici non ripristinabili.

Nota

Mentre viene sospesa un'operazione sull'indice online, qualsiasi transazione che richiede un blocco esclusivo a livello di tabella (X) sulla tabella che contiene l'indice sospeso ha esito negativo. Ad esempio, questo problema può verificarsi con le operazioni di INSERT ... WITH (TABLOCK). In questo caso, viene visualizzato l'errore 10637:

Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Per risolvere l'errore 10637, rimuovere l'hint TABLOCK dalla transazione oppure riattivare l'operazione sull'indice e attendere il completamento prima di provare di nuovo la transazione.

Opzioni online predefinite

È possibile impostare operazioni sugli indici online e ripristinabili come opzioni predefinite a livello di database impostando le configurazioni ELEVATE_ONLINE o ELEVATE_RESUMABLE a livello di ambito del database. Con queste opzioni predefinite, è possibile evitare di avviare accidentalmente un'operazione di indice offline che rende inaccessibile una tabella o un indice durante l'esecuzione. Entrambe le opzioni consentono al motore di database di elevare automaticamente determinate operazioni sugli indici all'esecuzione online o ripristinabile.

È possibile impostare un'opzione come FAIL_UNSUPPORTED, WHEN_SUPPORTEDo OFF. È possibile impostare valori diversi per ELEVATE_ONLINE e ELEVATE_RESUMABLE. Per altre informazioni, vedere ALTER DATABASE SCOPED CONFIGURATION.

Sia ELEVATE_ONLINE che ELEVATE_RESUMABLE si applicano solo a istruzioni DDL che supportano rispettivamente la sintassi online e la sintassi resumable (ripristinabile). Ad esempio, se si tenta di creare un indice XML con ELEVATE_ONLINE=FAIL_UNSUPPORTED, l'operazione viene eseguita offline perché gli indici XML non supportano l'opzione ONLINE. Le opzioni influiscono solo sulle istruzioni DDL inviate senza specificare un'opzione ONLINE o RESUMABLE. Ad esempio, inoltrando un'istruzione con ONLINE=OFF o RESUMABLE=OFF, l'utente può eseguire l'override di un'impostazione FAIL_UNSUPPORTED ed eseguire un'istruzione offline e/o in modo non ripristinabile.

Nota

ELEVATE_ONLINE e ELEVATE_RESUMABLE non si applicano alle operazioni sugli indici XML.

Contenuto correlato