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.
Best Answer
Achieving optimal import performance in this scenario requires three things:
Minimal Logging
Achieving minimally-logged inserts to an empty clustered table without nonclustered indexes requires:
SIMPLE
orBULK_LOGGED
database recovery modelsTABLOCK
andORDER
hints)Side note:
Building nonclustered indexes separately
The advantages of doing this are:
CREATE INDEX
is minimally logged if the recovery model is notFULL
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