By: Ben Snaidero | Updated: 2012-01-31 | Comments (13) | Fragmentation and Index Maintenance
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.
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 SyntaxThe 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:
|CPU (ms)||READS||WRITES||DURATION (ms)||CPU (ms)||READS||WRITES||DURATION (ms)|
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)|
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)|
Extra Disk Space Required During SQL Server Online Index RebuildsThe 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 RebuildsThis 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.
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.
- Investigate effects of using SORT_IN_TEMPDB parameter
- How to identify which indexes need to be rebuilt
- Check out these index related tips:
Last Updated: 2012-01-31
About the author
View all my tips
- Custom SQL Server Index Defrag and Rebuild Procedu...
- Index Rebuilds in SQL Server 2000 vs SQL Server 20...
- Rebuilding SQL Server indexes using the ONLINE opt...
- Selectively rebuild indexes with SQL Server mainte...
- Solving SQL Server Database Physical File Fragment...
- Efficiently Rebuild SQL Server Clustered Indexes w...
- More SQL Server DBA Tips...