Minimal logging and MERGE statement
Published Mar 23 2019 06:31 PM 1,389 Views
Microsoft
First published on MSDN on Jun 03, 2010

In my earlier blog http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging... , 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.


use bulktest
go


alter database bulktest set recovery SIMPLE


-- enable the trace flag 610
dbcc traceon (610, -1)


-- create the staging table
create table t_staging (c1 int, c2 int, c3 char (100), c4 char(1000))
go


-- load 10000 rows into the staging table
declare @i int
select @i = 0
while (@i < 10000)
begin
insert into t_staging values (@i, @i+10000, 'indexkey', 'hello')
select @i= @i + 1
end


-- create the target table with clustered index
DROP TABLE t_target
GO


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 log
while @@trancount > 0 rollback
checkpoint


-- execute a merge statement under a transaction
BEGIN TRAN
MERGE INTO t_target  AS Target using t_staging as Source
ON Target.c1 = Source.c1
when matched then
update set c4 = source.c4
when not matched by target then
insert (c1, c2, c3, c4) values (c1, c2, c3, c4) ;


-- Now look at the top 20 log records
select 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 Source
ON Target.c1 = Source.c1
when matched then
update set c4 = source.c4
when 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.


Thanks


Sunil Agarwal

Version history
Last update:
‎Mar 23 2019 11:31 AM
Updated by: