Linee guida per l'esecuzione di operazioni sugli indici in linea
Quando si eseguono operazioni sugli indici in linea sono da ritenersi valide le linee guida seguenti:
È necessario creare, ricostruire o eliminare indici cluster non in linea quando la tabella sottostante contiene tipi di dati LOB (Large Object): image, ntext, text, varchar(max), nvarchar(max), varbinary(max) e xml.
È possibile creare indici non cluster non univoci in linea 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). Gli indici non cluster definiti con colonne con tipo di dati LOB devono essere creati o ricostruiti non in linea.
Non è possibile creare, ricostruire o eliminare in linea indici su tabelle temporanee locali. Questa limitazione non è valida per gli indici su tabelle temporanee globali.
[!NOTA]
Le operazioni sugli indici in linea sono disponibili solo nelle edizioni Enterprise, Developer ed Evaluation di SQL Server.
Nella tabella seguente sono riportate le operazioni sugli indici che è possibile eseguire in linea e gli indici che sono esclusi da queste operazioni in linea. Sono inoltre incluse ulteriori limitazioni.
Operazione su indice in linea |
Indici esclusi |
Altre limitazioni |
|---|---|---|
ALTER INDEX REBUILD |
Indice cluster 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. Sono applicabili ulteriori restrizioni nella ricostruzione di indici disabilitati. Per ulteriori informazioni, vedere Linee guida per la disabilitazione di indici. |
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 in linea.
L'impostazione di un'opzione in linea (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 ricostruiti. Ad esempio, se l'indice cluster viene costruito in linea utilizzando CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, anche tutti gli indici non cluster associati vengono ricreati in linea.
Quando si crea o si ricostruisce un indice UNIQUE in linea, 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 in linea avrà esito negativo.
Un'operazione su un indice in linea 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 Server selezionerà l'attività dell'utente o dell'applicazione come vittima del deadlock.
È possibile eseguire operazioni DDL sugli indici in linea 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 in linea eseguita nello stesso istante avrà esito negativo. Ad esempio, non è possibile creare un nuovo indice in linea durante la ricostruzione di un indice in linea esistente sulla stessa tabella.
Considerazioni sullo spazio su disco
I requisiti dello spazio su disco sono generalmente gli stessi per operazioni sugli indici in linea e non in linea. Un'eccezione è costituita dallo spazio su disco aggiuntivo richiesto dall'indice di mapping temporaneo. Questo indice temporaneo è utilizzato nelle operazioni sugli indici in linea che creano, ricostruiscono o eliminano un indice cluster. L'eliminazione di un indice cluster in linea richiede lo stesso spazio che è necessario per la creazione di un indice cluster in linea. Per ulteriori informazioni, vedere Requisiti di spazio su disco per operazioni DLL sugli indici.
Considerazioni sulle prestazioni
Sebbene le operazioni sugli indici in linea 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 in linea saranno generalmente più lente delle operazioni sugli indici non in linea equivalenti, indipendentemente dal livello di attività di aggiornamento simultanee.
Dato che durante le operazioni sugli indici in linea 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 in linea vengono registrate completamente.
Sebbene le operazioni in linea siano consigliabili, è opportuno valutare l'ambiente e i requisiti specifici, in base ai quali l'esecuzione delle operazioni sugli indici non in linea 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 Enterprise Edition le istruzioni per gli indici, analogamente ad altre query, possono utilizzare più processori per eseguire le operazioni di scansione e ordinamento associate all'istruzione. È possibile utilizzare l'opzione dell'indice MAXDOP per controllare il numero di processori dedicati all'operazione di indice in linea. In questo modo è possibile bilanciare le risorse utilizzate dall'operazione sugli indici con quelle occupate dagli utenti simultanei. Per ulteriori informazioni, vedere Configurazione di operazioni a indici paralleli.
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 in linea 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.
Considerazioni sul log delle transazioni
Operazioni sugli indici su larga scala, eseguite in linea o non in linea, 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 ulteriori informazioni, vedere Spazio su disco per il log delle transazioni per operazioni sugli indici.
Vedere anche