In my earlier blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx, we discussued how we can get minimal logging using TF-610 when using TSQL INSERT statement. Interestingly, you can get minimal logging when using MERGE statement as well. Here is a simple example to illustrate it.
alter database bulktest set recovery SIMPLE
-- enable the trace flag 610dbcc traceon (610, -1)
-- create the staging tablecreate table t_staging (c1 int, c2 int, c3 char (100), c4 char(1000))go
-- load 10000 rows into the staging tabledeclare @i intselect @i = 0while (@i < 10000) begin insert into t_staging values (@i, @i+10000, 'indexkey', 'hello') select @i= @i + 1end
-- create the target table with clustered indexDROP TABLE t_targetGO
create table t_target (c1 int, c2 int, c3 char(100), c4 char(1000))go
create clustered index ci on t_target(c1)go
-- clean up the logwhile @@trancount > 0 rollbackcheckpoint
-- execute a merge statement under a transactionBEGIN TRANMERGE INTO t_target AS Target using t_staging as SourceON Target.c1 = Source.c1when matched then update set c4 = source.c4when not matched by target then insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ;
-- Now look at the top 20 log recordsselect top 20 operation,context, [log record fixed length], [log record length], AllocUnitId, AllocUnitName from fn_dblog(null, null) where allocunitname='dbo.t_target.ci'order by [Log Record Length] Desc
-- here is the output
You can see that after the first data page worth of rows, the rest of the inserts are minimally logged. The same can be done if the target table was a HEAP but in this case, you will need to use TABLOCK hint as shown here
MERGE INTO t_target with (TABLOCK) AS Target using t_staging as SourceON Target.c1 = Source.c1when matched then update set c4 = source.c4when not matched by target then insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ;
This will work even when you have non-empty target table when the inserted rows go to newly allocated pages.
Just to clarify, any updates or deletes that the merge does are still fully logged. (Right?)
>> dbcc traceon (610, -1)
This will enable the trace flag globally (for all connections). Was that your intent? Might be better for the example to use a session-level flag.
You are correct. We only get minimal logging for INSERTs on the rows that go to newly allocated pages. We will still do full logging for UPDATE/DELETE. Yes, session level setting will be a better choice. I was just demonstrating one example.
How can this be when MERGE doesn't explicitly enforce a SORT order?
i have enabled 610 traceflag at session level was that so dangerous? because i am scared