sqlserver性能优化之内存优化详解

以下是 SQL Server 内存优化的详细方案，结合核心配置、监控手段和高级技术，提供脚本示例及配置建议。

内存优化作为sqlserver性能优化的方向之一，可以作为了解。

一、核心内存配置优化

​​设置内存上下限​​

目标​​：防止 SQL Server 占用全部系统内存，确保操作系统和其他应用正常运行。

建议配置​​：

​​脚本示例​​：

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（大内存支持）​​

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 压力，提升读取性能。

二、缓冲池与缓存管理

​​监控关键内存指标​​

-- 实时内存状态 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 定时执行索引重建任务（每周低峰期）。

六、内存优化临时表

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; 作用​​：将临时表自动提升为内存优化表，减少 TempDB I/O 压力。

关键注意事项

​​总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。