Chapter 8. What makes a bulk insert a minimally logged operation?
By using a minimally logged operation, SQL Server doesn’t have to work as hard as if the operation was fully logged. The result is that your import will be much faster, and also that your log file could be a fraction of the size of a fully logged operation.
Before we start, we first need to understand what a minimally logged operation is. A minimally logged operation logs only the pages and extents that are affected; a fully logged operation will log all the individual insert, update, and delete statements. If you have 100 insert statements in a fully logged operation, you’d have 100 entries in the log file, whereas if it were minimally logged, you’d only have one entry.
A minimally logged bulk copy can be performed if all of these conditions are met:
- The recovery model is simple or bulk-logged.
- The target table isn’t being replicated.
- The target table doesn’t have any triggers.
- The target table either has zero rows or doesn’t have indexes.
- The TABLOCK hint is specified.
In this chapter, I’m going to concentrate on the TABLOCK hint (used to obtain a shared lock on a table which is held until the end of a T-SQL statement) and how it can be used to increase performance. I’ll show you that performance will greatly increase when you specify the TABLOCK hint.
