SQLServer Delayed durability 延迟持久化

DELAYED DURABILITY 简介

SQLServer自2014版本推出了一项新功能：“Delayed transaction durability”。

这项功能旨在通过推迟已提交的事务持久化到事务日志文件，减少事务日志文件IO，从而提升写入性能。
通俗地说，一个事务在commit后本应立即写到事务日志文件中去，现在推迟写操作，等到一定时机再写到事务日志文件中去。

DELAYED DURABILITY 性能提升效果

在描述DELAYED DURABILITY的具体细节前，先来看下他性能提升的神奇效果。

一张表定义如下：

create table dbo.t1 (
	id		int,
	name	varchar(20)
)

模拟海量小事务：10000个事务，每个事务向“dbo.t1”表中插入一条数据

--执行10000个事务，每个事务插入1条数据
declare @i int;    
declare @name varchar(20)
set @i = 1;  
while @i <= 10000    
begin    
	set @name = 'name' + convert(varchar, @i);
	begin transaction
    insert into dbo.t1 values (@i, @name);    
	commit
    set @i = @i + 1;    
end    
go 

在数据库默认配置下，也就是不开启DELAYED DURABILITY情况下，上面这段脚本在我的环境中执行时长：3分29秒

打开数据库的"DELAYED DURABILITY"功能

ALTER DATABASE testdb1 SET DELAYED_DURABILITY = FORCED

再次执行上面的批量命令，执行用时: 0秒！

没看错，性能提升就是这么明显！
然而，别太高兴，DELAYED DURABILITY 提升性能是以有可能丢失数据为代价换来的。
接下来我们学习下DELAYED DURABILITY原理，看看究竟怎么回事。

DELAYED DURABILITY 原理

WAL(Write Ahead Logging)机制
数据库设计，都要遵循ACID原理。
其中的“D”指的是持久化：Durability
从用户层的角度理解为：只要我一个事务提交成功，哪怕之后立即发生断电重启等异常故障，故障恢复后，都应看到之前提交成功的数据。

各种关系型数据库在实现持久化这一需求上，都采用了WAL：Write Ahead Logging 机制。
即：将事务变更的数据，先写到事务日志，然后再通过异步方式，将日志文件中的数据，刷到数据文件中去。
对于用户提交的事务，只要事务信息写入到日志文件成功，数据库就会给应用层返回事务执行成功的结果。

在这种机制下，假设用户的事务刚刚提交成功就发生了断电事件。
此时变化数据已经写入到了日志文件，但是还没刷到数据文件。
等主机恢复供电后数据库引启动，
数据库在启动时会首先扫描事务日志文件，发现有已提交但是还没来的及刷到数据文件中的日志记录，会执行redo重做操作，将这些数据刷到数据文件中，之后数据库才能完全启动成功。
此时用户发起新的查询，就能看到断电之前提交的数据。

SQLServer在实现WAL机制的具体细节上，其实还有一层叫做"Log Buffer"的缓存层，如下图：

事务日志先写入"Log Buffer"，"Log Buffer"在以下条件触发时，会将buffer中的内容刷到日志文件：

• buffer写满时(一共64KB)
• 遇到commit事件

这样设计既符合ACID要求，又通过"Log Buffer"缓存提升了性能。
然而遇到海量小事务的场景时，"Log Buffer"缓存几乎失效，起不到缓存作用，性能急剧下降。
如前面的测试用例，每个事务只提交一条数据，这条数据仅仅在"Log Buffer"中停留了一下，就因为commit事件，刷到日志文件中去了。
因而，有一万个事务，就触发一万次写IO操作，写日志文件就成了性能瓶颈！

SQLServer为了应对这种场景，推出了折中机制：“Delayed Durability”。
核心就一句话：事务即使commit了，也不立即写入日志文件，而是继续留在"Log Buffer"！等待特定时机在一起写入日志文件，通过减少文件IO来提升性能。

开启“Delayed Durability”功能后，将"Log Buffer"中的数据刷到日志文件的时机包括：

• Log Buffer 已满
• 执行了"sp_flush_log"强制刷日志
• 如果开启的模式是"ALLOWED"，而事务提交时使用了"WITH (DELAYED_DURABILITY = OFF)"
• 数据库也会定时刷日志，但是定时刷日志的时间间隔未知，官网的原话: “SQL Server does attempt to flush the log to disk both based on log generation and on timing, even if all the transactions are delayed durable.” (网上有说刷新时间是1ms，比如这篇文章)

DELAYED DURABILITY 常用命令

1）强制开启DELAYED DURABILTIY，所有事务都会强制延迟

ALTER DATABASE 数据库名 SET DELAYED_DURABILITY = FORCED

2）关闭DELAYED DURABILTIY

ALTER DATABASE 数据库名 SET DELAYED_DURABILITY = DISABLED

3）允许推迟: 每个事务自己决定是否要推迟持久化

ALTER DATABASE 数据库名 SET DELAYED_DURABILITY = ALLOWED

提交事务时显示指定推迟持久化，如：

BEGIN TRANSACTION
insert into dbo.t1 values(1, '111')
COMMIT TRANSACTION WITH (DELAYED_DURABILITY = ON)

4） 查询数据库是否开启了DELAYED DURABILTIY

select name, delayed_durability, delayed_durability_desc 
from sys.databases
where name = '数据库名'

5） 强制刷日志文件

exec sp_flush_log

DELAYED DURABILITY 缺点

有可能丢失数据！
因为已提交的数据驻留在Log Buffer中，发生异常断电就会丢失。

总结

如果数据库遇到了海量小事务性能问题，并且能够容忍一定程度数据丢失(比如本身就是备库)，那么就可以开启DELAYED DURABILITY来解决性能问题。

如果是生产库，零容忍数据丢失，那么就不要开启DELAYED DURABILITY功能，还是老老实实查下业务为什么那么多小事务。

参考文章：
[1] DELAYED DURABILITY 官网描述
[2] Improve SQL Server transaction log performance with Delayed Durability
[3] sql server2014新特性 Delayed durability（lazy commit）延迟事务
[4] The SQL Server Transaction Log, Part 2: Log Architecture