SQL Server 内存管理：Buffer Pool 与 Columnstore

内存是 SQL Server 最宝贵的资源。

一条查询如果能在内存中完成，根本不需要触碰磁盘。Buffer Pool 就是 SQL Server 的「内存数据库」——它缓存了热数据，让频繁访问的数据保持在高速缓存中。

但 Buffer Pool 不是越大越好。如何配置？如何诊断内存问题？如何利用 Columnstore 进一步提升性能？

今天，我们来深入理解 SQL Server 的内存管理。

SQL Server 内存架构

┌─────────────────────────────────────────────────────────────┐
│                 SQL Server 内存架构                         │
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐ │
│  │                      内存                              │ │
│  │                                                          │ │
│  │  ┌──────────────────────────────────────────────────┐ │ │
│  │  │              Buffer Pool（缓冲池）                │ │ │
│  │  │  ┌────────────┐ ┌────────────┐ ┌────────────┐ │ │ │
│  │  │  │ 数据页缓存  │ │ 索引页缓存  │ │  Free Page │ │ │ │
│  │  │  │ (8KB/页)   │ │  (8KB/页)   │ │            │ │ │ │
│  │  │  └────────────┘ └────────────┘ └────────────┘ │ │ │
│  │  │                                                   │ │ │
│  │  │  ┌────────────────────────────────────────────┐ │ │ │
│  │  │  │         Procedure Cache                    │ │ │ │
│  │  │  │  ┌─────────┐ ┌─────────┐ ┌─────────┐       │ │ │ │
│  │  │  │  │ 计划 1  │ │ 计划 2  │ │ 计划 3  │       │ │ │ │
│  │  │  │  └─────────┘ └─────────┘ └─────────┘       │ │ │ │
│  │  │  └────────────────────────────────────────────┘ │ │ │
│  │  └──────────────────────────────────────────────────┘ │ │
│  │                                                          │ │
│  │  ┌──────────────────────────────────────────────────┐ │ │
│  │  │              缓冲池外的内存                       │ │ │
│  │  │  ┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐     │ │ │
│  │  │  │  连接  │ │  线程  │ │  锁定  │ │ 其他  │     │ │ │
│  │  │  │ 工作区 │ │  堆栈  │ │  列表  │ │       │     │ │ │
│  │  │  └────────┘ └────────┘ └────────┘ └────────┘     │ │ │
│  │  └──────────────────────────────────────────────────┘ │ │
│  └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘

Buffer Pool（缓冲池）

Buffer Pool 的作用

Buffer Pool = SQL Server 的数据页缓存。

┌─────────────────────────────────────────────────────────────┐
│                 Buffer Pool 工作原理                         │
│                                                              │
│  查询 SELECT * FROM Orders WHERE order_id = 1001            │
│                                                              │
│  ┌─────────────────────────────────────────────────────┐ │
│  │ 1. 检查缓冲池是否有缓存                               │ │
│  │    ├─ 命中 → 直接返回（极快）                       │ │
│  │    └─ 未命中 → 继续                                 │ │
│  │                                                     │ │
│  │ 2. 从磁盘读取数据页                                 │ │
│  │    └─ 8KB 数据加载到缓冲池                          │ │
│  │                                                     │ │
│  │ 3. 返回查询结果                                     │ │
│  └─────────────────────────────────────────────────────┘ │
│                                                              │
│  写操作 UPDATE Orders SET status = 'shipped'                │
│                                                              │
│  ┌─────────────────────────────────────────────────────┐ │
│  │ 1. 检查缓冲池                                       │ │
│  │ 2. 内存中修改（标记为脏页）                         │ │
│  │ 3. 日志写入（必须先写日志）                         │ │
│  │ 4. 后台异步刷写到磁盘                               │ │
│  └─────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘

Buffer Pool 的大小配置

sql

-- 查看当前内存配置
SELECT 
    name,
    value,
    value_in_use,
    CASE name
        WHEN 'min server memory (MB)' THEN '最小内存'
        WHEN 'max server memory (MB)' THEN '最大内存'
    END AS description
FROM sys.configurations
WHERE name LIKE '%memory%';

-- 推荐配置（根据可用物理内存）
-- 32GB 物理内存：max server memory = 28GB
-- 64GB 物理内存：max server memory = 56GB
-- 128GB 物理内存：max server memory = 112GB

-- 保留 10-15% 给操作系统和其他用途

-- 设置最大内存（MB 为单位）
EXEC sp_configure 'max server memory (MB)', 57344;  -- 56GB
RECONFIGURE;

内存压力诊断

sql

-- Page Life Expectancy (PLE)：页在缓冲池中的平均存活时间
-- 低于 300 秒通常表示内存压力

SELECT 
    cntr_value AS PageLifeExpectancy,
    CASE 
        WHEN cntr_value &lt; 100 THEN '严重内存压力'
        WHEN cntr_value &lt; 300 THEN '内存压力'
        ELSE '正常'
    END AS Status
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy';

