Sign up ×

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( + ' on ' + + ' 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.

share|improve this question
When you say your only concern is import time, do you mean the time from the start of the import to the end of re-enabling the indexes? If so, you should just leave the indexes enabled during the import. 2,000 indexes for 200GB data sounds like a lot of indexes to me. Perhaps you should look at the index usage DMVs to see if there are some that could be removed. –  Max Vernon Sep 12 '13 at 13:56
Just to clarify, you need to do the same 200GB import repeatedly, and not just once? –  Jon Seigel Sep 12 '13 at 14:20
I only need to do the import once but as part of a bigger process with a limited window of time, so I'm currently testing that process to get it to fit within that window. @MaxVernon It looks like you are right that leaving the indexes enabled is the quickest way, although I am surprised as I'd read that it was normally quicker to disable the indexes, import the data, then re-enable the indexes. This is a third-party database so removing indexes or otherwise changing it is not really possible. –  paulH Sep 12 '13 at 14:39
Okay. About the CXPACKET waits: index rebuilds themselves scan indexes (even the index being rebuilt), and those scans can use parallelism. You shouldn't be concerned about those waits -- the parallelism is probably helping. –  Jon Seigel Sep 12 '13 at 14:53

1 Answer 1

up vote 7 down vote accepted

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


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

share|improve this answer
Thanks for that - lots of useful info there for me. I hadn't realised that some stuff could still be fully-logged when using Simple recovery mode! I think the indexes were being built in the right order as I was listing the disabled indexes during the process and on the larger tables I could see them disappearing one at a time, so I'm going to have a go with trace flag 610 and see how that works out. –  paulH Sep 17 '13 at 10:41

Your Answer


