通过

SQL Server I/O 基础知识

  • 项目

适用于：SQL ServerAzure SQL 托管实例Azure VM 上的 SQL Server

SQL Server 数据库的主要用途是存储和检索数据，因此，大量磁盘输入/输出 (I/O) 是该数据库引擎的一个核心特点。 由于磁盘 I/O 操作可能会占用消耗很多资源并且耗时较长，所以 SQL Server 侧重于使 I/O 极为高效。

SQL Server 的存储子系统具有多种外形规格，包括机械驱动器和固态存储。 本文详细介绍如何使用驱动器缓存原则来改进数据库引擎 I/O。

SQL Server 要求系统支持对稳定介质进行有保证的传递，如 SQL Server I/O 可靠性计划要求中所述。 有关 SQL Server 数据库引擎的输入和输出要求的详细信息，请参阅 SQL Server 数据库引擎磁盘输入/输出 (I/O) 要求。

磁盘 I/O

缓冲区管理器仅对数据库执行读写操作。 其他文件和数据库操作（如打开、关闭、扩展和收缩）则由数据库管理器和文件管理器组件执行。

缓冲区管理器的磁盘 I/O 操作具有以下特点：

  • I/O 操作通常异步执行。这样，在后台进行 I/O 操作的同时，即可调用线程继续处理。 在某些情况下（例如，日志 I/O 未对齐），可能会发生同步 I/O 操作。

  • 所有 I/O 操作均在调用线程中发出，除非 affinity I/O 选项处于使用状态。 关联 I/O 掩码 选项将 SQL Server 磁盘 I/O 绑定到指定的 CPU 子集。 在高端 SQL Server 联机事务处理 (OLTP) 环境中，此扩展可以提高 SQL Server 线程执行 I/O 的性能。

  • 可通过散播-聚集 I/O 实现多页 I/O，散播-聚集 I/O 允许数据传入或传出非连续内存区域。 这意味着 SQL Server 可以快速填充或刷新缓冲区缓存，同时避免多个物理 I/O 请求。

长时 I/O 请求

缓冲区管理器报告任何经过 15 秒或更长时间仍未完成的 I/O 请求。 这可以帮助系统管理员区分 SQL Server 问题和 I/O 子系统问题。 将报告错误消息 MSSQLSERVER_833 并且该消息在 SQL Server 错误日志中显示如下：

SQL Server has encountered ## occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [##] in database [##] (#). The OS file handle is 0x00000. The offset of the latest long I/O is: 0x00000.

长时 I/O 可以是读或写，不过当前消息并未指明。 长时 I/O 消息是警告而不是错误。 它们并不表示 SQL Server 存在问题，而是基础 I/O 系统存在问题。 报告这些消息是为了帮助系统管理员更快地找到 SQL Server 响应缓慢的原因，并找出 SQL Server 无法控制的问题。 因此，不需要执行任何操作，但系统管理员应调查 I/O 请求耗时很长的原因以及耗时是否合理。

长时 I/O 请求的原因

长时 I/O 消息可能指示 I/O 永久阻塞并且永远无法完成（称为“I/O 丢失”），或者只是它尚未完成。 虽然 I/O 丢失往往会导致闩锁超时，但无法根据消息确定是哪种情况。

长时 I/O 往往指示磁盘子系统的 SQL Server 工作负荷过于密集。 以下情况可能会指示磁盘子系统不足：

  • SQL Server 工作负荷很大时，错误日志中出现多个长时 I/O 消息。
  • 性能监视器计数器将显示磁盘长时间滞后、磁盘队列长或无磁盘空闲时间。

长时 I/O 还可能因以下原因所致：I/O 路径中的某个组件（如驱动程序、控制器或固件）不断延迟为早期 I/O 请求提供服务，而为更新的请求提供服务。 这可能发生在互连的环境中，例如 iSCSI 和光纤通道网络（由于配置错误或路径故障）。 它可能很难与性能监视器工具配合使用，因为多数 I/O 是立即获得服务的。 执行大量连续 I/O 的工作负荷可能会使长时 I/O 请求情况更严重，如备份和还原、表扫描、排序、创建索引、大容量加载以及清零文件。

单独出现的长时 I/O 如果与上述情况无关，则可能是由硬件或驱动程序问题所致。 系统事件日志可能会包含有助于进行问题诊断的相关事件。

低效查询或筛选器驱动程序导致的 I/O 性能问题

