SQL Server 性能优化实战（第七期）：内存架构——缓冲池、计划缓存与内存配置

SQL Server 性能优化实战（第七期）：内存架构——缓冲池、计划缓存与内存配置

一、SQL Server 内存架构概览

SQL Server 的内存主要分为以下几个区域：

核心原则：SQL Server 会尽可能多地占用内存用于缓存数据，减少物理 I/O。但需要给操作系统留足空间。

二、缓冲池（Buffer Pool）—— 性能的心脏

2.1 缓冲池的工作原理

当 SQL Server 需要读取一个数据页时：

1. 先在缓冲池中查找（内存命中）
2. 如果不在（内存未命中），从磁盘读取到缓冲池
3. 后续读取同一页时，直接从内存返回

缓冲池命中率 = 内存中找到的页数 / 总请求页数

-- 查看缓冲池命中率（目标 > 95%）
SELECT 
    object_name,
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Manager%'
  AND counter_name = 'Buffer cache hit ratio';

-- 更直观的查询
SELECT 
    CAST(SUM(CASE WHEN is_modified = 0 THEN 1 ELSE 0 END) AS FLOAT) 
    / COUNT(*) * 100 AS hit_rate
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID('YourDB');

2.2 查看缓冲池内容

-- 哪些表/索引占用了最多的内存
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    COUNT(*) AS BufferPages,
    COUNT(*) * 8 / 1024 AS BufferMB
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.partition_id
LEFT 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 bd.is_modified = 0  -- 干净的页（未修改）
GROUP BY p.object_id, i.name
ORDER BY BufferMB DESC;

-- 查看脏页（需要写入磁盘的页）
SELECT 
    COUNT(*) AS DirtyPages,
    COUNT(*) * 8 / 1024 AS DirtyMB
FROM sys.dm_os_buffer_descriptors
WHERE database_id = DB_ID()
  AND is_modified = 1;

2.3 内存压力诊断

-- 检查内存压力指标
SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Page life expectancy',          -- 页在缓冲池中的停留秒数，< 300 表示内存压力
    'Lazy writes/sec',               -- > 20 表示内存压力
    'Checkpoint pages/sec',          -- 检查点刷写的页数
    'Free list stalls/sec'           -- > 0 表示等待空闲页
);

-- 查看内存总量分布
SELECT 
    [type],
    name,
    pages_kb / 1024 AS MB
FROM sys.dm_os_memory_clerks
ORDER BY pages_kb DESC;

关键指标：

• Page Life Expectancy (PLE)：页在内存中的平均寿命（秒）。PLE < 300 秒说明内存严重不足。
• Lazy writes/sec：惰性写入器每秒刷出的页数。持续 > 20 表示内存压力。

三、计划缓存（Plan Cache）

3.1 为什么第二次查询更快？

第一次执行查询时：

1. SQL Server 解析 T-SQL → 语法树
2. 优化器生成多个执行计划
3. 选择成本最低的计划
4. 将计划存入计划缓存
5. 执行查询

第二次执行相同查询时：

1. 检查计划缓存中是否有匹配的计划
2. 直接重用（跳过编译和优化）

-- 查看计划缓存大小
SELECT 
    COUNT(*) AS PlanCount,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeMB
FROM sys.dm_exec_cached_plans;

-- 查看缓存计划占用的内存比例
SELECT 
    objtype,
    COUNT(*) AS Count,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS MB,
    SUM(CAST(size_in_bytes AS BIGINT)) * 100.0 / SUM(SUM(CAST(size_in_bytes AS BIGINT))) OVER() AS Percentage
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY MB DESC;

3.2 计划缓存的问题

问题1：参数嗅探（Parameter Sniffing）

当存储过程第一次执行时传入的“特殊值”导致生成一个对后续查询都不优的计划。

-- 示例：第一次传入 '2024-01-01'（数据量极少），生成了嵌套循环计划
-- 第二次传入 '2023-01-01'（数据量极大），仍然使用嵌套循环，性能极差

-- 解决方案1：使用本地变量
CREATE PROC GetOrders @Date DATE AS
BEGIN
    DECLARE @LocalDate DATE = @Date;
    SELECT * FROM Orders WHERE OrderDate = @LocalDate;
END

-- 解决方案2：使用 OPTION (RECOMPILE)
SELECT * FROM Orders WHERE OrderDate = @Date OPTION (RECOMPILE);

-- 解决方案3：使用 OPTION (OPTIMIZE FOR UNKNOWN)
SELECT * FROM Orders WHERE OrderDate = @Date OPTION (OPTIMIZE FOR UNKNOWN);

问题2：计划缓存膨胀

即席查询（Ad-hoc Queries）会导致计划缓存被大量一次性计划填满，浪费内存。

-- 检查即席查询占用的比例
SELECT 
    COUNT(*) AS TotalPlans,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS OneTimePlans,
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS OneTimePercent
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc';

-- 启用针对即席查询的优化（SQL Server 2008+）
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
-- 启用后，第一次执行的即席查询只存一个"存根"，第二次执行才存完整计划

3.3 手动管理计划缓存

-- 清除所有计划缓存（谨慎！会导致大量重编译）
DBCC FREEPROCCACHE;

-- 清除特定数据库的计划缓存
DBCC FLUSHPROCINDB(DB_ID('YourDB'));

-- 清除特定计划的缓存
-- 先找到 plan_handle
SELECT plan_handle, st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE st.text LIKE '%YourQuery%';
-- 然后清除
DBCC FREEPROCCACHE(plan_handle);

