Linee guida per operazioni di indice online
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 quando la tabella sottostante contiene i tipi di dati LOB (Large Object) seguenti:
image, ntext etext.Non è possibile creare, ricompilare o eliminare online indici su tabelle temporanee locali. Questa limitazione non è valida per gli indici su tabelle temporanee globali.
Nota
Le operazioni di indice online non sono disponibili in ogni edizione di MicrosoftSQL Server. Per un elenco di funzionalità supportate dalle edizioni di SQL Server, vedere Funzionalità supportate dalle edizioni di SQL Server 2014.
Nella tabella seguente sono riportate le operazioni sugli indici che è possibile eseguire online e gli indici che sono esclusi da queste operazioni online. Sono inoltre incluse ulteriori limitazioni.
| Operazione su indice online | Indici esclusi | Altre restrizioni |
|---|---|---|
| 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. |
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 SQL Server selezionerà l'attività 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
I requisiti dello spazio su disco sono generalmente gli stessi per operazioni sugli indici online e offline. Un'eccezione è costituita dallo spazio su disco aggiuntivo richiesto dall'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 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 2014, le istruzioni di indice possono usare più processori per eseguire le operazioni di analisi e ordinamento associate all'istruzione di indice esattamente come le altre query. È 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 Configurazione di operazioni parallele sugli indici. Per altre informazioni sulle edizioni di SQL Server che supportano operazioni indicizzate parallele, vedere Funzionalità supportate dalle edizioni di SQL Server 2014.
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 del log delle transazioni per le operazioni sull'indice.
Contenuto correlato
Funzionamento delle operazioni sugli indici online