Linee guida per operazioni di indice online

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2008)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 dell'oggetto LOB: image, ntexte text.

  • È possibile creare indici non cluster non univoci online quando la tabella contiene tipi di dati LOB ma nessuna di queste colonne è utilizzata nella definizione di indice come colonna chiave o non chiave (inclusa).

  • Non è possibile creare, ricompilare o eliminare online indici su tabelle temporanee locali. Questa limitazione non è valida per gli indici su tabelle temporanee globali.

  • Gli indici possono essere ripresi dal punto di interruzione dopo un errore imprevisto, il failover del database o l'esecuzione di un comando PAUSE. Vedere Alter Index. Questa funzionalità è in anteprima pubblica per SQL Server 2017 e il database SQL di Azure.

Nota

Le operazioni sugli indici online sono disponibili solo in alcune edizioni di MicrosoftMicrosoft SQL ServerSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL ServerSQL Server, vedere Funzionalità supportate dalle edizioni.

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 cluster disabilitato o vista indicizzata disabilitata

Indice XML

Indice columnstore

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.

Sono applicabili ulteriori restrizioni nella ricompilazione di indici disabilitati. Per altre informazioni, vedere Disabilitazione di indici e vincoli.
CREATE INDEX Indice XML

Indice cluster univoco iniziale su una vista

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

Indice di una tabella temporanea locale

Indice XML
DROP INDEX Indice disabilitato

Indice XML

Indice non cluster

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 cluster
È consentito l'utilizzo di una sola clausola secondaria alla volta. Ad esempio, non è possibile aggiungere ed eliminare vincoli PRIMARY KEY o UNIQUE nella stessa istruzione ALTER TABLE.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY o UNIQUE) Indice cluster

Non è possibile modificare, troncare o eliminare la tabella sottostante mentre è in corso un'operazione su un 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 utilizzando CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, anche tutti gli indici non cluster associati vengono ricreati online.

Quando si crea o si ricompila un indice UNIQUE online, il generatore dell'indice e una transazione utente simultanea potrebbero tentare di 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, il Motore di database di SQL ServerSQL Server Database Engine selezionerà l'attività dell'utente o dell'applicazione 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 se un indice contiene una colonna di tipo di oggetti di grandi dimensioni e nella stessa transazione sono presenti operazioni di aggiornamento prima di questa operazione online. Per risolvere questo problema, posizionare l'operazione online all'esterno della transazione o prima degli aggiornamenti all'interno della transazione.

Considerazioni sullo spazio su disco

Le operazioni sugli indici online hanno requisiti di spazio su disco maggiori 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.
  • È 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 Disk Space Requirements for Index DDL Operations.

Considerazioni sulle prestazioni

Sebbene le operazioni sugli indici online consentano l'esecuzione di attività simultanee di aggiornamento utente, le operazioni sugli indici impiegheranno più tempo se l'attività di aggiornamento genera un notevole carico. Le operazioni sugli indici online saranno generalmente più lente delle operazioni sugli indici offline equivalenti, indipendentemente dal livello di attività di aggiornamento simultanee.

Dato che durante le operazioni sugli indici online vengono mantenute sia la struttura di origine che quella di destinazione, l'utilizzo di risorse per l'inserimento, l'aggiornamento e l'eliminazione delle transazioni viene aumentato, potenzialmente fino al doppio. 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, in base ai quali l'esecuzione delle operazioni sugli indici offline potrebbe essere la soluzione ottimale. 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 le istruzioni per gli indici, analogamente ad altre query, possono usare più processori per eseguire le operazioni di analisi e ordinamento associate all'istruzione. È possibile utilizzare l'opzione dell'indice MAXDOP per controllare il numero di processori dedicati all'operazione di 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 operazioni parallele sugli indici. Per altre informazioni sulle edizioni di SQL Server che supportano le operazioni indicizzate parallele, vedere Funzionalità supportate dalle edizioni.

Poiché al termine dell'esecuzione dell'operazione sugli indici viene mantenuto un blocco S o Sch-M, è necessario prestare particolare attenzione quando si esegue un'operazione sugli indici online all'interno di una transazione utente esplicita, ad esempio un blocco BEGIN TRANSACTION...COMMIT. Una tale operazione causa il mantenimento del blocco fino alla fine della transazione, impedendo quindi la concorrenza degli utenti.

La ricompilazione degli indici online può aumentare la frammentazione quando è consentita l'esecuzione con le opzioni MAX DOP > 1 e ALLOW_PAGE_LOCKS = OFF . Per altre informazioni, vedere Funzionamento: Ricompilazione di indici online - Possibilità di aumento della frammentazione.

Considerazioni sul log delle transazioni

Operazioni sugli indici su larga scala, eseguite online oppure offline, possono generare volumi di dati elevati i quali possono esaurire rapidamente lo spazio disponibile nel log delle transazioni. 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 per il log delle transazioni per operazioni sugli indici.

Considerazioni sulla ricompilazione degli indici ripristinabili

Nota

Vedere Alter Index. Questa funzionalità è in anteprima pubblica per SQL Server 2017 e il database SQL di Azure.

Per eseguire la ricompilazione di un indice online ripristinabile, attenersi alle seguenti linee guida:

  • Gestione, pianificazione ed estensione delle finestre di manutenzione degli indici. È possibile sospendere e riavviare un'operazione di ricompilazione dell'indice più volte in base alle finestre di manutenzione.
  • Recupero da errori di ricompilazione degli indici, ad esempio failover del database o esaurimento dello spazio su disco.
  • 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.

  • Sono consentiti troncamenti dei log durante le operazioni di ricompilazione dell'indice (non possono invece essere eseguiti per le normali operazioni sull'indice online).

  • L'opzione SORT_IN_TEMPDB=ON non è supportata

Importante

Per la ricompilazione ripristinabile non è necessario tenere aperto un troncamento con esecuzione prolungata ed è possibile eseguire il troncamento del log durante l'operazione ottimizzando la gestione dello spazio del log. Con la nuova progettazione è possibile mantenere i dati necessari in un database insieme a tutti i riferimenti richiesti per riavviare l'operazione ripristinabile.

In generale non vi sono differenze di prestazioni tra la ricompilazione degli indici online ripristinabili e quella degli indici non ripristinabili. Quando si aggiorna un indice ripristinabile mentre un'operazione di ricompilazione di indice è in pausa:

  • Per i carichi di lavoro prevalentemente di lettura, l'impatto sulle prestazioni è trascurabile.
  • Per i carichi di lavoro con intensa attività di aggiornamento, è possibile riscontrare una riduzione delle prestazioni in termini di velocità effettiva (i test indicano una riduzione inferiore al 10%).

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

Funzionamento delle operazioni sugli indici online

Eseguire operazioni online sugli indici

ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)