Rebuilding SQL Server indexes using the ONLINE option


By:   |   Updated: 2012-01-31   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | More > Fragmentation and Index Maintenance

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

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 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:

OFFLINE ONLINE
CPU (ms) READS WRITES DURATION (ms) CPU (ms) READS WRITES DURATION (ms)
CLUSTERED 17828 841991 225377 21825 44063 3704666 508581 93890
NONCLUSTERED 20078 308781 25868 24160 19610 660834 65703 32045

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.

WITH ACTIVITY NO ACTIVITY
CPU (ms) READS WRITES DURATION (ms) CPU (ms) READS WRITES DURATION (ms)
CLUSTERED 85938 59243675 1583512 527312 44063 3704666 508581 93890
NONCLUSTERED 19735 665451 65738 34053 19610 660834 65703 32045

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.

LOG SPACE USED (MB)
OFFLINE ONLINE
CLUSTERED 1801 2131
NONCLUSTERED 215 262

Extra Disk Space Required During SQL Server Online Index Rebuilds

The first place where more disk space is required is the data file in which the index resides. During the initial phase of the online rebuild enough space for a second copy of this index is required since SQL Server creates a snapshot of the index. Also, for clustered indexes, a temporary mapping index is created which determines the records to modify in the new index when changes occur in the original index during the rebuild phase. Once the rebuild process is complete this index is dropped during the final phase. 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


Last Updated: 2012-01-31


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, November 30, 2016 - 1:38:24 PM - jdw3rd Back To Top

(ONLINE=ON) is only available in 2008 R2 Enterprise not Standard.  Same may hold true for newer versions of SQL 

 


Monday, December 15, 2014 - 12:02:40 AM - Jeff Moden Back To Top

I have to admit, I didn't know this, Ben.  I also did some of my own testing because I'm also a skeptic.  I'm totally amazed at your findings.  Well done and thanks for taking the time to not only write the article but to do all the testing you did to support the article.


Wednesday, October 01, 2014 - 8:58:57 AM - Hoang-Hai Nguyen Back To Top

I have sometime deadlock in database wenn index were online rebuilded

May be i will process only offline index wenn no activity with the database.


Tuesday, February 19, 2013 - 8:22:02 PM - Ben Snaidero Back To Top

Hi Anuj,

Only way to get these old indexes back would be from a database backup.

Thanks for reading

Ben


Tuesday, February 19, 2013 - 7:57:37 AM - Anuj Desai Back To Top

Hello,

I have made some changes to my indexes accidently and that has aroused many issues in the live environment. Is there any way to recover the old indexes on the table as it was earlier making the functioning of live  environment normal?

Please help me out!

Thanks in advance..

Regards,

Anuj


Wednesday, January 09, 2013 - 11:54:28 AM - Ben Snaidero Back To Top

Hi Shadab,

You can get reads, writes, duration, cpu and lots of other information using a SQL Profiler trace.

Thanks for reading,

Ben


Wednesday, January 09, 2013 - 2:21:25 AM - Shadab Shah Back To Top

Hi i am newbie in SQL Server, trying to learn performance improvement. In the above example you have mention about the count of reads & write. How are you able to take that count. I think that the time might be taken by seeing the result window and converting it in ms.

 


Sunday, May 13, 2012 - 1:25:36 AM - mathew Back To Top

Hi ,

Thanks for posting the informative blog!!.

Could you please share a script to findout the fragemented indexes and rebuild indexes in MSSQL server 2005 , we are using sap and facing some performance issue .

thanks in advance..

regards

mathew


Sunday, April 22, 2012 - 2:49:33 AM - Ben Snaidero Back To Top

Hi Lian,

When you run the alter index command you specify whether or not you want the online option to be used as well as setting any other options.  The system does not do reindexing when needed, you'd have to query the system tables yourself and decide when you want to rebuild any indexes.

Ben.


Friday, April 20, 2012 - 7:53:52 AM - Lian Back To Top

Good Day. The online indexing feature sounds wonderful, but I would like to know if we still have to run the regular  re-indexing jobs  but with the online paramter added or does the Databse system himself decide when to rebuild the indexes ? If the system does the re-indexing when needed where do you set the fragmentation threshold ? Any ideas ? 


Wednesday, February 08, 2012 - 2:57:13 PM - leon Back To Top

Pretty good article Ben.  Just curious, what recovery mode did you use for this test (or did you test multiple recovery modes)?  

Also did you benchmark the time it takes SQL to swap the metadata at the end of the online indexing process?  You say that swap period is 'short', but that's relative.  Curious to know what drives the time it takes to do the swap, ie table change rate, index/table size, etc.  Thanks.  

 


Tuesday, January 31, 2012 - 8:32:27 AM - Ben Snaidero Back To Top

Hi,

That is correct.  I mention a few other criteria in the article which negate the ability to use this option.

  • 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)

Ben


Tuesday, January 31, 2012 - 6:29:30 AM - Rajasekhar Reddy Back To Top

Hi Indexing with online option should not work if the tables having datatypes as BLOB,...Etc.



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools