Demo: Transaction Log Optimization for In Memory Tables

Note: This is part of a series on SQL Server 2014 In Memory. Start here

Courtesy - Jos de Bruijn's TechEd video

CREATE TABLE [dbo].[t2_inmem]
( [c1] int NOT NULL,
  [c2] char(100) NOT NULL,

  CONSTRAINT [pk_t2_inmem] PRIMARY KEY NONCLUSTERED HASH ([c1]) WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA)
go



CREATE TABLE [dbo].[t2_disk]
( [c1] int NOT NULL,
  [c2] char(100) NOT NULL)
go

CREATE UNIQUE NONCLUSTERED INDEX t2_disk_ix1 ON t2_disk(c1)
go

--
-- insert 100 recs into disk table
--
begin tran
declare @i int = 0
while (@i < 100)
begin
insert into t2_disk values(@i, replicate('1', 100));
set @i = @i + 1;
end;
commit;


-- 200 log records can be seen (b-tree and index)
select * from sys.fn_dblog(NULL, NULL)
where PartitionId in (select partition_id from sys.partitions where object_id = object_id('t2_disk'))
order by [Current LSN] asc

-- size of tx log bytes (32K)
select sum([Log Record Length]) from sys.fn_dblog(NULL, NULL)
where PartitionId in (select partition_id from sys.partitions where object_id = object_id('t2_disk'))

--
-- in mem table, insert 100 records
--
begin tran
declare @i int = 0;
while (@i < 100)
begin
insert into t2_inmem values (@i, replicate ('1', 100));
set @i = @i + 1;
end;
commit;

-- you will find just one record
select * from sys.fn_dblog(NULL, NULL)
order by [Current LSN] desc;

-- look inside the one record- will contain 100 rows; also size is smaller
select [Current LSN], [Transaction ID], Operation,
operation_desc, tx_end_timestamp, total_size, table_id
from sys.fn_dblog_xtp(null, null)
where [Current LSN] = '0000001f:0000067d:0002';