최소로그 Flag -T610

BTree 입력으로 로깅을 최소화 할 수 있다.

성능이슈인 2가지를 생각해야 한다.
쓰기가 두번 이루어지고, rollback을 위한 Log Space Reservation이 크다.

참고) http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/10/24/new-update-on-minimal-logging-for-sql-server-2008.aspx

-- 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