-- Buffer Pool 命中率
SELECT 
    (a.cntr_value * 1.0 / b.cntr_value) * 100 AS BufferCacheHitRatio,
    a.cntr_value AS BufferCacheHits,
    b.cntr_value AS BufferCacheMisses
FROM 
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio') a,
    (SELECT cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio base') b;

-- 目标：Buffer Cache Hit Ratio > 95%（OLTP）
-- 如果 &lt; 90%，需要增加内存或优化查询

Buffer Pool 内部结构

sql

-- 查看缓冲池中的数据库页
SELECT 
    DB_NAME(database_id) AS DatabaseName,
    COUNT(*) AS PageCount,
    COUNT(*) * 8 / 1024 AS SizeMB,
    CAST(SUM(CASE WHEN is_modified = 1 THEN 1 ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10,2)) AS DirtyPagesMB,
    CAST(SUM(CASE WHEN is_modified = 0 THEN 1 ELSE 0 END) * 8.0 / 1024 AS DECIMAL(10,2)) AS CleanPagesMB
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4  -- 排除系统数据库
GROUP BY database_id
ORDER BY SizeMB DESC;

-- 查看最热的缓存页
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    COUNT(*) AS PageCount,
    COUNT(*) * 8 / 1024 AS SizeMB
FROM sys.dm_os_buffer_descriptors bd
JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id
JOIN sys.partitions p ON au.container_id = p.hobt_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE bd.database_id = DB_ID()
  AND OBJECTPROPERTY(p.object_id, 'IsUserTable') = 1
GROUP BY p.object_id, i.name
ORDER BY SizeMB DESC;

Columnstore 索引与批处理模式

列存储索引的内存优势

┌─────────────────────────────────────────────────────────────┐
│                 行存储 vs 列存储                           │
│                                                              │
│  行存储（传统 B-Tree）：                                    │
│  ┌─────────────────────────────────────────────────────┐ │
│  │ Page 1: [Row1全部列][Row2全部列][Row3全部列]...     │ │
│  │ Page 2: [Row4全部列][Row5全部列][Row6全部列]...     │ │
│  └─────────────────────────────────────────────────────┘ │
│  读取需求：读取全部列，即使只需要 2 列                     │
│                                                              │
│  列存储：                                                   │
│  ┌─────────────────────────────────────────────────────┐ │
│  │ Page 1: [Col1所有值]  Page 2: [Col2所有值]          │ │
│  │ Page 3: [Col3所有值]  Page 4: [Col4所有值]          │ │
│  └─────────────────────────────────────────────────────┘ │
│  读取需求：只读取需要的列                                   │
└─────────────────────────────────────────────────────────────┘

批处理模式（Batch Mode）

批处理模式 = 一次处理一组行（900 行），而非逐行处理。

sql

-- 查看执行计划是否使用批处理模式
-- 在 SSMS 执行计划中：
-- 查看属性 → Execution Mode = Batch 或 Row

-- 批处理模式的优势
-- Row Mode: 1 行 × 1 行 × 1 行 × ... × CPU 开销大
-- Batch Mode: 900 行 × 900 行 × ... × CPU 开销小

-- 批处理模式自动用于：
-- 1. 列存储索引
-- 2. 多核 CPU（自动向量化）
-- 3. 大数据量聚合

创建列存储索引

sql

-- 聚集列存储索引（SQL Server 2014+）
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Orders
ON Orders;
-- 表数据本身以列存储格式存储
-- 适合分析型工作负载

-- 非聚集列存储索引（分析查询）
CREATE COLUMNSTORE INDEX CS_Orders
ON Orders (order_id, customer_id, order_date, total_amount);
-- 保留原表结构，额外创建列存储索引

-- 混合方案：事务用行存储，分析用列存储
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,  -- 行存储主键
    customer_id INT,
    order_date DATETIME,
    total_amount DECIMAL(10,2)
);
CREATE CLUSTERED COLUMNSTORE INDEX CCI ON Orders;  -- 全部列存

列存储索引的内存管理

sql

-- 查看列存储相关内存使用
SELECT 
    object_name AS CounterName,
    cntr_value AS Value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE '%columnstore%'
   OR counter_name LIKE '%batch%';

-- 列存储的元组移动器（Tuple Mover）
-- 监控列存储压缩状态
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    index_id,
    state_desc,
    total_rows,
    compressed_rows,
    100.0 * compressed_rows / NULLIF(total_rows, 0) AS CompressionPercent
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id = OBJECT_ID('Orders');

内存压力诊断

低内存诊断

sql

-- 查看 SQL Server 内存使用
SELECT 
    object_name AS Resource,
    counter_name AS Metric,
    cntr_value AS Value,
    CASE 
        WHEN counter_name = 'Total Server Memory (KB)' 
            THEN CAST(cntr_value / 1024.0 AS DECIMAL(10,2))
        WHEN counter_name = 'Target Server Memory (KB)'
            THEN CAST(cntr_value / 1024.0 AS DECIMAL(10,2))
        ELSE cntr_value
    END AS ValueMB
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Total Server Memory (KB)',
    'Target Server Memory (KB)',
    'Granted Workspace Memory (KB)',
    'Maximum Workspace Memory (KB)'
);

