SQL Server事务提交默认是完全持久性的（Full Durable），从SQL Server 2014开始，增加了新的功能延迟事务持久性，使得事务提交可设置为延时持久性的（Delayed Durable，也叫做（Lazy Commit））。

 

完全事务持久性（Full Transaction Durability）

 

在SQL Server 2014之前， SQL Server提交事务是一个同步的过程，也就是说，只有当SQL Server将该事务相对应的日志记录写入到了磁盘文件之后，才会返回事务提交成功的信号。这也是为了体现事务4个基本特性中的持久性而实现的功能。只有这样，我们才能保证当SQL Server因为某些原因突然Crash之后，再重启的时候，那些已经提交但还没有写入到数据文件上的记录可以通过日志文件进行恢复，或者那些还没有提交，但已经有部分数据写入到数据文件上的记录进行回滚。所以，我们可以看到，对于传统的事务提交，由于必须要保证日志写入到磁盘上，这个I/O操作就有可能成为性能的瓶颈。

 

应用场景

 

完全持久事务在将控制权归还给客户端之前把事务日志强制写入磁盘。 只要存在以下情况，就应使用完全持久事务：

1.系统无法承受任何数据丢失。

2.造成瓶颈的原因不是事务日志写入延迟。

通过在内存中保留事务日志记录并批量写入事务日志，延迟事务持续性可以缩短延迟，因而减少了所需的 I/O 操作。 延迟事务持续性可能会减少日志 I/O 争用，从而减少系统中的等待。

 

延迟事务持久性（Delayed Transaction Durability）

 

这个技术可以使得SQL Server在提交事务时，无需等待事务日志写入磁盘就直接返回事务提交成功的信号，I/O操作在后台会以异步的方式写入到数据库事务日志文件中。这样好处是，事务可以去除等待I/O操作完成所带来的延时，以此来提高整个SQL Server的性能。在这整个过程中，SQL Server会在内存中专门开辟出一个特殊的Log Buffer来存放DTD所产生的日志，当这个Log Buffer一旦存满之后会马上写入日志文件，由此将零散的I/O操作变成了一块一块的操作来提高效率，增加吞吐量。

 

应用场景

 

适合使用延迟事务持续性的部分情况如下：

1.可以容忍一定的数据丢失。

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

2.在事务日志写入时遭遇瓶颈。

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

3.工作负载有很高的争用率。

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

 

控制事务持久性

 

持久性可以在数据库级别（Database Level）、提交级别（COMMIT Level）或原子块级别（ATOMIC Block Level）进行控制。

 

数据库级别控制

 

您作为 DBA，可以控制用户是否可通过以下语句对数据库使用延迟事务持续性。 您必须使用 ALTER DATABASE 来设置延迟持续性设置。

 

 

原子块级别控制 - 本机编译的存储过程

 

下面的代码面向原子块内部。

 

 

提交级别控制 – T-SQL

 

COMMIT 语法已扩展，您可以强制实施延迟事务持续性。 如果 DELAYED_DURABILITY 在数据库级别设置为 DISABLED 或 FORCED，则忽略此 COMMIT 选项。

 

如何强制执行事务日志刷新

 

有两种方法可以强制将事务日志刷新到磁盘。

1.执行任何可改变相应数据库的完全持久事务。 这会强制将之前提交的所有延迟持续性事务的日志记录刷新到磁盘。

2.执行系统存储过程 sp_flush_log。 此过程会强制将之前提交的所有延迟持久事务的日志记录刷新到磁盘。

 

sys.sp_flush_log

 

将当前数据库的事务日志刷新至磁盘，从而强制将所有之前已提交的延迟持久事务的日志记录刷新到磁盘。

 

如果您出于性能优势原因而选择使用延迟事务持续性，但还想对在服务器崩溃或故障转移时丢失的数据量进行有保证的限制，请定期执行 sys.sp_flush_log。 例如，如果您想确保不丢失多于 x 秒的数据，则您需要每隔 x 秒执行一次 sp_flush_log。

 

执行 sys.sp_flush_log 可保证所有之前提交的延迟持久事务都是持久的。