在 SQL Server 中扩展数据可靠性的日志记录和数据存储算法的说明

项目
12/14/2024

原始产品版本： SQL Server 2014、SQL Server 2012、SQL Server 2008、SQL Server 2005
原始 KB 数： 230785

总结

本文讨论如何Microsoft SQL Server 日志记录和数据算法扩展数据可靠性和完整性。

若要详细了解引擎的基础概念以及恢复和隔离利用语义算法（ARIES），请参阅数据库系统上的以下 ACM 事务文档（1992 年 3 月第 1 卷第 1 卷下）：

外部链接： ARIES：使用预写日志记录支持精细锁定和部分回滚的事务恢复方法

本文档介绍与故障相关的 SQL Server 技术，以扩展数据可靠性和完整性。

建议阅读 Microsoft 知识库中的以下文章，了解有关缓存和备用故障模式讨论的详细信息：

本文中使用的术语

在开始深入讨论之前，下表定义了本文中使用的一些术语。

术语	定义
电池支持	直接可用的独立和本地化电池备份设施，并受缓存机制控制，以防止数据丢失。这不是一个不间断的电源（UPS）。 UPS 不保证任何写入活动，并且可以从缓存设备断开连接。
缓存	用于优化物理 I/O 操作并提高性能的中介存储机制。
脏页	包含尚未刷新到稳定存储的数据修改的页面。有关脏页缓冲区的详细信息，请参阅 SQL Server 联机丛书中的“写入页面 ”。内容也适用于 sql Server 2012 及更高版本Microsoft。
失败	任何可能导致 SQL Server 进程意外中断的任何内容。示例包括：电源中断、计算机重置、内存错误、其他硬件问题、坏扇区、驱动器中断、系统故障等。
刷新	强制将缓存缓冲区强制到稳定的存储。
闩锁	用于保护资源物理一致性的同步对象。
非易失性存储	在系统故障中保持可用的任何介质。
固定页	数据缓存中保留的页面，在稳定存储位置中保护所有相关日志记录之前，无法刷新到稳定存储中。
稳定存储	与非易失性存储相同。
易失性存储	任何在失败时不会保持不变的介质。

预写日志记录（WAL）协议

术语协议是描述 WAL 的绝佳方法。这是一组特定的和定义的实现步骤，以确保数据已正确存储和交换，并在发生故障时可以恢复到已知状态。正如网络包含一个定义的协议，用于以一致且受保护的方式交换数据一样，WAL 也描述了用于保护数据的协议。

ARIES 文档定义 WAL，如下所示：

WAL 协议断言，表示对某些数据的更改的日志记录必须已位于稳定的存储中，然后才允许更改的数据替换非易失性存储中的以前版本数据。也就是说，系统不允许将更新的页面写入到页面的非易失性存储版本，直到至少撤消日志记录的撤消部分，该部分描述页的更新已写入稳定存储。

有关预写日志记录的详细信息，请参阅 SQL Server 联机丛书中的 “预写事务日志 ”主题。

SQL Server 和 WAL

SQL Server 使用 WAL 协议。为了确保事务正确提交，与事务关联的所有日志记录都必须在稳定的存储中受到保护。

若要阐明这种情况，请考虑以下特定示例。

注意

对于此示例，假定没有索引，受影响的页面为第 150 页。

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

接下来，将活动分解为简单日志记录步骤，如下表所述。

语句	执行的操作
BEGINTRANSACTION	写入日志缓存区域。但是，不需要刷新到稳定存储，因为 SQL Server 没有进行任何物理更改。
INSERT INTO tblTest	1. 数据页 150 检索到 SQL Server 数据缓存（如果尚不可用）。 2.页面已锁定、固定和标记脏，并获取适当的锁。 3.生成插入日志记录并将其添加到日志缓存中。 4.向数据页添加新行。 5. 闩锁释放。 6.此时无需刷新与事务或页面关联的日志记录，因为所有更改都保留在易失性存储中。
COMMIT TRANSACTION	1.形成提交日志记录，并且必须将与事务关联的日志记录写入稳定存储。在将日志记录正确分配给稳定存储之前，不会将事务视为已提交。 2. 数据页 150 保留在 SQL Server 数据缓存中，不会立即刷新到稳定存储。正确保护日志记录后，恢复可以恢复操作（如有必要）。 3. 释放事务性锁。

不要被术语“锁定”和“日志记录”混淆。尽管处理 WAL 时，重要、锁定和日志记录是单独的问题。在前面的示例中，SQL Server 通常保留第 150 页的闩锁，以便执行页面上的物理插入更改，而不是事务的整个时间。建立适当的锁类型，以便根据需要保护行、范围、页或表。有关锁定类型的更多详细信息，请参阅 SQL Server 联机丛书锁定部分。

