Rebuilding SQL Server indexes using the ONLINE option

By: Ben Snaidero | Read Comments (6) | Print Ben has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning. Related Tips: More

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. This tip will look at a feature that was introduced in SQL Server 2005 that allows us to leave our indexes online and accessible while they are 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 that in SQL Server 2005 the online option for index rebuilds is only available in Enterprise edition while in SQL Server 2008 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

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 so the rebuild would take at least a minute or two. I used a table that had about 20,000,000 records in it 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

For this performance test I first compared the traditional OFFLINE index rebuild with using the ONLINE parameter for both a clustered and a no clustered index rebuild. This initial baseline test used the following commands and were run without any other concurrent activity on the table.

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)

Note that each of the commands was run 5 times and an average taken of 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. The extra reads and writes can be attributed to the second copy of the index that SQL Server creates during the initial phase of the index rebuild. More on this later.

Next, let's take a look at what happens when there is some activity (inserts/updates/selects) on the table while the index is being rebuilt. To simulate activity on the table 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 which would update about 600 records per statement. Here are the results of that test.

These results are quite interesting. Looking first at the no clustered index rebuild, we see that 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. This is probably due to the fact that it is basically maintaining two copies of the index (and data since it's clustered) while the rebuild is running and other processes are accessing the table.

SQL Server Transaction Log Usage

Another factor to take into consideration when running index rebuilds with the ONLINE option ON is the additional space required in the transaction log. To see just how much extra space is used I took a backup right before my rebuild command to clear out the log. 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

SQL Server Locks Acquired with SQL Server Online Index Rebuilds

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

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code