SQL Server 2005 Enterprise Edition supports online index operations. Index operations are creating, rebuilding and dropping indexes.
The question which I receive quite often – what is online operation? Is online operation is related to web, internet or local network?
Online operation means when online operations are happening the database are in normal operational condition, the processes which are participating in online operations does not require exclusive access to database. In case of Online Indexing Operations, when Index operations (create, rebuild, dropping) are occuring they do not require exclusive access to database, they do not lock any database tables. This is major important upgrade in SQL Server from previous versions.
Previous versions of SQL Server required exclusive access to database, and database table were not accessible by other normal processes. This is the reason of time-outs and lock-outs when index operations are performed on SQL Server. I have seen many times major performance issues with larger databases, as due to business requirement and rules it has to be online all the time and index operations were not performed on them due to this concurrent accesses (normal processes vs prioritized index operations) conflict issues. This requirement of highly-availability application is very well addressed in SQL Server 2005 by adding new feature of Online Index Operations.
Syntax of Online Index is very simple. When the index on database is created, ONLINE = ON clause is added to enable online operations on index. ONLINE = ON must be specified to have this feature enabled. The default value of ONLINE option is OFF. The example is taken from BOL.
USE AdventureWorks; GO ALTER INDEX ALL ON Production.Product REBUILD WITH (ONLINE = ON);
Online index operations occurs in three steps.
1) Preparation – Snapshot of original index structure, known as source index, is taken and copied at other place. (concurrent activity suspended for the moment)
2) Build – New operations are executed on this copy, which generates new required index known as target index.
3) Final – Original source index is replaced by new target index. (concurrent activity suspended for the moment)
ALTER INDEX REORGANIZE statement is always performed online, so it is highly recommended to use this new feature over DBCC INDEXDEFRAG of previous version. (Read Complete Article : SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005) DBCC INDEXDEFRAG may be deprecated in future versions of SQL Server so not recommended to use it. Creating partition index online is possible but altering partition index is not possible online.
Additional Notes:
If any of the column in Included Index (Read Complete Article : SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index) are VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX), they must be created with ONLINE = OFF options.
XML datatypes index can not be build online.
Initial clustered index on view and disabled clustered index can not be build online.
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL – Alter Index, BOL – Create Index