I/O 速度缓慢可能是由于查询未有效写入，或者未使用索引和统计信息进行正确优化。 I/O 延迟的另一个常见因素是存在防病毒软件或其他扫描数据库文件的安全程序。 这种扫描软件可能会扩展到网络层，从而增加网络延迟，进而间接影响数据库延迟。 尽管描述的大约 15 秒 I/O 延迟的情况在硬件组件中更为常见，但使用未优化查询或配置错误的防病毒程序，可以更频繁地观察到较低的 I/O 延迟。

有关如何解决这些问题的详细信息，请参阅排查 I/O 问题导致的 SQL Server 性能缓慢问题。

有关如何在 SQL Server 上配置防病毒软件保护的信息，请参阅配置防病毒软件以使用 SQL Server。

在存储控制器中写入缓存

在未使用缓存的情况下执行的 I/O 传输在机械驱动器中可能会明显更长，这是因为硬盘驱动器旋转速率、移动驱动器磁头所需的机械时间以及其他限制因素。 SQL Server 安装面向提供缓存控制器的系统。 这些控制器禁用磁盘上的缓存，并提供稳定的介质缓存以满足 SQL Server 的 I/O 要求。 它们通过使用缓存控制器的各种优化来避免存储查找和写入时间带来的性能问题。

注意

某些存储供应商使用永久性内存 (PMEM) 作为存储，而不是缓存，从而提高整体性能。 有关详细信息，请参阅为 Windows 上的 SQL Server 配置永久性内存 (PMEM)和为 Linux 上的 SQL Server 配置永久性内存 (PMEM)。

使用写入缓存（也称为回写式缓存）存储控制器可以提高 SQL Server 性能。 写入缓存控制器和存储子系统对于 SQL Server 是安全的，前提是它们设计用于数据关键型事务数据库管理系统 (DBMS) 环境。 如果发生系统故障，这些设计功能必须保留缓存的数据。 使用外部不间断电源 (UPS) 来实现这一点通常不够，因为可能会发生与电源无关的故障模式。

缓存控制器和存储子系统可以安全地供 SQL Server 使用。 包含这些的大多数新专用服务器平台都是安全的。 但是，你应与硬件供应商核实，确保存储子系统经过测试和批准，可以在数据关键型事务关系数据库管理系统 (RDBMS) 环境中使用。

预写日志

SQL Server 数据修改语句可生成逻辑页写入。 此写入流可描绘成两个位置：日志和数据库本身。 出于性能原因，SQL Server 通过自己的缓存缓冲区系统延迟对数据库的写入操作。 写入日志的操作只会暂时延迟到 COMMIT 时间。 它们的缓存方式与数据写入方式不同。 由于给定页的日志写入始终在页面的数据写入之前，因此日志有时称为预写日志 (WAL)。

预写日志记录 (WAL) 协议

术语协议是描述 WAL 的绝佳方法。 SQL Server 使用的 WAL 称为 ARIES（利用语义进行恢复和隔离的算法）。 若要了解更多信息，请参阅管理加速数据库恢复。

它是一组特定且已定义的实现步骤，确保正确存储和交换数据以及在发生故障时可以恢复到已知状态需要这些步骤。 正如网络包含一个定义的协议，用于以一致且受保护的方式交换数据一样，WAL 也描述了用于保护数据的协议。 所有版本的 SQL Server 都使用 Win32 CreateFile 函数打开日志和数据文件。 dwFlagsAndAttributes 成员包括 SQL Server 打开时的 FILE_FLAG_WRITE_THROUGH 选项。

FILE_FLAG_WRITE_THROUGH

SQL Server 使用 FILE_FLAG_WRITE_THROUGH 标志创建其数据库文件。 此选项指示系统通过任何中间缓存进行写入，并直接进入存储。 系统仍可以缓存写入操作，但无法延迟对它们的刷新。 有关详细信息，请参阅 CreateFileA。

FILE_FLAG_WRITE_THROUGH 选项可确保当写入操作返回成功完成时，数据被正确存储在稳定存储中。 这与预写日志记录 (WAL) 协议规范保持一致，以确保数据。 许多存储设备（NVMe、PCIe、SATA、ATA、SCSI 和基于 IDE 的设备）包含 512 KB、1 MB 和更大的载入缓存。 存储缓存通常依赖于电容，而不是电池支持的解决方案。 这些缓存机制不能保证在电源周期间或类似故障点之间进行写入。 它们只保证完成扇区写入操作。 随着存储设备的大小不断增长，缓存会变大，在发生故障期间，它们可能会暴露更大的数据量。

