SQL Server 了解日志缓存刷新

原创

JeesonHuang 2023-01-11 02:04:19 博主文章分类：SQLServer ©著作权

文章标签 数据库服务器 sql SQL Server 文章分类 Hadoop 大数据

©著作权归作者所有：来自51CTO博客作者JeesonHuang的原创作品，请联系作者获取转载授权，否则将追究法律责任

在此之前，你可能已经多次听说ACID事务属性了吧。本文主要关注D部分，即持久性。更具体地说，本文关注SQL Server日志记录体系结构的一个方面——日志缓冲区刷新。下面将讨论日志缓冲区提供的功能、SQL Server将日志缓冲区刷新到磁盘的条件、你可以做些什么来优化事务性能。

日志缓冲区冲

事务有四大属性————ACID，其中D表示事务的持久性。在逻辑层面，意味着只有当应用程序向 SQL Server 发送提交事务的指令时，SQL Server 在保证事务持久化后，才将控制器返回给调用者。也就是当调用者重新获得控制权时，可以确定事务已经准确完成，即使服务器宕机重启也不会丢失数据。

我们首先知道，用户操作数据是在内存中完成的。在检查点提交之前，日志缓冲区的数据写入到事务日志文件中，数据缓存中的脏数据此时并未写入数据文件。当你重启 SQL Server 时，在数据库恢复阶段，系统将对已提交的数据进行重做，未提交的数据进行回滚，以保证数据的完整性。

SQL Server 中日志写入是顺序的，如果 SQL Server 不使用日志缓冲区来缓解向磁盘写入日志，对于写入密集型系的统将很快遇到与日志写入相关的性能瓶颈。为了减轻频繁顺序日志写入磁盘的负面性能影响，SQL Server 在内存中使用日志缓冲区。日志写入首先写入日志缓冲区，某些情况会导致 SQL Server 将日志缓冲区刷新或固化到磁盘。固化单元（日志块）的范围可以从最小扇区大小（512 字节）到最大 60 KB。

以下是触发日志缓冲区刷新的条件：

SQL Server获取一个完全持久事务的提交请求
日志缓冲区已满，达到 60 KB 容量
SQL Server 需要固化脏数据页，例如，在检查点过程中，这些页更改的日志记录尚未固化（预写日志，或简称 WAL）
你可以通过执行过程 sys.sp_flush_log 手动请求日志缓冲区刷新
SQL Server写入一个新的序列缓存相关的恢复值

最后一点可能还不清楚，本文后面详细解释。

SQL Server等待处理日志缓冲区刷新的I/O操作完成的时间由WRITELOG等待类型反映。

这个信息如此有趣，我们如何利用它呢？了解触发日志缓冲区刷新的条件，可以帮助你了解某些工作负载遇到相关的瓶颈。此外，在某些情况下，你可以采取一些行动来减少或消除此类瓶颈。下面将介绍些示例，如一个大事务与许多小事务、完全持久事务与延迟持久事务、用户数据库与tempdb以及序列对象缓存。

一个大事务VS多个小事务

如前所述，触发日志缓冲区刷新的条件之一是提交事务以保证事务的持久性。这意味着涉及大量小事务的工作负载（如OLTP系统）可能会遇到与日志写相关的瓶颈。

有时也并非如此，如果一个会话提交了许多小更改，优化工作的一个简单而有效的方法是将更改应用于单个大事务，而不是多个小事务。

SET NOCOUNT ON;
USE TEST
ALTER DATABASE dbname SET DELAYED_DURABILITY = Disabled; -- default
--每次测试前均删除重建表
DROP TABLE IF EXISTS dbo.T1;
CREATE TABLE dbo.T1(col1 INT NOT NULL);
DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  BEGIN TRAN
    INSERT INTO dbo.T1(col1) VALUES(@i);
  COMMIT TRAN;
  SET @i += 1;
END;

此代码执行1百万个更改用户数据库中的数据的小事务。此工作将触发至少1百万次日志缓冲区刷新。由于日志缓冲区已满，你可能会得到一些额外的日志。你可以使用以下测试模板来计算日志缓冲区刷新的次数，并测量完成工作所花费的时间:

DECLARE @logflushes AS INT, @starttime AS DATETIME2, @duration AS INT;
-- Stats before
SET @logflushes = ( SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Flushes/sec' AND instance_name = 'TEST');
SET @starttime = SYSDATETIME();
-- ...以上 while 循环的 SQL 放置此处 ...以下测试均包括此上下文测量脚本
-- Stats after
SET @duration = DATEDIFF(second, @starttime, SYSDATETIME());
SET @logflushes = ( SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Flushes/sec' AND instance_name = 'TEST') - @logflushes;
SELECT  @duration AS durationinseconds, @logflushes AS logflushes;

即使性能计数器的名称是Log Flushes/sec，它实际上会持续累积到目前为止的日志缓冲区刷新次数。因此，代码从工作前计数减去工作后计数，从而计算出工作生成的日志刷新计数。这段代码还测量了完成工作所花费的秒数。

SQL Server 了解日志缓存刷新_Server

这段代码在我的系统上花了22秒完成，并触发了1000027次日志缓冲区刷新。这非常慢，因为大量的日志刷新。在不显示开启事务情况下，默认是每次执行SQL都是提交一次，如上面情况一样。

在典型的OLTP工作负载中，不同的会话并发地在不同的小事务中提交小的更改，类似上面的场景，并不是在单个大事务中封装大量的小更改。但是，如果你的情况是所有小更改都是从同一个会话提交的，那么优化工作的一个简单方法是将其封装在单个事务中。这将给你带来两个主要好处。一是你的工作将写入更少的日志记录。对于1000000个小事务，每个事务实际上写三个日志记录：一个用于开始事务，一个用于更改，一个用于提交事务。因此，你将看到大约30000000个事务日志记录，而作为一个大事务执行时则略多于1000000个。但更重要的是，对于一个大事务，大多数日志刷新仅在日志缓冲区满时触发，或在事务提交时触发，在事务的最后再进行一次日志刷新。性能差异可能非常显著！

要在一个大事务中测试工作，代码如下:

BEGIN TRAN;
DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  INSERT INTO dbo.T1(col1) VALUES(@i);
  SET @i += 1;
END;
COMMIT TRAN;

SQL Server 了解日志缓存刷新_SQL_02

在我的系统上，30秒内完成操作，并触发了1756 次日志刷新。但是，在典型的OLTP工作负载中，你实际上没有选择将来自不同会话的许多小事务替换为来自同一会话的一个大事务。不过在一些ETL、大量数据变更等场景，DBA可以人为写脚本控制，或给相关人员提供建议。

完全持久事务VS延迟持久事务

从SQL Server 2014开始，你可以使用事务的延迟持久性特性，它允许你通过牺牲常规的完全持久性保证来提高具有许多小事务的工作负载的性能，即使是由不同的会话提交也能实现。当提交延迟的持久事务后，一旦提交日志记录被写入日志缓冲区，SQL Server就会确认提交，而不会触发日志缓冲区刷新。由于前面提到日志缓冲区被填满时，日志缓冲区都会被刷新，但在延迟的持久事务提交时不会刷新。

在使用这个特性之前，你需要非常仔细地考虑它是否适合你的场景。就性能而言，它的仅在具有大量小事务的工作负载中效果显著。如果你的工作负载一开始主要涉及大型事务，那么你可能不会看到任何性能优势。更重要的是，你需要认识到有数据丢失的可能性。假设应用程序提交了一个延迟的持久事务。提交记录被写入日志缓冲区并立即得到确认。如果SQL Server在刷新日志缓冲区之前发生电源故障，那么在重新启动后，恢复过程将撤销事务所做的所有更改，即使应用程序认为事务已提交。

那么，什么时候可以使用这个功能呢？一个明显的情况是数据丢失不是问题。另一种情况是，在重新启动SQL Server 服务之后，你可以确定哪些更改没有入库，并且能够重新生成它们，避免数据丢失。如果你的情况不属于这两种情况之一，尽管有些性能好处，也不要使用这个特性。

要使用延迟的持久事务，需要设置一个名为DELAYED_DURABILITY 的数据库选项。这个选项可以设置为以下三个值之一:

禁用Disabled (默认)：数据库中的所有事务都是完全持久的，因此每次提交都会触发日志缓冲区刷新

强制Forced：数据库中的所有事务都是延迟的持久事务，因此提交不会触发日志缓冲区刷新

允许Allowed：事务是完全持久的，提交会触发日志缓冲区刷新。但是，如果在COMMIT TRAN语句或（本机编译过程的）原子块中使用DELAYED_DURABILITY = ON选项，则该特定事务将被持久延迟，因此提交它不会触发日志缓冲区刷新

刚才测试的例子都禁用了延迟持久化，现在开启延迟持久化再测试一遍：

ALTER DATABASE TEST SET DELAYED_DURABILITY = Forced;
DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  BEGIN TRAN
    INSERT INTO dbo.T1(col1) VALUES(@i);
  COMMIT TRAN;
  SET @i += 1;
END;

或者，你可以在数据库级别使用Allowed模式，然后在COMMIT TRAN命令中添加WITH (DELAYED_DURABILITY = ON)。

SQL Server 了解日志缓存刷新_服务器_03

在我的系统上，操作花了44秒完成，并触发了76886次日志刷新。这比作为一个大事务运行工作时间30秒要长，因为会生成更多的日志记录（记住，每个事务，一个用于开始事务，一个用于更改，一个用于提交事务）。但是，这比使用1000000个完全持久事务完成工作所需的222秒要快得多，因为日志刷新的次数从超过1000000次降到了不足80000次。另外，使用延迟的持久性，即使事务是从不同的会话提交的，也可以获得性能上的提高。

为了证明在处理大型事务时使用延迟持久性没有任何好处，在上次测试的准备部分保持相同的代码，并在实际工作部分使用以下代码:

BEGIN TRAN;
DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  INSERT INTO dbo.T1(col1) VALUES(@i);
  SET @i += 1;
END;
COMMIT TRAN;

SQL Server 了解日志缓存刷新_服务器_04

现在我汇总一个表格，看看对比情况。

SQL Server 了解日志缓存刷新_sql_05

存储类内存

这些技术本人并没有使用过，只是了解一点。

延迟持久性特性可以显著提高oltp风格工作负载的性能，这些工作负载涉及大量需要高频率和低延迟的小事务，但可能有数据丢失风险。如果不允许任何数据丢失，又希望获得类似延迟耐久性的性能提升，即日志缓冲区不会在每次提交时刷新，而是在它被填满时刷新，该怎么办呢？

你可以在SQL Server 2016 SP1或更高版本中通过使用存储类内存（即NVDIMM-N非易失性存储）来实现这一点，能够将事务提交时间（延迟）最多加快 2-4 倍。这个硬件本质上是一个内存模块，为你提供内存级的性能，但那里的信息是持久的，因此在电源关闭时不会丢失。SQL Server 2016 SP1中的新增功能允许你将日志缓冲区配置为此类硬件上的持久日志缓冲区。要做到这一点，你需要在Windows中将SCM设置为卷，并将其格式化为直接访问模式（DAX）的卷。然后使用ALTER DATABASE <db> ADD LOG FILE 命令将日志文件添加到数据库。而SQL Server则识别出它是一个DAX卷，并将日志缓冲区视为该卷上的持久日志。事务提交事件不再触发日志缓冲区刷新，而是一旦在日志缓冲区中记录了提交，SQL Server就知道它实际上被持久化了，因此将控制权返回给调用者。当日志缓冲区填满时，SQL Server将其刷新到传统存储上的事务日志文件中。

使用 Windows Server 2016/SQL Server 2016 SP1 中的存储类内存加速事务提交延迟：

https://docs.microsoft.com/zh-cn/archive/blogs/sqlserverstorageengine/transaction-commit-latency-acceleration-using-storage-class-memory-in-windows-server-2016sql-server-2016-sp1

奇怪的是，SQL Server 2019增强了对存储类内存的支持，而不仅仅是持久日志缓存场景。它支持在此类硬件上放置数据文件、日志文件和In-Memory OLTP检查点文件。你需要做的就是在操作系统级别将其公开为卷，并且将其格式化为DAX。SQL Server 2019自动识别这一技术，并以一种开明的模式工作直接访问设备，绕过操作系统的存储堆栈。

用户数据库VS tempdb

每次重新启动SQL Server时，都会将tempdb数据库作为模型数据库的新副本重新创建。因此，写入临时表、表变量的任何数据重启后就都没了。知道了这一点，SQL Server可以放松大量与日志记录相关的需求。例如，无论是否启用延迟持久性选项，提交事件都不会触发日志缓冲区刷新。此外，需要记录的信息量也减少了，因为SQL Server只需要足够的信息来支持回滚事务或撤消工作，而不需要前滚事务或重做工作。因此，与用户数据库中的对象时更改相比，tempdb中对象更改的事务日志记录往往更小。

为了演示这一点，我们将运行与之前相同的测试，只是这次是在tempdb中。当数据库选项DELAYED_DURABILITY设置为Disabled(默认)时，我们将首先测试许多小事务。

DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  BEGIN TRAN
    INSERT INTO dbo.T1(col1) VALUES(@i);
  COMMIT TRAN;
  SET @i += 1;
END;

SQL Server 了解日志缓存刷新_Server_06

这项工作生成了5098个日志刷新，耗时19秒完成。相比之下，在一个具有完整持久性的用户数据库中，日志刷新次数超过100万次，用时222秒。这甚至比用户数据库中的延迟持久性（76886个日志刷新和44秒）更好，因为日志记录的大小减少了。

要测试一个大型事务，保持准备部分不变，并在实际工作部分使用以下代码:

BEGIN TRAN;
DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  INSERT INTO dbo.T1(col1) VALUES(@i);
  SET @i += 1;
END;
COMMIT TRAN;

日常缓存刷新了1228，运行时间为7秒。由于tempdb中的日志记录大小减少，因此与用户数据库相比，tempdb的日志刷新更少。

你也可以尝试将DELAYED_DURABILITY选项设置为Forced来运行测试，但这对tempdb没有影响，因为如前所述，无论如何提交事件不会在tempdb中触发日志刷新。

SQL Server 了解日志缓存刷新_SQL_07

序列对象缓存

触发日志缓冲区刷新的情况可能与序列对象缓存选项有关。以下面的序列定义为例:

CREATE SEQUENCE dbo.Seq1 AS BIGINT MINVALUE 1 CACHE 50; -- the default cache size is 50;

每次你需要一个新的序列值时，你可以使用NEXT value FOR函数，像这样:

SELECT NEXT VALUE FOR dbo.Seq1;

CACHE属性是一个性能特性。如果没有它，每次请求新的序列值时，SQL Server都必须将当前值写入磁盘以进行保留。实际上，这就是使用NO CACHE模式时的行为。相反，当该选项设置为大于零的值时，SQL Server在每次缓存大小的请求中只向磁盘写入一次保留值，写入磁盘上的系统表中。SQL Server在内存中维护两个成员，一个保存当前值，另一个保存剩余数量。在断电情况下，SQL Server在重新启动时，将当前序列值设置为保留值。

用一个例子来解释更方便立即。考虑上面的序列定义，将CACHE选项设置为50（默认值）。你可以通过运行上面的SELECT语句第一次请求一个新的序列值。SQL Server将上述成员设置为以下值:

磁盘保留值: 50,内存当前值: 1, 内存剩余数: 49, 获取值: 1

另外49个请求不会访问磁盘，而是更新内存成员。总共50个请求后，成员被设置为以下值:

磁盘保留值: 50,内存当前值: 50, 内存剩余数: 0 获取值:50

再次请求一个新的序列值，这将触发对保留值100的磁盘写操作。然后将成员设置为以下值:

磁盘保留值: 100,内存当前值: 51, 内存剩余数: 49, 获取值: 51

如果此时系统电源故障，重新启动后，系统将当前序列值设置为100（从磁盘恢复的值）。对序列值的下一个请求产生101（将保留值150写入磁盘）。你失去了52到100范围内的所有值。由于SQL Server进程异常终止，最多可能损失的值与缓存大小一样多。缓存数量越多，对保留值的磁盘写操作就越少，因此性能就越好。同时，在停电的情况下，两个序列值之间可以产生的间隙越大。

所有这些都非常简单，也许你对它的工作原理非常熟悉。但是，每次SQL Server向磁盘写入一个新的保留值（上面示例中，每50个请求一次）时，它还会刷新日志缓冲区。而identity属性的列却不是这样，尽管SQL Server内部使用了与序列对象相同的缓存特性，但它不允许你控制其大小。默认情况下，BIGINT和NUMERIC的大小为10000，INT为1000，SMALLINT为100，TINYINT为10。如果需要，可以使用跟踪标志272或IDENTITY_CACHE配置关闭它（2017+）。SQL Server在将与标识缓存相关的保留值写入磁盘时不需要刷新日志缓冲区，原因是，只有在向表中插入一行时才能创建新的标识值。在电源故障的情况下，当系统重新启动时，作为数据库恢复过程的一部分，由未提交的事务插入到表中的行将从表中取出。因此，即使在重新启动SQL Server后生成与未提交事务中创建的相同标识值一样的标识值，也不会出现重复，因为该行已从表中取出。如果事务已提交，这将触发日志刷新，这也将持久写入与缓存相关的保留值。这没必要必须在每次将保留值写入与标识缓存相关的磁盘时刷新日志缓冲区。

对于序列对象，情况是不同的。应用程序可以请求一个新的序列值而不用将其存储在数据库中。如果在一个没有提交的事务中创建了一个新的序列值后出现电源故障，为了避免在重新启动后创建与先前生成的序列值相等的新序列值，SQL Server在每次将新的序列缓存相关的恢复值写入磁盘时强制刷新日志。当然也有例外，当序列对象在tempdb中创建时，当然不需要这样的日志刷新，因为在系统重启后，都会重新创建tempdb。

当使用非常小的序列缓存大小时，或者在一个事务中生成大量序列值时，频繁的日志刷新对性能的负面影响特别明显。例如，当向表中插入大量行时，如果没有序列，事务将在日志缓冲区填满时刷新日志缓冲区，并在事务提交时再刷新一次。但是，使用序列时，每次在磁盘上写入恢复值时都会刷新日志。这就是为什么要避免使用较小的缓存大小，更不用说NO cache模式了。

为了演示这一点，用以下代码测试:

SET NOCOUNT ON;
USE test;
ALTER DATABASE test SET DELAYED_DURABILITY = Disabled; -- try Disabled, Forced
DROP TABLE IF EXISTS dbo.T1;
DROP SEQUENCE IF EXISTS dbo.Seq1;
CREATE SEQUENCE dbo.Seq1 AS BIGINT MINVALUE 1 CACHE 50; -- try NO CACHE, CACHE 50, CACHE 10000
CREATE TABLE dbo.T1(col1 INT NOT NULL);
GO
DECLARE @logflushes AS INT, @starttime AS DATETIME2, @duration AS INT;
SET @logflushes = ( SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Flushes/sec' AND instance_name = 'TEST' );
SET @starttime = SYSDATETIME();
BEGIN TRAN;
DECLARE @i AS INT = 1;
WHILE @i <= 1000000
BEGIN
  INSERT INTO dbo.T1(col1) SELECT NEXT VALUE FOR dbo.Seq1;
  SET @i += 1;
END;
COMMIT TRAN;
SET @duration = DATEDIFF(second, @starttime, SYSDATETIME());
SET @logflushes = ( SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Log Flushes/sec' AND instance_name = 'TEST' ) - @logflushes;
SELECT  @duration AS durationinseconds, @logflushes AS logflushes;
GO