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.