Bulk Import Optimizations (Minimal Logging)

re: Bulk Import Optimizations (Minimal Logging)

Alex Rosa — Tue, 05 Feb 2008 21:59:37 GMT

Hi,

Interisting post.

I've been trying to reproduce it with the script below, but with no success when I change the recovery model.

USE AdventureWorks;

CREATE TABLE t_heap (c1 INT, c2 INT, c3 DECIMAL (38,2), c4 CHAR(1000));

-- HERE OK, I got the same result.

BULK INSERT t_heap

FROM 'C:\t_heap.dat' WITH (TABLOCK, FIELDTERMINATOR ='|')

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

-- HERE OK, I got the same result.

ALTER DATABASE AdventureWorks SET RECOVERY FULL

DECLARE @I INT

SELECT @I = 0

WHILE (@I < 5)

BEGIN

INSERT INTO T_HEAP WITH (TABLOCK) VALUES

(@I, @I + 5000, 10.2, REPLICATE ('A', 60))

SET @I = @I + 1

END

SELECT TOP 5 operation,context,[log record length]

FROM fn_dblog(null, null)

WHERE allocunitname='dbo.t_heap'

ORDER BY [Log Record Length] DESC

Simple/Bulk recovery model I'm getting this result:

LOP_INSERT_ROWS LCX_HEAP 1116

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

ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE

-- repeat INSERT and SELECT above

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

ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED

-- repeat INSERT and SELECT above

My setup:

Microsoft SQL Server 2005 - 9.00.3200.00 (Intel X86)

Oct 2 2007 11:33:27

Developer Edition on Windows NT 6.0 (Build 6000: )

If I got it right, the log record length result with SIMPLE/BULK recovery model should be 92 instead of 1116, right?

re: Bulk Import Optimizations (Minimal Logging)

Sunil Agarwal — Tue, 05 Feb 2008 22:21:01 GMT

You don't get minimal logging with regular TSQL INSERT statement independent of the recovery model. However, we are looking into if we can remove this restriction for regular inserts in future releases including SQL2008

SQL Shorts

SELECT IsUseless(blabber) from sys.innarwebs CROSS APPLY jmass(blog) — Wed, 06 Feb 2008 01:03:47 GMT

The big news of the week is Windows Server 2008 RTM'ing. Read more here. The storage engine team has begin a series on bulk insert. It is definitely recommended reading. Part 1, Part 2, Part 3. The S ...

re: Bulk Import Optimizations (Minimal Logging)

Alex Rosa — Fri, 08 Feb 2008 01:13:21 GMT

Thanks for the answer.

I was looking for an article talking about the differences among FULL, SIMPLE and RECOVERY model (which each one of this setting really write in the log file), but I haven't find it.

Could you talk about this or blog about it?

re: Bulk Import Optimizations (Minimal Logging)

Sunil Agarwal — Fri, 08 Feb 2008 01:56:01 GMT

BOL has a good description on different recovery model. If you think something is not well covered, please send me a mail and we will update the BOL. In a nutshell, under SIMPLE recovery model, SQL Server truncates the inactive portion of the log but otherwise the logging is similar to what we have under bilk-logging recovery model.

re: Bulk Import Optimizations (Minimal Logging)

jl999 — Fri, 22 Feb 2008 03:36:10 GMT

If you have a table with clustered index, and with existing data, say you have 30 million in the table. When you bulk import 10 million more, each one would be fully logged, correct?

What's the best way to achieve minimal logging in this scenario, drop the clustered index first, then add the clustered index back after bulk import? For a large table, creating a clustered index might take a while. But is it still a better option than having a clustered index in place when importing?

re: Bulk Import Optimizations (Minimal Logging)

Sunil Agarwal — Fri, 22 Feb 2008 03:46:13 GMT

I recommend reading the following white paper that dealt with exactly these kinds of questions. This is still applicable for SQL2005. I will follow up this thread with some changes we have made for minimal logging in SQL Server 2008.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx

re: Bulk Import Optimizations (Minimal Logging)

navneet.sqldba — Wed, 09 Dec 2009 19:22:39 GMT

Hello Vishal

It is a very good article.

Can You Elabrate on the Below.I have picked the lines from your article.

"This is because during the transaction log backup, the state of the pages involved in the bulk import may have changed between the time bulk import was done and the transaction log back is initiated. "

re: Bulk Import Optimizations (Minimal Logging)

Sunil Agarwal — Mon, 14 Dec 2009 23:37:52 GMT

The pages containing the minimally logged operations are backed up as part of log backup. When these log records are restored as part of restore, we don't have the UNDO information in case the changes in these pages need to be undone.