第二期：缓冲池与内存架构 —— 为什么缓存命中率决定性能？

第二期：缓冲池与内存架构 —— 为什么缓存命中率决定性能？

1. 内存整体视图：SQL Server 如何使用操作系统内存？

SQL Server 实例启动后，会向操作系统申请一块内存（Min Server Memory ~ Max Server Memory），主要由以下部分组成：

内存区域	作用	是否可释放
缓冲池（Buffer Pool）	缓存数据页（.mdf/.ndf）	✅ 可释放给其他缓存或 OS
计划缓存	存储执行计划、查询编译结果	✅ 可释放（受内存压力时）
日志缓存	暂存待写入 .ldf 的日志记录	很小的固定区域
连接/线程/锁等结构	管理并发、会话、事务	相对固定
其他组件	CLR、链接服务器、扩展事件等

• 缓冲池通常占整个 SQL Server 内存的 70%~80%，是绝对主力。
• 现象解释：为什么任务管理器显示 SQL Server 占用内存很高且不释放？因为缓冲池是主动缓存，它会尽量多占内存以减少磁盘 I/O。一旦被 OS 或其他进程挤压，SQL Server 会适当释放（但默认“不主动让出”）。

2. 缓冲池内部结构 —— 不只是“一堆内存页”

缓冲池按 8KB 数据页 为单位管理，每个页代表磁盘上的一个数据页或索引页。关键组件：

• 页头：记录页的元信息（对象ID、分区ID、LSN、页类型等）。

• 实际数据：行、索引键、或大对象指针。

• 页的三种状态：

  • Clean（干净页）：内存中的内容与磁盘完全一致，可直接丢弃。
  • Dirty（脏页）：已被修改但未写回磁盘，不能随意丢弃。
  • Free（空闲页）：还未被使用。

• 可用页列表（Free List）：空页的链表，用于快速分配新页。

• 懒写入器（LazyWriter）：当内存压力出现时，扫描缓冲池，将干净页直接丢弃，将脏页触发写入磁盘后再丢弃，腾出空间。

• 检查点（Checkpoint）：定期或主动触发，将所有脏页写回磁盘，为恢复缩短重做时间。

3. 页面读取流程（命中 vs 未命中）

查询请求一个数据页（PageID）

1. 缓冲池查找 -> 命中 -> 直接使用（逻辑读）
                    |
                   未命中
                    ↓
2. 从磁盘异步读取（物理读）到缓冲池，可能触发淘汰：
   - 优先从 Free List 取空页
   - 如果 Free List 太少，LazyWriter 清理干净页/脏页
   - 若 LazyWriter 也赶不上，查询等待（ASYNC_IO_COMPLETION）

• 现象解释：为什么第一次查询慢，后来快？因为第一次是物理读（从 .mdf 读到内存），后续是逻辑读（直接从缓冲池命中）。

4. 页面写入 —— 脏页如何落盘？

• Checkpoint（自动每 1 分钟或手动）：

  • 扫描每个数据库的脏页，批量写出到数据文件。
  • 写入完成后在数据库的 启动页 记录最后一个 LSN（日志序列号），供恢复使用。

• LazyWriter（内存压力时）：

  • 行为类似 Checkpoint，但目标更激进（腾出空间而非仅做恢复点）。

• EagerWrite（大操作如创建索引）：主动提前写出，避免内存爆炸。

• 间接检查点（Indirect Checkpoint）（SQL Server 2012+）：允许按数据库设置目标恢复时间，写得更平滑。

• 关键保证：WAL 原则 —— 日志总是先于数据页写入磁盘。脏页可以延迟写回，但对应的日志必须已经硬化。

5. 监控内存与缓冲池的关键指标

指标	意义	健康阈值
Page Life Expectancy (PLE)	一个页在缓冲池中平均停留的秒数。过低表示频繁被挤出。	一般 >300 秒，OLTP 可更高
Buffer Cache Hit Ratio	(逻辑读 - 物理读)/逻辑读，反映命中率。	>95% 较好，<90% 需关注
Free List Stalls/sec	查询等待空页的次数。持续大于0 → 严重内存压力	接近 0
Lazy Writes/sec	LazyWriter 每秒刷出的页数。过高说明内存不足	持续高需增加内存或减少工作集
Checkpoint Pages/sec	检查点刷脏页的速率	正常波动大，突然飙升无大碍

• 如何查看：

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%';

SELECT [name], [target_time], [actual_time] 
FROM sys.dm_os_ring_buffers;  -- 间接检查点记录

6. 常见内存压力现象与排查

现象	典型原因	架构层面解释
PLE 持续低于 60 秒	内存不足，页刚进缓冲池就被挤出	LazyWriter 频繁清空脏页/干净页，导致大量物理读
Free List Stalls > 0	没有可用空页，所有页都在使用或等待写入	内存太小，或存在大量无法快速写入的脏页（I/O 瓶颈）
系统整体响应慢，但 CPU 低	巨型物理读，磁盘忙	缓冲池太小，或查询无索引导致表扫描频繁
重启实例后 PLE 很低	正常，缓冲池需要“预热”	启动时空缓冲池，所有读都是物理读
一个查询占用大量内存且不释放	内存授予（Memory Grant）过大（如排序、哈希连接）	执行引擎从缓冲池外额外申请内存，可能引起内存压力

7. 调优建议（按推荐顺序）

1. 正确设置 Max Server Memory

   • 为 OS 和其他进程保留 10%~20% 内存（例如总 64GB，SQL 设 48~50GB）。
   • 避免设过高导致 OS 内存交换（pagefile）拖累整体。

2. 为 tempdb 增加文件并放在快速磁盘

   • tempdb 页操作很频繁，减少它的 I/O 压力能解放缓冲池。

3. 优化查询以减少不必要的数据页读入

   • 覆盖索引、搜索参数 SARG、避免全表扫描。

4. 使用间接检查点

   • 平滑脏页写入，缓解周期性 I/O 尖峰：

ALTER DATABASE <dbname> SET TARGET_RECOVERY_TIME = 60 SECONDS;

5. 监控并释放计划缓存（谨慎）
   • 只有确认计划缓存占用过大时才手动清除：

DBCC FREEPROCCACHE;  -- 不推荐生产随意用

---

第二期小结

缓冲池是 SQL Server 内存架构的核心，它通过缓存数据页极大减少物理 I/O。理解 页状态（干净/脏）、Checkpoint vs LazyWriter、以及关键计数器（PLE、Free List Stalls）能帮你快速定位内存不足或 I/O 瓶颈。大多数“慢”的根本原因，都可以追溯到缓冲池命中和写入压力。

下期预告：存储引擎深度（二）—— 事务日志、WAL 与崩溃恢复，为什么即使断电也很难丢数据？