Linee guida per operazioni di indice onlineGuidelines for Online Index Operations

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:When you perform online index operations, the following guidelines apply:

  • 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.Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and 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).Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

  • Non è possibile creare, ricompilare o eliminare online indici su tabelle temporanee locali.Indexes on local temp tables cannot be created, rebuilt, or dropped online. Questa limitazione non è valida per gli indici su tabelle temporanee globali.This restriction does not apply to indexes on global temp tables.

  • Gli indici possono essere ripresi dal punto di interruzione dopo un errore imprevisto, il failover del database, o un pausa comando.Indexes can be resumed from where it stopped after an unexpected failure, database failover, or a PAUSE command. Vedere Alter Index.See Alter Index. Questa funzionalità è in anteprima pubblica per SQL Server 2017 e il database SQL di Azure.This feature is in public preview for SQL Server 2017 and Azure SQL Database.
Nota

Le operazioni sugli indici online sono disponibili solo in alcune edizioni di MicrosoftMicrosoft SQL ServerSQL Server.Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Per un elenco delle funzionalità supportate dalle edizioni di SQL ServerSQL Server, vedere Funzionalità supportate dalle edizioni.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features supported by editions.

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.The following table shows the index operations that can be performed online, the indexes that are excluded from these online operations, and resumable index restrictions. Sono inoltre incluse ulteriori limitazioni.Additional restrictions are also included.

Operazione su indice onlineOnline index operation Indici esclusiExcluded indexes Altre limitazioniOther restrictions
ALTER INDEX REBUILDALTER INDEX REBUILD Indice cluster disabilitato o vista indicizzata disabilitataDisabled clustered index or disabled indexed view

Indice XMLXML index

Indice columnstoreColumnstore index

Indice di una tabella temporanea localeIndex on a local temp table
L'operazione può avere esito negativo se la tabella contiene un indice escluso e si utilizza la parola chiave ALL.Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.

Sono applicabili ulteriori restrizioni nella ricompilazione di indici disabilitati.Additional restrictions on rebuilding disabled indexes apply. Per altre informazioni, vedere Disabilitazione di indici e vincoli.For more information, see Disable Indexes and Constraints.
CREATE INDEXCREATE INDEX Indice XMLXML index

Indice cluster univoco iniziale su una vistaInitial unique clustered index on a view

Indice di una tabella temporanea localeIndex on a local temp table
CREATE INDEX WITH DROP_EXISTINGCREATE INDEX WITH DROP_EXISTING Indice cluster disabilitato o vista indicizzata disabilitataDisabled clustered index or disabled indexed view

Indice di una tabella temporanea localeIndex on a local temp table

Indice XMLXML index
DROP INDEXDROP INDEX Indice disabilitatoDisabled index

Indice XMLXML index

Indice non clusterNonclustered index

Indice di una tabella temporanea localeIndex on a local temp table
Non è possibile specificare più indici in una singola istruzione.Multiple indexes cannot be specified within a single statement.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY o UNIQUE)ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE) Indice di una tabella temporanea localeIndex on a local temp table

Indice clusterClustered index
È consentito l'utilizzo di una sola clausola secondaria alla volta.Only one subclause is allowed at a time. Ad esempio, non è possibile aggiungere ed eliminare vincoli PRIMARY KEY o UNIQUE nella stessa istruzione ALTER TABLE.For example, you cannot add and drop PRIMARY KEY or UNIQUE constraints in the same ALTER TABLE statement.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY o UNIQUE)ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) Indice clusterClustered index

Non è possibile modificare, troncare o eliminare la tabella sottostante mentre è in corso un'operazione su un indice online.The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

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.The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. 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.For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

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à.When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction may try to insert the same key, therefore violating uniqueness. 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.If a row entered by a user is inserted into the new index (target) before the original row from the source table is moved to the new index, the online index operation will fail.

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.Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. 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.In these rare cases, the Motore di database di SQL ServerSQL Server Database Engine will select the user or application activity as a deadlock victim.