更详细地查看该示例，你可能会询问 LazyWriter 或 CheckPoint 进程运行时会发生什么情况。 SQL Server 针对与脏页和固定页关联的事务日志记录，向稳定存储发出所有适当的刷新。这可确保在刷新关联的事务日志记录之前，无法将 WAL 协议数据页写入稳定存储。

SQL Server 和稳定存储

SQL Server 通过了解磁盘扇区大小（通常为 4,096 字节或 512 字节）来增强日志和数据页操作。

若要维护事务的 ACID 属性，SQL Server 必须考虑故障点。在发生故障期间，许多磁盘驱动器规范仅保证有限数量的扇区写入操作。大多数规范都保证在发生故障时完成单个扇区写入。

SQL Server 在扇区大小的倍数上使用 8 KB 的数据页和日志（如果刷新）。（大多数磁盘驱动器使用 512 字节作为默认扇区大小。如果发生故障，SQL Server 可以通过采用日志奇偶校验和撕裂写入技术来考虑大于扇区的写入操作。

撕裂页面检测

此选项允许 SQL Server 检测因电源故障或其他系统中断而导致的 I/O 操作不完整。如果为 true，则每当将页面写入磁盘时，就会在 8 千字节（KB）数据库页中为每个 512 字节扇区翻转一个位。如果某位在 SQL Server 稍后读取页面时处于错误状态，则页面写入错误;检测到已撕裂的页面。在恢复期间检测到撕裂页，因为任何未正确写入的页面都可能是由恢复读取的。

尽管 SQL Server 数据库页为 8 KB，但磁盘使用 512 字节扇区执行 I/O 操作。因此，每个数据库页写入 16 个扇区。如果系统发生故障（例如，由于电源故障）在操作系统将前 512 字节扇区写入磁盘和完成 8 KB I/O 操作之间，可能会发生撕裂页。如果在失败之前成功写入了数据库页的第一个扇区，则磁盘上的数据库页将显示为更新，尽管它可能未成功。

通过使用电池支持的磁盘控制器缓存，可以确保数据已成功写入磁盘或根本不写入。在这种情况下，请勿将撕裂的页面检测设置为“true”，因为这不是必要的。

注意

默认情况下，SQL Server 中未启用撕裂页检测。有关详细信息，请参阅 ALTER DATABASE SET 选项 (Transact-SQL)。

日志奇偶校验

日志奇偶校验检查类似于已撕裂的页面检测。每个 512 字节扇区都包含奇偶校验位。这些奇偶校验位始终使用日志记录写入，并在检索日志记录时进行评估。通过在 512 字节边界上强制写入日志，SQL Server 可以确保将提交操作写入物理磁盘扇区。

性能影响

所有版本的 SQL Server 都使用 Win32 CreateFile 函数打开日志和数据文件。 dwFlagsAndAttributes 成员在 SQL Server 打开时包含 FILE_FLAG_WRITE_THROUGH 该选项。

FILE_FLAG_WRITE_THROUGH 指示系统通过任何中间缓存进行写入，并直接转到磁盘。系统仍可以缓存写入操作，但无法延迟对它们的刷新。

该 FILE_FLAG_WRITE_THROUGH 选项可确保当写入操作返回成功完成时，数据正确存储在稳定存储中。这与 WAL 协议保持一致，可确保数据。

许多磁盘驱动器（SCSI 和 IDE）包含 512 KB、1 MB 或更大的载入缓存。但是，驱动器缓存通常依赖于电压，而不是电池支持的解决方案。这些缓存机制不能保证在电源周期间或类似故障点之间进行写入。它们只保证完成扇区写入操作。这正是 SQL Server 7.0 及更高版本中内置了撕裂的写入和日志奇偶校验检测的原因。随着驱动器的大小继续增长，缓存会变大，在发生故障期间，它们可能会公开更大的数据量。

许多硬件供应商提供电池支持的磁盘控制器解决方案。这些控制器缓存可以保留缓存中的数据数天，甚至允许将缓存硬件放置在第二台计算机中。正确还原电源后，将刷新未写入的数据，然后才允许进一步的数据访问。其中许多允许建立读取缓存与写入缓存的百分比，以实现最佳性能。有些包含较大的内存存储区域。事实上，对于市场的特定细分市场，一些硬件供应商为高端电池支持的磁盘缓存控制器系统提供 6 GB 缓存。这些可大幅度提高数据库性能。

高级缓存实现将通过不禁用控制器缓存来处理 FILE_FLAG_WRITE_THROUGH 请求，因为它们可以在系统重置、电源故障或其他故障点时提供真正的重写功能。

无需使用缓存的 I/O 传输可能更长，因为移动驱动器头、旋转速率和其他限制因素所需的机械时间。

扇区排序

