我要评论以下是 sql server 内存优化的详细方案，结合核心配置、监控手段和高级技术，提供脚本示例及配置建议。
内存优化作为sqlserver性能优化的方向之一，可以作为了解。
一、核心内存配置优化
设置内存上下限
目标：防止 sql server 占用全部系统内存，确保操作系统和其他应用正常运行。
建议配置：
- max server memory = ram物理内存的 75%~80%（预留 20%~25% 给 os 及其他进程）
- min server memory = ram物理内存的 10%~20%（避免频繁内存收缩）
脚本示例：
exec sp_configure 'show advanced options', 1; reconfigure; exec sp_configure 'max server memory (mb)', 24576; -- 例如 24gb（适用于 32gb 内存服务器） exec sp_configure 'min server memory (mb)', 4096; -- 最小 4gb reconfigure;
启用 awe（大内存支持）
- 适用场景：32 位 sql server 需使用超过 4gb 内存时（64 位无需配置）。
- 配置步骤：
exec sp_configure 'awe enabled', 1; reconfigure;
启用缓冲池扩展（buffer pool extension）
-- 将缓冲池扩展至 ssd（需 sql server 2014+） alter server configuration set buffer pool extension on (filename = 'f:\ssd_cache\bp_extend.bpe', size = 32 gb);
- 作用：将冷数据缓存至 ssd，减少物理 i/o 压力，提升读取性能。
二、缓冲池与缓存管理
监控关键内存指标
- 核心 dmv 查询：
-- 实时内存状态
select
counter_name as [指标],
cntr_value as [值(kb)]
from sys.dm_os_performance_counters
where counter_name in (
'target server memory (kb)', -- 目标内存
'total server memory (kb)', -- 已用内存
'page life expectancy' -- 页生命周期（秒），>300 为佳
);
- 输出解读：
- page life expectancy 过低（<60s）表明内存压力大，需优化或扩容。
清理缓存策略
场景：计划缓存过多或测试环境需重置状态时。
脚本示例：
dbcc freeproccache; -- 清除执行计划缓存
dbcc dropcleanbuffers; -- 清除数据缓存
dbcc freesystemcache('all'); -- 清除系统缓存
- 注意：生产环境谨慎使用，可能引发短期性能波动。
三、索引优化减少内存压力
定期维护索引碎片
- 脚本示例：
-- 检查碎片率（>30% 建议重建）
select
index_id,
avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'detailed')
where avg_fragmentation_in_percent > 30;
四、高级内存优化技术
内存优化表（in-memory oltp）
适用场景：高频读写表（如会话状态、实时交易）。
配置步骤：
--创建内存优化文件组：
alter database [dbname]
add filegroup [memopt_fg] contains memory_optimized_data;
--创建容器
alter database [dbname]
add file (name='memopt_file', filename='e:\work\develop\sqldata\test') --注意这里是文件夹
to filegroup [memopt_fg];
--创建内存优化表：
create table [dbo].[memtable] (
id int primary key nonclustered,
data nvarchar(100)
) with (memory_optimized = on, durability = schema_and_data);
--查看内存优化表
select object_id,
object_schema_name(object_id) as schema_name,
name as table_name
from sys.tables
where is_memory_optimized = 1;
use [salesdb];
go
-- 查看文件组及文件
select
fg.name as filegroupname,
f.name as filename,
f.physical_name as filepath
from sys.filegroups fg
left join sys.database_files f on fg.data_space_id = f.data_space_id;
内存优化表适用于oltp高频交易系统、实时分析与决策的系统、高并发与资源争用场景等。
目前我的感受下来，内存优化表（mot），个人认为，一般不会用到。
首先，mot针对的是高频读写的表，那么针对读，可以使用redis缓存提高查询性能，在进行更新时先更db在删cache。实在业务数据量巨大时，也可以考虑读写分离分库分表，redis 分片集群等。
mot会把数据写入磁盘的，这样会很吃内存，并且，如果做了mot还要保证db和mot的一致性，如果用了redis，就得保证mot、redis、db三方的一致性。
鉴于上述，个人认为实在没啥必要使用内存优化表。当然这里仅限个人认为。
五、自动化维护与监控
定期内存巡检脚本
-- 检查内存分配状态 dbcc memorystatus; -- 输出缓冲池、计划缓存等详细信息[4,8](@ref) -- 监控内存等待事件 select * from sys.dm_os_wait_stats where wait_type like '%memory%';
- 自动化索引维护计划
- 使用 sql agent 定时执行索引重建任务（每周低峰期）。
六、内存优化临时表
- 启用内存优化临时表（sql server 2019+）
alter database current set memory_optimized_elevate_to_snapshot = on;
- 作用：将临时表自动提升为内存优化表，减少 tempdb i/o 压力。
关键注意事项
- 避免过度配置：max server memory 过高可能导致操作系统内存不足引发崩溃。
- 监控 ple（page life expectancy）：持续低于 300 秒需扩容或优化查询。
- 版本兼容性：内存优化表仅支持 sql server 2014 及以上版本。
总结
以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。
发表评论