印刻数据库

外观

SQLServer 内存管理 ​

内存管理概述 ​

SQL Server 内存管理是数据库性能优化的关键组成部分，合理的内存配置和管理可以提高数据库性能、减少 I/O 操作并避免内存相关的故障。SQL Server 2012 及以上版本采用动态内存管理机制，自动根据系统负载调整内存使用。

内存架构 ​

SQL Server 内存组成 ​

SQL Server 内存主要由以下部分组成：

  • 缓冲池 (Buffer Pool)：

    • 存储数据页的缓存
    • 占 SQL Server 内存的大部分
    • 减少磁盘 I/O 操作

  • 计划缓存 (Plan Cache)：

    • 存储执行计划
    • 减少编译开销
    • 包括查询计划和执行计划

  • 查询执行内存：

    • 用于排序、哈希连接等操作
    • 动态分配

  • 其他内存组件：

    • 锁管理器
    • 事务日志缓存
    • 扩展存储过程
    • CLR 集成
    • 连接管理器

内存管理机制 ​

SQL Server 内存管理采用以下机制：

  • 动态内存分配：

    • 根据系统负载自动调整内存使用
    • 由 max server memory 和 min server memory 控制

  • 内存通知：

    • 响应 Windows 内存通知
    • 在系统内存不足时释放内存

  • 内存压力检测：

    • 通过性能计数器和动态管理视图检测内存压力
    • 自动调整内存分配

版本差异 ​

SQL Server 版本内存管理特性差异生产影响
2012- 基础动态内存管理
- 有限的内存优化功能
- PLE 监控		- 适合中小规模工作负载
- 内存配置相对简单
2014- 增强的内存管理
- 引入内存中 OLTP
- 改进的计划缓存		- 支持更高并发
- 适合内存密集型工作负载
2016- 进一步优化的内存管理
- 增强的查询执行内存
- Query Store 集成		- 更好的内存利用率
- 改进的查询性能
2017- Linux 上的内存管理
- 自适应内存管理
- 增强的内存授予		- 跨平台支持
- 更智能的内存分配
2019- 智能查询处理
- 自适应内存授予反馈
- 增强的缓冲池		- 减少内存授予等待
- 更好的内存使用效率
2022- 增强的内存管理
- 加速数据库恢复 (ADR)
- Azure 集成优化		- 更快的恢复速度
- 更好的云集成

内存配置 ​

核心内存配置参数 ​

max server memory ​

控制 SQL Server 实例可以使用的最大内存量，是最重要的内存配置参数。

sql
-- 查看当前设置
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)';

-- 修改设置（示例：16GB）
EXEC sp_configure 'max server memory (MB)', 16384;
RECONFIGURE;

建议设置：

  • 对于专用 SQL Server，设置为物理内存的 80-85%
  • 为操作系统预留足够内存（通常 4-16GB 或 10-20% 的物理内存）
  • 考虑其他运行在同一服务器上的应用
  • 2017+ Linux 环境：考虑 Linux 内存管理差异，可能需要调整比例

min server memory ​

控制 SQL Server 实例保持的最小内存量。

sql
-- 查看当前设置
EXEC sp_configure 'min server memory (MB)';

-- 修改设置（示例：4GB）
EXEC sp_configure 'min server memory (MB)', 4096;
RECONFIGURE;

建议设置：

  • 根据系统负载和可用内存设置
  • 避免设置过高，影响系统和其他应用
  • 通常设置为最大内存的 20-30%
  • 2019+：考虑自适应内存授予反馈的影响

其他内存相关参数 ​

参数名称描述默认值建议值版本差异
min memory per query每个查询的最小内存分配1024 KB保持默认无
index create memory创建索引时的最大内存分配0（动态）保持默认无
query wait查询等待内存的最大时间-1（基于查询成本）保持默认或设置为 300 秒无
max worker threads最大工作线程数自动保持默认2016+ 自动调整更智能
optimize for ad hoc workloads优化临时查询的计划缓存01（对于临时查询较多的场景）2012+ 支持

内存配置最佳实践 ​

专用 SQL Server ​

  • 设置 max server memory 为物理内存的 80-85%
  • 为操作系统预留足够内存
  • 2022+：考虑加速数据库恢复 (ADR) 的内存需求

