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))
CREATE INDEX TEST_IND_1 ON dbo.TEST(ID)
CREATE INDEX TEST_IND_2 ON dbo.TEST(TEST_DESC)
DECLARE @I INT
SET @I = 0
WHILE @I <= 1000000
INSERT INTO dbo.TEST(ID, TEST_DESC)
SELECT @I, ‘Hello Team ‘ + CAST(@I AS VARCHAR(10))
SET @I = @I + 1
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.
ALTER INDEX ALL ON dbo.TEST REBUILD WITH (ONLINE = ON);
Now from another session fire the update statement to update the TEST_DESC column.
— Go to another session and fire following update.
SET TEST_DESC = ‘Hello World !!!’
WHERE ID <= 10000
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.