MS SQL Server – 架构

MS SQL Server – 架构

为了便于理解，我们将 SQL Server 的架构分为以下几部分 –

  • 一般架构
  • 内存架构
  • 数据文件架构
  • 日志文件架构

通用架构

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

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

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

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

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

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

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

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

  • VIA-Virtual Interface Adapter（需要由供应商设置的特殊硬件，并且在 SQL 2012 版本中已弃用）。

服务器– SQL 服务安装和数据库所在的位置。

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

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

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

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

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

存储引擎– 负责存储和检索存储系统（磁盘、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，这将覆盖为服务器指定的恢复间隔并避免在此类数据库上自动检查点。

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

  • 内部– 作为用户，您无法控制内部检查点。针对特定操作发出，例如

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

    • 如果恢复模式从 Full\Bulk-logged 更改为 Simple。

    • 在备份数据库时。

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

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

    • 当数据库的恢复模型是大容量日志并执行最少日志操作时，也会发生检查点。

    • 数据库快照创建。

  • Lazy Writer Process – Lazy Writer 会出于完全不同的原因将脏页推送到磁盘，因为它需要释放缓冲池中的内存。当 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 操作的数量来优化性能，这些操作本身比从内存中检索数据要慢。

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

缓冲池的其他部分包括 –

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

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

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

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

数据文件架构

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

文件组

数据库文件可以组合成文件组以用于分配和管理目的。任何文件都不能属于多个文件组。日志文件从不属于文件组。日志空间与数据空间分开管理。

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

每个数据库中的一个文件组作为默认文件组运行。当 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) 语句对任何表（包括系统表）所做的更改。

  • 每个extent和page分配或de分配。

  • 创建或删除表或索引。

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

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

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

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

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

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

觉得文章有用？

点个广告表达一下你的爱意吧 ！😁