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."

Steve10/1/2009 8:35:52 AM

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 :)

Steve9/30/2009 12:37:49 PM

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

Barry9/23/2009 11:48:45 AM

I knew about the INSERT INTO performance improvement, but this articls disects and helps quantify one of the many benefits of moving into SQL 2008 from SQL 2005 or older versions.

Balaji8/15/2009 10:26:49 AM