MS SQL Server 架构

---

---

为了方便理解，我们将 SQL Server 的架构分为以下几个部分：

• 总体架构
• 内存架构
• 数据文件架构
• 日志文件架构

总体架构

客户端 - 请求发起的地方。

查询 - SQL 查询，是一种高级语言。

逻辑单元 - 关键字、表达式和运算符等。

网络数据包 - 网络相关代码。

协议 - SQL Server 中有 4 种协议。

• 共享内存（用于本地连接和故障排除）。

• 命名管道（用于局域网连接）。

• TCP/IP（用于广域网连接）。

• VIA - 虚拟接口适配器（需要供应商设置专用硬件，并且从 SQL 2012 版本开始已弃用）。

服务器 - SQL 服务安装和数据库驻留的地方。

关系引擎 - 真正执行的地方。它包含查询解析器、查询优化器和查询执行器。

查询解析器（命令解析器）和编译器（翻译器） - 检查查询语法并将查询转换为机器语言。

查询优化器 - 以查询、统计信息和代数树为输入，准备执行计划作为输出。

执行计划 - 就像路线图一样，包含查询执行过程中所有步骤的顺序。

查询执行器 - 在执行计划的帮助下逐步执行查询，并联系存储引擎。

存储引擎 - 负责在存储系统（磁盘、SAN 等）上存储和检索数据、数据操作、锁定和事务管理。

SQL OS - 位于主机（Windows 操作系统）和 SQL Server 之间。数据库引擎上执行的所有活动都由 SQL OS 负责。SQL OS 提供各种操作系统服务，例如内存管理（处理缓冲池、日志缓冲区）和使用阻塞和锁定结构进行死锁检测。

检查点进程 - 检查点是一个内部进程，它将所有脏页（已修改的页）从缓冲区高速缓存写入物理磁盘。除此之外，它还将日志记录从日志缓冲区写入物理文件。将脏页从缓冲区高速缓存写入数据文件也称为脏页硬化。

这是一个专用进程，由 SQL Server 以特定间隔自动运行。SQL Server 为每个数据库单独运行检查点进程。检查点有助于减少 SQL Server 在意外关机或系统崩溃/故障事件中的恢复时间。

SQL Server 中的检查点

在 SQL Server 2012 中，有四种类型的检查点：

• 自动 - 这是最常见的检查点，它作为后台进程运行，以确保 SQL Server 数据库可以在恢复间隔 - 服务器配置选项定义的时间限制内恢复。

• 间接 - 这是 SQL Server 2012 中的新增功能。它也在后台运行，但为了满足为配置了该选项的特定数据库指定的特定用户目标恢复时间。一旦为给定数据库选择了 Target_Recovery_Time，这将覆盖为服务器指定的恢复间隔，并避免对该数据库进行自动检查点。

• 手动 - 一旦发出检查点命令，它就会像任何其他 T-SQL 语句一样运行，直到完成。手动检查点只针对当前数据库运行。您还可以指定 Checkpoint_Duration（可选）- 此持续时间指定您希望检查点完成的时间。

• 内部 - 作为用户，您无法控制内部检查点。在特定操作时发出，例如：

  • 关机会在所有数据库上启动检查点操作，除非关机不干净（使用 nowait 关机）。

  • 如果恢复模式从完全/批量日志更改为简单。

  • 备份数据库时。

  • 如果您的数据库处于简单恢复模式，则检查点进程会在日志满 70% 或基于服务器选项恢复间隔时自动执行。

  • 更改数据库命令以添加或删除数据/日志文件也会启动检查点。

  • 当数据库的恢复模式为批量日志记录并且执行了最小日志记录操作时，也会发生检查点。

  • 数据库快照创建。

• 惰性写入器进程 - 惰性写入器将脏页推送到磁盘的原因完全不同，因为它需要释放缓冲池中的内存。当 SQL Server 处于内存压力下时，就会发生这种情况。据我所知，这是由内部进程控制的，并且没有对其进行设置。

