Online Indexing in SQL Server 2005

Example:

Now let’s find the fragmentation level of the indexes of the table “HumanResources.Employee” in the AdventureWorks Database.

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),Object_ID(‘HumanResources.Employee’),NULL,NULL,NULL

To defragment the indexes, we will rebuild all the indexes.

ALTER INDEX ALL on HumanResources.Employee REBUILD WITH (ONLINE=ON)

Drop Index

DROP INDEX
{ <drop_relational_or_xml_index> [ ,…n ]
}

<drop_relational_or_xml_index> ::=
          index_name ON <object>
      [ WITH ( <drop_clustered_index_option> [ ,…n ] ) ]

<object> ::=
{
      [ database_name. [ schema_name ] . | schema_name. ]
          table_or_view_name
}

<drop_clustered_index_option> ::=
{
     MAXDOP = max_degree_of_parallelism
     | ONLINE = { ON | OFF }
     | MOVE TO { partition_scheme_name ( column_name )
          | filegroup_name
          | “default”
          }
}

Example:

In the below code, the clustered index is dropped online.

DROP INDEX PK_Employee_EmployeeID on [HumanResources.Employee] WITH (ONLINE=ON)

Thus, we have seen how online index operations can be performed by using T-SQL statements.

Disk Space Considerations

The disk space required for performing offline index operations and online index operations varies. Additional disk space is required for sorting operations and storing row versions. A temporary mapping index is additionally created in case of clustered index. This temporary index consumes additional disk space when the user creates, drops, or rebuilds the clustered index. In case of creation of nonclustered index, a temporary mapping index is not created. Sorting operation is performed when a clustered or non-clustered index is created. The sorting operation can be done in TEMPDB database. This can be done by setting the SORT_IN_TEMPDB=ON. This option increases the amount of temporary disk space required, but the time required for creation of new index online can be reduced by having the TEMPDB and the user database on different physical disks.

If the SORT_IN_TEMPDB is set to ON, then TEMPDB must have enough space to accommodate the index creation or rebuild operation.

Performance Considerations

The online index operations will take longer if concurrent update activity is very heavy. Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

The source and the target structures have to be maintained during the online operations. So the resource usage increases for the DML operations. This can cause performance degradation.

Best Practices

1. Backup the transaction log and truncate it before running large-scale index operations online. This will prevent the transaction log from becoming full.
2. It is recommended to have the SORT_IN_TEMPDB option set to ON. This will separate the index operations and the user transactions. Performance can be improved by having the TempDB and user databases in different physical disks. By separating the user transactions and index operations, the DBA can truncate the user database in midst of the index operations, if required.
3. Have the recovery model set to SIMPLE or bulk logged so that minimal logging of index transactions takes place.
4. Do not run the online index operation in an explicit transaction. The log of the user database cannot be truncated until the explicit transaction ends.

Conclusion

Online indexing is a powerful feature in SQL Server 2005, but should be used judiciously. Online Indexing should be considered in 24-7 environments where it is difficult to perform standard index maintenance operations.

]]>