Systems Engineering and RDBMS

Archive for October 10th, 2007

Rebuilding Indexes Online in SQL Server 2005

Posted by decipherinfosys on October 10, 2007

In one of our previous blog post, we covered how to enable or disable an index. As mentioned in the post, one way to enable the index is by rebuilding it. In this blog post, we will discuss how to rebuild the index online. Apart from rebuilding index, one can create and drop indexes also online.

By specifying the online option, concurrent users can access the underlying table / clustered index data including data from non-clustered indexes while index operation (Create/ Rebuild or Drop) is going on. When indexes are rebuilt off line, let’s say (we are rebuilding clustered index), it holds an exclusive lock on the table data and associated indexes, This prevents other users from accessing such data during the index operation.

Online option becomes very useful in 24 x 7 environment, where there is no downtime for performing index maintenance.

Let us create table and populate it with data.

CREATE TABLE dbo.TEST(ID INT NOT NULL PRIMARY KEY, TEST_DESC VARCHAR(50))
–ON FILEGROUP
GO
CREATE INDEX TEST_IND_1 ON dbo.TEST(ID)
–ON FILEGROUP
GO
CREATE INDEX TEST_IND_2 ON dbo.TEST(TEST_DESC)
–ON FILEGROUP
GO

DECLARE @I INT
SET @I = 0
WHILE @I <= 1000000
BEGIN
INSERT INTO dbo.TEST(ID, TEST_DESC)
SELECT @I, ‘Hello Team ‘ + CAST(@I AS VARCHAR(10))
SET @I = @I + 1
END
GO

We are creating 1 million records using above T-SQL block. Now let us rebuild the indexes using ONLINE option. Run following SQL from database you are connected to. Change the DECIPHER to appropriate database name.

USE DECIPHER
GO
ALTER INDEX ALL ON dbo.TEST REBUILD WITH (ONLINE = ON);
GO

Now from another session fire the update statement to update the TEST_DESC column.

– Go to another session and fire following update.

USE DECIPHER
GO

UPDATE dbo.TEST
SET TEST_DESC = ‘Hello World !!!’
WHERE ID <= 10000
GO

The above statement will be executed successfully while index operation is still going on because of the different locking behavior. When ONLINE option is defined during index creation, only intent share (IS) lock is taken on the source table which allows DML operations on underlying table and index data. When clustered index are created or rebuilt in offline mode, schema modification (SCH-M) lock is taken on the table and for non-clustered index creation shared (S) lock is taken on the table. So for the duration of index operation user updates are prohibited.

It also has some exceptions. One cannot perform ONLINE operation, for following scenarios. Exception list has been taken from BOL article.

1. XML index.
2. Index on a local temp table.
3. Initial unique clustered index on a view.
4. Disabled clustered indexes.
5. Clustered index if the underlying table contains LOB data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml.
6. Nonclustered index defined with LOB data type columns.

One thing to keep in mind is, rebuilding indexes ONLINE is available only in Enterprise Edition.

Posted in SQL Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 83 other followers