适用于：
SQL ServerAzure SQL 托管实例Azure 虚拟机上的 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 操作均在调用线程中发出，除非关联 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）实现此保护通常是不够的，因为与电源无关的故障模式可能发生。
Important
SQL Server 依赖于 保证传送到稳定的媒体 ，实现事务完整性和恢复。 不安全的缓存如果无法确保在故障期间保留数据，则可能会损坏数据库，而与事务日志写入的一致性无关。 始终验证任何写入缓存机制是否提供完全持续性保证。
缓存控制器和存储子系统可以安全地供 SQL Server 使用。 包含这些控制器的大多数新的专用服务器平台都是安全的。 但是，应与硬件供应商核实，确保存储子系统经过测试和批准，以便在数据关键事务性关系数据库管理系统（RDBMS）环境中使用。
缓存子系统安全准则
如果写回缓存控制器满足特定的安全要求，则可以提高性能：
- 包括电池支持的缓存或非易失性内存，例如 NVDIMM 或超级电压支持的闪存。
- 由供应商针对数据关键 OLTP 数据库环境进行认证。
- 提供涵盖所有故障条件的保护，而不仅仅是断电。
Important
不要单独依赖外部 UPS。 与电源无关的故障（例如固件 bug 或硬件故障）仍可能导致缓存丢失。
预写日志
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 可确保当写入操作返回成功完成时，数据正确存储在稳定存储中。 此功能与 Write-Ahead 日志记录（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 事务日志的写入，并在控制器板卡的硬件缓存中进行缓冲，但在系统故障时不保留这些写入的页面，就可能会出现此问题。
Warning
如果由于系统重置而放弃缓存写入，即使有 UPS 设备，数据库也可能会损坏。 始终确保写入缓存由电池或等效技术提供支持，以确保数据持久性。
磁盘写入缓存风险
大多数存储设备缓存控制器执行写入缓存。 不能始终禁用写入缓存函数。
即使服务器使用 UPS，设备也不保证缓存写入的安全性。 会发生 UPS 无法解决的许多类型的系统故障。 例如，内存奇偶校验错误、操作系统 (OS) 陷阱或导致系统重置的硬件信号故障可能会导致不受控制的系统中断。 硬件写入缓存中的内存故障也可能会导致重要的日志信息丢失。
与写入缓存控制器相关的另一个可能问题可能会在系统关闭时发生。 在配置更改期间 重启 OS 或重启系统并不少见。 即使操作人员很谨慎地遵循 OS 建议，等待所有存储活动停止后才重启系统，缓存写入仍可存在于控制器中。 按下 Ctrl+Alt+Del 组合键或按下硬件重置按钮时，可能会丢弃缓存的写入，从而可能损坏数据库。
可以设计一个硬件写缓存，该缓存可以考虑到丢弃脏缓存数据的所有可能原因，这样设计使其可以安全地被数据库服务器使用。 其中一些设计功能包括截获 RST（重置）总线信号，以避免不受控制的缓存控制器重置、板载电池备份以及镜像或错误检查和更正（ECC）内存。 请咨询硬件供应商，以确保写入缓存包含这些功能以及避免数据丢失所需的任何其他功能。
将存储缓存与 SQL Server 配合使用
数据库系统首先负责准确存储和检索数据，即使在发生意外的系统故障时也是如此。
系统必须保证事务的原子性和持久性，同时考虑到当前执行、多个事务和各种故障点。 此特性通常被称为 ACID（原子性、一致性、隔离性和持久性）属性。
本节介绍存储缓存的含义。 有关缓存和备用故障模式讨论的详细信息，请参阅以下文章：
另请查看以下存档内容：
这两篇文章中的概念仍广泛适用于 SQL Server 的当前版本。
电池支持的缓存解决方案
增强的缓存控制器系统禁用磁盘上的缓存并提供功能性电池支持的缓存解决方案。 这些缓存可以将缓存中的数据保留数天，甚至允许将缓存卡放置在第二台计算机中。 当电源正常恢复后，在允许任何进一步的数据访问之前，将完全刷新未写入的数据。 其中许多系统允许建立读取缓存与写入缓存的百分比，以获得最佳性能。 某些系统包含大型内存存储区域。 一些硬件供应商提供高端的电池支持的驱动器缓存系统，具有多个 GB 的缓存。 这些系统可以显著提高数据库性能。 电池支持的缓存解决方案提供 SQL Server 所需的数据的持久性和一致性。
存储子系统实现
存储子系统存在于许多类型中。 两个常见示例是 RAID（独立磁盘冗余阵列）和 SAN（存储区域网络）。 这些系统通常使用基于 SCSI 的驱动器。 以下部分介绍高级存储注意事项。
SCSI、SAS 和 NVMe
SCSI、SAS 和 NVMe 存储设备：
- 通常专为高负荷用途而设计。
- 通常以多用户、基于服务器的实现为目标。
- 通常，与其他实现方式相比，平均故障间隔时间更长。
- 包含复杂的启发法，可帮助预测一触即发的故障。
注意
SQL Server 支持满足 Windows 硬件兼容性计划要求的 Internet Small Computer System Interface （iSCSI） 技术组件。 尽管 SQL Server 不直接与 iSCSI 交互，但它可以无缝运行，因为 Windows 将 iSCSI 存储作为标准驱动器提供。 然后，SQL Server 可以跨 IP 网络读取和写入远程块级存储。 由于 iSCSI 依赖于网络，因此可能会遇到延迟或瓶颈。 确保服务器的缓存性能是最佳的，并且延迟降到最低。 有关详细信息，请参阅 iSCSI 目标服务器可伸缩性限制。
非 SCSI
其他驱动器实现，例如 IDE、ATA 和 SATA：
- 通常设计用于轻型和中等负荷使用。
- 通常以单用户应用程序为目标。
非 SCSI、基于桌面的控制器需要更多主处理器（CPU）带宽，并且通常受单个活动命令的限制。 例如，当非 SCSI 驱动器调整错误块时，驱动器要求主机命令等待。 ATA 总线是另一个示例：ATA 总线支持两个设备，但只有一个命令可以处于活动状态。 此限制使一个驱动器处于空闲状态，而另一个驱动器则为待处理的指令提供服务。 基于桌面技术构建的 RAID 系统都会经历这些症状，并会受到最慢的反应者的极大影响。 除非这些系统使用高级设计，否则它们的性能不如基于 SCSI 的系统高效。
存储注意事项
在某些情况下，基于桌面的驱动器或阵列可以是低成本的解决方案。 例如，如果为报告设置了只读数据库，则禁用驱动器缓存时，不会遇到 OLTP 数据库的许多性能因素。
存储设备大小会继续增加。 低成本、高容量驱动器可能很有吸引力。 但是，为 SQL Server 配置驱动器和业务响应时间需求时，请仔细考虑以下问题：
- 访问路径设计
- 禁用磁盘上的缓存的要求
机械硬盘驱动器
机械驱动器使用旋转磁盘来存储数据。 它们以多种容量和外形规格提供，例如 IDE、SATA、SCSI 和串行附加 SCSI（SAS）。 一些 SATA 驱动器包括故障预测构造。 SCSI 驱动器专为更重的工作周期和更低的故障率而设计。
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。
压缩
只要驱动器保持稳定介质的意图，压缩就可以延长驱动器寿命，并且可能会对性能产生积极影响。 但是，某些固件可能已经无形地压缩了数据。 在将新存储方案部署到生产环境之前，请记住测试这些方案。
总结
- 维护适当的备份和灾难恢复程序和过程。
- 使固件保持最新。
- 密切听取硬件制造商的指导。
驱动器缓存配置
若要将驱动器与 SQL Server 配合使用，请禁用驱动器缓存。 默认情况下，启用驱动器缓存。 在 Windows Server 中，使用 “磁盘属性>硬件>策略 ”选项卡在 OS 级别禁用写入缓存。
不要仅依赖于 OS 级设置。 某些驱动器忽略 Windows 设置，并要求制造商提供的实用程序或固件设置来禁用写入缓存。 通过供应商工具确认写缓存确实已被禁用。
注意
即使禁用了写入缓存，驱动器固件的内部优化仍可能导致刷新命令的延迟。 始终使用 SQLIOSim 等测试工具在部署之前确认有效的缓存状态。
缓存注意事项和 SQLIOSim
若要确认事务持续性保证，请在迁移到生产环境之前使用 SQLIOSim 验证 I/O 子系统。 此工具模拟对模拟的数据设备和日志设备的大量异步读取和写入活动。 有关详细信息，请参阅 使用 SQLIOSim 实用工具模拟磁盘子系统上的 SQL Server 活动。
注意
确保任何备用缓存机制都能正确处理多种类型的故障。
许多电脑制造商会订购禁用写入缓存的驱动器。 但是，测试表明此条件可能并不总是这样，因此应始终完全测试它。 如果对存储设备的缓存状态有任何疑问，请联系制造商并获取相应的实用工具以禁用写入缓存操作。 在较旧的存储介质上，可能还需要跳线设置。
SQL Server 要求系统支持对稳定介质进行有保证的传递，如 SQL Server I/O 可靠性计划要求中所述。 有关 SQL Server 数据库引擎的输入和输出要求的详细信息，请参阅 SQL Server 数据库引擎磁盘输入/输出 (I/O) 要求。
不当写入缓存带来的风险
在没有适当安全措施的情况下启用写入缓存时，某些存储子系统在将数据安全写入到持久媒体之前确认写入操作已完成。 如果发生断电或系统故障，则此情况可能会导致：
- 数据丢失，其中提交的事务永远不会进行持久化。
- 由于写入顺序保证中断，数据库损坏。
Important
禁用 SQL Server 数据和日志驱动器的写入缓存，除非已通过硬件供应商的文档确认：
- 缓存由电池支持或使用永久性闪存存储。
- 驱动器确保在停电和系统崩溃时的耐用性。
外部 UPS 设备是不够的，因为它们可能无法防范所有故障模式，例如控制器固件故障。