四、查询工作区内存

排序、哈希 JOIN、批量插入等操作需要额外的工作区内存。

-- 查看查询内存授予情况
SELECT 
    session_id,
    request_id,
    granted_memory_kb / 1024 AS GrantedMB,
    required_memory_kb / 1024 AS RequiredMB,
    used_memory_kb / 1024 AS UsedMB,
    is_small
FROM sys.dm_exec_query_memory_grants
WHERE session_id > 50;

-- 查看内存等待（RESOURCE_SEMAPHORE 表示等待内存授予）
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type = 'RESOURCE_SEMAPHORE';

优化内存授予：

-- 限制查询的最大内存使用（SQL Server 2016+）
SELECT * FROM Orders 
ORDER BY Amount DESC 
OPTION (MAX_GRANT_PERCENT = 5);  -- 最多使用 5% 的查询内存

-- 限制最小内存（避免小查询占用大内存）
SELECT * FROM Orders 
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
OPTION (MIN_GRANT_PERCENT = 10);

五、内存配置：max server memory 应该设多少？

5.1 常见错误配置

5.2 推荐配置公式

max server memory = 总物理内存 - (操作系统内存 + 其他应用内存)

操作系统内存建议（Windows Server）：
- 4GB 或以下：2GB
- 8GB：3-4GB
- 16GB：4GB
- 32GB：6GB
- 64GB：8GB
- 128GB+：10-16GB

示例：

• 32GB 内存服务器：max server memory = 32 - 6 = 26GB
• 64GB 内存服务器：max server memory = 64 - 8 = 56GB
• 128GB 内存服务器：max server memory = 128 - 12 = 116GB

5.3 配置方法

-- 查看当前配置
SELECT name, value_in_use 
FROM sys.configurations 
WHERE name LIKE '%memory%';

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

-- 设置最小内存（通常保持默认 0，除非需要预留）
EXEC sp_configure 'min server memory (MB)', 4096;
RECONFIGURE;

5.4 坚控内存使用

-- SQL Server 实际使用的内存
SELECT 
    [Memory Used (MB)] = total_physical_memory_kb / 1024,
    [SQL Server Memory (MB)] = (physical_memory_in_use_kb) / 1024,
    [Memory Available (MB)] = available_physical_memory_kb / 1024
FROM sys.dm_os_sys_memory
CROSS JOIN sys.dm_os_process_memory;

-- 查看目标内存 vs 当前内存
SELECT 
    counter_name,
    cntr_value / 1024 AS MB
FROM sys.dm_os_performance_counters
WHERE counter_name IN ('Total Server Memory (KB)', 'Target Server Memory (KB)');
-- 如果 Total < Target，说明还在增加内存使用（冷启动）
-- 如果 Total > Target，说明内存被限制或存在压力

六、列存储索引的内存特性

列存储索引（Columnstore）是 SQL Server 2012+ 引入的高压缩、高扫描性能的技术。

-- 创建列存储索引
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Orders
ON Orders (OrderDate, CustomerID, Amount);

-- 查看列存储段（Segment）使用情况
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    cs.segment_id,
    cs.row_count,
    cs.compressed_page_count
FROM sys.column_store_segments cs
JOIN sys.partitions p ON cs.partition_id = p.partition_id
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id;

列存储索引的内存特性：

• 列段压缩后常驻缓冲池
• 删除位图（Delete Bitmap）存储在内存中
• 批量模式（Batch Mode）处理需要额外内存

七、实战案例：内存压力诊断与优化

场景：系统每天下午响应变慢，PLE 持续低于 200

Step 1：确认内存压力

SELECT cntr_value AS [PLE] FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy';
-- 结果：PLE = 180（低于 300 警戒线）

SELECT cntr_value AS [Lazy writes/sec] FROM sys.dm_os_performance_counters
WHERE counter_name = 'Lazy writes/sec';
-- 结果：35（持续大于 20，内存压力明显）

Step 2：找出内存消耗大户

SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    COUNT(*) * 8 / 1024 AS BufferMB
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.partition_id
WHERE bd.database_id = DB_ID()
GROUP BY p.object_id
ORDER BY BufferMB DESC;

发现：一张 5 年前的日志表占用了 40% 的缓冲池。

Step 3：解决方案

1. 立即：将日志表迁移到独立的文件组，或使用页压缩
2. 短期：调整 max server memory 从 20GB 提到 28GB（总内存 32GB）
3. 长期：将冷数据归档到历史库

Step 4：验证

一周后 PLE 从 180 升到 450，Lazy writes/sec 从 35 降到 8。

八、核心总结

一句话记住本期内容：

快速检查清单

• PLE 是否 > 300？如果不是，考虑增加内存或减少数据扫描
• Lazy writes/sec 是否持续 > 20？检查内存压力
• max server memory 是否合理配置？（总内存减 4-16GB）
• 是否有大量即席查询（OneTimePercent > 70%）？启用 optimize for ad hoc workloads
• 是否存在参数嗅探问题？使用本地变量或 OPTION (RECOMPILE)
• 查询内存等待（RESOURCE_SEMAPHORE）是否高？检查大查询的内存授予

下一期预告

性能优化综合实战——从慢到快的完整案例

• 收集性能指标（等待统计、PLE、执行计划）
• 定位瓶颈（I/O、CPU、内存、网络）
• 制定优化方案（索引、查询重写、配置调整）
• 验证效果并建立基线
• 完整案例：一个 30 秒的查询如何优化到 0.5 秒

本系列持续更新中，点击关注不错过第八期（系列收官之作）。