为了便于理解,我们将SQL Server的体系结构分为以下几部分 -
- 一般性架构
- 内存架构
- 数据文件架构
- 日志文件架构
一般性架构
客户端 - 请求发起的地方。
查询 - 高级语言的SQL查询。
逻辑单位 - 关键字,表达式和运算符等
N/W数据包 - 与网络相关的代码。
协议 - 在SQL Server中,有4个协议。
- 共享内存(用于本地连接和故障排除目的)。
- 命名管道(用于LAN连接的连接)。
- TCP/IP(用于WAN连接的连接)。
- VIA-虚拟接口适配器(需要由供应商设置的特殊硬件,也不适用于SQL 2012版本)。
服务器 - 安装了SQL服务并驻留数据库的位置。
关系引擎 - 这是实际执行的地方。 它包含查询解析器,查询优化器和查询执行器。
查询解析器(Command Parser)和编译器(Translator) - 这将检查查询的语法,并将查询转换为机器语言。
查询优化器 - 它将通过以查询,统计和代数树作为输入来准备执行计划作为输出。
执行计划 - 就像一个路线图,它包含作为查询执行的一部分执行的所有步骤的顺序。
查询执行器 - 这是在执行计划的帮助下逐步执行查询的位置,同时还会联系存储引擎。
存储引擎 - 它负责存储和检索存储系统(磁盘,SAN等)上的数据,数据操作,锁定和管理事务。
SQL OS - 位于主机(Windows OS)和SQL Server之间。 所有在数据库引擎上执行的活动都由SQL OS负责。 SQL OS提供各种操作系统服务,例如使用阻塞和锁定结构处理缓冲池,日志缓冲区和死锁检测的内存管理。
检查点进程 - 检查点是一个内部进程,它将缓冲区高速缓存中的所有脏页(修改页)写入物理磁盘。 除此之外,它还将日志记录从日志缓冲区写入物理文件。 从缓冲区缓存写入脏页面到数据文件也称为脏页面的硬化。
它是一个专用的进程,由SQL Server以特定的时间间隔自动运行。 SQL Server分别为每个数据库运行检查点进程。 在发生意外关机或系统崩溃/故障时,Checkpoint有助于减少SQL Server的恢复时间。
SQL Server中的检查点
在SQL Server 2012中,有四种类型的检查点 -
- 自动 - 这是在后台运行的最常见的检查点,以确保SQL Server数据库可以在恢复间隔 - 服务器配置选项定义的时间限制内恢复。
- 间接 - 这是SQL Server 2012中的新增功能。它也在后台运行,但要满足配置选项的特定数据库的用户指定目标恢复时间。 一旦选择了给定数据库的Target_Recovery_Time,它将覆盖为服务器指定的恢复时间间隔,并避免此类数据库上的自动检查点。
- 手动 - 这个运行就像任何其他T-SQL语句一样,一旦发出检查点命令,它将运行到完成状态。 手动检查点仅针对当前数据库运行。 您还可以指定可选的
Checkpoint_Duration
- 此持续时间指定希望检查点完成的时间。 内部 - 作为用户,无法控制内部检查点。针对特定操作发布如:
- 除非关闭不干净(Shutdown with nowait),否则关机会在所有数据库上启动检查点操作。
- 如果恢复模式从Full/Bulk-logged更改为
Simple
。 - 在备份数据库时。
- 如果数据库处于简单(
Simple
)恢复模式,则检查点进程会在日志变满70%
或基于服务器选项 - 恢复间隔时自动执行。 - 修改数据库命令以添加或删除
data/log
文件也会启动检查点。 - 当数据库的恢复模型被批量记录并执行最小化记录操作时,检查点也会发生。
- 数据库快照创建。
惰性编写器进程 - 懒惰的编写器会将脏页面推送到磁盘,原因完全不同,因为它需要释放缓冲池中的内存。 当SQL服务器受到内存压力时会发生这种情况。这是由一个内部进程控制,并不能设置它。
SQL服务器不断监视内存使用情况以评估资源争用(或可用性); 其工作是确保在任何时候都有一定数量的可用空间。 作为此过程的一部分,当它发现任何此类资源争用时,它会触发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
语句中使用文件组关键字指定的任何文件组。
每个数据库中的一个文件组用作默认文件组。 当SQL Server将页面分配给创建时未指定文件组的表或索引时,将从默认文件组中分配页面。 要将默认文件组从一个文件组切换到另一个文件组,它应该具有db_owner
固定的数据库角色。
默认情况下,主文件组是默认文件组。 用户应该拥有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和图像数据以外的所有数据的数据行。
- 索引 - 索引条目。
- Text/Image - 文本,图像和ntext数据。
- GAM - 关于分配盘区的信息。
- SGAM - 有关系统级分配扩展的信息。
- 页面可用空间(PFS) - 有关页面上可用空间的信息。
- 索引分配映射(IAM) - 关于表或索引使用的范围的信息。
- 批量更改映射(BCM) - 有关自上次备份日志语句以来批量操作修改的区的信息。
- 差异更改映射(DCM) - 有关自上次备份数据库语句以来发生更改的范围的信息。
日志文件架构
SQL Server事务日志在逻辑上运行,就好像事务日志是一串日志记录一样。 每个日志记录由日志序列号(LSN)标识。 每个日志记录都包含它所属的事务的ID。
用于数据修改的日志记录记录执行的逻辑操作,或者记录修改后数据的前后镜像。 之前镜像是执行操作之前的数据的副本; 后镜像是操作执行后的数据的副本。
恢复操作的步骤取决于日志记录的类型 -
- 逻辑操作已记录。
- 要向前滚动逻辑操作,将再次执行操作。
- 要回滚逻辑操作,执行反向逻辑操作。
- 记录镜像之前和之后。
- 要向前滚动操作,应用后镜像。
- 要回滚操作,应用之前的镜像。
事务日志中记录了不同类型的操作。 这些操作包括 -
- 每个事务的开始和结束。
- 每个数据修改(插入,更新或删除)。 这包括系统存储过程或数据定义语言(DDL)语句对任何表(包括系统表)的更改。
- 每个程度和页面分配或分配。
- 创建或删除表或索引。
还会记录回滚操作。 每个事务都在事务日志中保留空间,以确保存在足够的日志空间来支持由显式回滚语句或遇到错误导致的回滚。 当事务完成时,这个保留空间被释放。
第一个日志记录中必须存在的日志文件中用于成功数据库范围回滚到最后写入日志记录的部分称为日志的活动部分或活动日志。 这是完全恢复数据库所需的日志部分。 活动日志的任何部分都不能被截断。 这第一个日志记录的LSN被称为最小恢复LSN(最小LSN)。
SQL Server数据库引擎将每个物理日志文件内部分为多个虚拟日志文件。 虚拟日志文件没有固定大小,并且物理日志文件没有固定数量的虚拟日志文件。
数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。 数据库引擎会尝试维护少量的虚拟文件。 虚拟日志文件的大小或数量不能由管理员配置或设置。 虚拟日志文件唯一影响系统性能的时间是物理日志文件是否由size
和growth_increment
值定义。
size
值是日志文件的初始大小,growth_increment
值是每次需要新空间时添加到文件的空间量。 如果由于许多小增量而使日志文件增长到很大,则它们将具有许多虚拟日志文件。 这可能会降低数据库启动速度,还会记录备份和恢复操作。
建议您为日志文件分配一个接近所需最终大小的大小值,并且还具有相对较大的growth_increment
值。 SQL Server使用预写日志(WAL),它保证在关联的日志记录写入磁盘之前不会将数据修改写入磁盘。 这保持了事务的ACID属性。