Rebuild indexes online with SQL Server 2005

Rebuild indexes online with SQL Server 2005

http://blogs.techrepublic.com.com/datacenter/?p=249

Online index rebuild

SQL Server 2005 introduces the ability to rebuild your indexes in an online fashion so that other processes are able to access the table while the rebuild is occurring. Because you can access the indexes during the rebuild, you are not limited to only rebuilding indexes during off-peak hours.

To accomplish this, the database engine takes some special actions to rebuild the index and to allow access to the index at the same time. The original index will remain available to users for reading data and data modification. Row versioning is used to allow for transactional consistency. During the rebuild, a new index is created that mimics the old index. Any data modifications that alter the original index will also be applied to this index by SQL Server during the rebuild. This new index is not read from at all — it is write-only. It is essential that you have enough available disk space to accommodate the data for the two concurrent indexes during the online rebuild. While the rebuild is taking place, SQL Server uses a mapping index to determine records to modify in the new index when modifications occur in the original index. Once the rebuild process has finished, any queries or data modifications occur to the new index, and the original index is dropped.

Example

The process to rebuild an index online is not much different than the typical rebuild process; however, there are a few ways to accomplish the rebuild. One way is to simply drop the index using a DROP INDEX statement followed by a CREATE INDEX statement. Rebuilding indexes in this fashion leaves the table without an index until the index is completely created. For this reason (and a number of other reasons), dropping the index and recreating it is not recommended.

The CREATE INDEX statement can still be used to rebuild an index if the DROP_EXISTING option is used. This feature allows the definition of the specified index to change, and it allows the DBA to change the location of the index to another filegroup or partition.

The ALTER INDEX statement allows for the rebuilding of the clustered and all nonclustered indexes on the table. The drawback with this statement is that you cannot change the index definition. Both of these statements have options that will build the index online.

The following statement will rebuild the clustered index (which is on the SaleID column) on the SalesHistory table. The existing index will be dropped in the process, but it will be available during the operation because the ONLINE option is specified.

CREATE CLUSTERED INDEX cl_SalesHistory_SaleID ON SalesHistory(SaleID)

 WITH(DROP_EXISTING = ON, ONLINE = ON)

This statement is very similar to the one used above, but it changes the actual index definition to include an additional column. The index is still rebuilt in the same fashion.

CREATE CLUSTERED INDEX cl_SalesHistory_SaleID ON SalesHistory(SaleID ASC, SaleDate ASC)

 WITH(DROP_EXISTING = ON, ONLINE = ON)

Using the ALTER INDEX statement, I can rebuild all indexes on a specified table. The ONLINE syntax remains the same as the CREATE INDEX statement. This new syntax replaces the DBCC DBREINDEX statement used in previous versions of SQL Server.

ALTER INDEX ALL ON SalesHistory

 REBUILD WITH(ONLINE = ON)

This statement rebuilds the clustered index on the SalesHistory table. The ONLINE option is omitted, which means that the table will not be accessible during the rebuild operation.

ALTER INDEX cl_SalesHistory_SaleID ON SalesHistory

 REBUILD

This statement is the same statement as above, but the rebuild will be performed online, so operations can continue to be performed on the table.

ALTER INDEX cl_SalesHistory_SaleID ON SalesHistory

 REBUILD WITH(ONLINE = ON)