Minimally Logged Inserts

you talk about some times performance penalty when using the T-610 but don't specify when or why.

Some guidance?

Incredible article, as usually, but the links are bloken (figures and listings), aren't them?

I should clarify that all I care to minimally log are data warehouse INSERTs. Thus, I can get away with the simple rule of "...ignoring 'Log Record Length' and simply look at aggregate counts of LOP_INSERT_ROWS."

lovalvob...
Itzik mentioned TF 610, but his code does not use it. Giving credit where it's due, he also pointed to Sunil's blog, where you may find the following helpful: http://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-par
t-2.aspx

TF 610 is responsible for virtually all the magic shown (INSERT INTO non-empty clustered index w/out TABLOCK, yet no meaningful logging!)

BTW, I always have to keep in mind that BULK INSERT (my preference) behaves differently than INSERT INTO, with respect to locking. For more on BULK INSERT's behavior, go to part 3 of Sunil's blog post.

As Byzantine as minimal logging is, the real trick is detecting minimal logging--or a lack thereof. Itzik's code makes a great contribution in this area. Personally, however, I ignore "Log Record Length" and simply look at aggregate counts of LOP_INSERT_ROWS. Anything less than the number of rows actually inserted means that minimal logging took place...or that you're pulling from a truncated log :)

Very useful, but it appears to be a good deal less effective when there are indexes (even non-clustered). Can you comment?