-- 分析：
-- Total Server Memory：当前使用
-- Target Server Memory：目标（等于 max server memory 或更低）
-- 如果 Total &lt;&lt; Target：内存压力，Buffer Pool 被压缩

内存 clerk 分析

sql

-- 查看各类内存 clerk 的使用
SELECT 
    type AS MemoryClerkType,
    SUM(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) / 1024 AS TotalMB,
    SUM(single_pages_kb) / 1024 AS SinglePageMB,
    SUM(virtual_memory_committed_kb) / 1024 AS VirtualMemoryMB,
    COUNT(*) AS ClerkCount
FROM sys.dm_os_memory_clerks
WHERE database_id > 4
GROUP BY type
ORDER BY TotalMB DESC;

-- 主要类型：
-- CACHESTORE_SQLCP：SQL 计划缓存
-- CACHESTORE_OBJCP：对象计划缓存
-- USERSTORE_SCHEMA：架构信息缓存
-- MEMORYCLERK_SQLBUFFERPOOL：缓冲池

解决内存问题

sql

-- 1. 增加 SQL Server 内存
EXEC sp_configure 'max server memory (MB)', 65536;  -- 64GB
RECONFIGURE;

-- 2. 清理过程缓存
DBCC FREESYSTEMCACHE('SQL Plans');  -- 清理 SQL 计划
DBCC FREEPROCCACHE;                  -- 清理所有计划（慎用！）

-- 3. 清理缓冲池（测试用）
DBCC DROPCLEANBUFFERS;  -- 删除所有干净页，保留脏页

内存优化表（In-Memory OLTP）

内存优化表简介

内存优化表（Memory-Optimized Tables）= 完全在内存中的表，没有磁盘 I/O。

sql

-- 创建内存优化文件组
ALTER DATABASE MyDB ADD FILEGROUP MOFD FILE (
    NAME = 'MofFile',
    FILENAME = 'D:\Data\MofFile'
) CONTAINS MEMORY_OPTIMIZED_DATA;

-- 创建内存优化表
CREATE TABLE Orders_MemOpt (
    order_id INT NOT NULL PRIMARY KEY NONCLUSTERED,
    customer_id INT NOT NULL,
    order_date DATETIME2 NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    INDEX ix_customer HASH (customer_id) WITH (BUCKET_COUNT = 1024),
    INDEX ix_date NONCLUSTERED (order_date)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

内存优化表的类型

类型	说明	持久化
SCHEMA_AND_DATA	完全持久化，支持恢复	✓ 日志 + 内存
SCHEMA_ONLY	仅持久化结构	✗ 仅内存

限制与注意事项

sql

-- 内存优化表的限制：
-- 1. 每表必须有主键
-- 2. 不支持某些数据类型（text, ntext, image）
-- 3. 索引必须是哈希或非聚集
-- 4. 不能有外键约束
-- 5. 内存占用来自 max server memory 配置

-- 查看内存优化表使用
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    memory_allocated_for_table_kb / 1024 AS TableAllocatedMB,
    memory_used_by_table_kb / 1024 AS TableUsedMB
FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id > 0;

面试追问方向

SQL Server 内存架构是怎样的？Buffer Pool 的作用是什么？
如何配置 SQL Server 的最大内存？
Page Life Expectancy (PLE) 是什么指标？如何诊断内存压力？
列存储索引和行存储索引在内存方面有什么区别？
什么是批处理模式？有什么优势？
内存优化表（In-Memory OLTP）是什么？有什么限制？

下一步

理解了内存管理，我们来看 SQL Server 表分区：分区函数与分区方案，学习如何用分区表提升性能。

SQL Server 内存管理：Buffer Pool 与 Columnstore ​

SQL Server 内存架构 ​

Buffer Pool（缓冲池） ​

Buffer Pool 的作用 ​

Buffer Pool 的大小配置 ​

内存压力诊断 ​

Buffer Pool 内部结构 ​

Columnstore 索引与批处理模式 ​

列存储索引的内存优势 ​

批处理模式（Batch Mode） ​

创建列存储索引 ​

列存储索引的内存管理 ​

内存压力诊断 ​

低内存诊断 ​

内存 clerk 分析 ​

解决内存问题 ​

内存优化表（In-Memory OLTP） ​

内存优化表简介 ​

内存优化表的类型 ​

限制与注意事项 ​

面试追问方向 ​

下一步 ​

SQL Server 内存管理：Buffer Pool 与 Columnstore

SQL Server 内存架构

Buffer Pool（缓冲池）

Buffer Pool 的作用

Buffer Pool 的大小配置

内存压力诊断

Buffer Pool 内部结构

Columnstore 索引与批处理模式

列存储索引的内存优势

批处理模式（Batch Mode）

创建列存储索引

列存储索引的内存管理

内存压力诊断

低内存诊断

内存 clerk 分析

解决内存问题

内存优化表（In-Memory OLTP）

内存优化表简介

内存优化表的类型

限制与注意事项

面试追问方向

下一步