È 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.You can perform concurrent online index DDL operations on the same table or view only when you are creating multiple new nonclustered indexes, or reorganizing nonclustered indexes. Qualsiasi altra operazione sugli indici online eseguita nello stesso istante avrà esito negativo.All other online index operations performed at the same time fail. Ad esempio, non è possibile creare un nuovo indice online durante la ricompilazione di un indice online esistente sulla stessa tabella.For example, you cannot create a new index online while rebuilding an existing index online on the same table.

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.An online operation cannot be performed when an index contains a column of the large object type, and in the same transaction there are update operations before this online operation. Per risolvere questo problema, posizionare l'operazione online all'esterno della transazione o prima degli aggiornamenti all'interno della transazione.To work around this issue, place the online operation outside the transaction or place it before any updates in the transaction.

Considerazioni sullo spazio su discoDisk Space Considerations

Le operazioni sugli indici online hanno requisiti di spazio su disco maggiori rispetto alle operazioni sugli indici offline.Online index operations require more disk space requirements than offline index operations.

  • Durante le operazioni di creazione dell'indice e di ricompilazione dell'indice, è necessario spazio aggiuntivo per l'indice in corso di ricompilazione o ricompilato.During index creation and index rebuild operations, additional space is required for the index being built (or rebuilt).
  • È richiesto anche spazio su disco aggiuntivo per l'indice di mapping temporaneo.In addition, disk space is required for the temporary mapping index. Questo indice temporaneo è utilizzato nelle operazioni sugli indici online che creano, ricompilano o eliminano un indice cluster.This temporary index is used in online index operations that create, rebuild, or drop a clustered index.
    • L'eliminazione di un indice cluster online richiede lo stesso spazio che è necessario per la creazione o la ricompilazione di un indice cluster online.Dropping a clustered index online requires as much space as creating (or rebuilding) a clustered index online.

Per altre informazioni, vedere Disk Space Requirements for Index DDL Operations.For more information, see Disk Space Requirements for Index DDL Operations.

Considerazioni sulle prestazioniPerformance Considerations

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.Although online index operations permit concurrent user update activity, the index operations will take longer if the update activity is very heavy. Le operazioni sugli indici online saranno generalmente più lente delle operazioni sugli indici offline equivalenti, indipendentemente dal livello di attività di aggiornamento simultanee.Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

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.Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. Ciò può causare una riduzione delle prestazioni e un maggior utilizzo di risorse durante l'operazione sugli indici, specialmente del tempo CPU.This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Le operazioni sugli indici online vengono registrate completamente.Online index operations are fully logged.

Sebbene le operazioni online siano consigliabili, è opportuno valutare l'ambiente e i requisiti specifici,Although we recommend online operations, you should evaluate your environment and specific requirements. in base ai quali l'esecuzione delle operazioni sugli indici offline potrebbe essere la soluzione ottimale.It may be optimal to run index operations 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.In doing this, users have restricted access to the data during the operation, but the operation finishes faster and uses fewer resources.

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.On multiprocessor computers that are running SQL Server 2016, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. È possibile utilizzare l'opzione dell'indice MAXDOP per controllare il numero di processori dedicati all'operazione di indice online.You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. In questo modo è possibile bilanciare le risorse utilizzate dall'operazione sugli indici con quelle occupate dagli utenti simultanei.In this way, you can balance the resources that are used by index operation with those of the concurrent users. Per altre informazioni, vedere Configurare operazioni parallele sugli indici.For more information, see Configure Parallel Index Operations. Per altre informazioni sulle edizioni di SQL Server che supportano le operazioni indicizzate parallele, vedere Funzionalità supportate dalle edizioni.For more information about the editions of SQL Server that support Parallel indexed operations, see Features Supported by editions.

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.Because an S-lock or Sch-M lock is held in the final phase of the index operation, be careful when you run an online index operation inside an explicit user transaction, such as BEGIN TRANSACTION...COMMIT block. Una tale operazione causa il mantenimento del blocco fino alla fine della transazione, impedendo quindi la concorrenza degli utenti.Doing this causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

