I have a bulk loading process that loads millions of records into a few fact tables within a warehouse. Those tables are primarily clustered on time. The non-clustered indexes are in place for how the data is used to speed up performance.

I typically drop some of the non-clustered indexes to both speed up inserts and to reduce index fragmentation after large data loads. However, this process of dropping and rebuilding is causing a huge amount of time as the data grows.

Example: One table took 2 hours to apply a new non-clustered index on 100m+ rows.

Likewise, if I leave the non-clustered indexes in place, they will increase the inserts 3x to 10x in some cases that force your hand to drop and rebuild.

While it's awesome to drop and rebuild indexes, they don't really pan out that well as data grows in those tables. What options are available to me? Should I bulk up the server with more memory (currently 32GB) and cpu (4 vCPU)? Should I rethink my indexes? Should I find a balance of keeping some indexes on for reorganization versus dropping and rebuilding?

(Note: I don't have enterprise edition.)

I'm thinking my only option is enterprise edition with table partitioning where I can rebuild indexes per partition as opposed to the whole table.

  • You might look at changing the fill factor on your non-clustered indexes to slow fragmentation down. This might alleviate some of the performance penalties as well. It might be beneficial to post the table creation and index scripts. You can look into doing a reorganize on the non-clustered indexes which is an online operation. Specifying the create index to use sort_in_tempdb might speed up the index rebuild/reorganization as well. – Jonathan Fite Dec 7 '15 at 14:25
  • Yeah, after one load one of the indexes is 95% fragmentation. I'll lower the fill factor. – Fastidious Dec 7 '15 at 16:03

At 4 vCPU you're on the smallest Enterprise Edition, so your cost isn't prohibitive. You will need to keep up to date with the Software Assurance if you want HA on your VM.

I would recommend adding memory to this (assuming its available in the hypervisor) and taking this VM to the maximum that edition of SQL can support (64GB for 2008 R2). That's probably your lowest cost option.

If that doesn't give what you want / need then realistically you are looking at Enterprise Edition and table partitioning to manage the data which isn't being manipulated by the ETL.

If the majority of the data isn't being changed, and it just gets reloaded time after time, then that needs to change as its not going to be sustainable. Only load the data which needs to be reloaded.

  • Added the max memory I could. Reorganizing my heaviest table took 20 minutes this time around. – Fastidious Dec 9 '15 at 5:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.