Improve speed of index rebuild on SQL server

Question

I'm importing a large amount of data into an empty database, and before I start I disabled all non-unique non-clustered indexes to see if I could improve the performance of the import.

Now I want to re-enable the indexes, and I'm wondering if there is anything that I can do to optimize this.

There are > 100 tables and almost 2,000 indexes to be rebuilt. The database is 200GB in size.

The key section of the script I'm running is this:

declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for
    select  'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild'
    from    sys.indexes as i
    Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1
    and i.type > 1
    and i.is_disabled = 1

I considered setting ONLINE=OFF for the alter index statement, but as the indexes start out disabled I wasn't sure that this setting would have any effect. I also considered setting SORT_IN_TEMPDB = ON, but as the tempdb files are on the same drive as the .mdf files of the databases I assumed that there was also no benefit to doing that.

Whilst running the rebuild script I have noticed that I have a lot of CXPACKET wait types. I don't really understand why that would be or if it's a problem that I should be looking to address.

One final point that may be relevant: my entire server is currently inactive other than this import of data into the database. There is no other user activity to consider or worry about; my only concern is importing the data into the database in the shortest possible time.

Answer 1

Achieving optimal import performance in this scenario requires three things:

1. Minimally-logged base table inserts
2. Minimally-logged nonclustered index builds
3. Avoiding physical reads

Minimal Logging

Achieving minimally-logged inserts to an empty clustered table without nonclustered indexes requires:

1. Using either the SIMPLE or BULK_LOGGED database recovery models
2. Specifying a table lock and ordered input (e.g. TABLOCK and ORDER hints)

Side note:

It is also possible to achieve minimally-logged inserts to a clustered table that has nonclustered indexes provided trace flag 610 is enabled. Whether nonclustered index inserts are minimally-logged or not depends on the query plan selected by the query optimizer.

If the query plan uses a separate iterator for the nonclustered index, and the iterator has the DMLRequestSort property set to true, the nonclustered index inserts will be minimally logged, provided the other conditions mentioned previously are met.

Building nonclustered indexes separately

The advantages of doing this are:

1. Clustered index inserts can be minimally-logged without enabling TF 610
2. CREATE INDEX is minimally logged if the recovery model is not FULL

Avoiding physical reads

Ideally, the data to be imported will be stored on a separate machine, or at least on separate physical storage from that used to host the database.

The database server should have enough memory to hold the largest base table in cache, with enough left over for sorting operations necessary when building nonclustered indexes.

A good pattern is to fast-load the base table (minimally-logged clustered index load) and then to build all nonclustered indexes for that table while its data pages are still cached.

The question outlines a process whereby base tables are loaded first, and then nonclustered indexes built. The cursor definition does not use an ORDER BY clause to at least group nonclustered index builds on the same table together.

The likely result is that data pages for different tables are repeatedly read into cache and then discarded as nonclustered indexes are built in a non-deterministic order.

The cost of repeated physical reads completely dominates the benefits of minimal logging gained by building nonclustered indexes separately. This explains why you found that loading tables with existing indexes is faster (because all nonclustered indexes for a given table are maintained before moving on to the next table).

Summary

The import process should be reworked to bulk-load one table at a time. This means loading the table and building all nonclustered indexes before moving on to the next one. The SQL Server instance should have enough memory available to hold the largest table and perform the largest nonclustered index sort at the same time.

You could also try enabling TF 610 before loading the data into tables with nonclustered indexes already in place. This is not usually as fast as the previous method, but it may be fast enough.

See the following for more information:

The Data Loading Performance Guide

Operations That Can Be Minimally Logged