有关 Linux 分发版的 FUA 支持的更多信息及其对 SQL Server 的影响，请参阅 Linux 上的 SQL Server：强制单元访问 (FUA) 内部结构。

事务完整性和 SQL Server 恢复

事务完整性是关系数据库系统的基本概念之一。 事务被视为完全应用或完全回滚的原子性工作单元。 SQL Server 预写事务日志是实现事务完整性的重要组件。

任何关系数据库系统还必须处理与事务完整性密切相关的概念，即从计划外的系统故障中恢复。 一些非理想的真实效果可能会导致这种失败。 在许多数据库管理系统上，系统故障可能会导致冗长的人工指导的手动恢复过程。

相比之下，SQL Server 恢复机制是自动的，无需人工干预即可运行。 例如，SQL Server 可以支持任务关键型生产应用程序，并会因瞬间电源波动而遇到系统故障。 恢复电源后，服务器硬件将重启，网络软件将加载并初始化，且 SQL Server 将重启。 当 SQL Server 初始化时，它会根据事务日志中的数据自动运行其恢复过程。 整个过程无需人工干预即可完成。 每当客户端工作站重启时，用户都会发现其所有数据都存在，最多他们输入的最后一个事务为止。

SQL Server 中的事务完整性和自动恢复构成了强大的省时省力功能。 如果写入缓存控制器未正确设计用于数据关键型事务 DBMS 环境，则可能会损害 SQL Server 恢复的能力，从而损坏数据库。 如果控制器拦截 SQL Server 事务日志写入，并在控制器板上的硬件缓存中缓冲它们，但在系统故障期间不保留这些写入页，则可能会出现这种情况。

写入缓存和存储设备控制器

大多数存储设备缓存控制器执行写入缓存。 不能始终禁用写入缓存功能。

即使服务器使用 UPS，也不能保证缓存写入的安全性。 会发生 UPS 无法解决的许多类型的系统故障。 例如，内存奇偶校验错误、操作系统 (OS) 陷阱或导致系统重置的硬件信号故障可能会导致不受控制的系统中断。 硬件写入缓存中的内存故障也可能会导致重要的日志信息丢失。

与写入缓存控制器相关的另一个可能问题可能会在系统关闭时发生。 在配置更改期间循环运行 OS 或重启系统的情况并不少见。 即使操作人员很谨慎地遵循 OS 建议，等待所有存储活动停止后才重启系统，缓存写入仍可存在于控制器中。 按下 Ctrl+Alt+Del 组合键或按下硬件重置按钮时，可能会丢弃缓存的写入，从而可能损坏数据库。

可以设计一个硬件写入缓存，它会考虑到丢弃脏缓存数据的所有可能原因，因此数据库服务器可以安全地使用这些数据。 其中一些设计功能包括拦截 RST 总线信号，以避免不受控制的缓存控制器重置、板载电池备份以及镜像或错误检查和更正 (ECC) 内存。 请咨询硬件供应商，以确保写入缓存包含这些功能以及避免数据丢失所需的任何其他功能。

将存储缓存与 SQL Server 配合使用

数据库系统首先负责准确存储和检索数据，即使在发生意外的系统故障时也是如此。

系统必须保证事务的原子性和持久性，同时考虑到当前执行、多个事务和各种故障点。 这通常称为 ACID（原子性、一致性、隔离和持久性）属性。

本节介绍存储缓存的含义。 建议阅读 Microsoft 知识库中的以下文章，以了解缓存和备用故障模式讨论的进一步说明：

还建议使用以下文档：

这两个文档适用于当前支持的所有 SQL Server 版本。

电池支持的缓存解决方案

增强的缓存控制器系统禁用磁盘上的缓存并提供功能性电池支持的缓存解决方案。 这些缓存可以将缓存中的数据保留数天，甚至允许将缓存卡放置在第二台计算机中。 当电源正常恢复后，在允许任何进一步的数据访问之前，将完全刷新未写入的数据。 它们中的许多都允许建立读取与写入缓存的百分比，以获得最佳性能。 有些包含较大的内存存储区域。 一些硬件供应商提供高端的电池支持的驱动器缓存系统，具有多个 GB 的缓存。 这些可大幅度提高数据库性能。 使用电池支持的缓存解决方案可提供 SQL Server 预期的数据持久性和一致性。

存储子系统实现

子系统实现有很多类型。 RAID（独立磁盘冗余阵列）和 SAN（存储区域网络）是此类子系统实现的两个示例。 这些系统通常使用基于 SCSI 的驱动器构建。 其原因有若干： 以下部分一般描述了高级存储注意事项。

