Monday 28 May 2007

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.

2 comments:

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03