共享服务器 ​

  • 根据其他应用的内存需求调整
  • 考虑使用资源调控器
  • 2017+：使用 Linux cgroups 进行更精细的内存控制

虚拟环境 ​

  • 考虑虚拟化开销
  • 避免内存过度分配
  • 使用动态内存管理
  • 2016+：考虑使用 Hyper-V 动态内存优化

Azure VM ​

  • 根据 VM 大小和工作负载调整
  • 考虑 Azure 内存优化 VM 类型
  • 2019+：利用 Azure 混合权益优化成本

内存压力监控 ​

内存压力指标 ​

Page Life Expectancy (PLE) ​

  • 数据页在缓冲池中的平均停留时间
  • 理想值：> 300 秒
  • 监控：sys.dm_os_performance_counters
  • 2016+：考虑使用 Query Store 关联 PLE 下降与查询变化

Memory Grants Pending ​

  • 等待内存授予的查询数量
  • 理想值：0
  • 监控：sys.dm_os_performance_counters
  • 2019+：利用自适应内存授予反馈减少等待

Buffer Cache Hit Ratio ​

  • 缓冲池命中率
  • 理想值：> 95%
  • 监控：sys.dm_os_performance_counters

Page Reads/sec 和 Page Writes/sec ​

  • 每秒页读取和写入次数
  • 监控磁盘 I/O 压力
  • 监控：sys.dm_os_performance_counters

生产场景监控脚本 ​

1. 监控内存使用情况 ​

sql
-- 查看 SQL Server 内存使用
SELECT 
    physical_memory_in_use_kb / 1024 AS physical_memory_mb,
    locked_page_allocations_kb / 1024 AS locked_pages_mb,
    virtual_address_space_committed_kb / 1024 AS virtual_memory_mb,
    available_commit_limit_kb / 1024 AS available_commit_limit_mb,
    page_fault_count
FROM sys.dm_os_process_memory;

-- 查看内存组件使用（按大小排序）
SELECT 
    type, 
    name, 
    pages_kb / 1024 AS memory_mb
FROM sys.dm_os_memory_clerks
WHERE pages_kb > 0
ORDER BY pages_kb DESC;

2. 监控内存压力 ​

sql
-- 查看内存压力指标
SELECT 
    counter_name,
    cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (
    'Page Life Expectancy',
    'Memory Grants Pending',
    'Buffer Cache Hit Ratio',
    'Page Reads/sec',
    'Page Writes/sec'
) AND object_name LIKE '%Buffer Manager%';

-- 查看等待内存的查询
SELECT 
    session_id,
    wait_type,
    wait_time_ms,
    resource_description,
    text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE wait_type LIKE '%MEMORY%';

3. 监控计划缓存 ​

sql
-- 查看计划缓存使用
SELECT 
    objtype,
    cacheobjtype,
    COUNT(*) AS plan_count,
    SUM(size_in_bytes) / 1024 / 1024 AS total_size_mb,
    AVG(usecounts) AS avg_use_count
FROM sys.dm_exec_cached_plans
GROUP BY objtype, cacheobjtype
ORDER BY total_size_mb DESC;

-- 查看未使用的计划
SELECT 
    objtype,
    cacheobjtype,
    COUNT(*) AS plan_count,
    SUM(size_in_bytes) / 1024 / 1024 AS total_size_mb
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
GROUP BY objtype, cacheobjtype
ORDER BY total_size_mb DESC;

内存优化策略 ​

缓冲池优化 ​

  • 增加 max server memory，如果 PLE 低且有可用内存
  • 优化查询，减少 I/O 操作
  • 增加数据文件数量，减少 I/O 竞争
  • 使用更快的存储设备
  • 2016+：考虑使用 Columnstore 索引减少缓冲池压力

计划缓存优化 ​

  • 避免参数嗅探
  • 使用参数化查询
  • 定期清理未使用的计划
  • 优化查询，减少编译开销
  • 2012+：启用 optimize for ad hoc workloads 减少计划缓存膨胀
sql
-- 清理特定数据库的计划缓存
DBCC FREEPROCCACHE('database_id');

-- 清理特定计划
DBCC FREEPROCCACHE('plan_handle');

-- 清理所有计划缓存（谨慎使用）
DBCC FREEPROCCACHE;