SCSI、SAS 和 NVMe

SCSI、SAS 和 NVMe 存储设备：

  • 通常为重型使用制造。
  • 通常以多用户、基于服务器的实现为目标。
  • 通常比其他实现具有更好的故障率平均时间。
  • 包含复杂的启发法，可帮助预测一触即发的故障。

非 SCSI

其他驱动器实现，例如 IDE、ATA 和 SATA：

  • 通常为轻型和中等使用而制造。
  • 通常针对基于单个用户的应用程序。

非 SCSI，基于桌面的控制器需要更多的主处理器 (CPU) 带宽，并且通常受单个活动命令的限制。 例如，当非 SCSI 驱动器调整错误块时，驱动器要求主机命令等待。 ATA 总线是另一个示例：ATA 总线支持两个设备，但只有一个命令可以处于活动状态。 这会使一个驱动器处于空闲状态，而另一个驱动器则服务于挂起的命令。 基于桌面技术构建的 RAID 系统都会经历这些症状，并会受到最慢的反应者的极大影响。 除非这些系统使用高级设计，否则它们的性能不如基于 SCSI 的系统高效。

存储注意事项

在某些情况下，基于桌面的驱动器或阵列是一种合适的低成本解决方案。 例如，如果为报告设置了只读数据库，则禁用驱动器缓存时，你不应遇到 OLTP 数据库的许多性能因素。

存储设备大小会继续增加。 低成本、高容量的驱动器可能很有吸引力。 但是，为 SQL Server 和业务响应时间需求配置驱动器时，你应仔细考虑以下问题：

  • 访问路径设计
  • 禁用磁盘上的缓存的要求

机械硬盘驱动器

机械驱动器使用旋转磁盘来存储数据。 它们具有多种容量和外形规格，例如 IDE、SATA、SCSI 和串行附加 SCSI (SAS)。 一些 SATA 驱动器包括故障预测构造。 SCSI 驱动器专为更重的工作周期和更低的故障率而设计。

应禁用驱动器缓存，以便将驱动器与 SQL Server 结合使用。 默认情况下，启用驱动器缓存。 在 Windows Server 中，使用“磁盘属性”>“硬件”选项卡访问“属性”>“策略”选项卡来控制驱动器缓存设置。

注意

有些驱动器不遵循此设置。 这些驱动器需要特定的制造商实用程序来禁用缓存。

当 IDE 和基于 ATA 的系统执行错误的块调整等活动时，它们可能会推迟主机命令。 这可能会导致 I/O 活动停滞一段时间。

SAS 的优势包括最高 256 级的高级队列，以及队列头和乱序队列。 按照 SAS 背板的设计，允许在同一系统中同时使用 SAS 和 SATA 驱动器。

SQL Server 安装取决于控制器禁用磁盘上的缓存并提供稳定的 I/O 缓存的能力。 只要控制器提供正确的稳定介质缓存功能，将数据无序地写入不同的驱动器对 SQL Server 来说就不是障碍。 控制器设计的复杂性随着高级数据安全技术（如镜像）而增加。

固态存储

固态存储比机械（旋转）硬盘驱动器具有优势，但易受许多与旋转介质相同的故障模式的影响。 固态存储使用各种接口（包括 NVM Express (NVMe)、PCI Express (PCIe) 和 SATA）连接到服务器。 将固态介质视为旋转介质，并确保为电源故障提供适当的安全措施，如电池支持的缓存控制器。

电源故障导致的常见问题包括：

  • 位损坏：记录显示随机位错误。
  • 飞行写入：格式良好的记录最终出现在错误的位置。
  • Shorn 写入：部分操作在低于预期扇区大小的水平上完成。
  • 元数据损坏：FTL 中的元数据已损坏。
  • 无响应设备：设备根本不起作用，或者大部分不起作用。
  • 不可序列化：存储的最终状态不是由可序列化的操作顺序产生的。

512e

大多数固态存储报告 512 字节的扇区大小，但在 1 MB 擦除块内使用 4 KB 页。 对 SQL Server 日志设备使用 512 字节对齐扇区可以生成更多的读取/修改/写入 (RMW) 活动，从而降低性能和驱动器磨损。

建议：确保缓存控制器知道存储设备的正确页面大小，并且可以将物理写入与固态存储基础设施适当对齐。

0xFFFFFFFF

新格式化的驱动器通常全部为零。 已擦除的固态设备块全部为 1，从而使已擦除块的原始读取全部为 0xFF。 但是，用户通常不能在正常 I/O 操作期间读取擦除块。

