SQLServer 物理架构

SQL Server 物理架构描述了数据在磁盘上的存储方式和组织形式，包括数据库文件、文件组、页面结构和I/O管理等。了解 SQL Server 物理架构对于 DBA 来说至关重要，它有助于优化存储设计、提高 I/O 性能和确保数据安全。本文将详细介绍 SQL Server 的物理架构，包括核心组件、存储结构和最佳实践。

数据库文件

SQL Server 数据库由三种类型的文件组成：

1. 主要数据文件 (Primary Data File)

• 扩展名：.mdf
• 作用：包含数据库的系统表和元数据
• 要求：每个数据库必须有且只有一个主要数据文件
• 默认位置：通常位于 SQL Server 安装目录的 DATA 文件夹
• 大小限制：最大可达 524 PB

2. 次要数据文件 (Secondary Data Files)

• 扩展名：.ndf
• 作用：存储用户数据，可以分散到多个磁盘以提高 I/O 性能
• 要求：可选，每个数据库可以有零个或多个次要数据文件
• 位置：可以位于不同的磁盘驱动器上
• 大小限制：最大可达 524 PB

3. 事务日志文件 (Transaction Log File)

• 扩展名：.ldf
• 作用：记录所有数据修改操作，用于恢复和回滚事务
• 要求：每个数据库至少有一个事务日志文件
• 位置：建议与数据文件放在不同的磁盘上，以提高性能和安全性
• 大小限制：最大可达 524 PB

文件组

文件组是数据库文件的逻辑集合，用于组织和管理数据文件：

1. 主要文件组 (Primary Filegroup)

• 包含主要数据文件和所有未指定文件组的对象
• 包含系统表和元数据
• 不能被删除

2. 用户定义文件组

• 由用户创建，用于组织用户数据
• 可以包含一个或多个数据文件
• 可以用于优化 I/O 性能，将不同的表或索引分配到不同的文件组

3. 默认文件组

• 新创建的对象默认所属的文件组
• 默认情况下，主要文件组是默认文件组
• 可以通过 ALTER DATABASE 语句修改默认文件组

4. 文件流文件组 (Filestream Filegroup)

• 用于存储 FILESTREAM 数据
• 需要单独的数据容器
• 用于存储大型二进制数据，如文档、图片和视频

页面结构

SQL Server 数据存储的基本单位是页面 (Page)，每个页面的大小为 8 KB：

1. 页面类型

页面类型	描述
数据页 (Data Page)	存储表数据
索引页 (Index Page)	存储索引数据
LOB 页 (LOB Page)	存储大型对象数据 (text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml)
行溢出页 (Row Overflow Page)	存储超过 8060 字节的行数据
IAM 页 (Index Allocation Map)	跟踪区 (Extent) 的分配情况
GAM 页 (Global Allocation Map)	跟踪已分配的区
SGAM 页 (Shared Global Allocation Map)	跟踪已分配且有空闲空间的区
PFS 页 (Page Free Space)	跟踪页面的可用空间
DCM 页 (Differential Changed Map)	跟踪自上次完整备份以来修改的区
BCM 页 (Bulk Changed Map)	跟踪自上次日志备份以来使用大容量操作修改的区

2. 数据页结构

每个数据页包含以下部分：

• 页头 (96 字节)：包含页面元数据，如页面类型、页码、对象 ID 等
• 数据行：存储实际的数据行
• 行偏移数组：存储每行数据的偏移量，从页面末尾开始向前增长
• 可用空间：页面中未使用的空间

3. 行结构

数据行包含以下部分：

• 行头：包含行状态、列计数等信息
• 列偏移数组：存储每列数据的偏移量
• 列数据：存储实际的列值

4. 区 (Extent)

区是由 8 个连续页面组成的存储单元，大小为 64 KB：

• 统一区 (Uniform Extent)：由单个对象独占使用
• 混合区 (Mixed Extent)：可以由多个对象共享使用（最多 8 个对象）

存储分配

SQL Server 使用以下机制管理存储分配：

1. 分配单元 (Allocation Unit)

分配单元是对象存储数据的基本单位，每个表或索引至少包含一个分配单元：

• IN_ROW_DATA：存储行内数据和非大型对象数据
• LOB_DATA：存储大型对象数据
• ROW_OVERFLOW_DATA：存储超过 8060 字节的行数据

2. 分配映射页

