Online Indexing in SQL Server 2005

Online Indexing is a new feature available in SQL Server 2005. In SQL Server 2005, DBAs can create, rebuild, or drop indexes online. The index operations on the underlying table can be performed concurrently with update or query operations. During these offline operations, the indexing operations hold exclusive locks on the underlying table and associated indexes. During online index operations, SQL Server 2005 eliminates the need of exclusive locks
Online Index Operation

The online index operation can be divided into three phases Preparation, Build and Final which are explained in detail

Preparation Phase
During the preparation phase, the following activities take place:

• A snapshot of the source table is acquired. When taking a snapshot of the table, a shared lock is obtained on the table. This is done for transactional level read consistency.
• An Intent share lock is also obtained. This lock is maintained until the final phase.
• A resource lock INDEX_BUILD_INTERNAL_RESOURCE is acquired to prevent concurrent DDL operations
• Metadata is created. The logical metadata consists of Index ID, Index name, keys, and attributes

Build Phase

During the build phase:

• The new index structure created in the preparation phase is populated with the sorted data from existing data source.
• The shared lock acquired in the preparation phase is released. Concurrent DML operations can be performed during the Build phase.
• If the clustered index is created or rebuilt online, a temporary nonclustered index, called the mapping index, is created in the build phase.
  

When a clustered index is created or dropped and non-clustered indexes exist for that table, the preparation and build phases are repeated twice; once for the new clustered index and again for the nonclustered indexes

Final Phase

The Final phase is used to inform the system that the index is ready. These are the following activities that take place during this phase:

• The IS lock is upgraded to a Schema Modify (Sch-M) or Shared(S) lock depending on the type of index operation.
• If a clustered index is created, then all the nonclustered indexes are rebuilt. Due to the locks held on the table, concurrent DML operations are not allowed on the table at this time.
• The Sch-M or S lock is released once the index is in the ready state.
  

Online indexing can be performed through the following T-SQL statement as shown below

CREATE INDEX IX_Patient on PDS.Patient (Identifier) (ONLINE=ON)

Best Practices

The following points should be considered before using the online indexing feature

• Backup the transaction log and truncate it before running large-scale index operations online.
• It is recommended to have the SORT_IN_TEMPDB option set to ON. This will separate the index operations and the user transactions.
• Have the recovery model set to SIMPLE or bulk logged so that minimal logging of index transactions takes place.
• 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.

The Online Indexing feature is available only in the Enterprise Edition of SQL Server 2005.

Performance tuning using Include columns in SQL Server 2005

SQL Server 2005 extends the functionality of non clustered indexes by adding non key columns to the leaf level of the non clustered index using the INCLUDE option in the CREATE INDEX statement. These INCLUDE index option is a slight variation of covering index for improved performance. By including non key columns, you can create non clustered indexes that cover more queries. The benefits of using the INCLUDE option (also called non key non clustered index) in the INDEX are

Advantages

• All data types are supported, except text, ntext, and image. So more data type options than a covering index.
• The maximum number of columns that can be included is 1024, where as only 16 in covering indexes.
• Included Columns are not considered by the Database Engine when calculating the number of index key columns or index key size.The actual index is narrower so the key can be more efficient and can offer better performance where as in covering index all of the columns are part of the key.

The include columns indexes are also having the same disadvantages of the covering indexes such as

• More space is required to store indexes with non key columns. Non key column data is stored at both the leaf level of the index and in the table itself.
• Larger indexes mean fewer rows can fit on a page, potentially increasing disk I/O.
• Index maintenance is increased for data modifications, potentially hurting performance if non key columns are large and the database experiences a high level of data modifications.
  

Syntax for INCLUDE column

CREATE INDEX IX_INDEX1
ON dbo.Employee (KEYCOLUMN1)
INCLUDE (NONKEYCOLUMN1, NONKEYCOLUMN2, AND NONKEYCOLUMN3);

However care should be taken before converting the covering indexes to non key / include column indexes. The execution plan of both the INDEX options should be compared before deciding the best INDEX option.