Chapter 8 – What makes a bulk insert a minimally logged transaction?

Ahhh the bulk insert.  It’s like a big glass of ice-cold water in the Sahara.  And the minimally logged bulk insert is like a swimming pool near a grape-vine in the middle of the Saraha. 

For years I’ve used these methods and learned some of these lessons the hard way.  Unlike many different activities in SQL Server, there is only ONE way to minimally log bulk inserts into SQL Server and Denis (LessThanDot – blog,  sqlblog,  Blogspotblog,  @DenisGobo – Twitter) nailed it in this chapter in a concise, articulate and very easy to understand manner.  I spent about ten minutes prior to beginning this blog post on MSDN / BOL to find one source with all of the rules that Denis neatly lays out in this chapter and I found most of them in four different references.  That’s one of the values that this book provides… it distills a very important topic and then goes into depth on that topic, all in one place. 

My experience with bulk inserting data spans many years and there are many items to take into account from a performance perspective.  In my experience, there are several elements that allow bulk insert to perform optimally.  While your mileage may vary depending on your unique circumstances, ensuring that the data is loaded in the order it will be persisted is huge. 

Other important items are choosing the optimal batch size, running the build insert process in parallel, ensuring that there’s plenty of tempdb IO, placing a table lock on the object you are importing into, disabling or dropping indexes, disabling auto create and auto update statistics and the format of the file (using native format).  Each of those items has constraints, rules and guidelines around them that can be found in BOL. 

I’d strongly recommend that if you are interested in making your bulk insert processes scream that you take the time to research all of the items I just mentioned along with ensuring that the rules that Denis went over in the chapter. 

Another thing to consider is partitioning in SQL Server 2005, but more so SQL Server 2008 as partition parallelism was introduced in the 2008 version.  As always, test, test and test some more.  A lot of the fine tuning with bulk inserting is based on your specific file structure, your server hardware and your I/O subsystem.  If this is something that you are really interested in or if it’s something that you’ve already implemented on your databases, take a look at this connect item:  https://connect.microsoft.com/SQLServer/feedback/details/468517/update-statistics-at-the-partition-level and then Vote for it!


0 Responses to “Chapter 8 – What makes a bulk insert a minimally logged transaction?”

  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 11 other followers