SQL SERVER – 2008 – Introduction to Online Indexing Operation

When index is created or recreated it usually decreases performance of database. Either SQL takes long time for response or it does not response at all as transactions are blocked. When new table or database goes live it is not possible to find out exactly how many indexes are needed. After running queries on near to production data it is possible to find out which index can perform better. It is important in highly sensitive application to have data always available.

SQL Server 2005 and later versions have provided feature called “Online Indexing”. Everytime index is updated it puts lock on table where index operations are happening. Depending on the situation SQL Server puts schema lock or shared lock while index is modified. If feature of “Online Indexing” is used SQL Server will behave normally when Index is modified.

One shortcoming of “Online Indexing” is when this feature is on, it takes little more time to complete index modification due to it lets database its normal operation continue. If there is no need of database or table offline indexing feature should be used as it is faster to complete whole process.

Let us see example of how index is specified with ONLINE option along with it.

CREATE INDEX idx_Table
ON MyTable (MyColumn)
WITH (ONLINE = ON)

The default value for the ONLINE option is OFF. This feature is not available with datatypes like TEXT, NTEXT, IMAGE or binnary.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – 2008 – Introduction to Online Indexing Operation

  1. I know in SQL 2005 you must have Enterprise Edition to do online indexing. Do you know if they have made this available for all versions of SQL 2008, or do you still have to get the Enterprise Edition?

    We are going to have multiple database servers, but would like some of the servers to use SQL 2008 Web Edition for some of our web sites, but the indexes must be run online.

    Like

  2. Good Day,
    I deployed online indexing, but alas, this does not solve my problem at all . Batchruns are still locked at night , systemjs are running slower than normal.

    Like

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #042 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s