SQLSERVER2014事务日志延迟提交测试

一、事务日志延迟提交简介

       sqlserver2014中新增了事务日志延迟提交的新特性，在以往版本的数据库系统中，事务提交时日志会立刻写入到磁盘，在sqlserver2014中，数据库允许事务日志延迟写入磁盘。通过在内存中保留事务日志记录并批量写入事务日志，延迟事务持续性可以缩短延迟，因而减少了所需的 I/O 操作。 延迟事务持续性可能会减少日志 I/O 争用，从而减少系统中的等待。

       延迟事务持续性使用向磁盘的异步日志写入来实现， 事务日志记录保留在缓冲区中并在缓冲区充满或发生缓冲区刷新事件时写入磁盘。 并发事务争用日志 IO 的可能性更小，日志缓冲区现在可以更大的区块刷新到磁盘，从而减少争用和提高吞吐量。

二、何时使用延迟事务持续性

 

可以容忍一定的数据丢失。
如果可以容忍一定的数据丢失，例如只要有大部分数据即可，个别记录不是非常重要，就值得考虑延迟持续性。 如果无法容忍任何数据丢失，则不要使用延迟事务持续性。

在事务日志写入时遭遇瓶颈。
如果性能问题是由于事务日志写入延迟造成的，则应用程序可能适合使用延迟事务持续性。

工作负载有很高的争用率。
如果系统工作负载争用级别很高，则会花费大量时间等待锁释放。 延迟事务持续性会缩短提交时间，因此能够更快地释放锁，从而实现更大的吞吐量。

三、如何控制事务日志延迟提交

 

1、使用 ALTER DATABASE 来设置事务日志延迟提交，语法如下：

ALTER DATABASE db_name SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }    

DISABLED
[默认] 使用此设置时，不管提交级别设置如何 (DELAYED_DURABILITY=[ON | OFF])，对数据库提交的所有事务都是完全持久事务。 无需更改和重新编译存储过程。 这样能确保任何数据都不会因延迟持续性面临风险。

ALLOWED
使用此设置时，每个事务的持续性都在事务级别确定 - DELAYED_DURABILITY = { OFF | ON }

FORCED
使用此设置，对数据库提交的每个事务都是延迟持久事务。 无论事务指定完全持久 (DELAYED_DURABILITY = OFF) 还是不进行任何指定，事务都是延迟持久事务。 当数据库适合使用延迟事务持续性，并且不希望更改任何应用程序代码时，此设置很有用。

 

2、在t-sql语句中控制事务日志延迟提交，语法如下：

 

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ] 

或：

CREATE PROCEDURE <procedureName> …    
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER    
AS BEGIN ATOMIC WITH     
(    
    DELAYED_DURABILITY = ON,    
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,    
    LANGUAGE = N'English'    
    …    
)    
END

四、在什么情况下会丢失数据？

如果对表实施延迟持续性，则应了解某些情况会导致数据丢失。 如果无法容忍任何数据丢失，则不要对表使用延迟持续性。

灾难性事件

发生灾难性事件（如服务器崩溃）时，将丢失已提交但未保存到磁盘的所有事务的数据。 根据数据库中的任何表（持久内存优化或基于磁盘）执行完全持久的事务时，或调用 sp_flush_log 时，延迟的持久事务保存到磁盘。

SQL Server 关闭和重新启动

对于延迟的持久性，SQL Server 的意外关闭和预期关闭/重新启动没有区别。  在进行关闭/重新启动时，一些尚未写入磁盘的事务需要首先保存到磁盘。

五、事务日志延迟提交性能测试

测试环境为192.168.50.11，192.168.50.12

1、insert操作测试，测试脚本如下：

ALTER DATABASE test SET DELAYED_DURABILITY = disabled

go

if object_id('test') is not null
begin
drop table test
end
create table test(i int,i_date datetime)

declare @i int,@date datetime,@i2 int
declare @date1 datetime,@date2 datetime
set @i2=1
set @date1=getdate()

while @i2 <=50000
begin
set @i=rand()*1000000
set @date=getdate()
insert into test values(@i,@date)
set @i2=@i2+1
end

set @date2=getdate()
select datediff(ss,@date1,@date2) as "开启前"
go

ALTER DATABASE test SET DELAYED_DURABILITY = FORCED

go

if object_id('test') is not null
begin
drop table test
end
create table test(i int,i_date datetime)

declare @i int,@date datetime,@i2 int
declare @date1 datetime,@date2 datetime
set @i2=1
set @date1=getdate()

while @i2 <=50000
begin
set @i=rand()*1000000
set @date=getdate()
insert into test values(@i,@date)
set @i2=@i2+1
end

set @date2=getdate()
select datediff(ss,@date1,@date2) as "开启后"
go

2、bulkinsert操作测试，测试脚本如下：

ALTER DATABASE test SET DELAYED_DURABILITY = disabled 
go
if object_id('test') is not null
begin
drop table test
end
create table test(i int,i_date datetime)

declare @i int,@date datetime,@i2 int
declare @date1 datetime,@date2 datetime
set @i2=1
set @date1=getdate()

while @i2 <=1000
begin

EXEC master.dbo.proc_dba_bulkinsert
@sourceConnString ='data source=192.168.50.12,2014;initial catalog=test;user id=dba;password=dbadba;', --源库连接串
@targetConnString ='data source=192.168.50.11,2014;initial catalog=test;user id=dba;password=dbadba;', --目标库连接串
@sourceSql ='select * from a', --源SQL
@targetTableName ='test', --目标库上的插入表
@tableColumns ='i,i_date'
set @i2=@i2+1
end

set @date2=getdate()
select datediff(ss,@date1,@date2) as "开启前"
go

ALTER DATABASE test SET DELAYED_DURABILITY = FORCED

go

if object_id('test') is not null
begin
drop table test
end
create table test(i int,i_date datetime)

declare @i int,@date datetime,@i2 int
declare @date1 datetime,@date2 datetime
set @i2=1
set @date1=getdate()

while @i2 <=1000
begin
set @i=rand()*1000000
set @date=getdate()
EXEC master.dbo.proc_dba_bulkinsert
@sourceConnString ='data source=192.168.50.12,2014;initial catalog=test;user id=dba;password=dbadba;', --源库连接串
@targetConnString ='data source=192.168.50.11,2014;initial catalog=test;user id=dba;password=dbadba;', --目标库连接串
@sourceSql ='select * from a', --源SQL
@targetTableName ='test', --目标库上的插入表
@tableColumns ='i,i_date'
set @i2=@i2+1
end

set @date2=getdate()
select datediff(ss,@date1,@date2) as "开启后"
go

3、测试结果

单位：（秒）	insert	bulkinsert
非事务日志延迟提交	6	19
事务日志延迟提交	3	11

从测试结果可以看出，延迟事务日志提交在IO较差的情况下，性能有一定的提升。