Go Guidelines for Online Index Operations

Online functionality

The underlying table cannot be modified, truncated, or dropped and an online index operation are in process.

The online option setting (on or OFF) specified if you create or drop a clustered index was applied to any nonclustered I Ndexes that must is rebuilt. For example, if the clustered index was built online by using CREATE index with drop_existing, Online=on, all associated No nclustered indexes is re-created online also.

When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction could try to insert th E Same key, therefore violating uniqueness. If a row entered by a user is inserted to the new index (target) before the original row from the source table is moved To the new index, the online index operation would fail.

Although not common, the online index operation can cause a deadlock if it interacts with database updates because of us ER or application activities. In these rare cases, the SQL Server Database Engine would select the user or application activity as a deadlock victim.

You can perform concurrent online index DDL operations on the same table or view only if you are creating multiple new n onclustered indexes, or reorganizing nonclustered indexes. All other online index operations performed at the same time fail. For example, you cannot create a new index online while rebuilding an existing index online on the same table.

An online operation cannot is performed when an index contains a column of the large object type, and in the same Transact Ion there is update operations before this online operation. To work around this issue, place the online operation outside the transaction or place it before any updates in the Transa Ction.

Disk Space considerations

Generally, disk space requirements is the same for online and offline index operations. An exception are additional disk space required by the temporary mapping index. This temporary index is used on online index operations that create, rebuild, or drop a clustered index. Dropping a clustered index online requires as much space as creating a clustered index online. For more information, see Disk Space Requirements for Index DDL Operations.

Performance Considerations

Although online index operations permit concurrent User update activity, the index operations would take longer if the UPDA Te activity is very heavy. Typically, online index operations'll be slower than equivalent offline index operations regardless of the concurrent up Date activity level.

Because both the source and target structures is maintained during the online index operation, the resource usage for INS ERT, update, and delete transactions are increased, potentially up to double. This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Online index operations is fully logged.

Although we recommend online operations, you should evaluate your environment and specific requirements. It is optimal to run index operations offline. In doing this, users has restricted access to the data during the operation, but the operation finishes faster and uses F Ewer Resources.

On multiprocessor computers is running SQL Server, index statements may use more processors to perform the S Can and sort operations associated with the index statement just as other queries do. You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. The This is the balance the resources that is used by index operation with those of the concurrent users. For more information, see Configure Parallel Index Operations. For more information about the editions of SQL Server, Parallel indexed operations, see Features supported by The Editions of SQL Server (http://go.microsoft.com/fwlink/?linkid=232473).

Because an s-lock or SCH-M lock are 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. Doing This causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

Online index rebuilding may increase fragmentation when it's allowed to run with MAX DOP > 1 and Allow_page_lo CKS = OFF options. For more information, see how It works:online Index rebuild-can cause increased fragmentation.

Transaction Log considerations Large-scale index operations, performed offline or online, can generate Large data loads that can cause the transaction lo g to quickly fill. To make sure that the index operation can is rolled back and the transaction log cannot be truncated until the index Operatio n has been completed; However, the log can backed up during the index operation. Therefore, the transaction log must has sufficient space to store both the index operation transactions and any Concurren T user transactions for the duration of the index operation. For more information, see Transaction Log Disk Space for Index Operations.

Reference Documentation:

https://msdn.microsoft.com/en-us/library/ms190981 (v=sql.110). aspx

Go Guidelines for Online Index Operations