Figure 2: Output from Running Listing 2 with the TABLOCK hint

numrecords  size_mb     duration_sec

----------- --------    -------------

94351       6.25        105.123

 

n           lb          hb          numrecords

----------- ----------  ----------- -----------

1           59          63.2        62592

2           63.2        66.4        0

3           66.4        69.6        0

4           69.6        72.8        92

5           72.8        76          0

6           76          79.2        0

7           79.2        82.4        1

8           82.4        85.6        1

9           85.6        88.8        31297

10          88.8        92          368

 

Operation         Context     AvgLen      Cnt

----------------  --------    ----------- -----------

LOP_SET_BITS      LCX_GAM     60          31342

LOP_SET_BITS      LCX_IAM     60          31342

LOP_FORMAT_PAGE   LCX_IAM     84          1

LOP_MODIFY_ROW    LCX_IAM     88          1

LOP_MODIFY_ROW    LCX_PFS     88          31665

End of Article

Prev. page     1 2 3 4 5 6 7 [8]     next page -->



Post Your Comments Here

You must be a registered user or online subscriber to comment on this article. Please log on before posting a comment. Are you a new visitor? Register now

Reader Comments

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.

sqlmagbm21- August 15, 2009

Article Rating 5 out of 5

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

lovalvob- September 23, 2009

Article Rating 5 out of 5

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

smong- September 30, 2009

Article Rating 5 out of 5

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

smong- October 01, 2009

Article Rating 5 out of 5

 
 

ADS BY GOOGLE