La ricompilazione degli indici online può aumentare la frammentazione quando è consentita l'esecuzione con le opzioni MAX DOP > 1 e ALLOW_PAGE_LOCKS = OFF .Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. Per altre informazioni, vedere Funzionamento: Ricompilazione di indici online - Possibilità di aumento della frammentazione.For more information, see How It Works: Online Index Rebuild - Can Cause Increased Fragmentation.

Considerazioni sul log delle transazioniTransaction Log Considerations

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.Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. 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.To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. È 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.Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. Per altre informazioni, vedere Spazio su disco per il log delle transazioni per operazioni sugli indici.For more information, see Transaction Log Disk Space for Index Operations.

Considerazioni sulla ricompilazione di indice può essere ripristinatoResumable Index Rebuild Considerations

Nota

Vedere Alter Index.See Alter Index. Questa funzionalità è in anteprima pubblica per SQL Server 2017 e il database SQL di Azure.This feature is in public preview for SQL Server 2017 and Azure SQL Database.

Quando si esegue una ricompilazione dell'indice online può essere ripristinato si applicano le linee guida seguenti:When you perform resumable online index rebuild the following guidelines apply:

  • La gestione, la pianificazione e l'estensione dell'indice finestre di manutenzione.Managing, planning and extending of index maintenance windows. È possibile sospendere e riavviare un'operazione di ricompilazione dell'indice più volte per adattare le finestre di manutenzione.You can pause and restart an index rebuild operation multiple times to fit your maintenance windows.
  • Recupero da errori di ricompilazione indice (ad esempio failover del database o di esaurire lo spazio su disco).Recovering from index rebuild failures (such as database failovers or running out of disk space).
  • Quando un'operazione di indice è sospesa, sia l'indice originale e quello appena creato richiede spazio su disco e devono essere aggiornati durante le operazioni DML.When an index operation is paused, both the original index and the the newly created one require disk space and need to be updated during DML operations.

  • Abilitare il troncamento del log di troncamento durante un'operazione di ricompilazione indice (Impossibile eseguire questa operazione per un'operazione di indice online normale).Enables truncation of truncation logs during an index rebuild operation (this operation cannot be performed for a regular online index operation).

  • L'opzione SORT_IN_TEMPDB = ON opzione non è supportataSORT_IN_TEMPDB=ON option is not supported
Importante

La ricompilazione può essere ripristinata non occorre tenere aperto un troncamento con esecuzione prolungata, consentendo il troncamento del log durante l'operazione e una migliore gestione dello spazio di log.Resumable rebuild does not require you to keep open a long running truncation, allowing log truncation during this operation and a better log space management. Con la nuova progettazione, siamo riusciti a mantenere i dati necessari in un database insieme a tutti i riferimenti necessari per riavviare l'operazione può essere ripristinato.With the new design, we managed to keep necessary data in a database together with all references required to restart the resumable operation.

In genere, non vi è alcuna differenza nelle prestazioni tra la ricostruzione dell'indice online può essere ripristinato e non ripristinabili.Generally, there is no performance difference between resumable and non-resumable online index rebuild. Quando si aggiorna un indice può essere ripristinato durante l'operazione di ricompilazione di un indice viene sospesa:When you update a resumable index while an index rebuild operation is paused:

  • Prevalentemente di sola lettura carichi di lavoro, l'impatto sulle prestazioni è trascurabile.For read-mostly workloads, the performance impact is insignificant.
  • Per carichi di lavoro con intensa attività di aggiornamento, è possibile che si verifichi un peggioramento di velocità effettiva (la riduzione di Mostra minore di 10% test).For update-heavy workloads, you may experience some throughput degradation (our testing shows less than 10% degradation).

In genere, è indifferente tra ricostruzione dell'indice online può essere ripristinato e non ripristinabili in qualità di deframmentazione in linea.Generally, there is no difference in defragmentation quality between resumable and non-resumable online index rebuild.

Funzionamento delle operazioni sugli indici onlineHow Online Index Operations Work

Eseguire operazioni online sugli indiciPerform Index Operations Online

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)