Here are some important points that every new DBA should be aware of about enabling indexes and online index operations, few of them are extracted from SQL 2005 Books online and reliable resources:
- Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
- 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. Nonclustered indexes defined with LOB data type columns must be created or rebuilt offline.
- Indexes on local temp tables cannot be created, rebuilt, or dropped online. This restriction does not apply to indexes on global temp tables.
- 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. 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.
Be aware that the online index operation can cause a deadlock during its process, when the same time if there are any insert and/or update operation activities. Thus causes a deadlock victim from the process it is generated. To avoid this you could take care of concurrent online index DDL operations with non-clustered indexes or reorganizing those indexes too. Also the online index operation is unique and you cannot perform a create index while rebuilding an existing index online on the same table.
Further you must know about disk space requirements in this indexing operation, both for online and offline index operations. An exception is additional disk space required by the temporary mapping index. This temporary index is used in 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.
When a update activity that is concurrent is persistent then avoid using online index operation at the same time, as it will not create deadlock operation and transction log & tempdb usage will be quite heavy. Few times this will lead to disk free space issues causing a total downtime to the SQL Server services. So be wise to use online index operation as this could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Online index operations are fully logged.
