SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

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

SQL Index, SQL Scripts, SQL Server DBCC
Previous Post
SQLAuthority News – Subscribed to SQLAuthority Emails
Next Post
SQL SERVER – Find Last Day of Any Month – Current Previous Next

Related Posts

4 Comments. Leave new

  • jason robertson
    August 23, 2007 4:00 pm

    good article to the point

    Reply
  • Performed some tests of the ALTER INDEX Rebuild using both the OFFLINE and ONLINE methods. I restored the database between each test. I captured the stats from the sys.dm_db_index_physical_stats dmv view before and after. When using the online methed the number of datapages went up in some cases. This was not the same for the offline. Do you have any thoughts?

    Reply
  • Was excited about ALTER INDEX ALL ON table WITH (ONLINE = ON). However, the SUSPENDED status on the online rebuild does not leave me with a good feeling.

    subject table has 103151154 rows, no columns defined as a large object, a 2 column non-unique non-clustered index, a 1 column primary clustered key index. Row size is less than 100 bytes.

    recovery mode is “full” on the subject database.

    running SQL 2005 Enterprise 64bit on a 64bit Windows 2003 server with 16gb of memory, 24gb of virtual memory.

    Was running a script to rebuild all indexes in all tables. Eleven smaller tables had processed successfully. I left for for the evening and came back this morning to find the SUSPENDED status.

    Looked at the SQL lock and found an “unresolved deadlock” …on a test server, and I am the only one logged in.

    Any insight you have would be greatly appreciated.

    Reply
  • Is there alternative manual method to add an index to a table without locking on SQL Server 2005 Standard?

    Reply

Leave a Reply

Menu
Exit mobile version