SQL Server 持续监控内存使用情况以评估资源争用（或可用性）；其工作是确保始终有足够的可用空间。在此过程中，当它注意到任何此类资源争用时，它会触发惰性写入器通过将脏页写入磁盘来释放一些内存页。它采用最近最少使用 (LRU) 算法来决定哪些页要刷新到磁盘。

如果惰性写入器始终处于活动状态，则可能表示内存瓶颈。

内存架构

以下是内存架构的一些主要特征。

• 所有数据库软件的主要设计目标之一是最小化磁盘 I/O，因为磁盘读写是最资源密集型操作之一。

• Windows 中的内存可以用虚拟地址空间来调用，该空间由内核模式（操作系统模式）和用户模式（SQL Server 等应用程序）共享。

• SQL Server“用户地址空间”分为两个区域：MemToLeave 和缓冲池。

• MemToLeave (MTL) 和缓冲池 (BPool) 的大小由 SQL Server 在启动时确定。

• 缓冲区管理是实现 I/O 高效率的关键组件。缓冲区管理组件由两种机制组成：缓冲区管理器用于访问和更新数据库页，缓冲池用于减少数据库文件 I/O。

• 缓冲池进一步细分为多个部分。最重要的部分是缓冲区高速缓存（也称为数据高速缓存）和过程高速缓存。缓冲区高速缓存将数据页保存在内存中，以便可以从高速缓存检索经常访问的数据。另一种方法是从磁盘读取数据页。从高速缓存读取数据页通过最大限度地减少所需 I/O 操作的数量来优化性能，这些操作本质上比从内存检索数据慢。

• 过程高速缓存保留存储过程和查询执行计划，以最大限度地减少必须生成查询计划的次数。您可以使用 DBCC PROCCACHE 语句查找有关过程高速缓存中大小和活动的信息。

缓冲池的其他部分包括：

• 系统级数据结构 - 保存有关数据库和锁的 SQL Server 实例级数据。

• 日志高速缓存 - 保留用于读取和写入事务日志页。

• 连接上下文 - 每个与实例的连接都有一个小内存区域来记录连接的当前状态。此信息包括存储过程和用户定义函数参数、游标位置等等。

• 堆栈空间 - Windows 为 SQL Server 启动的每个线程分配堆栈空间。

数据文件架构

数据文件架构具有以下组件：

文件组

可以将数据库文件组合到文件组中，以进行分配和管理。没有文件可以是多个文件组的成员。日志文件永远不是文件组的一部分。日志空间与数据空间分开管理。

SQL Server 中有两种类型的文件组：主文件组和用户定义文件组。主文件组包含主数据文件和未专门分配给另一个文件组的任何其他文件。系统表的页面都分配在主文件组中。用户定义的文件组是使用 create database 或 alter database 语句中的文件组关键字指定的文件组。

每个数据库中的一个文件组充当默认文件组。当 SQL Server 将页面分配给创建时未指定文件组的表或索引时，页面将从默认文件组分配。要将默认文件组从一个文件组切换到另一个文件组，它应该具有 db_owner 固定数据库角色。

默认情况下，主文件组是默认文件组。用户应该具有 db_owner 固定数据库角色才能单独备份文件和文件组。

文件

数据库有三种类型的文件：主数据文件、辅助数据文件和日志文件。主数据文件是数据库的起点，并指向数据库中的其他文件。

每个数据库都有一个主数据文件。我们可以为主数据文件指定任何扩展名，但推荐的扩展名是.mdf。辅助数据文件是该数据库中除主数据文件以外的文件。某些数据库可能有多个辅助数据文件。某些数据库可能没有任何辅助数据文件。辅助数据文件的推荐扩展名是.ndf。

日志文件保存用于恢复数据库的所有日志信息。数据库必须至少有一个日志文件。一个数据库可以有多个日志文件。日志文件的推荐扩展名是.ldf。

数据库中所有文件的所在位置都记录在主数据库和数据库的主文件中。大多数情况下，数据库引擎使用来自主数据库的文件位置。

文件有两个名称：逻辑名称和物理名称。逻辑名称用于在所有 T-SQL 语句中引用文件。物理名称是 OS_file_name，它必须遵循操作系统的规则。数据和日志文件可以放在 FAT 或 NTFS 文件系统上，但不能放在压缩的文件系统上。一个数据库最多可以有 32,767 个文件。

