Rebuilding SQL Server indexes using the ONLINE option

Problem

As each year goes by the uptime requirement for our databases gets larger and larger, meaning the downtime that we have to do maintenance on our databases is getting smaller and smaller. Learn how to leave indexes online and accessible while being rebuilt.

Solution

SQL Server Online Index Rebuild Background Information

There are a few things I want to mention regarding online index rebuilds before we get into the details. First, I want to make sure you understand the difference between rebuilding and reorganizing an index. This tip is going to focus on rebuilds only. If you would like to learn more about the differences between the two operations you can read more here. Second, we should note in SQL Server 2005 the online option for index rebuilds is only available in Enterprise edition. With SQL Server 2008 and later it is available in Enterprise, Developer, and Evaluation editions. Finally, this option is not available if:

• the index is an XML index
• the index is a Spatial index
• the index is on a local temp table
• the index is clustered and the table contains a LOB database column(s)
• the index is no clustered and the index itself contains a LOB database column(s)

SQL Server ALTER INDEX Syntax

The syntax for rebuilding indexes is very simple, we just add the “WITH ONLINE=ON” clause to the ALTER INDEX command. Here are a couple of examples. The first rebuilds a single index on a table and the second rebuilds all the indexes on the table. You can read more on rebuilding indexes here.

ALTER INDEX [IX_Test] ON [dbo].[Test] REBUILD WITH (ONLINE = ON);
ALTER INDEX ALL ON [dbo].[Test] REBUILD WITH (ONLINE = ON);

Performance of SQL Server Online vs. Offline Index Rebuilds

To test the performance of rebuilding indexes online I wanted to use a fairly large table. I used a table that had about 20,000,000 records and two indexes. One clustered primary key and one no clustered single column index. Here is the complete table and index definition.

CREATE TABLE [dbo].[Test] (
 [PKID] [int] NOT NULL,
 [IndCol] [int] NOT NULL,
 [Col1] [int] NULL,
 [Col2] [int] NULL,
 [Col3] [datetime] NULL,
 [Col4] [varchar](1000) NULL,
 [Col5] [timestamp] NOT NULL,
 [Col6] [int] NULL,
 [Col7] [varchar](200) NULL,
 [Col8] [int] NULL,
 CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED 
(
 [PKID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
       IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_Test] ON [dbo].[Test] 
(
 [IndCol] ASC
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
      SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
      DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Sample Index Rebuilds

For the performance test I compared the OFFLINE index rebuild versus the ONLINE rebuild for both a clustered and non clustered index rebuild.

ALTER INDEX [PK_Test] ON [dbo].[Test] REBUILD WITH (ONLINE=ON);
ALTER INDEX [IX_Text] ON [dbo].[Test] REBUILD WITH (ONLINE=ON);
ALTER INDEX [PK_Test] ON [dbo].[Test] REBUILD WITH (ONLINE=OFF);
ALTER INDEX [IX_Text] ON [dbo].[Test] REBUILD WITH (ONLINE=OFF);

Results from Index Rebuilds

Each command ran 5 times to get an average value for each metric. Below are the results of the tests:

It’s pretty obvious from these results that the ONLINE index rebuild does not perform even close to as well as when run in OFFLINE mode as it uses considerably more resources to complete the rebuild. During the initial phase of the index rebuild SQL Server creates a second copy of the index generating extra reads and writes. More on this later.

Results With Database Activity

Next, let’s take a look at what happens when there is some activity (inserts/updates/selects) on the table. To simulate activity while the rebuild is running I opened 3 other sessions with each running one statement per second. One ran a simple select, one ran an insert and the last session ran a random update. The update would update about 600 records per statement. Here are the results of that test.

These results are quite interesting. Looking at the non-clustered index rebuild, we see it uses only marginally more resources with concurrent activity on the table. The clustered index however, uses much more resources with activity on the table, taking almost 5 times longer to complete. Due to SQL Server maintaining two copies of the index while processes are accessing the table.

SQL Server Transaction Log Usage

The transaction log requires additional space when running index rebuilds with the ONLINE option ON. I issued a backup right before the rebuild command to see the extra space usage. I then ran each rebuild command and between each rebuild I took another transaction log backup. Below are the results. You can see in both cases, clustered or no clustered indexes, each online operation generates more redo than a normal OFFLINE rebuild.

Extra Disk Space Required During SQL Server Online Index Rebuilds

The data file where the index resides requires more disk space. SQL Server creates a snapshot of the index during the initial phase of the online rebuild which is a second copy of this index. For clustered indexes, a temporary mapping index gets created. This determines the records to modify in the new index when changes occur in the original index during the rebuild phase.

Once the rebuild process completes, the final phase drops this index. A good explanation of these phases can be found here. There is also extra space required in the version store portion of the tempdb. During an online rebuild you can query the sys.dm_db_file_space_usage DMV to see how many pages are currently reserved. In my test cases I found that during the clustered index rebuild the version store required about 1880 pages. This number would be completely dependent on the nature of the activity on your table during the rebuild. I also noticed that for no clustered index rebuilds the version store is only used if you are updating a column that is a part of the index. In cases where I updated a column that was not a part of the index being rebuilt (as in the examples in this tip), the version store was not used at all.

SQL Server Locks Acquired with SQL Server Online Index Rebuilds

This link also shows exactly which types of locks are acquired during the online index rebuild. The locks in the preparation and build phase, IS and S, are there mainly to ensure that another process does not get an exclusive lock on the object while the index is being rebuilt. The last lock acquired during the final phase is a Sch-M lock. This schema modification lock blocks all other concurrent access to the table, but it is only held for a very short period of time while the old index is dropped and the metadata updated.

Summary

We can see from the above tests that ONLINE index rebuilds do require many more resources and take much longer to complete than its old OFFLINE counterpart. For those of us that have available downtime in our systems, it’s probably a better idea to perform these maintenance activities during this window, but for those of us that do not have that luxury, ONLINE index rebuilds are a very handy feature.

Next Steps

• Investigate effects of using SORT_IN_TEMPDB parameter
• How to identify which indexes need to be rebuilt
• Check out these index related tips:
  • Fragmentation and Index Maintenance
  • Indexing