New update on minimal logging for SQL Server 2008

  • Article

Based on the customer feedback, we have decided to make minimal logging functionality into a btree available to SQL 2008 customers. All you need to do is to enable TF-610 in RTM bits. As a cautionary note, we have seen some slowdown if you are loading data into a btrree using TF-610 on a slower IO subsystem. So please make sure you test it first.

Here are the two performance issues you need to be aware of. I will walk you through each of these with examples

1.     Double writes

2.     Log space reservation.

Example:

-- create the source table. This table stores 2 row per page

create table t_source (c1 int, c2 int, c3 char (3000))

go

-- I insert 10000 rows. With 2 rows/page, I will have 5000 pages so

-the size of the table is 40MB

declare @i int

select @i = 1

while (@i < 10000)

begin

      insert into t_source values (@i, @i+10000, 'hello')

      select @i= @i + 1

end

-- create target table with clusttered index

create table t_ci (c1 int,  c2 int, c3 char(3000))

go

create clustered index ci on t_ci(c1)

go

-- I will now insert all the rows from the source table into the target

-- table and measure (a) logspace used (b)logspace reserved (c) number of writes

-- both with TF-610 ON and OFF.

-- Here is the INSERT statement

begin tran

insert into t_ci select * from t_source order by c1

rollback

 

  

# of page writes(appx)

 

Logspace used (appx)

 

Logspace reserved (appx)

 

TF-610 OFF

 

5000

 

34MB

 

8MB

 

TF-610 ON

 

10000

 

3.5MB

 

82MB

 

What is interesting to note, is that without TF-610, we write the data page only once and it is fully logged. While with TF-610, we write the data page twice (i.e. double write) but we do get minimal logging (i.e. we took wrote only 3.5MB log). However, the log reserved space balloons to 82MB. This is done to allow for rollback of the transaction and this space is reserved assuming full-logging. You may wonder that it should atmost be 34MB, but it is because of conservative log reservation algorithm.

For details, please refer to my blog entries

https://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-3.aspx https://blogs.msdn.com/sqlserverstorageengine/archive/2008/03/23/minimal-logging-changes-in-sql-server-2008-part-2.aspx

thanks