用于提高 I/O 性能的常见技术是扇区排序。为了避免机械头部移动，可对读/写请求进行排序，从而允许头部更一致的运动来检索或存储数据。

缓存可以同时保存多个日志和数据写入请求。 WAL 协议的 WAL 协议和 SQL Server 实现需要先将日志写入刷新到稳定存储，然后才能发出页面写入。但是，使用缓存可能会从日志写入请求中返回成功，而不会将数据写入实际驱动器（即写入稳定存储）。这可能会导致发出数据页写入请求的 SQL Server。

在写入缓存参与的情况下，数据仍被视为在易失性存储中。但是，从 Win32 API WriteFile 调用中，SQL Server 看到的确切方式是获取成功的返回代码。 SQL Server 或使用 WriteFile API 调用的任何进程只能确定数据已正确获取稳定存储。

出于讨论目的，假定数据页的所有扇区在匹配日志记录的扇区之前进行写入。这立即违反了 WAL 协议。缓存在日志记录之前写入数据页。除非缓存完全支持电池，否则故障可能会导致灾难性结果。

评估数据库服务器的最佳性能因素时，需要考虑许多因素。最重要的是，“我的系统是否允许有效 FILE_FLAG_WRITE_THROUGH 功能？”

注意

你正在使用的任何缓存都必须完全支持电池支持的解决方案。所有其他缓存机制都容易损坏和数据丢失。 SQL Server 通过启用 FILE_FLAG_WRITE_THROUGH来尽一切努力确保 WAL。

测试表明，许多磁盘驱动器配置可能包含写入缓存，而无需适当的电池备份。 SCSI、IDE 和 EIDE 驱动器充分利用写入缓存。有关 SSD 如何与 SQL Server 协同工作的详细信息，请参阅以下 CSS SQL Server 工程师博客文章：

SQL Server 和 SSD - RDORR 的学习说明 - 第 1 部分

在许多配置中，正确禁用 IDE 或 EIDE 驱动器的写入缓存的唯一方法是使用特定制造商实用工具或使用位于驱动器本身上的跳线。若要确保为驱动器本身禁用写入缓存，请联系驱动器制造商。

SCSI 驱动器还具有写入缓存。但是，这些缓存通常可由操作系统禁用。如有任何问题，请联系驱动器制造商获取相应的实用工具。

写入缓存堆栈

写入缓存堆栈类似于扇区排序。以下定义直接从领先的 IDE 驱动器制造商的网站获取：

通常，此模式处于活动状态。写入缓存模式接受主机将数据写入缓冲区，直到缓冲区已满或主机传输完成。

磁盘写入任务开始将主机数据存储到磁盘。继续接受主机写入命令，并将数据传输到缓冲区，直到写入命令堆栈已满或数据缓冲区已满。驱动器可以重新排序写入命令以优化驱动器吞吐量。

自动写入重新分配（AWR）

用于保护数据的另一种常见技术是在数据操作过程中检测不良扇区。以下说明来自领先的 IDE 驱动器制造商网站：

此功能是写入缓存的一部分，可降低延迟写入操作期间数据丢失的风险。如果在磁盘写入过程中发生磁盘错误，磁盘任务将停止，可疑扇区将重新分配到位于驱动器末尾的备用扇区池。重新分配后，磁盘写入任务将一直持续到完成。

如果为缓存提供电池备份，则此功能可能是一项强大的功能。这在重启时提供适当的修改。最好检测磁盘错误，但 WAL 协议的数据安全性将再次要求实时完成，而不是以延迟的方式完成。在 WAL 参数中，AWR 技术无法解释日志写入失败的情况，因为扇区错误，但驱动器已满。数据库引擎必须立即知道故障，以便可以正确中止事务，可以发出警报，并采取正确的步骤来保护数据并更正媒体故障情况。

数据安全

数据库管理员应采取一些预防措施来确保数据的安全性。

确保备份策略足以从灾难性故障中恢复，这始终是个好主意。异地存储和其他预防措施是适当的。
经常在辅助数据库或测试数据库中测试数据库还原操作。
确保任何缓存设备都可以处理所有故障情况（电源中断、故障扇区、坏驱动器、系统中断、锁定、电源峰值等）。
确保缓存设备：
- 已集成电池备份
- 可以在开机时重新发出写入
- 如有必要，可以完全禁用
- 实时处理坏扇区重新映射
启用撕裂的页面检测。（这对性能影响不大。
配置 RAID 驱动器，允许热交换坏磁盘驱动器（如果可能）。
使用较新的缓存控制器，无需重启 OS 即可添加更多磁盘空间。这可以是理想的解决方案。

测试驱动器

若要完全保护数据，应确保正确处理所有数据缓存。在许多情况下，必须禁用磁盘驱动器的写入缓存。

注意

确保备用缓存机制可以正确处理多种类型的故障。