模式标记

我们过去使用的技术是将已知模式写入整个驱动器。 然后，当我们针对同一驱动器执行数据库活动时，如果模式意外出现，我们可以检测不正确的行为（过时的读取/丢失写入/读取不正确的偏移量/等等）。

此方法不适用于固态存储。 写入的擦除和 RMW 活动会破坏模式。 固态存储垃圾回收 (GC) 活动、磨损均衡、成比例/预留列表块和其他优化往往导致写入获取不同的物理位置，这与旋转介质的扇区重用不同。

固件

与旋转介质的对应项相比，在固态存储中使用的固件往往比较复杂。 许多驱动器使用多个处理核心来处理传入请求和垃圾回收活动。 确保将固态设备固件保持最新状态，以避免发生已知问题。

读取数据损坏和磨损均衡

用于固态存储的常见垃圾回收 (GC) 方法有助于防止重复读取数据损坏。 重复读取同一单元格时，电子活动可能会泄漏并导致邻近的细胞受损。 固态存储使用各种级别的错误更正代码 (ECC) 和其他机制保护数据。

其中一种机制与磨损均衡有关。 固态存储将跟踪存储设备上的读取和写入活动。 垃圾回收可以确定比其他位置磨损更快速的热点或位置。 例如，GC 确定块在一段时间内处于只读状态，并且需要移动。 此移动通常针对具有更多磨损的块，因此原始块可用于写入。 这有助于平衡驱动器上的磨损，但会将只读数据移动到具有更多磨损的位置并在数学上提高故障几率，即使是轻微的。

磨损均衡的另一个副作用可能会发生在 SQL Server 上。 假设执行 DBCC CHECKDB，并报告错误。 如果再次运行它，则 DBCC CHECKDB 报告其他或不同的错误模式的几率很小，因为固态存储 GC 活动可能会在 DBCC CHECKDB 次执行之间进行更改。

OS 错误 665 和碎片整理

旋转介质需要保持块彼此靠近，以减少驱动器头的移动并提高性能。 固态存储没有物理头，这消除了搜寻时间。 许多固态设备旨在允许并行处理不同块上的并行操作。 这意味着不需要对固态介质进行碎片整理。 串行活动是最佳的 I/O 模式，用于最大程度地提高固态存储设备上的 I/O 吞吐量。

注意

固态存储受益于 trim 功能，一种操作系统 (OS) 级命令，用于擦除被视为不再使用的块。 在 Windows 中，使用“优化驱动器”工具设置用于优化驱动器的每周计划。

建议：

  • 使用合适的、有电池支持的控制器来优化写入操作。 这可以提高性能，降低驱动器磨损和物理碎片化程度。

  • 请考虑使用 ReFS 文件系统来避免 NTFS 属性限制。

  • 请确保文件增长大小适当。

有关排查与碎片相关的 OS 错误 665 的详细信息，请参阅针对 SQL Server 文件报告 OS 错误 665 和 1450。

压缩

只要驱动器保持稳定介质的意图，压缩就可以延长驱动器寿命，并且可能对性能产生积极影响。 但是，某些固件可能已经无形地压缩了数据。 在将新存储方案部署到生产环境之前，请记得对其进行测试。

总结

  • 维护适当的备份和灾难恢复程序和过程。
  • 使固件保持最新。
  • 仔细听取硬件制造商的指导。

缓存注意事项和 SQLIOSim

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

注意

确保任何备用缓存机制都能正确处理多种类型的故障。

Microsoft 使用 SQLIOSim 实用工具对多个 SCSI 和 IDE 驱动器执行测试。 此实用工具对模拟的数据设备和日志设备模拟繁重的异步读取/写入活动。 有关 SQLIOSim 的详细信息，请参阅使用 SQLIOSim 实用工具模拟磁盘子系统上的 SQL Server 活动。

许多电脑制造商会订购禁用写入缓存的驱动器。 但是，测试表明，情况可能并非总是如此，因此你应该始终对其进行全面测试。 如果对存储设备的缓存状态有任何疑问，请联系制造商并获取适当的实用工具或跳线设置以禁用写入缓存操作。

SQL Server 要求系统支持对稳定介质进行有保证的传递，如 SQL Server I/O 可靠性计划要求中所述。 有关 SQL Server 数据库引擎的输入和输出要求的详细信息，请参阅 SQL Server 数据库引擎磁盘输入/输出 (I/O) 要求。

相关内容