扩展

扩展是为表和索引分配空间的基本单元。一个扩展是 8 个连续页或 64KB。SQL Server 有两种类型的扩展：统一扩展和混合扩展。统一扩展仅由单个对象组成。混合扩展由多达八个对象共享。

页

它是 MS SQL Server 中数据存储的基本单元。页的大小为 8KB。每页的开头是 96 字节的页眉，用于存储系统信息，例如页的类型、页上的可用空间量以及拥有该页的对象 ID。SQL Server 中有 9 种类型的数据页。

• 数据 - 数据行，包含除文本、ntext 和图像数据之外的所有数据。

• 索引 - 索引条目。

• 文本/图像 - 文本、图像和 ntext 数据。

• GAM - 关于已分配扩展的信息。

• SGAM - 关于系统级已分配扩展的信息。

• 页空闲空间 (PFS) - 关于页面上可用空闲空间的信息。

• 索引分配映射 (IAM) − 关于表或索引使用的扩展的信息。

• 批量更改映射 (BCM) − 关于自上次备份日志语句以来通过批量操作修改的扩展的信息。

• 差异更改映射 (DCM) − 关于自上次备份数据库语句以来已更改的扩展的信息。

日志文件架构

SQL Server 事务日志在逻辑上运行，就像事务日志是一串日志记录一样。每个日志记录都由日志序列号 (LSN) 标识。每个日志记录都包含其所属事务的 ID。

数据修改的日志记录要么记录执行的逻辑操作，要么记录修改数据的修改前和修改后映像。修改前映像是在执行操作之前的数据库副本；修改后映像是操作执行后的数据库副本。

恢复操作的步骤取决于日志记录的类型 −

• 记录的逻辑操作。
  • 要向前回滚逻辑操作，请再次执行该操作。
  • 要向后回滚逻辑操作，请执行相反的逻辑操作。
• 记录的修改前和修改后映像。
  • 要向前回滚操作，请应用修改后映像。
  • 要向后回滚操作，请应用修改前映像。

事务日志中记录了不同类型的操作。这些操作包括 −

• 每个事务的开始和结束。

• 每个数据修改（插入、更新或删除）。这包括系统存储过程或数据定义语言 (DDL) 语句对任何表的更改，包括系统表。

• 每个扩展和页面分配或取消分配。

• 创建或删除表或索引。

回滚操作也会记录在日志中。每个事务都会在事务日志上预留空间，以确保存在足够的日志空间来支持由显式回滚语句或遇到错误引起的回滚。当事务完成后，此预留空间将被释放。

从必须存在才能成功进行数据库范围回滚的第一个日志记录到最后写入的日志记录的日志文件部分称为日志活动部分或活动日志。这是数据库完全恢复所需的日志部分。活动日志的任何部分都不能被截断。此第一条日志记录的 LSN 称为最小恢复 LSN (Min LSN)。

SQL Server 数据库引擎会在内部将每个物理日志文件划分为多个虚拟日志文件。虚拟日志文件没有固定大小，物理日志文件的虚拟日志文件数量也没有固定数量。

数据库引擎在创建或扩展日志文件时会动态选择虚拟日志文件的大小。数据库引擎尝试维护少量虚拟文件。管理员无法配置或设置虚拟日志文件的大小或数量。虚拟日志文件影响系统性能的唯一时间是当物理日志文件由较小的 size 和 growth_increment 值定义时。

size 值是日志文件的初始大小，growth_increment 值是每次需要新空间时添加到文件中的空间量。如果由于许多小的增量导致日志文件增长到很大，则它们将具有许多虚拟日志文件。这可能会减慢数据库启动以及日志备份和还原操作的速度。

我们建议您为日志文件分配接近最终所需大小的 size 值，并使用相对较大的 growth_increment 值。SQL Server 使用预写日志 (WAL)，它保证在将关联的日志记录写入磁盘之前，不会将任何数据修改写入磁盘。这保持了事务的 ACID 属性。