As the famous Bard once said, “To disable indexes or not to disable indexes, that is the question?”.  Well maybe that wasn’t quite what he said but it was a discussion that came up here just recently.

We’ve found ourselves inheriting a process that has to copy all the data from one database into another, empty database.  The current process is rather convoluted and fiddly with far too many manual, intermediate steps that are just crying out for a spot of human error.

To cut a long story short, we decided that there must be a better way to do this.  Unfortunately SSIS and BCP were ruled out fairly early on for one reason or another. Instead we decided on a simple approach, create a schema only copy of the database and just run a bunch of INSERT… SELECT statements to bring the data in.  But the question was how best to go about doing this, should we disable indexes first, import and re-enable them or should we just import directly with the indexes enabled?

Conventional wisdom seems to think that disabling the indexes is the way to go but this opinion (like many things in the database world) seems mainly based on hear say with very little in the way of evidence to back it up.  Being the cynic that I am, I decided to do some tests on the various approaches that I could take.

The test database was a fairly modest (and about average for this particular product) 25GB in size, containing 460 tables and 1,500 indexes.

Insert With Indexes Enabled – Import Abandoned After An Hour 

So the first test was to insert all data from all tables with indexes enabled.  Not a good start with me getting fed up and abandoning the import after 1 hour.

Insert With Indexes Disabled – Import and Index Rebuild Finished After 23 Minutes

After having to abandon the first import you can image how out little down trodden faces lit up when, after disabling the indexes we saw the last insert statement complete with only 13 minutes on the clock.  Add to that the 10 minutes that it took to rebuild all indexes and the whole import was complete in 23 minutes.  But can we do better?

Insert With Tab Lock and Indexes Disabled – 21 Minutes

I wonder if adding a tab lock hint would help things?  Now, I’ve never been the biggest fan of using hints but in this case when we’re inserting in some cases, millions of rows into empty and unused tables it seemed to make sense.  This time the whole process, including the rebuilding of the indexes was done and dusted in 21 minutes.  The tab lock has saved us 2 minutes.

Insert With Tab Lock, Indexes Disabled and Database in Bulk Logged – 16 Minutes

When you insert into an empty table with tab lock, your insert suddenly becomes a minimally logged operation.  Could switching the database to bulk logged help improve our performance further?

BOOM!  The insert statements all finished in just under 9 minutes!  Leaving the database in bulk logged I tried running the index rebuilds, a mere 7 minutes had passed before they were complete meaning that the whole process was done in 16 minutes.

To put that into perspective, we’d just taken a convoluted, labour intensive and error prone routine that was taking around 45 minutes to complete and made a simple process that’s done in 16 minutes.

Conclusion

This test was based on a very specific example but if you do find yourself needing to copy a large amount of data from one into another, empty table, you may well find that disabling your indexes, using tab lock and putting the database into bulk logged will significantly increase the performance of the transfer.