SQL Server 使用以下映射页跟踪存储分配：

IAM 页 (Index Allocation Map)

• 跟踪对象使用的所有区
• 每个表或索引至少有一个 IAM 页
• 存储区的分配状态

GAM 页 (Global Allocation Map)

• 跟踪数据库中所有已分配的区
• 每个 GAM 页覆盖 64,000 个区（约 4 GB）
• 每个位代表一个区，1 表示未分配，0 表示已分配

SGAM 页 (Shared Global Allocation Map)

• 跟踪已分配且有空闲空间的混合区
• 每个 SGAM 页覆盖 64,000 个区（约 4 GB）
• 每个位代表一个区，1 表示区是混合区且有空闲空间，0 表示其他情况

PFS 页 (Page Free Space)

• 跟踪页面的可用空间
• 每个 PFS 页覆盖 8,088 个页面（约 64 MB）
• 每个字节代表一个页面，记录页面的可用空间百分比

DCM 页 (Differential Changed Map)

• 跟踪自上次完整备份以来修改的区
• 用于差异备份，只备份修改的区

BCM 页 (Bulk Changed Map)

• 跟踪自上次日志备份以来使用大容量操作修改的区
• 用于日志备份，优化大容量操作的备份性能

I/O 管理

SQL Server 使用以下机制管理 I/O 操作：

1. 缓冲区管理

• 缓冲池：内存中的数据页缓存，减少磁盘 I/O 操作
• 预读：提前加载预计需要的数据页到缓冲池
• 延迟写入：异步将脏页写入磁盘，提高写入性能
• 检查点：定期将脏页写入磁盘，减少恢复时间

2. 写入机制

• 事务日志写入：同步写入，保证事务持久性
• 数据页写入：异步写入，提高写入性能
• 批量写入：合并多个小的写入操作，减少 I/O 次数

3. 读取机制

• 单页读取：读取单个页面，用于随机访问
• 预读读取：读取连续的多个页面，用于顺序访问
• 异步读取：允许在等待 I/O 完成时处理其他任务

存储优化最佳实践

1. 文件和文件组设计

• 分离数据和日志文件：将数据文件和日志文件放在不同的物理磁盘上，减少 I/O 冲突
• 使用多个数据文件：将大型表分布到多个数据文件，提高并行 I/O 性能
• 使用文件组隔离大型对象：将 LOB 数据和行溢出数据放在单独的文件组
• 考虑存储性能特性：将频繁访问的数据放在高性能存储上，将不常用的数据放在低成本存储上

2. 页面和区管理

• 避免页分裂：合理设计索引，避免频繁的页分裂操作
• 监控页面密度：定期检查页面填充度，避免碎片过多
• 使用合适的填充因子：根据数据修改频率设置合适的填充因子
• 定期重建或重组索引：减少索引碎片，提高查询性能

3. I/O 性能优化

• 使用 RAID 配置：根据性能和可靠性要求选择合适的 RAID 级别（如 RAID 10 用于数据文件，RAID 1 用于日志文件）
• 优化存储子系统：使用 SSD 存储提高 I/O 性能，合理配置存储控制器缓存
• 调整 I/O 调度策略：根据工作负载类型选择合适的 I/O 调度器
• 监控 I/O 性能：使用性能计数器和 DMV 监控 I/O 延迟和吞吐量

4. 备份和恢复优化

• 使用多个备份设备：并行备份到多个设备，提高备份速度
• 合理设置备份压缩：使用备份压缩减少备份大小和 I/O 操作
• 考虑备份位置：将备份存储在与生产数据不同的物理位置
• 优化恢复时间目标 (RTO)：根据业务需求调整数据库配置，减少恢复时间

版本差异

SQL Server 不同版本在物理架构上的主要差异：

1. SQL Server 2012 及之前

• 传统的存储架构
• 有限的压缩选项
• 基本的 I/O 优化功能

2. SQL Server 2014

• 引入缓冲区池扩展 (Buffer Pool Extension)，支持使用 SSD 扩展缓冲池
• 增强的备份压缩
• 改进的检查点机制

3. SQL Server 2016

• 引入 Stretch Database，将冷数据自动迁移到 Azure Blob 存储
• 增强的行级压缩和页级压缩
• 改进的存储引擎性能

4. SQL Server 2017

• 跨平台支持，支持在 Linux 上运行
• 增强的 TempDB 性能
• 改进的 I/O 子系统

