MS SQL Server - 架构

---

为了便于理解，我们将 SQL Server 的体系结构分为以下部分：

• 通用架构
• 内存架构
• 数据文件架构
• 日志文件架构

总体架构

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

查询- SQL 查询是高级语言。

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

N/W 数据包- 网络相关代码。

协议- 在 SQL Server 中，我们有 4 个协议。

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

• 命名管道（用于 LAN 连接中的连接）。

• TCP/IP（适用于 WAN 连接中的连接）。

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

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

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

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

查询优化器- 它将通过将查询、统计信息和 Algebrizer 树作为输入来准备执行计划作为输出。

执行计划- 它就像一个路线图，其中包含作为查询执行的一部分要执行的所有步骤的顺序。

查询执行器- 这是在执行计划的帮助下逐步执行查询的地方，并且还将联系存储引擎。

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

SQL OS - 它位于主机（Windows 操作系统）和 SQL Server 之间。数据库引擎上执行的所有活动均由 SQL 操作系统负责。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 不断监视内存使用情况以评估资源争用（或可用性）；它的工作是确保始终有一定数量的可用空间。作为此过程的一部分，当它注意到任何此类资源争用时，它会触发 Lazy Writer 通过将脏页写入磁盘来释放内存中的一些页。它采用最近最少使用（LRU）算法来决定将哪些页面刷新到磁盘。

如果 Lazy Writer 始终处于活动状态，则可能表明存在内存瓶颈。

内存架构

以下是内存架构的一些显着特征。

• 所有数据库软件的主要设计目标之一是最大限度地减少磁盘 I/O，因为磁盘读取和写入是资源最密集的操作之一。

• Windows中的内存可以通过虚拟地址空间来调用，由内核模式（OS模式）和用户模式（如SQL Server的应用程序）共享。

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

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

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

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

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

缓冲池的其他部分包括 -

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

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

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

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

数据文件架构

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

文件组

出于分配和管理目的，可以将数据库文件分组到文件组中。任何文件都不能属于多个文件组的成员。日志文件绝不是文件组的一部分。日志空间与数据空间分开管理。

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

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

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

文件

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

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

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

数据库中所有文件的位置都记录在主数据库和数据库的主文件中。大多数时候，数据库引擎使用主数据库中的文件位置。

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

范围

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

页数

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

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

• 索引- 索引条目。

• Tex\Image - 文本、图像和 ntext 数据。

• GAM - 有关分配范围的信息。

• SGAM - 有关系统级别分配的范围的信息。

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

• 索引分配映射 (IAM) - 有关表或索引使用的范围的信息。

• 批量更改映射 (BCM) - 有关自上次备份日志语句以来批量操作修改的范围的信息。

• 差异更改映射 (DCM) - 有关自上次备份数据库语句以来已更改的范围的信息。

日志文件架构

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

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

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

• 记录逻辑操作。
  • 要向前滚动逻辑操作，请再次执行该操作。
  • 为了回滚逻辑操作，执行相反的逻辑操作。
• 记录图像之前和之后。
  • 要向前滚动操作，将应用后像。
  • 要回滚操作，将应用之前的图像。

不同类型的操作都会记录在事务日志中。这些操作包括 -

• 每笔交易的开始和结束。

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

• 每个范围和页面分配或取消分配。

• 创建或删除表或索引。

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

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

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

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

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

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