Ms Sql Server 简明教程

MS SQL Server - Architecture

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

  1. General architecture

  2. Memory architecture

  3. Data file architecture

  4. Log file architecture

General Architecture

Client − 请求发起的地方。

Query − 作为高级语言的 SQL 查询。

Logical Units − 关键字、表达式和运算符等。

N/W Packets − 与网络相关的代码。

Protocols − 在 SQL Server 中我们有 4 个协议。

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

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

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

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

Server − SQL 服务已安装的位置以及数据库所在的目录。

Relational Engine − 此处将执行实际执行。它包含查询解析器、查询优化器和查询执行器。

Query Parser (Command Parser) and Compiler (Translator) − 这将检查查询的语法，并将查询转换为机器语言。

Query Optimizer − 采用查询、统计和 Algebrizer 树作为输入，准备执行计划作为输出。

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

Query Executor − 在此，将借助执行计划逐步执行查询，还将联系存储引擎。

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

SQL OS - SQL Server 和 SQL OS 的区别在于：SQL OS 位于主机（Windows 操作系统）和 SQL Server 之间。数据库引擎上执行的所有活动均由 SQL OS 处理。SQL OS 提供各种操作系统服务，例如内存管理，它通过阻塞和锁定结构来处理缓冲池、日志缓冲以及死锁检测。

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

这是一个专用进程，并且由 SQL Server 在特定时间间隔内自动运行。SQL Server 为每个数据库单独运行检查点进程。检查有助于缩短 SQL Server 在发生意外关机或系统崩溃/故障时的恢复时间。

Checkpoints in SQL Server

在 SQL Server 2012 中有四种类型的 checkpoints -

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

  2. Indirect - 这是 SQL Server 2012 中的新功能。它也运行在后台，但是为了达到已配置了该选项的某个特定数据库的用户指定的目标恢复时间。一旦为给定的数据库选择了 Target_Recovery_Time，这将覆盖服务器的恢复间隔并避免对此类数据库执行自动检查。

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

  4. Internal - 作为用户，你无法控制内部检查点。在某些特定操作（例如关机）上发起的操作会在除关机不正常（立即关机）外的所有数据库发起检查点操作。如果恢复模式从完全/全部日志记录更改为简单。在备份数据库时。如果你的数据库处于简单恢复模式，则当日志已满 70%，或基于服务器选项（恢复间隔）时，检查点进程自动执行。Alter 数据库命令以添加或删除数据/日志文件也会启动检查点。当数据库的恢复模式是大容量日志记录并且执行了最小日志记录操作时，也会进行检查点。数据库快照创建。

  5. Lazy Writer Process - Lazy Writer 将脏页推送到磁盘，原因完全不同，因为它需要释放缓冲池中的内存。在 SQL 服务器出现内存压力时就会发生这种情况。据我所知，这是由一个内部进程控制的，并且没有针对它的设置。

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

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

Memory Architecture

以下是内存体系结构的一些显着特征：

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

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

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

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

  5. Buffer management 是实现高 I/O 效率的关键组件。缓冲管理组件包含两种机制：缓冲区管理器以访问和更新数据库页，以及缓冲区池以减少数据库文件 I/O。

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

  7. Procedure cache 保存存储过程和查询执行计划，以最大限度地减少必须生成查询计划的次数。你可以使用 DBCC PROCCACHE 语句了解过程缓存中的大小和活动。

缓冲池的其他部分包括 −

  1. System level data structures − 持有关于数据库和锁的 SQL Server 实例级别数据。

  2. Log cache − 保留用于读写事务日志页。

  3. Connection context − 每个数据库实例连接都有一小块内存用于记录当前连接状态。此信息包括存储过程和用户定义函数参数、游标位置等。

  4. Stack space − Windows 为 SQL Server 启动的每个线程分配堆栈空间。

Data File Architecture

数据文件架构有以下组件 −

File Groups

数据库文件可以组合在文件组中以用于分配和管理。没有文件可以成为多个文件组的成员。日志文件从不作为文件组的一部分。日志空间与数据空间分开管理。

SQL Server 中有两种类型文件组，主文件组和用户自定义文件组。主文件组包含主数据文件和未具体指定到其他文件组的任何其他文件。系统表的所有页都在主文件组中分配。用户自定义文件组是使用创建数据库或更改数据库语句中的文件组关键字指定的任何文件组。

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

默认情况下，主文件组是默认文件组。用户应具有 db_owner 固定数据库角色，以便分别备份文件和文件组。

Files

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

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

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

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

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

Extents

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

Pages

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

  1. Data − 带有除文本、ntext 和图像数据之外所有数据的行。

  2. Index − Index entries.

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

  4. GAM −关于已分配范围的信息。

  5. SGAM −关于系统级别分配的范围的信息。

  6. Page Free Space (PFS) −关于页面可用空间的信息。

  7. Index Allocation Map (IAM) −关于表或索引使用的范围的信息。

  8. Bulk Changed Map (BCM) −关于自最后一次备份日志语句以来通过批量操作修改的范围的信息。

  9. Differential Changed Map (DCM) −关于自最后一次备份数据库语句以来已更改的范围的信息。

Log File Architecture

SQL Server 的事务日志在逻辑上将事务日志当作一系列的日志记录。每个日志记录由日志序列号 (LSN) 识别。每个日志记录都包含它所属的事务的 ID。

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

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

  1. 已记录的逻辑操作。为向前滚动逻辑操作，再次执行操作。为向后滚动逻辑操作，执行相反的逻辑操作。

  2. 已记录的修改前和修改后映像。为向前滚动操作，应用修改后映像。为向后滚动操作，应用修改前映像。

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

  1. 每个事务的开始和结束。

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

  3. 每次范围和页面的分配或取消分配。

  4. 创建或删除表或索引。

取消操作也会被记录。每个事务保留事务日志上的空间，以确保存在足够的日志空间来支持因明确取消语句或遇到错误而导致的取消。此保留空间在事务完成后被释放。

从必须用于成功对整个数据库执行取消以恢复的第一个日志记录到最后写入的日志记录的对数文件部分称为日志的活动部分或活动日志。这是数据库完全恢复所需的日志部分。活跃日志的任何部分都不能被截断。此第一个日志记录的 LSN 被称为最小恢复 LSN（最小 LSN）。

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

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

大小值是日志文件的初始大小，而 growth_increment 值每次需要新的空间时添加到文件中的空间量。如果日志文件由于很多小增量而增长到很大的大小，它们就会有许多虚拟日志文件。这会降低数据库启动速度，还会降低日志备份和还原操作速度。

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