查询执行内存优化 ​

  • 优化查询，减少排序和哈希操作
  • 增加 min memory per query（如果需要）
  • 调整 query wait 设置
  • 考虑使用 Columnstore 索引
  • 2019+：利用自适应内存授予反馈自动优化

内存压力缓解 ​

  • 识别并优化内存密集型查询
  • 增加服务器内存
  • 调整 max server memory 设置
  • 考虑使用读写分离
  • 实现资源调控器
  • 2022+：考虑加速数据库恢复 (ADR) 的内存影响

生产场景示例 ​

场景1：大型数据仓库内存配置 ​

项目内容
服务器配置96GB RAM, 24核CPU, 全闪存存储
SQL Server 版本2019 Enterprise
工作负载大型数据仓库，复杂查询，大量排序操作
配置参数- max server memory: 80GB
- min server memory: 16GB
- optimize for ad hoc workloads: 1
监控重点- PLE > 500秒
- Memory Grants Pending = 0
- 缓冲池命中率 > 98%
优化策略- 使用 Columnstore 索引
- 优化复杂查询，减少排序
- 定期更新统计信息

场景2：OLTP 系统内存优化 ​

项目内容
服务器配置32GB RAM, 8核CPU, SSD存储
SQL Server 版本2022 Standard
工作负载高并发 OLTP 系统
配置参数- max server memory: 24GB
- min server memory: 8GB
- max worker threads: 自动
监控重点- PLE > 300秒
- Memory Grants Pending = 0
- 锁内存使用合理
优化策略- 启用内存中 OLTP（适用于热点表）
- 优化索引设计
- 使用参数化查询

场景3：Linux 上的 SQL Server ​

项目内容
服务器配置64GB RAM, 16核CPU, Linux Ubuntu 22.04
SQL Server 版本2017 Linux
工作负载混合工作负载
配置参数- max server memory: 52GB
- 启用 Linux 大页内存
- 调整 Linux 内存限制
监控重点- Linux OOM 杀手不触发
- 内存使用率稳定
- PLE > 300秒
优化策略- 使用 Linux cgroups 进行内存控制
- 调整 Linux swap 设置
- 监控 Linux 内存压力指标

内存相关故障排查 ​

内存不足错误 ​

错误信息：There is insufficient system memory in resource pool 'default' to run this query.

排查步骤：

  1. 检查 max server memory 设置
  2. 检查内存压力指标
  3. 识别内存密集型查询
  4. 考虑增加服务器内存
  5. 优化查询
  6. 2019+：利用自适应内存授予反馈自动调整

高内存使用率 ​

症状：SQL Server 内存使用率接近 100%

排查步骤：

  1. 检查内存组件使用情况
  2. 检查计划缓存大小
  3. 检查是否有内存泄漏
  4. 考虑调整 max server memory
  5. 优化查询
  6. 2016+：检查 Query Store 内存使用

频繁的内存授予等待 ​

症状：Memory Grants Pending > 0

排查步骤：

  1. 识别等待内存的查询
  2. 优化这些查询，减少内存需求
  3. 考虑增加 max server memory
  4. 调整 min memory per query
  5. 实现资源调控器
  6. 2019+：利用自适应内存授予反馈减少等待

内存管理最佳实践 ​

合理配置内存参数 ​

  • 根据服务器角色和工作负载调整 max server memory
  • 为操作系统预留足够内存
  • 避免设置过高的 min server memory
  • 2012+：启用 optimize for ad hoc workloads 减少计划缓存膨胀

定期监控内存使用 ​

  • 监控 PLE、Memory Grants Pending 等关键指标
  • 设置内存压力告警
  • 定期生成内存使用报告
  • 2016+：使用 Query Store 关联查询变化与内存压力

优化查询性能 ​

  • 优化内存密集型查询
  • 使用适当的索引
  • 减少排序和哈希操作
  • 使用参数化查询
  • 2019+：利用智能查询处理自动优化

管理计划缓存 ​

  • 避免计划缓存膨胀
  • 定期清理未使用的计划
  • 优化查询，减少编译开销
  • 2012+：启用 optimize for ad hoc workloads

考虑硬件升级 ​

  • 增加服务器内存
  • 使用更快的存储设备
  • 考虑使用内存优化 VM 或服务器
  • 2022+：考虑加速数据库恢复 (ADR) 的硬件需求

