通过

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

  • 项目

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

摘要

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

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

外部链接： ARIES：支持使用 Write-Ahead 日志记录 Fine-Granularity 锁定和部分回滚的事务恢复方法

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

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

本文中使用的术语

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

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

Write-Ahead 日志记录 (WAL) 协议

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

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

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

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

SQL Server和 WAL

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

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

注意

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

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

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

语句 执行的操作
BEGIN TRANSACTION 写入日志缓存区域。 但是，不需要刷新到稳定的存储，因为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，则每次将页面写入磁盘时，它都会) 数据库页 (KB 的每个 512 字节扇区发生翻转。 如果稍后SQL Server读取页面时某个位处于错误状态，则表示页面写入错误;检测到页面撕裂。 在恢复期间检测到撕裂的页面，因为恢复可能会读取错误写入的任何页。

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

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

注意

默认情况下，在 SQL Server 中不启用页面撕裂检测。 有关详细信息，请参阅 ALTER DATABASE SET Options (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 驱动器执行了测试。 此实用工具模拟模拟模拟数据设备和日志设备的大量异步读/写活动。 测试性能统计信息显示，禁用写入缓存且 RPM 范围为 5,200 到 7,200 的驱动器每秒平均写入操作数介于 50 到 7200 之间。

有关该 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 操作包装到单个事务的过程大约在 4 秒内运行。 这是因为所需的日志刷新次数。 如果不创建单个事务，则每个事务都 INSERT 作为单独的事务进行处理。 因此，必须刷新事务的所有日志记录。 每次刷新的大小为 512 字节。 这需要大量的机械驱动干预。

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

警告

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

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

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 数据库引擎输入/输出要求。