JaniceCLee.com SQL…ish

SQL Server MVP Deep Dives: What I learned from Chapter 8

Had I taken the marshmallow test when I was a kid, I'd have failed it. I'd probably pass it now because I hate marshmallows (humor me--I think that counts ). I remember reading Harry Potter and the Deathly Hallows. I was doing so well until I got to Chapter 7. Then the excitement and uncertainty just became unbearable so I went ahead and read the last few pages.  Urgh! What a knucklehead, right! Who's crazy enough to do that?? Apparently, me. It ruins the whole reading experience.

I received my SQL Server MVP Deep Dives book from Amazon yesterday. The book's a collection of short articles written by SQL Server MVPs. Short enough for anyone who doesn't know how to delay gratification. In other words, perfect for me.  I read around 5 chapters last night and decided to sleep write about "What makes a bulk insert a minimally logged operation?" by Denis Gobo. I don't want to call this a review because it isn't.  Let's just call it a short discussion of what I learned.

1. Why would you want a bulk insert to be minimally logged?
Article says for faster imports which I agree with. It also says "so that your log file could be a fraction of the size of a fully logged operation". Gotta be careful with this one...or before you know it, we'd have another DBA myth. I think the author meant so that the operation would consume less space on the log file--which doesn't necessarily mean that the log file would be smaller. If you have a log file with an initial size of 4GB, whether a set of operations consumes 3GB or 1GB of space, the log file's size would still be 4GB, right? So "your log file could be a fraction of the size" can be a bit misleading.

2. The article discusses the conditions that need to be met for a minimally logged bulk copy to be performed.
The last condition was that the TABLOCK hint must be specified." (I encourage you to get the book if you want to know the other conditions. Some are mentioned here.)

I actually thought this statement was wrong. What is minimally logged in the bulk-logged recovery model? Bulk operations. How are bulk operations logged in the simple recovery model? MINIMALLY too. That's what I knew. So I honestly thought the author probably just meant that the TABLOCK hint would make for more minimal logging. But I executed the sample scripts myself and checked the BOL even. It is how the author says it is. Not only does the recovery model have to be simple or bulk-logged, the TABLOCK hint is required and must be specified. Check out the image  below when I tried the example discussed in the article:

All log files that I outlined above have the same file sizes and belong to databases with different recovery models. The same bulk import operation without the TABLOCK hint was executed on the databases. As you can see, without the TABLOCK hint, the recovery model didn't even make a difference.

3. I also checked this: sys.sysfiles. There's a table in the article with a column labeled as "Size in KB" and refers to the size of the corresponding (log) file as selected from sys.sysfiles. Actually, the size field is specifically size in 8-KB pages and not just "Size in KB". It's good to review the small stuff as you encounter them. It makes the learning experience better.

So in closing, I actually learned a lot for just 8 pages. Denis Gobo knows how to simplify concepts to make them easy to understand. And the book? I'm certainly looking forward to reading more. So far, I think it's worth all the hype.