使用资源调控器 ​

  • 为不同工作负载分配内存资源
  • 限制内存密集型查询的资源使用
  • 实现服务级别协议 (SLA)
  • 2016+：资源调控器功能增强

常见问题 (FAQ) ​

  1. 如何计算合适的 max server memory 设置？

    • 计算 max server memory 的建议公式：
      max server memory = 物理内存 - 操作系统预留内存 - 其他应用内存
    • 对于专用 SQL Server：物理内存的 80-85%
    • 对于共享服务器：根据其他应用的内存需求调整
    • 2017+ Linux 环境：考虑 Linux 内存管理差异，可能需要调整比例

  2. Page Life Expectancy (PLE) 低怎么办？

    • 增加 max server memory（如果有可用内存）
    • 优化查询，减少 I/O 操作
    • 增加数据文件数量
    • 使用更快的存储设备
    • 2014+：考虑使用内存中 OLTP

  3. 如何识别内存密集型查询？

    • 使用以下 T-SQL 查询：
      sql
      SELECT TOP 10
    total_worker_time / execution_count AS avg_cpu_time,
    total_logical_reads / execution_count AS avg_logical_reads,
    total_logical_writes / execution_count AS avg_logical_writes,
    SUBSTRING(text, statement_start_offset / 2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(max), text)) * 2
            ELSE statement_end_offset
        END - statement_start_offset) / 2) AS query_text
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
ORDER BY avg_logical_reads DESC;
    • 2016+：使用 Query Store 查找资源密集型查询

  4. 计划缓存膨胀怎么办？

    • 使用参数化查询
    • 清理未使用的计划
    • 优化查询，减少编译开销
    • 考虑使用 OPTION (RECOMPILE) 处理参数敏感查询
    • 启用 optimize for ad hoc workloads：
      sql
      EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

  5. Azure SQL Database 如何监控内存使用？

    • 使用 Azure 门户中的指标：Memory Usage, CPU Usage, DTU Usage
    • 使用动态管理视图：
      sql
      SELECT * FROM sys.dm_db_resource_stats;
    • 使用 Azure SQL Analytics

  6. 内存中 OLTP 如何影响内存管理？

    • 需要额外的内存分配
    • 不占用缓冲池内存
    • 由 max memory for memory-optimized data 控制
    • 提高 OLTP 性能
    • 2014+ 支持

  7. Linux 上的 SQL Server 内存管理有什么不同？

    • 使用 Linux 内存管理机制
    • 支持大页内存
    • 可以使用 cgroups 进行精细控制
    • 2017+ 支持

  8. 如何处理内存泄漏？

    • 识别泄漏的内存组件
    • 检查 SQL Server 版本，是否有已知的内存泄漏问题
    • 应用最新的累积更新
    • 考虑重启 SQL Server 实例（作为临时解决方案）
    • 联系 Microsoft 支持

  9. 资源调控器如何帮助内存管理？

    • 为不同工作负载分配内存资源
    • 限制内存密集型查询的资源使用
    • 实现服务级别协议 (SLA)
    • 2016+ 功能增强

  10. 2022 年加速数据库恢复 (ADR) 对内存有什么影响？

    • ADR 需要额外的内存用于持久化版本存储
    • 减少长事务对内存的影响
    • 提高恢复速度
    • 2022+ 支持

总结 ​

SQL Server 内存管理是数据库性能优化的关键组成部分，合理的内存配置和管理可以提高数据库性能、减少 I/O 操作并避免内存相关的故障。内存管理包括内存配置、内存压力监控、内存优化和故障排查等方面。

随着 SQL Server 版本的不断升级，内存管理功能也在不断增强，从基础的动态内存管理到智能查询处理和自适应内存授予反馈，SQL Server 提供了越来越强大的内存管理能力。

建议 DBA 在进行内存管理时，注意以下几点：

  1. 合理配置 max server memory 和 min server memory 参数
  2. 定期监控内存压力指标，如 PLE、Memory Grants Pending 等
  3. 优化查询性能，减少内存需求
  4. 管理计划缓存，避免膨胀
  5. 考虑硬件升级或使用内存优化技术
  6. 使用资源调控器分配内存资源
  7. 关注版本差异，利用新版本的内存管理增强功能

通过实施这些最佳实践，可以构建高效、可靠的内存管理体系，确保 SQL Server 数据库在高负载下的稳定运行。