5. SQL Server 2019

• 引入持久化内存 (PMEM) 支持，提高 I/O 性能
• 增强的 Columnstore 索引性能
• 改进的批量导入性能

6. SQL Server 2022

• Azure Synapse Link 集成，支持直接查询 Azure Synapse Analytics
• 增强的 Always On 可用性组存储性能
• 改进的智能查询处理，减少 I/O 操作

监控和维护

1. 监控存储使用情况

• 使用 DMV 监控：

  • sys.dm_db_file_space_usage：监控数据库文件空间使用情况
  • sys.dm_db_index_physical_stats：监控索引碎片情况
  • sys.dm_io_virtual_file_stats：监控文件 I/O 统计信息

• 使用性能计数器监控：

  • SQLServer:Databases 计数器：监控数据库大小和增长情况
  • PhysicalDisk 计数器：监控磁盘 I/O 性能
  • LogicalDisk 计数器：监控逻辑磁盘空间使用情况

2. 维护任务

• 定期备份数据库：确保数据安全，支持恢复
• 重建或重组索引：减少索引碎片，提高查询性能
• 更新统计信息：确保查询优化器有准确的统计信息
• 检查数据库完整性：使用 DBCC CHECKDB 检查数据库完整性
• 监控和清理事务日志：避免事务日志过大导致的问题

常见问题 (FAQ)

Q: 如何确定数据库文件的最佳大小？

A: 确定数据库文件最佳大小的方法包括：

• 分析历史数据增长趋势
• 考虑业务增长预期
• 避免频繁的自动增长操作（设置合适的初始大小和增长增量）
• 监控文件空间使用情况，及时调整大小

Q: 如何优化事务日志性能？

A: 优化事务日志性能的方法包括：

• 将事务日志放在单独的物理磁盘上
• 使用合适的 RAID 级别（如 RAID 1 或 RAID 10）
• 避免过度的事务日志增长（设置合适的恢复模式和备份策略）
• 优化写入密集型应用，减少事务大小和频率

Q: 什么是页分裂，如何避免？

A: 页分裂是指当页面空间不足时，SQL Server 将页面分裂为两个页面的操作。避免页分裂的方法包括：

• 使用合适的填充因子，为数据增长预留空间
• 合理设计索引，避免在递增键上的频繁插入
• 定期重建或重组索引
• 避免在索引列上进行大量更新操作

Q: 如何监控和减少索引碎片？

A: 监控和减少索引碎片的方法包括：

• 使用 sys.dm_db_index_physical_stats DMV 监控碎片情况
• 当碎片率在 5%-30% 之间时，使用 ALTER INDEX REORGANIZE 重组索引
• 当碎片率超过 30% 时，使用 ALTER INDEX REBUILD 重建索引
• 考虑使用在线重建索引，减少对业务的影响

Q: 如何优化 TempDB 性能？

A: 优化 TempDB 性能的方法包括：

• 创建多个 TempDB 数据文件，数量等于或略少于 CPU 核心数
• 确保所有 TempDB 数据文件大小相同，避免文件自动增长
• 将 TempDB 放在高性能存储上
• 监控 TempDB 使用情况，避免过度使用
• 优化需要大量 TempDB 空间的查询

Q: 什么是 CHECKDB，何时应该运行？

A: DBCC CHECKDB 是用于检查数据库完整性的命令，包括：

• 检查数据库对象的逻辑和物理完整性
• 检查索引和数据页的一致性
• 检查系统表的完整性

建议定期运行 DBCC CHECKDB，频率取决于数据库的重要性和修改频率，通常每周或每月运行一次。对于大型数据库，可以考虑使用 PHYSICAL_ONLY 选项减少运行时间。

总结

SQL Server 物理架构是数据库性能和可靠性的基础，了解物理架构有助于 DBA 优化存储设计、提高 I/O 性能和确保数据安全。通过合理设计文件和文件组、优化页面和区管理、调整 I/O 子系统配置，DBA 可以显著提高 SQL Server 数据库的性能和可靠性。

同时，定期监控存储使用情况、执行维护任务和优化存储配置，也是确保 SQL Server 数据库长期稳定运行的关键。随着存储技术的不断发展（如 SSD、持久化内存等），DBA 需要不断学习和适应新的存储技术，以充分利用硬件优势，提高数据库性能。