Microsoft使用 SQLIOSim 实用工具对多个 SCSI 和 IDE 驱动器执行了测试。此实用工具对模拟的数据设备和日志设备模拟繁重的异步读取/写入活动。测试性能统计信息显示禁用写入缓存的驱动器每秒平均写入操作数介于 50 到 70 之间，RPM 范围为 5,200 到 7,200。

有关实用工具 SQLIOSim 的详细信息，请参阅Microsoft知识库中的以下文章：

如何使用 SQLIOSim 实用工具模拟磁盘子系统上的 SQL Server 活动

许多计算机制造商通过禁用写入缓存来订购驱动器。但是，测试表明这种情况可能并不总是如此。因此，始终完全测试。

数据设备

在非记录的所有情况下，SQL Server 只需要刷新日志记录。执行非记录操作时，还必须将数据页刷新到稳定的存储;在发生故障时，没有单独的日志记录可重新生成操作。

数据页可以保留在缓存中，直到 LazyWriter 或 CheckPoint 进程将其刷新到稳定的存储。使用 WAL 协议确保正确存储日志记录，确保恢复可以将数据页恢复到已知状态。

这并不意味着建议将数据文件放在缓存的驱动器上。当 SQL Server 将数据页刷新到稳定存储时，可以从事务日志中截断日志记录。如果数据页存储在易失性缓存上，则可能会截断在发生故障时用于恢复页的日志记录。确保数据和日志设备都能正确容纳稳定的存储。

提高性能

可能出现的第一个问题是：“我有一个正在缓存的 IDE 驱动器。但是，当我禁用它时，我的表现变得低于预期。为什么？

Microsoft测试的许多 IDE 驱动器以 5,200 RPM 运行，SCSI 驱动器为 7,200 RPM。禁用 IDE 驱动器的写入缓存时，机械性能可能会成为一个因素。

为了解决性能差异问题，要遵循的方法是明确的：“处理事务速率”。

许多联机事务处理（OLTP）系统都需要较高的事务速率。对于这些系统，请考虑使用缓存控制器，该控制器可以适当地支持写入缓存，并提供所需的性能提升，同时仍确保数据完整性。

为了观察缓存驱动器上的 SQL Server 中发生的重大性能更改，使用小型事务提高了事务速率。

测试显示，小于 512 KB 或大于 2 MB 的缓冲区的高写入活动可能会导致性能缓慢。

请考虑以下示例：

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

下面是 SQL Server 的示例测试结果：

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

在所有配置中，将整个一 INSERT 系列操作包装成单个事务的过程大约在四秒内运行。这是因为需要日志刷新的数量。如果未创建单个事务，则每个 INSERT 事务都会作为单独的事务进行处理。因此，必须刷新事务的所有日志记录。每个刷新大小为 512 字节。这需要大量的机械驱动干预。

使用单个事务时，可以捆绑事务的日志记录，并使用单个较大的写入来刷新收集的日志记录。这大大减少了机械干预。

警告

建议不要增加事务范围。长时间运行的事务可能会导致过度和不需要的阻塞和增加开销。使用 SQL Server：Databases SQL Server 性能计数器查看基于事务日志的计数器。具体而言，日志字节刷新数/秒可以指示许多可能导致高机械磁盘活动的小型事务。

检查与日志刷新关联的语句，以确定是否可以减少 Log Bytes Flushed/sec 值。在前面的示例中，使用了单个事务。但是，在许多情况下，这可能会导致意外锁定行为。检查事务的设计。可以使用类似于以下代码的代码来运行批处理，以减少频繁和较小的日志刷新活动：

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server 要求系统支持保证传送到稳定媒体，如 SQL Server I/O 可靠性计划评审要求下载文档中所述。有关 SQL Server 数据库引擎的输入和输出要求的详细信息，请参阅Microsoft SQL Server 数据库引擎输入/输出要求。

通过

在 SQL Server 中扩展数据可靠性的日志记录和数据存储算法的说明

总结

本文中使用的术语

预写日志记录（WAL）协议

SQL Server 和 WAL

SQL Server 和稳定存储

撕裂页面检测

日志奇偶校验

性能影响

扇区排序

写入缓存堆栈

自动写入重新分配（AWR）

数据安全

测试驱动器

数据设备

提高性能

反馈

其他资源

通过

在 SQL Server 中扩展数据可靠性的日志记录和数据存储算法的说明

总结

本文中使用的术语

预写日志记录 （WAL） 协议

SQL Server 和 WAL

SQL Server 和稳定存储

撕裂页面检测

日志奇偶校验

性能影响

扇区排序

写入缓存堆栈

自动写入重新分配 （AWR）

数据安全

测试驱动器

数据设备

提高性能

反馈

其他资源

预写日志记录（WAL）协议

自动写入重新分配（AWR）